How to Work with Relational Database with Python

1. Python Relational Database

In our last Python Database tutorial, we checked how to work with NoSQL databases with Python. In the same way today we talk about working of Relational Database with Python Programming Language. Moreover, we will read some important prerequisites of Relational Database and how to read a relation table. Along with this, we will get to know how to delete an entire table and a single row in relation database with Python.
So, let start working with Relational Database with Python.

How to Work with Relational Database with Python

2. Prerequisites for Relational Database

For this demo, we need three things- sqlalchemy, pandas, and a CSV file.

Prerequisites for Relational Database

a. SQLAlchemy

How to Work with Relational Database with Python

SQLAlchemy is an open-source toolkit and an object-relational mapper. You can download this using pip-
C:\Users\lifei>pip install sqlalchemy
Requirement already satisfied: sqlalchemy in c:\users\lifei\appdata\local\programs\python\python36\lib\site-packages (1.2.9)
Note- If your import works for the command line and not for the IDLE, then you may be running different versions of Python in the two. You can add this to the path in your IDLE-

>>> sys.path.append('C:\\Users\\lifei\\AppData\\Local\\Programs\\Python\\Python36\\Lib\\site-packages')

b. Pandas

sqlalchemy, pandas, and a CSV file

Pandas is a Python library for data manipulation and analysis. You can download it using pip-
C:\Users\lifei>pip install pandas
Collecting pandas
Let’s Read a New Topic – Python 3 Extension Programming with C & Others Languages
Downloading https://files.pythonhosted.org/packages/ad/0b/f65a63cccc5e37a7af983fc87a8c9e56feb4998d8877f8f2af21f40bcf3c/pandas-0.23.1-cp36-cp36m-win_amd64.whl (10.5MB)
100% |████████████████████████████████| 10.5MB 385kB/s
Collecting pytz>=2011k (from pandas)
Downloading https://files.pythonhosted.org/packages/30/4e/27c34b62430286c6d59177a0842ed90dc789ce5d1ed740887653b898779a/pytz-2018.5-py2.py3-none-any.whl (510kB)
100% |████████████████████████████████| 512kB 385kB/s
Collecting numpy>=1.9.0 (from pandas)
Downloading https://files.pythonhosted.org/packages/0d/b7/0c804e0bcba6505f8392d042d5e333a5e06f308e019517111fbc7767a0bc/numpy-1.14.5-cp36-none-win_amd64.whl (13.4MB)
100% |████████████████████████████████| 13.4MB 461kB/s
Collecting python-dateutil>=2.5.0 (from pandas)
Downloading https://files.pythonhosted.org/packages/cf/f5/af2b09c957ace60dcfac112b669c45c8c97e32f94aa8b56da4c6d1682825/python_dateutil-2.7.3-py2.py3-none-any.whl (211kB)
100% |████████████████████████████████| 215kB 504kB/s
Requirement already satisfied: six>=1.5 in c:\users\lifei\appdata\local\programs\python\python36\lib\site-packages (from python-dateutil>=2.5.0->pandas) (1.11.0)
Installing collected packages: pytz, numpy, python-dateutil, pandas
Successfully installed numpy-1.14.5 pandas-0.23.1 python-dateutil-2.7.3 pytz-2018.5

c. A Sample CSV File

A Sample CSV File

To work with the relational databases, we download a sample CSV file from the web. Here’s what it looks like:

3. Reading a Relational Table

Now let’s try reading from this Relational table

>>> from sqlalchemy import create_engine
>>> import pandas
>>> import os
>>> os.chdir('C:\\Users\\lifei\\Desktop')
>>> data=pandas.read_csv('samplecsv.csv',encoding="ISO-8859-1")
>>> engine=create_engine('sqlite:///:memory:') #Create db engine
>>> data.to_sql('data_table',engine) #Store dataframe as table
>>> print(pandas.read_sql_query('SELECT * FROM data_table',engine))

This produces the following output:

Reading a Relational Table – Outputv

4. How to Insert Values in Relational Database with Python?

Now to add another row of content into Relational Database with Python, we use the following code:

>>> from sqlalchemy import create_engine
>>> from pandas.io import sql
>>> import pandas
>>> data=pandas.read_csv('samplecsv.csv',encoding="ISO-8859-1")
>>> engine=create_engine('sqlite:///:memory:') #Create db engine
>>> data.to_sql('data_table',engine) #Store dataframe as table
>>> sql.execute('INSERT INTO data_table VALUES(?,?,?,?,?,?,?,?,?,?,?)',\
                                engine,params=[(9,11,’Xerox 198’,'Frederick Jones',752,-321.57,2.2,9.8,'Nunavut','Paper',0.37)])
<sqlalchemy.engine.result.ResultProxy object at 0x00FA3530>
>>> print(pandas.read_sql_query('SELECT * FROM data_table',engine))

This produces the following output:
index   1   Eldon Base for stackable storage shelf, platinum  \
0      0   2  1.7 Cubic Foot Compact “Cube” Office Refrigera…
1      1   3   Cardinal Slant-D® Ring Binder, Heavy Gauge Vinyl
2      2   4                                               R380
3      3   5                           Holmes HEPA Air Purifier
4      4   6  G.E. Longer-Life Indoor Recessed Floodlight Bulbs
5      5   7  Angle-D Binders with Locking Rings, Label Holders
6      6   8            SAFCO Mobile Desk Side File, Wire Frame
7      7   9              SAFCO Commercial Wire Shelving, Black
8      8  10                                          Xerox 198
9     9   11                                          Xerox 198
Muhammed MacIntyre    3  -213.25   38.94     35  Nunavut  \
0       Barry French  293   457.81  208.16  68.02  Nunavut
1       Barry French  293    46.71    8.69   2.99  Nunavut
2       Clay Rozendal  483  1198.97  195.99   3.99  Nunavut
3       Carlos Soltero  515    30.94   21.78   5.94  Nunavut
4       Carlos Soltero  515     4.43    6.64   4.95  Nunavut
5       Carl Jackson  613   -54.04    7.30   7.72  Nunavut
6       Carl Jackson  613   127.70   42.76   6.22  Nunavut
7       Monica Federle  643  -695.26  138.14  35.00  Nunavut
8       Dorothy Badders  678  -226.36    4.98   8.33  Nunavut
9       Frederick Jones  752  -321.57    2.20   9.80  Nunavut
Storage & Organization   0.8
0                      Appliances  0.58
Have a Look at Difference Between Method and Function in Python

5. How to Delete Values in Relational Database with Python?

Relational Database with Python can delete values raw vise and delete the entire table. So, let’s see how it works.

a. Deleting the Entire Table

Now finally, we want to delete the entire file. How do we do that? Let’s find out.

>>> from sqlalchemy import create_engine
>>> from pandas.io import sql
>>> import pandas
>>> data=pandas.read_csv('samplecsv.csv',encoding="ISO-8859-1")
>>> engine=create_engine('sqlite:///:memory:') #Create db engine
>>> data.to_sql('data_table',engine) #Store dataframe as table
>>> sql.execute('DELETE FROM data_table',engine)
<sqlalchemy.engine.result.ResultProxy object at 0x00E6D9F0>
>>> print(sql.read_sql_query('SELECT * FROM data_table',engine))

Empty DataFrame
Columns: [index, 1, Eldon Base for stackable storage shelf, platinum, Muhammed MacIntyre, 3, -213.25, 38.94, 35, Nunavut, Storage & Organization, 0.8]
Index: []

b. Deleting a Single Row

Now we don’t have column names in this CSV file, but we can use the first value to delete a row this way-

>>> sql.execute('DELETE FROM data_table where 35=(?)',engine,params=[(9.80)])
<sqlalchemy.engine.result.ResultProxy object at 0x00FA3170>
>>> print(sql.read_sql_query('SELECT * FROM data_table',engine))

index   1   Eldon Base for stackable storage shelf, platinum  \
0      0   2  1.7 Cubic Foot Compact “Cube” Office Refrigera…
1      1   3   Cardinal Slant-D® Ring Binder, Heavy Gauge Vinyl
2      2   4                                               R380
3      3   5                           Holmes HEPA Air Purifier
4      4   6  G.E. Longer-Life Indoor Recessed Floodlight Bulbs
5      5   7  Angle-D Binders with Locking Rings, Label Holders
6      6   8            SAFCO Mobile Desk Side File, Wire Frame
7      7   9              SAFCO Commercial Wire Shelving, Black
8      8  10                                          Xerox 198

Muhammed MacIntyre    3  -213.25   38.94     35  Nunavut  \
0       Barry French  293   457.81  208.16  68.02  Nunavut
1       Barry French  293    46.71    8.69   2.99  Nunavut
2      Clay Rozendal  483  1198.97  195.99   3.99  Nunavut
3     Carlos Soltero  515    30.94   21.78   5.94  Nunavut
4     Carlos Soltero  515     4.43    6.64   4.95  Nunavut
5       Carl Jackson  613   -54.04    7.30   7.72  Nunavut
6       Carl Jackson  613   127.70   42.76   6.22  Nunavut
7     Monica Federle  643  -695.26  138.14  35.00  Nunavut
8    Dorothy Badders  678  -226.36    4.98   8.33  Nunavut

Storage & Organization   0.8
0                      Appliances  0.58
1  Binders and Binder Accessories  0.39
2    Telephones and Communication  0.58
3                      Appliances  0.50
4              Office Furnishings  0.37
5  Binders and Binder Accessories  0.38
6          Storage & Organization   NaN
7          Storage & Organization   NaN
8                           Paper  0.38
Follow this link for Python GUI Programming – Tkinter Tutorial
So, this was all about the Working of Relational Database with Python. Hope you like our explanation.

6. Conclusion

Hence, in this Python Relation Database tutorial, we learned about working of a relational database with Python. Now you can read CSV files, insert data into them, and delete data from them using simple SQL queries. Got any questions? Drop them in the comments below.
Related Topic- Top 30 Python Interview Questions and Answers in 2018
For reference