How to Create a Database Table with pyodbc in Python

In this article, you will get the necessary information on how to create a table in SQLite using the package pyodbc in Python.  Example code is included and a link to a Github repo will be provided in the references section.

Prerequisites

To use pyodbc in this article some prerequisites need to be met.

1. ODBC Driver – you need to download and install the ODBC driver for the database provider you are using. In this article, I am using SQLite.  Downloaded an ODCB driver for sqlite3 from http://www.ch-werner.de/sqliteodbc/,
2. SQLite3 – this article assumes that you have SQLite3 in your system and have created a database called testDB.db.
3. Database DDL – different database providers use different data definition languages.  DDL is the language needed to create tables in a database.  You must be familiar with this syntax to successfully create your table.  Creating tables in SQLite can be found here: https://sqlite.org/lang_createtable.html
4. Design your table – This is the table that will be implemented in the example below:

Here is an example of why you need to know the Data Definition Language of your database.  The following information was taken from https://www.sqlite.org/datatype3.html

“SQLite does not have a storage class set aside for storing dates and/or times. Instead, the built-in Date And Time Functions of SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values:

• TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS").
• REAL as Julian day numbers, the number of days since noon in Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian calendar.
• INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC.

Applications can choose to store dates and times in any of these formats and freely convert between formats using the built-in date and time functions.”

SQLite Create Table Data Definition Language for the PeopleInfo table:

CREATE TABLE PeopleInfo (
        PersonId INTEGER PRIMARY KEY,
        FirstName TEXT NOT NULL,
        LastName  TEXT NOT NULL,
        Age INTEGER NULL,
        CreatedAt TEXT DEFAULT CURRENT_TIMESTAMP NOT NULL
 
);

Let’s dive into the steps to create a table in SQLite using pyodbc in Python.

Step 1: Install the pyodbc Package

Install the Pyodbc package using the following command:

pip install pyodbc

For Anaconda use the following command:

conda install -c anaconda pyodbc

Step 2: Connect Your Python Script to SQLite

Next, you will need to connect your script to SQLite.

You may use this template to perform the connection:

Remember to import the pyodbc package first. Connect to the database using the connect method with the ODBC connection string for SQLite.

import pyodbc
conn = pyodbc.connect('Driver={SQLite3 ODBC Driver};'
                      'Server=server_name;'
                      'Database=database_name;'
                      'Trusted_Connection=yes;')

Step 3: Create Cursor from Your Connection

The cursor object created by cursor() allows you to execute queries.

cursor = conn.cursor()

Step 4: Create the Table in SQLite

Now you will be able to create your table in SQLite

For our example, here is the code that I used to create the table in SQL Server using Python: 

cursor.execute('''
 CREATE TABLE PeopleInfo (
        PersonId INTEGER PRIMARY KEY,
        FirstName TEXT NOT NULL,
        LastName  TEXT NOT NULL,
        Age INTEGER NULL,
        CreatedAt TEXT DEFAULT CURRENT_TIMESTAMP NOT NULL
 
);

               ''')

Step 5: Commit the Transaction

conn.commit()

Committing the transaction is an important topic to discuss.  The statement above explicitly commits the transaction.  If you do not commit the transaction, the database will not create the table.  The transaction will be rolled back.

 Other options to consider are:

 1 – Add the autocommit parameter to connect.  This will not require a manual commit.  For example:

con = pyodbc.connect(your_connection_string, autocommit = True)

2 – Use a with block and anything will be committed before the connection is terminated at the end of the with block.  For example:

with pyodbc.connect(your_connection_string) as con:
    CREATE_TABLE_CODE

Step 6: Insert Records to Verify Your Table is Configured Correctly.

cursor.execute('''
                INSERT INTO PeopleInfo (PersonId, FirstName, LastName, Age)
                VALUES
                (1,'Bob','Smith', 55),
                (2, 'Jenny','Smith', 66)
                ''')
conn.commit()

Step 7: Run a SELECT Query to Retrieve the Records.

cursor.execute('SELECT * FROM PeopleInfo')
for row in cursor:
    print(row)

Step 8: Close Your Connection If It Isn’t Needed.

conn.close()

Database connections are an expensive resource and there might be limited connections available to your database.  Remember to close your connection explicitly if you are not using a “with“ block as explained in Step 5.

Complete code:

import pyodbc
conn = pyodbc.connect('Driver={SQLite3 ODBC Driver};'
                      'Server=localhost;'
                      'Database=testDB.db;'
                      'Trusted_Connection=yes;')
cursor = conn.cursor()
cursor.execute('''
 CREATE TABLE PeopleInfo (
        PersonId INTEGER PRIMARY KEY,
        FirstName TEXT NOT NULL,
        LastName  TEXT NOT NULL,
        Age INTEGER NULL,
        CreatedAt TEXT DEFAULT CURRENT_TIMESTAMP NOT NULL
 
);

               ''')
conn.commit()

cursor.execute('''
                INSERT INTO PeopleInfo (PersonId, FirstName, LastName, Age)
                VALUES
                (1,'Bob','Smith', 55),
                (2, 'Jenny','Smith', 66)
                ''')
conn.commit()

cursor.execute('SELECT * FROM PeopleInfo ')
for row in cursor:
    print(row)

conn.close()

Output:

(1, 'Bob', 'Smith', 55, '2020-08-01 20:37:04')
(2, 'Jenny', 'Smith', 66, '2020-08-01 20:37:04')

References

1 – Pyodbc Github repo: https://github.com/mkleehammer/pyodbc

2 – Pyodbc Getting Started: https://code.google.com/archive/p/pyodbc/wikis/GettingStarted.wiki

3 – Create tables with SQLite: https://www.sqlitetutorial.net/sqlite-create-table/

4 – Write table into a CSV file: https://blog.finxter.com/how-to-convert-access-accdb-table-to-a-csv-in-python/

Programming Humor

💡 Programming is 10% science, 20% ingenuity, and 70% getting the ingenuity to work with the science.

~~~

• Question: Why do Java programmers wear glasses?
• Answer: Because they cannot C# …!

Feel free to check out our blog article with more coding jokes. 😉