Create an account


Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
[Tut] How to Create a Database Table with pyodbc in Python

#1
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:

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 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/



https://www.sickgaming.net/blog/2020/08/...in-python/
Reply



Possibly Related Threads…
Thread Author Replies Views Last Post
  [Tut] How To Format A String That Contains Curly Braces In Python? xSicKxBot 0 3 09-25-2020, 07:51 PM
Last Post: xSicKxBot
  [Tut] Python One Line Dictionary xSicKxBot 0 16 09-21-2020, 05:58 PM
Last Post: xSicKxBot
  [Tut] Python Import Error (ModuleNotFoundError) xSicKxBot 0 10 09-20-2020, 04:15 PM
Last Post: xSicKxBot
  [Tut] Python Unicode Encode Error xSicKxBot 0 12 09-19-2020, 03:25 PM
Last Post: xSicKxBot
  [Tut] How to Solve Python “TypeError: ‘int’ object is not iterable”? xSicKxBot 0 12 09-18-2020, 01:49 PM
Last Post: xSicKxBot
  [Tut] Python One Line Generator xSicKxBot 0 11 09-18-2020, 12:29 AM
Last Post: xSicKxBot
  [Tut] Python Keyboard Errors xSicKxBot 0 18 09-14-2020, 08:39 PM
Last Post: xSicKxBot
  [Tut] Replacements For Switch Statement In Python? xSicKxBot 0 21 09-13-2020, 04:31 PM
Last Post: xSicKxBot
  [Tut] Python raw_input() vs input() xSicKxBot 0 26 09-13-2020, 03:48 AM
Last Post: xSicKxBot
  [Tut] What Is Python Output Buffering and How to Disable It? xSicKxBot 0 22 09-12-2020, 02:00 PM
Last Post: xSicKxBot

Forum Jump:

Become a Patron!
[-]
Upcoming Events

[-]
Latest Threads
LEd — Awesome New Level Editor From Dead...
Last Post: xSicKxBot
Today 04:37 PM
» Replies: 0
» Views: 1
News - Video: Hyrule Warriors: Age Of C...
Last Post: xSicKxBot
Today 04:36 PM
» Replies: 0
» Views: 0
News - Destiny 2: Where Is Xur This Week...
Last Post: xSicKxBot
Today 04:36 PM
» Replies: 0
» Views: 0
(Indie Deal) ?Sexy Garden Bundle, Microi...
Last Post: xSicKxBot
Today 01:48 PM
» Replies: 0
» Views: 2
Steam - Dota 2 Update – September 23rd 2...
Last Post: xSicKxBot
Today 01:47 PM
» Replies: 0
» Views: 1
Xbox Wire - Xbox Insider Release Notes –...
Last Post: xSicKxBot
Today 01:47 PM
» Replies: 0
» Views: 1
News - How to get nearly 250k Steam wish...
Last Post: xSicKxBot
Today 01:47 PM
» Replies: 0
» Views: 1
News - Left 4 Dead 2 “Last Stand” Update
Last Post: xSicKxBot
Today 01:47 PM
» Replies: 0
» Views: 1
News - Pokemon Go Zapdos Raid Guide: Bes...
Last Post: xSicKxBot
Today 08:41 AM
» Replies: 0
» Views: 1
Microsoft - Find out about storage optio...
Last Post: xSicKxBot
Today 01:12 AM
» Replies: 0
» Views: 3

[-]
Twitter

[-]
Sponsored
Get the Deal of the Week at RefurBees.com

Copyright © SickGaming.net 2012-2020