{"id":116208,"date":"2020-08-02T10:30:45","date_gmt":"2020-08-02T10:30:45","guid":{"rendered":"https:\/\/blog.finxter.com\/?p=11636"},"modified":"2020-08-02T10:30:45","modified_gmt":"2020-08-02T10:30:45","slug":"how-to-create-a-database-table-with-pyodbc-in-python","status":"publish","type":"post","link":"https:\/\/sickgaming.net\/blog\/2020\/08\/02\/how-to-create-a-database-table-with-pyodbc-in-python\/","title":{"rendered":"How to Create a Database Table with pyodbc in Python"},"content":{"rendered":"

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

Prerequisites<\/h2>\n

To use pyodbc<\/a><\/code> in this article some prerequisites need to be met.<\/p>\n

    \n
  1. ODBC Driver<\/strong> \u2013 you need to download and install the ODBC driver for the database provider you are using. In this article, I am using SQLite.\u00a0 Downloaded an ODCB driver for sqlite3 from http:\/\/www.ch-werner.de\/sqliteodbc\/<\/a>,<\/li>\n
  2. SQLite3 <\/strong>– this article assumes that you have SQLite3 in your system and have created a database called testDB.db<\/code>.<\/li>\n
  3. Database DDL<\/strong> \u2013 different database providers use different data definition languages.\u00a0 DDL is the language needed to create tables in a database.\u00a0 You must be familiar with this syntax to successfully create your table.\u00a0 Creating tables in SQLite can be found here: https:\/\/sqlite.org\/lang_createtable.html<\/a><\/li>\n
  4. Design your table<\/strong> \u2013 This is the table that will be implemented in the example below:<\/li>\n<\/ol>\n
    \n\n\n\n\n\n\n\n\n
    Column<\/td>\nData Type<\/td>\nConstraint<\/td>\nDefault Value<\/td>\n<\/tr>\n
    PersonId<\/td>\nInteger<\/td>\nPRIMARY KEY<\/td>\n <\/td>\n<\/tr>\n
    FirstName<\/td>\nText<\/td>\nNOT NULL<\/td>\n <\/td>\n<\/tr>\n
    LastName<\/td>\nText<\/td>\nNOT NULL<\/td>\n <\/td>\n<\/tr>\n
    Age<\/td>\nIneger<\/td>\nNULL<\/td>\n <\/td>\n<\/tr>\n
    CreatedAt<\/td>\nText<\/td>\nNOT NULL<\/td>\nNow<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/figure>\n

    Here is an example of why you need to know the Data Definition Language<\/em><\/strong> of your database.\u00a0 The following information was taken from https:\/\/www.sqlite.org\/datatype3.html<\/a><\/p>\n

    \n

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