Ruan Bekker's Blog

From a Curious mind to Posts on Github

Using Python to Write Data to a MySQL Database

From our previous post, we used python to read data from mysql. In this post we will be using the random library to write random data into mysql.

We will define our lists with the categorized data, and then using for loop, to write data into our mysql database:

Create The Database:

Using Python to Create the Database:

1
2
3
4
5
6

>>> conn = pdb.connect(host=db_host, user=db_username, passwd=db_password)
>>> cursor = conn.cursor()
>>> cursor.execute("CREATE DATABASE testdb1")
1L
>>> cursor.execute("CREATE TABLE testdb1.myusers(name VARCHAR(50), surname VARCHAR(50), countries VARCHAR(50), job VARCHAR(20), os VARCHAR(20), car VARCHAR(20))")
0L

Now to list our databases:

1
2
3
4
5
6
7
8
9
10
11
12

>>> cursor.execute("show databases")
12L

>>> dbs = cursor.fetchall()
>>> for x in dbs:
...     print(x)
...

('information_schema',)
('mysql',)
('performance_schema',)
('testdb1',)

Python Code to Write to MySQL

We will create a mysql_write.py file, with the following contents to define our random data that we will write to our mysql database. The config module can be found from this post.

mysql_write.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

import MySQLdb as pdb
from config import credentials as secrets
import random
import datetime

db_host = secrets['mysql']['host']
db_username = secrets['mysql']['username']
db_password = secrets['mysql']['password']
db_name = secrets['mysql']['database']

for x in range(10):
    a = random.choice(names)
    b = random.choice(surnames)
    c = random.choice(countries)
    d = random.choice(job)
    e = random.choice(os)
    f = random.choice(car)

    cursor.execute("INSERT INTO myusers values('{name}', '{surname}', '{countries}', '{job}', '{os}', '{car}');".format(name=a, surname=b, countries=c, job=d, os=e, car=f))

conn.commit()
conn.close()

After running the file: python mysql_write.py we should have 10 records in our database.

Reading the Data from MySQLL

To verify that the data is in our MySQL Database, lets logon to our mysql database:

1

$ mysql -u root -p

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

mysql> select * from testdb1.myusers;
+----------+----------+-----------+----------------+---------+---------------+
| name     | surname  | countries | job            | os      | car           |
+----------+----------+-----------+----------------+---------+---------------+
| James    | James    | New York  | Waiter         | Mac     | Volkswagen    |
| Jennifer | Smith    | New York  | Scientist      | Windows | Audi          |
| Michelle | Jacobs   | Italy     | Police Officer | Mac     | Ford          |
| Michelle | Anderson | Italy     | Waiter         | Windows | Ford          |
| Jennifer | Smith    | England   | Doctor         | Windows | Toyota        |
| Peter    | Jacobs   | England   | IT             | Windows | BMW           |
| Samantha | James    | England   | Doctor         | Mac     | Mazda         |
| Frank    | Phillips | England   | IT             | Mac     | BMW           |
| Samantha | James    | England   | Banker         | Linux   | Mercedez-Benz |
| Peter    | Anderson | Sweden    | Doctor         | Windows | BMW           |
+----------+----------+-----------+----------------+---------+---------------+

Next, lets use Python to do the same, create a file mysql_read.py with the following content:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

import MySQLdb as pdb
from config import credentials as secrets

db_host = secrets['mysql']['host']
db_username = secrets['mysql']['username']
db_password = secrets['mysql']['password']
db_name = secrets['mysql']['database']

conn = pdb.connect(host=db_host, user=db_username, passwd=db_password, db=db_name)
cursor = conn.cursor()

cursor.execute("select * from myusers")
read = cursor.fetchall()

for x in read:
    print(x)

conn.close()

Running the Python file, to read the data:

1
2
3
4
5
6
7
8
9
10
11
12

$ python mysql_read.py

('James', 'James', 'New York', 'Waiter', 'Mac', 'Volkswagen')
('Jennifer', 'Smith', 'New York', 'Scientist', 'Windows', 'Audi')
('Michelle', 'Jacobs', 'Italy', 'Police Officer', 'Mac', 'Ford')
('Michelle', 'Anderson', 'Italy', 'Waiter', 'Windows', 'Ford')
('Jennifer', 'Smith', 'England', 'Doctor', 'Windows', 'Toyota')
('Peter', 'Jacobs', 'England', 'IT', 'Windows', 'BMW')
('Samantha', 'James', 'England', 'Doctor', 'Mac', 'Mazda')
('Frank', 'Phillips', 'England', 'IT', 'Mac', 'BMW')
('Samantha', 'James', 'England', 'Banker', 'Linux', 'Mercedez-Benz')
('Peter', 'Anderson', 'Sweden', 'Doctor', 'Windows', 'BMW')