Everyone uses SQL, and everyone uses Python. SQL is the de-facto standard for databases. Python on the other hand is an all-star, a top language for data analytics, machine learning, and web development. Imagine both, together.
This is actually incredibly easy to setup. We can quickly utilize the dynamic nature of Python, to control and build queries in SQL. The best part? After set-up, you don’t need to do anything.
Both of these amazing tools together, allow us to reach new heights of automation and efficiency.
Pyodbc
Our bridge between the two technologies is pyodbc. This library allows easy access to ODBC databases.
ODBC, short for Open Database Connectivity, is a standardised application programming interface (API) for accessing databases, developed by the SQL Access group back in the early 90’s.
Compliant database management systems (DBMS) include:
MS Access
MS SQL Server
MySQL
Oracle
In this article, we will be using MS SQL Server. For the most part, this should be directly transferable for use with any ODBC compliant database. The only change required should be with the connection setup.
Connect
The first thing we need to do is create a connection to the SQL server. We can do this using pyodbc. connect. Within this function, we must also pass a connection string.
This connection string must specify the DBMS Driver, the Server, a specific Database to connect to, and our connection settings.
So, let’s assume we want to connect to server test, database addend, to do this we want to use SQL Server Native Client 11.0.
We will be connecting from an internal, and thus trusted connection (we do not need to enter our username and password).
conn_str = (“Driver= {SQL Server Native Client 11.0};”
“Server=test;”
“Database=addend;”
“Trusted_Connection=yes;”)
Our connection is now initialized with:
db = pyodbc.connect(conn_str)
If we are not accessing the database via a trusted connection, we will need to enter the username and password that we would usually use to access the server via SQL Server Management Studio (SSMS).
For example, if our username is shubham, and our password is Password123, we should immediately change our password.
But before changing that horrible password, we can connect like so:
conn_str = (“Driver= {SQL Server Native Client 11.0};”
“Server=test;”
“Database=addend;”
“UID=shubham;”
“PWD=Password123;”)
db = pyodbc.connect(conn_str)
Now we are connected to the database, we can begin performing SQL queries via Python.
Run a Query
Every query we run on SQL Server now will consist of a cursor initialization and query execution. Additionally, if we make any changes inside the server, we also need to commit these changes to the server (which we cover in the next section).
To initialize a cursor:
cursor = db.cursor()
Now, whenever we want to perform a query, we use this cursor object.
Let’s first select the top 1000 rows from a table called customers:
cursor.execute(“SELECT TOP(1000) * FROM customers”)
This performs the operation, but within the server, and so nothing is actually returned to Python. So, let’s look at extracting this data from SQL.
Extract Data
To extract our data from SQL into Python, we use pandas. Pandas provides us with a very convenient function called read_sql, this function, as you may have guessed, reads data from SQL.
read_sql requires both a query and the connection instance db, like so:
data = pd.read_sql(“SELECT * FROM customers”, db)
This returns a dataframe containing the top 1000 rows from the customer’s table.
Next Steps
Once we have performed whichever manipulation tasks we needed to do. We can extract our data to Python alternatively, we can extract the data to Python and manipulate it there too.
Whichever approach you take, once the data is there in Python, we can do a multitude of useful things with it that were simply not possible before.
So, in a few simple, easy steps, we’ve taken a first look at quickly setting up a more efficient, automated workflow using an integration of both SQL and Python. Python simply kicks open new routes that were impassable to us before with just SQL alone.
Thanks for reading!
Shubham Kokane
Data Engineer
Addend Analytics