Earlier I posted about Python. Since then I’ve worked a Python project that interacted with a SQL database using SQL Alchemy and got an opportunity to do quite a bit in the way of data transformation and data integration, leveraging Python’s remarkably flexible capabilities and rich libraries for use with analytics, machine learning and deep learning, and more. It’s a great language. I love Java but I can certainly see why Python has the momentum right now. It’s an easy language to pick up. Whereas a typical Java program requires quite a bit to reach the simple “Hello, World” test program, Python gets you there much more quickly.

print("Hello, world");

That’s it! Crazy easy. Compare this to Java:

public class demonstrateHelloWorld {
    public static void main(String[] args) {
        System.out.println("Hello, World");
    }
}

Now – to be fair – there’s a reason this Java example is more involved, its due to Java’s requirement that code be structured in the form of a class. Python doesn’t require that but offers the same option. If you’ve seen this sort of Python code:

if __name__ == "__main__":
  print('Hello, world')

… then you know what I’m talking about.

A side-by-side comparison of the two languages is more involved that I intend to do here, my only point is that Python is very easy to start with and get productive with quickly.

For example, interacting with local text files can be involved with other languages. With Python, its simple:

file = open('output.txt', 'w')
file.write('Hello World')
file.close()

That’s it. Stupid simple.

My last project this year involved middleware servers, REST services, data tranformations of all sorts, multiple databases and data stores leveraging SQL and JSON, abstracted environment configurations to support dev ops in the larger environment, and more. Easy to manage in Python.

It’s a great language. Java is still number one, and the last I checked I think even C++ still dominates over Python on the list of world’s most in-use software languages.

But Python is the one to watch.

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.

Getting Ready

I downloaded and used the following software:

Python	http://www.python.org	3.5.2	Popular software language!
SQLite	http://sqlite.org	3.15.2	Embedded server-less SQL database
Oracle	http://www.oracle.com	12c	The world’s leading relational database management system
cx_Oracle	http://cx-oracle.sourceforge.net/	5.2.1	Python extension module

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.