Starting with SQL in Python

A quick and easy way to be able to run SQL queries with Python is using SQLite. SQLite is a library that utilizes an SQL database engine. It performs relatively fast and has been proven to be highly reliable. SQLite is the most commonly used database engine in the test environment.

Here are simple steps to getting started.

To start, we will need to import SQLite into our Jupyter notebook.

Using the connect() function allows you to create a database in your environment. This helps users name their database to be called in Python later on. The connect() function maintains the connection while you are working with the database.

An important thing to note is that while you are connected to the database, other users will not be able to access it at the same time. This is why it is essential to close() the connection when you are done(I’ll discuss closing a connection near the end).

For this example, I will be referencing a CIA database called “factbook.db”.

The cursor() function is used to assist with executing our SQL queries.

It is important that you use the cursor() to return a Cursor instance corresponding to the database we want to query.

Using SQLite and Python, a SQL query will need to be passed throughout a string. Though it is not necessary, I believe it is good practice to save your queries to a variable so it can be reference later without having to re-write the entire query.

The table from our factbook.db file is called facts.

Save SQL Query as a string

2. Execute the query using the cursor variable from earlier. This will convert the results to tuples and store it as a local variable. To get all the results we use fetchall().

To run the query we saved early with pandas we do the following.

Output:

A snippet of the output from our query

Remember it is important to close your connection when you are finished. Closing the connection will grant others access to the database.

Here is a good template for SQLite’s thought process.

If you were like me then you probably wondered why are you running SQL queries in Python when it seems much easier to run them in PostgreSQL or MySQL.

Python has a huge number of libraries (for example, Pandas, StatsModel, and SciPy) that are intended for measurable and scientific analysis. The libraries likewise work to the perfection of abstracting without end the subtleties, so you don’t have to figure all the basic math by hand. In addition, you can get your outcomes promptly, so you can utilize Python iteratively to investigate your information. (Janbansk Training)

Here is a snippet of plotting histograms in Python with an SQL query.