Is it me? Or is there a lot of buzz about Python lately? Python is a great language for doing certain work quickly. It supports a variety of software design approaches. It is interpreted, and yet is object-oriented. You can use it like an interactive calculator, and it comes with powerful math libraries. It’s succinct – you can do a lot with fewer lines of code than is typical, but it can be used for large scale programming as well. For these reasons and more, Python is growing in popularity, particularly in the fields of data analytics and data science.
It makes sense that it works well with SQL to provide data persistence. This blog post shows you how you can leverage existing SQL data stores with the power of Python.
It makes sense that it works well with SQL to provide data persistence. This blog post shows you how you can leverage existing SQL data stores with the power of Python.
Getting Ready
I downloaded and used the following software:
I downloaded and installed Python and Oracle. I also downloaded SQLite and cx_Oracle for use with Python.
Python and SQLite
SQLite is an embedded SQL tool. It doesn’t have a separate server process, but is easily incorporated into applications. Here’s an example of Python code that uses SQLite, version 3:
import sqlite3
connection = sqlite3.connect("ships.db")
cursor = connection.cursor();
sql_statement = """CREATE TABLE ships
( ship_id INTEGER
, ship_name TEXT);"""
cursor.execute(sql_statement)
sql_insert = """INSERT INTO ships VALUES(1, 'Codd One');"""
cursor.execute(sql_insert)
sql_insert = """INSERT INTO ships VALUES(2, 'Codd Two');"""
cursor.execute(sql_insert)
sql_insert = """INSERT INTO ships VALUES(3, 'Codd Three');"""
cursor.execute(sql_insert)
connection.commit()
The code above will successfully create a SQLite database file “ships.db”, then create table “ships” and then add three rows to it, and COMMIT the records.
In a subsequent script, you can query the same database.
import sqlite3
connection = sqlite3.connect("ships.db")
cursor = connection.cursor();
sql_statement = """SELECT ship_id, ship_name FROM ships;"""
cursor.execute(sql_statement)
print("fetchall:")
result = cursor.fetchall()
for r in result:
print(r)
The rows returned by the SELECT statement are processed within the “for” loop. “Result” captures the result of the query, and “r” serves as a fetched value for each row. In this case, each row represents an array of two values, and both values are displayed with the “print(r)” statement.
The output:
fetchall:
(1, 'Codd One')
(2, 'Codd Two')
(3, 'Codd Three')
>>>
Note: the “>>>” symbol is the Python cursor, indicating the interactive system is awaiting the next instruction.
Python and Oracle
Now let’s use Python to send SQL statements to Oracle 12c. To establish the connection, we use the Python extension module “cx_Module”, available from SourceForge.net – see above for the links to all the software necessary to run these scripts.
Also – note that while SQL strings submitted directly to Oracle require semi-colons, SQL statements sent via cx_Oracle reject them.
In this example, we’ll do the same thing we did with SQLite, with one change: instead of breaking up the CREATE/INSERT and SELECT statements into two scripts, we’ll do it all in one script:
import cx_Oracle
connection = cx_Oracle.connect('username/password@localhost/orcl')
print("Oracle RDBMS version " + connection.version)
cursor = connection.cursor()
sql_statement = """DROP TABLE ships2"""
cursor.execute(sql_statement)
sql_statement = """CREATE TABLE ships2
( ship_id NUMBER
, ship_name VARCHAR2(20))"""
cursor.execute(sql_statement)
sql_insert = """INSERT INTO ships2 VALUES(1, 'Codd Crystal')"""
cursor.execute(sql_insert)
sql_insert = """INSERT INTO ships2 VALUES(2, 'Codd Elegance')"""
cursor.execute(sql_insert)
sql_insert = """INSERT INTO ships2 VALUES(3, 'Codd Champion')"""
cursor.execute(sql_insert)
connection.commit()
cursor.execute("SELECT ship_id, ship_name FROM ships2")
print("fetchall:")
result = cursor.fetchall()
for r in result:
print(r)
connection.close()
print("End of script, database connection closed.");
The output of this script is the same we saw earlier:
fetchall:
(1, 'Codd One')
(2, 'Codd Two')
(3, 'Codd Three')
>>>
Regardless of which database we use, the results are the same.
Closing Thoughts
In this blog post, we looked at how to use Python to execute SQL statements. We created SQL objects, and sent data to the database, as well as queried data from the database. We passed data to and from Python structures and set t he stage for any data manipulation we wish to do using these powerful tools.
Python is easy to use, and capable of performing complex math functions and data manipulation. Using SQL to persist data for Python is an ideal combination for data analytics.
NOTE: This data and the accompanying scripts are available at my GitHub site: https://github.com/Skere9.
