[Tut] How to Create a Database Table with pyodbc in Python

[xSicKxBot]

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

Code:

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 fromhttp://www.ch-werner.de/sqliteodbc/,
   
   
2. SQLite3 – this article assumes that you have SQLite3 in your system and have created a database called
   Code:

   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:
   

Column |
Data Type |
Constraint |
Default Value

PersonId |
Integer |
PRIMARY KEY |

FirstName |
Text |
NOT NULL |

LastName |
Text |
NOT NULL |

Age |
Ineger |
NULL |

CreatedAt |
Text |
NOT NULL |
Now

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

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

• TEXT as ISO8601 strings (
  Code:

  "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-indate and time functions.”

SQLite Create Table Data Definition Language for the PeopleInfo table:

Code:

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 thePyodbc package using the following command:

Code:

pip install pyodbc

For Anaconda use the following command:

Code:

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

Code:

pyodbc

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

Code:

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

Code:

cursor()

allows you to execute queries.

Code:

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: 

Code:

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

Code:

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:

Code:

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

2 – Use a

Code:

with

block and anything will be committed before the connection is terminated at the end of the

Code:

with

block.  For example:

Code:

with pyodbc.connect(your_connection_string) as con: CREATE_TABLE_CODE

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

Code:

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.

Code:

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

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

Code:

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:

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:

Code:

(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...arted.wiki

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