How to Talk to Mysql Databases

by: George El., August 2019, Reading time: 7 minutes

In this post I will describe how to use python to execute raw queries on mysql databases.

install the database

First we need to install mysql or maria-db (a fork of mysql) either in unix or windows. if you have windows 10 you can install mysql on wsl (windows subsystem for linux). if you have ubuntu on wsl it is as easy as writing:

sudo apt-get install mysql-server
or
sudo apt-get install mariadb-server

to start the service you can use (systemd is not running on wsl, so you have to use service)

root@DESKTOP-0V7SV0F:~# service mysql status
 * MariaDB is stopped.
root@DESKTOP-0V7SV0F:~# service mysql start
 * Starting MariaDB database server mysqld

or in linux

sudo systemctl start mariadb

Then we need to connect to db and create a user with username test, password test, and access to testdb. you can type “mysql -u root” to connect.

root@DESKTOP-0V7SV0F:~# mysql -u root 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 62
Server version: 10.0.38-MariaDB-0ubuntu0.16.04.1 Ubuntu 16.04

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

create the database testdb and use test

MariaDB [(none)]> CREATE DATABASE testdb;
Query OK, 1 row affected (0.08 sec)

MariaDB [(none)]> USE testdb;
Database changed
MariaDB [testdb]> CREATE USER 'test'@'localhost' IDENTIFIED BY 'test123';
Query OK, 0 rows affected (0.12 sec)

MariaDB [testdb]> GRANT ALL PRIVILEGES ON testdb.* To 'test'@'locahost';
Query OK, 0 rows affected (0.06 sec)

MariaDB [testdb]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.04 sec)

if you are using oracle mysql you may have to write

GRANT ALL PRIVILEGES ON testdb.* To 'test'@'locahost' identified by 'test123';

install a driver

Then we need to install a driver for mysql. There are many like pymysql and MySQLdb (mysqlcient). MySQLdb is a Python 2 legacy database module for MySQL. (If you want to use MySQLdb you have to do pip install mysqlclient and then import MySQLdb). For modern Python 3 MySQL programming, the PyMySQL module is recommended.

pip install pymysql

test the connection

to test your db run the following

import pymysql

con = pymysql.connect("localhost", "test", "test123", "testdb")

try:
    cursor = con.cursor():
    cursor.execute("SELECT VERSION()")
    result = cursor.fetchone()
    print(result)
except pymysql.MySQLError as e:
    print(e)
finally:
    con.close()

con.close()

you should get something like this

('10.0.38-MariaDB-0ubuntu0.16.04.1',)

or this for oracle mysql

('5.7.27-0ubuntu0.16.04.1',)
  • We connect to the database with connect(). We pass four parameters: the hostname, the MySQL user name, the password, and the database name. We get back a connection object

  • From the connection object, we create a cursor calling con.cursor(). The cursor is used to traverse the records from the result set.

  • We call the execute() method of the cursor and execute the SQL statement.

  • The fetchone() method fetches the next row of a query result set, returning a single sequence, or None when no more data is available. In this case we got back a single line. There is also fetchall() which brings back all the lines in a form of a list as we will see later.

create a table

Now lets create a table

import pymysql

con = pymysql.connect("localhost","test","test123","testdb" )

cursor = con.cursor()
cursor.execute("DROP TABLE IF EXISTS PERSON")

query = "CREATE TABLE PERSON(  \
          id int(11) NOT NULL AUTO_INCREMENT,\
          name varchar(40),\
          year int(11),\
          email varchar(40),\
          phone varchar(40),\
          PRIMARY KEY (id))"

cursor.execute(query)

con.close()

insert data

Lets insert some data. We will use the Faker module to create random data

from faker import Faker
import pymysql

def generate_random_data(num):
    values = []
    fake = Faker()
    for i in range(num):
        name = fake.name() 
        year = int(fake.year())
        email = fake.email()
        phone = fake.phone_number()
        if num==1:
            values = (name, year, email, phone)
            # return a tuple
        else:
            values.append((name, year, email, phone))
            # return a list of tuples
        print(values)
    return values

def insert_random_values(con, cursor):
    query = "insert into PERSON( name, year, email, phone)  \
            VALUES ( %s, %s, %s, %s)"
    values_one = generate_random_data(1)
    values_many = generate_random_data(10)

    try:
        cursor.execute(query, values_one)
        cursor.executemany(query, values_many)
        con.commit()
    except Exception as e:
        con.rollback()
        print("there was an error" + str(e))


def connect_to_db():
    try:
        con = pymysql.connect("localhost", "test", "test123", "testdb")
        cursor = con.cursor()
        return con, cursor
    except pymysql.MySQLError as e:
        print(e)
        print("there was an error connecting to db" + str(e))

mysql has the ability to insert multiple rows at one query. The query structure is the same but we pass as argument a list of tuples.

select data

lets do a select to see the table top 10 rows

import pymysql

con = pymysql.connect("localhost", "test", "test123", "testdb")

cursor = con.cursor()

query = "SELECT * FROM PERSON LIMIT 10"
try:
    cursor.execute(query)
    resultList = cursor.fetchall()
    for row in resultList:
       print(row)

except Exception as e:
    print ("Encountered error while retrieving data from database"+str(e))
finally:
    con.close()

the output is

(1, 'David Smith', 1978, 'jacksonmelissa@yahoo.com', '301-413-4024')
(2, 'James Stanley', 1979, 'kimberlycarr@perez.com', '001-298-922-3152x517')
(3, 'Scott Berg', 1995, 'omarbarr@miles-estrada.com', '565-163-7730x3916')
(4, 'Patricia Thompson', 1995, 'emily51@yahoo.com', '001-961-606-0798x438')
(5, 'Maurice Jackson', 1995, 'randy89@spencer.com', '667-603-9602x00309')
(6, 'David Mcclure', 2011, 'sierrachapman@mahoney-hunter.c', '(622)724-2146x85480')
(7, 'Jennifer Henson', 1979, 'mercereric@gmail.com', '984-840-7012x337')
(8, 'Fred Smith', 2015, 'hwright@yahoo.com', '001-710-695-1919x321')
(9, 'Rachel Taylor', 2004, 'emmamartin@frey-baldwin.biz', '487-807-8110')
(10, 'Bianca Barnes', 2006, 'traceyjohnson@lowery-mcdonald.', '(575)796-1800x784')

if you create your query string using python format string methods and call execute(query), you should make sure you escape your strings, otherwise there is a security risk. pymysql provides a function to do this, escape_string(). So name should be pymysql.escape_string(name). On the other hand if you call execute(query,values) it will auto escape special characters for you.

select query with parameters

import pymysql

dbconnect = pymysql.connect("localhost", "test", "test123", "testdb")

cursor = dbconnect.cursor()

query = "SELECT * FROM PERSON WHERE name LIKE %s LIMIT 10"
param = 'Richard'
try:
    cursor.execute(query, ("%" + param + "%",))
    resultList = cursor.fetchall()
    for row in resultList:
        print(row)

except Exception as e:
    print("Encountered error while retrieving data from database"+str(e))
finally:
    dbconnect.close()

the output is

(25, 'Richard Lewis', 2008, 'flopez@mills.com', '092.922.0664')
(26, 'Richard Stevenson', 2003, 'hugheschristina@mendoza.com', '001-115-645-5117x689')

update data

lets update record 10

import pymysql

con = pymysql.connect("localhost", "test", "test123", "testdb")
cursor = con.cursor()
updatequery = "update PERSON set year = '2000' where id = 10"
cursor.execute(updatequery)
# with parameters
updatequery = "UPDATE PERSON SET name=%s where id=11"
param = "george"
cursor.execute(updatequery, (param,))

con.commit()
print(cursor.rowcount, "record(s) affected")
PS C:\projects\mysql> python .\update.py
1 record(s) affected

delete data

lets delete record 1

import pymysql

con = pymysql.connect("localhost", "test", "test123", "testdb")
cursor = con.cursor()
updatequery = "DELETE FROM PERSON WHERE id = 1"
cursor.execute(updatequery)
con.commit()
print(cursor.rowcount, "record(s) deleted")

you see that 10 and 11 records have changed

(1, 'Karina Harvey DDS', 2004, 'nelsonjeffrey@stewart.com', '137-685-4456x652')
(2, 'Charles Pierce', 1976, 'ericsmith@gmail.com', '346-869-9187x92823')
(3, 'Pam Moore', 2005, 'timothyellis@hotmail.com', '706-203-3823x616')
(4, 'William Barber', 2002, 'brosales@gmail.com', '063-953-8721x199')
(5, 'Wendy Scott', 1998, 'lindagibson@yahoo.com', '469.540.6891')
(6, 'Lynn Mendoza', 1992, 'spugh@hotmail.com', '375-023-2626x6905')
(7, 'Amanda Hanson', 1986, 'austindaniel@gmail.com', '979.197.8806x748')
(8, 'Patricia Huff', 2019, 'jonathangreen@hernandez.com', '001-622-253-0806x41678')
(9, 'Jennifer Wise MD', 1981, 'carlaflores@taylor.com', '+1-324-451-4813x4985')
(10, 'Darlene White', 2000, 'lopezpatrick@yahoo.com', '(414)264-5189')
(11, 'george', 1987, 'trujillopamela@hotmail.com', '993-132-1974x509')
PS C:\projects\mysql> python .\select.py
(2, 'James Stanley', 1979, 'kimberlycarr@perez.com', '001-298-922-3152x517')
(3, 'Scott Berg', 1995, 'omarbarr@miles-estrada.com', '565-163-7730x3916')
(4, 'Patricia Thompson', 1995, 'emily51@yahoo.com', '001-961-606-0798x438')
(5, 'Maurice Jackson', 1995, 'randy89@spencer.com', '667-603-9602x00309')
(6, 'David Mcclure', 2011, 'sierrachapman@mahoney-hunter.c', '(622)724-2146x85480')
(7, 'Jennifer Henson', 1979, 'mercereric@gmail.com', '984-840-7012x337')
(8, 'Fred Smith', 2015, 'hwright@yahoo.com', '001-710-695-1919x321')
(9, 'Rachel Taylor', 2004, 'emmamartin@frey-baldwin.biz', '487-807-8110')
(10, 'Bianca Barnes', 2000, 'traceyjohnson@lowery-mcdonald.', '(575)796-1800x784')
(11, 'Megan Lopez', 1977, 'zcaldwell@gmail.com', '889-542-9305x3337')

use a dict cursor

we can use a dict cursor to get the data as a list of dictionaries

import pymysql
import pymysql.cursors

con = pymysql.connect(host='localhost',
        user='test',
        password='test123',
        db='testdb',
        cursorclass=pymysql.cursors.DictCursor)

with con:
    cur = con.cursor()
    cur.execute("SELECT * FROM PERSON LIMIT 10")
    rows = cur.fetchall()
    for row in rows:
        print(row)

the output will be a list of dictionaries

{'id': 2, 'name': 'James Stanley', 'year': 1979, 'email': 'kimberlycarr@perez.com', 'phone': '001-298-922-3152x517'}
{'id': 3, 'name': 'Scott Berg', 'year': 1995, 'email': 'omarbarr@miles-estrada.com', 'phone': '565-163-7730x3916'}
{'id': 4, 'name': 'Patricia Thompson', 'year': 1995, 'email': 'emily51@yahoo.com', 'phone': '001-961-606-0798x438'}
{'id': 5, 'name': 'Maurice Jackson', 'year': 1995, 'email': 'randy89@spencer.com', 'phone': '667-603-9602x00309'}
{'id': 6, 'name': 'David Mcclure', 'year': 2011, 'email': 'sierrachapman@mahoney-hunter.c', 'phone': '(622)724-2146x85480'}
{'id': 7, 'name': 'Jennifer Henson', 'year': 1979, 'email': 'mercereric@gmail.com', 'phone': '984-840-7012x337'}
{'id': 8, 'name': 'Fred Smith', 'year': 2015, 'email': 'hwright@yahoo.com', 'phone': '001-710-695-1919x321'}
{'id': 9, 'name': 'Rachel Taylor', 'year': 2004, 'email': 'emmamartin@frey-baldwin.biz', 'phone': '487-807-8110'}
{'id': 10, 'name': 'Bianca Barnes', 'year': 2000, 'email': 'traceyjohnson@lowery-mcdonald.', 'phone': '(575)796-1800x784'}
{'id': 11, 'name': 'Megan Lopez', 'year': 1977, 'email': 'zcaldwell@gmail.com', 'phone': '889-542-9305x3337'}

so we can access columns as a dict

    for row in rows:
        print(row['name'])
James Stanley
Scott Berg
Patricia Thompson
Maurice Jackson
David Mcclure
Jennifer Henson
Fred Smith
Rachel Taylor
Bianca Barnes
Megan Lopez

In this post we saw, how to talk to mysql databases. If you want to talk to another sql database it is just a matter of changing the driver. For ms sql server there are two drivers pyodbc and pymssql. You can find more information here

comments powered by Disqus