Python and SQL

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:

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.

Number one again!

I was pleased to see that my SQL Expert book is yet again listed as a number one best seller this afternoon by Amazon.com’s book ranking system for two of the book’s categories:

  • Computers and Technology -> Databases and Big Data -> Oracle
  • Computers and Technology -> Certification -> Oracle

Trailing are excellent books in their own right, including many authored or co-authored by Oracle ACE Directors: a brand new Oracle Public Cloud book co-authored by Charles Kim, Mark Rittman‘s Business Intelligence book from Oracle Press, and Steve Feuerstein‘s legendary PL/SQL book.

I’m busy working on a new book from Oracle Press, more on that in the very near future – stay tuned! In the meantime, the 047 book makes a great holiday present for anyone in your life who wants to become a certified SQL Expert!

Below is the screenshot from the “Databases and Big Data” category:

Oracle SQL Expert Number One on the Amazon Best Selling Charts 11/29/2016 for its category.

Larry Ellison, and “cloud computing” vs. “utility computing”

“Everyone doesn’t have their own well at their house … we tap into the water network, we tap into the electricity network … we get better service at a lower price by having our water come from a utility … we’ll experience the same economic advantages … as we get our data from an information utility.” – Larry Ellison, Oracle Open World, September 18, 2016, spoken during his keynote presentation: “The Cloud: A New Era of Utility Computing”

Don’t be misled.   Larry Ellison is not late to the Cloud Party.  Consider this quote from 1998:

… Larry Ellison, the founder and CEO of the Oracle Corporation, has frequently mused at what life would be like if common household appliances had the same complexity of maintenance as a PC … “Sorry, I can’t go out tonight, I’m staying home so I can upgrade my TV to version 7.0.” … Ellison’s argument … the PC must become as easy to use as any common household appliance.

In a recent presentation … Ellison was asked … if the network will be stable enough—won’t it crash from time to time? … Ellison’s response … what is the last thing that crashed on you: your telephone, or Windows … ? The audience roared with laughter, making the answer obvious …

The number of networks we already depend on is impressive: plumbing, electricity, highways, television, radio—all networks professionally run by others … Why should a computer user experience anything different?

— Excerpted from Chapter 27, “Oracle Web Application Server”, in the book “Oracle8 Server Unleashed” published by Macmillan Computer Publishing.

That’s a brilliant quote, isn’t it?  You better say ‘yes’ because I wrote that – all the way back in 1998.

And I wrote it because of Larry Ellison’s leadership.  Ellison, way ahead of his time, had already seen the potential of the World Wide Web and its underlying protocol, HTTP, all of which had been invented barely five years prior.  Ellison had already seen the vision and was leading the charge towards “utility computing”.

So I was thrilled to see Ellison keeping that phrase alive yesterday at OOW.  To me, “utility” computing is significantly more descriptive than the phrase “cloud” computing.  Clouds paint images of vague, nebulous, fragmented things somewhere far away, without specificity or form or shape.  But utility computing is much more descriptive of what is really intended: a ubiquitous workhorse that is simultaneously both specific and dependable, scientifically complex at it core, cutting edge in its usefulness, yet easy for everyone to access and use for common purposes.

Cloud computing is not just a “computer somewhere else”, as I’ve heard it described – that’s a funny one-liner and I’ve been guilty of laughing at it myself.  But cloud computing – utility computing – is significantly much more than that.  It is a series of complex hardware, network, and software components, brought together in an easy-to-configure, easy-to-access, ever-present simple interface through which an end-user can quickly set up (provision) whatever resources he or she needs quickly to meet whatever business requirement is currently demanding attention, in a more cost-effective manner than ever before, by compartmentalizing the various desired components and optimizing their deployment.

It is true that Amazon Web Services has much of the market momentum at the moment, and has captured a lot of the headlines in the tech industry.

But the truth is that Oracle is still, to this moment, the unquestioned leader in all forms of serious professional business software applications, and he has been actively and purposely working for years to leverage the power of the network in support of business objectives.  The multi-year re architecture of most of the world’s leading enterprise resource planning (ERP) applications is just one example of what they’ve done toward this goal.

This week’s Oracle Open World should be very eventful.

For Larry Ellison’s keynote last night, see this link, but recognize this link might not survive much longer than OOW 2016: https://www.oracle.com/openworld/on-demand/index.html

For an OOW YouTube channel highlight that might survive the link above, see here: https://youtu.be/S1p_TcS9bxk