Insert Values Into SQL Using Pyodbc and Python

The pyodbc is an open-source Python module used to access ODBC databases. This article discusses using pyodbc to insert values into an SQL database.


Before using the pyodbc module, we need to take care of some prerequisites. They include:

Installing pyodbc

Make sure that pyodbc is installed. You can do that using pip by running the command

pip install pyodbc

Have your favorite database management system installed

For example, MySQL, SQL Server, PostgreSQL, etc. (if you want to install SQL, check the links at the end of this article)

Install ODBC driver/connector for your SQL System

You can use the links provided at the end of this post to download and install ODBC drivers for any platform.

Create a Database on your SQL

In this case, you can follow a manual for your SQL system. In my case, I will be running SQL Server on Debian 11; therefore, I can proceed as follows.

Login to the SQL Server using the following command and the password created during SQL installation.

Where -S is the Server Name, and -U is the user (also created during installation).

Create SampleDB2 database by running:

Show all databases by executing the query:

Commands are not executed automatically on SQL Server. You must type GO on a new line to execute the previous command(s). See Figure below.

Note: The SQL query can be supplied directly from the terminal using -the Q option. In that case, the following one-liner can be used to create a database from the terminal.

Inserting Values into SQL

We are now ready to insert values into SQL Server using the pyodbc module. We will work on three examples to demonstrate concepts. In each example, we will:

  • Establish a connection to the database using pyodbc,
  • Insert values to SQL, and,
  • Verify that the data was added.

Example 1

Step 1: Create a connection to SQL Server

Since I am running locally, I will use the localhost as the Server Name. If unsure, you can use the following query to get the Server Name.

The code above will create a connection to SampleDB2, the database we created earlier.

Step 2: Insert values into a Table in SampleDB2

First, we need to create the table that will hold our data,

Second, insert a row into the BabyNames table

We can also use positional parameters marked by “?” on the query as follows

In cases with missing values in the rows, we need to specify column names on the query. See below.

Step 3: Verify results

To do that, we need to execute these SQL commands.

The first command changes the database we want to use, and the second selects all records for all fields in the BabyNames table.

You can also run the SELECT command to view table contents within Python as follows


(11350, 'Emma', 2004, 'F', 'AK', 62)
(11351, 'Madison', 2004, 'F', 'AK', 48)
(11352, 'Hannah', None, 'F', 'AK', None)

Example 2: Insert Python DataFrame into SQL table

The data to be loaded as a DataFrame and inserted into SQL are contained in bp.csv. Here is the actual content of the CSV.

1,Male,30-45, ,153

Step 1: Establish a connection to the SQL database and load CSV data

Note: The last line in the above code snippet is critical if your data contain missing values. It is used to convert pandas NaN value into Python None. The pyodbc cursor cannot handle NaNs well.

Step 2: Create a table and insert values to it

Step 3: Verify that data was inserted

To do that, we need to run the query
“SELECT * FROM Patients2;” on SQL Server or Python, as shown below.

Output (truncated):

(1, 'Male', '30-45', None, 153)
(2, 'Male', '30-45', 163, 170)
(3, 'Male', '30-45', 153, 168)
(4, 'Male', '30-45', 153, 142)

Example 3: CSV data into SQL Table using pandas.DataFrame.to_sql()

This is an alternative method to what we did in Example 2. We will make use of pandas.DataFrame.to_sql() function and sqlalchemy package.

For demonstration, we will save the following data in baby_names.csv.

11351,Madison, ,F,AK,48
11353, Grace,2004,F,AK,44
11354,Emily,2004,F, ,41

The above CSV file’s content can be sent to SQL Table using the code below.

Output (formatted for better viewing):


This article discussed how to enter values into SQL using the pyodbc package. We worked on three examples – the first covered how to enter data into SQL, row by row and piece by piece. The other two examples discussed using the pyodbc module to enter CSV data.

Links you May Find Useful