Reading and writing SQL files in Pandas (2023)

When I started learning Data Analytics a few years ago, the first thing I learned was SQL and Pandas. As a data analyst, it is essential to have a strong foundation in working with SQL and Pandas. Both are powerful tools that help data analysts efficiently analyze and manipulate data stored in databases.

Overview of SQL and Pandas

SQL (Structured Query Language) is a programming language used to manage and manipulate relational databases. On the other hand, Pandas is a Python library used for data manipulation and analysis.

Data analysis involves working with large amounts of data, and databases are often used to store this data. SQL and Pandas provide powerful tools for working with databases, allowing data analysts to efficiently extract, manipulate and analyze data. By leveraging these tools, data analysts can gain valuable insights from data that would otherwise be difficult to obtain.

In this article, we will explore how to use SQL and Pandas to read and write to a database.

Connects to DB

Installation of the libraries

We first need to install the necessary libraries before we can connect to the SQL database with Pandas. The two main libraries required are Pandas and SQLAlchemy. Pandas is a popular data manipulation library that allows for storing large data structures, as mentioned in the introduction. In contrast, SQLAlchemy provides an API to connect to and interact with the SQL database.

We can install both libraries using the Python package manager, pip, by running the following commands at the command prompt.

$pip install pandas$pip installer sqlalchemy

Establishing the connection

With the libraries installed, we can now use Pandas to connect to the SQL database.

To begin with, we will create a SQLAlchemy engine object withcreate_engine(). Thecreate_engine()function connects the Python code to the database. It takes as an argument a connection string specifying the database type and connection details. In this example, we use the SQLite database type and the database file path.

Create an engine object for an SQLite database using the example below:

importpandasaspdfromsqlalchemyimportcreate_engine# Create an engine objectmotor = create_engine('sqlite:///C/SQLite/student.db')

If the SQLite database file, student.db in our case, is in the same directory as the Python script, we can use the filename directly, as shown below.

motor = create_engine('sqlite:///student.db')

Reading SQL files with pandas

Now that we have established a connection, let's read the data. In this section we will look atread_sql,read_sql_table, andread_sql_queryfunctions and how to use them to work with a database.

Executing SQL queries using Panda'sread_sql()Work

Theread_sql()is a Pandas library function that allows us to execute an SQL query and retrieve the results into a Pandas dataframe. Thatread_sql()function connects SQL and Python so we can take advantage of the power of both languages. The function is wrappedread_sql_table()andread_sql_query(). Theread_sql()function is routed internally based on the supplied input, meaning that if the input is to execute an SQL query, it will be routed toread_sql_query(), and if it's a database table, it will be redirected toread_sql_table().

Theread_sql()syntax is as follows:

pandas.read_sql(sql, con, index_col=No, coerce_float=Right, params=No, parse_dates=No, columns=No, chunksize=No)

SQL and con parameters are required; the rest is optional. However, we can manipulate the result using these optional parameters. Let's take a closer look at each parameter.

• sql: Name of SQL query or database table
• when: Connection object or connection URL
• index_col: This parameter allows us to use one or more columns from the SQL query result as a data frame index. It can take either a single column or a list of columns.
• coerce_float: This parameter specifies whether non-numeric values ​​should be converted to floats or left as strings. It is set to true by default. If possible, it converts non-numeric values ​​to floating types.
• params: The parameters provide a safe method of passing dynamic values ​​to the SQL query. We can use the params parameter to pass a dictionary, tuple or list. Depending on the database, the syntax of parameters varies.
• parse_dates: This allows us to specify which column in the resulting data frame should be interpreted as a date. It accepts a single column, a list of columns, or a dictionary with the key as the column name and the value as the column format.
• columns: This allows us to retrieve only selected columns from the list.
• pieces: When working with a large data set, chunk size is important. It retrieves the query result in smaller chunks, which improves performance.

Here is an example of how to useread_sql():

Code:

importpandasaspdfromsqlalchemyimportcreate_engine# Create an engine objectmotor = create_engine('sqlite:///C/SQLite/student.db')# Get all records from the Student table and manipulate the resultdf = pd.read_sql("SELECT * FROM student", motor, index_col='Roll number', parse_dates='date of birth')Print(df)Print("The data type for dateOfBirth: ", df.dateOfBirth.dtype)# Close the database connectionengine.dispose()

Production:

first name last name email dateOfBirthrollNumber1 Mark Simson[email protected]2000-02-232 Peter Griffen[email protected]2001-04-153 Meg Aniston[email protected]2001-09-20 Date type for date of birth: datetime64[ns]

After connecting to the database, we execute a query that returns all records fromStudenttable and stores them in the DataFramedf. The "Roll Number" column is converted to an index usingindex_colparameter, and the "dateOfBirth" data type is "datetime64[ns]" due toparse_dates. We can useread_sql()not only to retrieve data but also to perform other operations such as insert, delete and update.read_sql()is a generic function.

Loading specific tables or views from the database

Loading a specific table or view with Pandasread_sql_table()is another technique for reading data from the database into a Pandas dataframe.

What isread_sql_table?

Panda's library offersread_sql_tablefunction which is specifically designed to read an entire SQL table without executing any queries and return the result as a Pandas dataframe.

The syntax ofread_sql_table()is as below:

pandas.read_sql_table(tablename, con, schema=No, index_col=No, coerce_float=Right, parse_dates=No, columns=No, chunksize=No)

With the exception oftabelnavnand scheme, the parameters are explained in the same way asread_sql().

• tabelnavn: parameterstabelnavnis the name of the SQL table in the database.
• scheme: This optional parameter is the name of the schema that contains the table name.

After establishing a connection to the database, we will useread_sql_tablefunction to loadStudenttable into a Pandas DataFrame.

importpandasaspdfromsqlalchemyimportcreate_engine# Create an engine objectmotor = create_engine('sqlite:///C/SQLite/student.db')# Load student table from the databasedf = pd.read_sql_table('Student', motor)Print(df.head())# Close the database connectionengine.dispose()

Production:

rollNumber first name last name e-mail date of birth0 1 Mark Simson[email protected]2000-02-231 2 Peter Griffen[email protected]2001-04-152 3 Meg Aniston[email protected]2001-09-20

We assume that it is a large table that may be memory intensive. Let's explore how we can usepiecesparameter to solve this problem.

Code:

importpandasaspdfromsqlalchemyimportcreate_engine# Create an engine objectmotor = create_engine('sqlite:///C/SQLite/student.db')# Load student table from the databasedf_iterator = pd.read_sql_table('Student', motor, chunksize =1)# Repeat the data frametodfidf_iterator:Print(df.head())# Close the database connectionengine.dispose()

Production:

rollNumber first name last name e-mail date of birth0 1 Mark Simson[email protected]2000-02-230 2 Peter Griffen[email protected]2001-04-150 3 Meg Aniston[email protected]2001-09-20

Please remember thatpiecesI use here is 1 because I only have 3 records in my table.

Query the database directly with Pandas' SQL syntax

Extracting insights from the database is an important part for data analysts and scientists. To do so, we will take advantage ofread_sql_query()function.

What is read_sql_query()?

Using pandasread_sql_query()function, we can run SQL queries and get the results directly into a DataFrame. Thatread_sql_query()function is created especially forCHOOSEstatement. It cannot be used for other operations, such asDELETEorUPDATE.

Syntax:

pandas.read_sql_query(sql, con, index_col=No, coerce_float=Right, params=No, parse_dates=No, chunksize=No, dtype=No, dtype_backend=_NoDefault.no_default)

All parameter descriptions are the same asread_sql()function. Here is an example ofread_sql_query():

Code:

importpandasaspdfromsqlalchemyimportcreate_engine# Create an engine objectmotor = create_engine('sqlite:///C/SQLite/student.db')# Query the student tabledf = pd.read_sql_query('Select firstname, lastname from student Where rollNumber = 1', motor)Print(df)# Close the database connectionengine.dispose()

Production:

first name last name0 Mark Simson

Writing SQL files with pandas

While analyzing data, suppose we discovered that a few records need to be changed or that a new table or view with the data is required. To update or insert a new record is a method to useread_sql()and write a query. However, that method can be long. Pandas provides a great method calledto_sql()for situations like this.

In this section we will first build a new table in the database and then edit an existing one.

Creating a new table in the SQL database

Before we create a new table, let's discuss firstto_sql()in details.

What isto_sql()?

Theto_sql()function of the Pandas library allows us to write or update the database. Thatto_sql()function can save DataFrame data to a SQL database.

Syntax forto_sql():

DataFrame.to_sql(name, con, schema=No, if_exists='betray', index=Right, index_label=No, chunksize=No, dtype=No, method=No)

Whennameandwhenparameters are mandatory to runto_sql(); however, other parameters provide additional flexibility and customization options. Let's discuss each parameter in detail:

• name: The name of the SQL table to be created or modified.
• when: The connection object for the database.
• scheme: The schema of the table (optional).
• if_exists: The default value for this parameter is "fail". This parameter allows us to decide what action to perform if the table already exists. Options include "fail", "replace" and "add".
• index: The index parameter accepts a boolean value. By default, it is set to True, which means that the index of the DataFrame will be written to the SQL table.
• index_etiquette: This optional parameter allows us to specify a column label for the index columns. By default, the index is written to the table, but a specific name can be given using this parameter.
• pieces: The number of rows to be written at a time to the SQL database.
• dtype: This parameter accepts a dictionary with keys as column names and values ​​as their data types.
• method: The method parameter allows specifying the method used to insert data into SQL. By default it is set to None, which means pandas will find the most efficient way based on the database. There are two main options for method parameters:
  • multi: It allows to insert multiple rows in a single SQL query. However, not all databases support multi-row insert.
  • Callable function: Here we can write a custom function for insertion and call it using method parameters.

Here is an example of usingto_sql():

importpandasaspdfromsqlalchemyimportcreate_engine# Create an engine objectmotor = create_engine('sqlite:///C/SQLite/student.db')# Create a new dataframe that will be our new tabledata = {'Name': ['Paul','Tom','Jerry'],'Alder': [9,8,7]}df = pd.DataFrame(data)# Create a new table called Customerdf.to_sql('Could', con=engine, if_exists='betray')# Close the database connectionengine.dispose()

A new table called Customer is created in the database with two fields called "Name" and "Age".

Database snapshot:

Updating existing tables with Pandas Dataframes

Updating data in a database is a complex task, especially when dealing with large data. But by usingto_sql()feature in Pandas can make this task much easier. To update the existing table in the database,to_sql()function can be used withif_existsparameter set to "replace". This will overwrite the existing table with the new data.

Here is an example ofto_sql()that updates the previously created oneCouldtable. Suppose, iCouldtable we want to update the age of a customer named Paul from 9 to 10. To do that we can first change the corresponding row in the DataFrame and then useto_sql()function to update the database.

Code:

importpandasaspdfromsqlalchemyimportcreate_engine# Create a connection to the SQLite databasemotor = create_engine('sqlite:///C/SQLite/student.db')# Load customer table into a data framedf = pd.read_sql_table('Could', motor)# Edit the age of the customer named Pauldf.loc[df['Name'] =='Paul','Alder'] =10# Update the customer table with the changed DataFramedf.to_sql('Could', con=engine, if_exists='substitute')# Close the database connectionengine.dispose()

In the database, Paul's age is updated:

Conclusion

In conclusion, Pandas and SQL are both powerful tools for data analysis tasks such as reading and writing data to the SQL database. Pandas provides an easy way to connect to the SQL database, read data from the database into a Pandas dataframe, and write dataframe data back to the database.

The Pandas library makes it easy to manipulate data in a data frame, whereas SQL provides a powerful language for querying data in a database. Using both Pandas and SQL to read and write the data can save time and effort in data analysis tasks, especially when the data is very large. Overall, leveraging SQL and Pandas together can help data analysts and scientists streamline their workflow.