Python has become one of the most popular programming languages and is used in various contexts, including in WEB applications and data science. In this post, we introduce the use of SQL in Python. We present how to connect to database management systems (Oracle, Microsoft SQL Server, MS Access and SQLite), submit DDL commands, query and update data, and control transactions. We use a Jupyter Notebook to run Python code.
Connecting to Database Management Systems
The first step to use SQL in Python is to import the library required for the DBMS that is being accessed. Then, we can create a connection to the database.
The library to be imported depends on the DBMS being accessed. To create a connection, we should call the connect method provided in the library. This process requires a connection string, which also depends on the DBMS presented as in the following examples.
Connecting to Oracle database
If you intend to connect to Oracle, then you should import cx_Oracle (available in Oracle client). Then, to connect to Oracle, you should use the connect method with the appropriate connection string, as in the following figure.
Create a connection to Microsoft SQL Server and to MS Access
There are several drivers to connect to MS SQL Server from Python, but Microsoft recommends the use of pyodbc. After importing pyodbc, you should call the connect. In the following, we use several variables to store the values used in the connection string.
Pyodbc can also be used to connect to MS Access from Python, then you may use a connection string that contains the MS Access driver used and the location of the database file, just like in the following example.
Connection to SQLite
To connect to SQLite, you should import sqlite3. Then, you can access a persisted database or use a main memory database, just like presented in the following figure.After importing the required library, we can create a connection. , like the examples of the following figure.
Closing a connection
Don’t forget to close your connection to the database, using close method of your connection object. In the following, we present a simple code that connects to a database print a message and closes the connection. We used a Jupyter notebook to run this code.
Creating a table, inserting and querying data
After creating a connection, you may create a cursor to run SQL commands. The cursor object is instantiated using the cursor method of the connection object. Then, you should use the cursor to run DML and DDL statements.
In the following figure, we present how to create a table and to execute insert and select statements. Use SQLite. But the main differences between such conde and the ones to use Oracle and Microsoft SQL Server are in the library and connection creation (explained above) and in particular SQL variations of each DBMS (ex: data types).
Update, delete, and transaction control
In the previous script, we calles the commit method of the connection object just before closing the database connection. Calling the commit method makes the database to commit the user transaction. On the other hand, if one wants to rollback a transaction, just have to call the rollback method. In the following script, the Execute method of a cursor is used to execute na Update statement, but the transaction is rolledback and the description update is not persisted in the database.
The execute method of a cursor can also be used to execute delete statements. In the following code, the product with id = 4 is deleted. But, then, the transaction is rolledback and the table still stores 3 rows.
Using bind variables and inserting lists of rows
In many real world situations, you would like to insert data on your table based on the values of variables. In such situations, you should use bind variables in SQL execution, just like in the following code.
Also, you may also want to store lists of data into your database. Such lists may be become several rows in a table, just like in the following code. Note that we delete all the records of the products table and then insert 6 rows using a single insert statement.
Now you have the basis to start using SQL in your Python programs!