+- Sick Gaming (https://www.sickgaming.net)
+-- Forum: Programming (https://www.sickgaming.net/forum-76.html)
+--- Forum: Python (https://www.sickgaming.net/forum-83.html)
+--- Thread: [Tut] How to Create a Database Table with pyodbc in Python (/thread-96528.html)
[Tut] How to Create a Database Table with pyodbc in Python - xSicKxBot - 08-03-2020
How to Create a Database Table with pyodbc in Python
<div><p>In this article, you will get the necessary information on how to <strong>create a table in SQLite</strong> using the package pyodbc in Python. Example code is included and a link to a Github repo will be provided in the references section.</p>
<h2>Prerequisites</h2>
<p>To use <code><a href="https://pypi.org/project/pyodbc/" target="_blank" rel="noreferrer noopener" title="https://pypi.org/project/pyodbc/">pyodbc</a></code> in this article some prerequisites need to be met.</p>
<ol>
<li><strong>ODBC Driver</strong> – 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<a href="http://www.ch-werner.de/sqliteodbc/"> http://www.ch-werner.de/sqliteodbc/</a>,</li>
<li><strong>SQLite3 </strong>– this article assumes that you have SQLite3 in your system and have created a database called <code>testDB.db</code>.</li>
<li><strong>Database DDL</strong> – 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:<a href="https://sqlite.org/lang_createtable.html"> https://sqlite.org/lang_createtable.html</a></li>
<li><strong>Design your table</strong> – This is the table that will be implemented in the example below:</li>
</ol>
<figure class="wp-block-table is-style-stripes">
<table>
<tbody>
<tr>
<td>Column</td>
<td>Data Type</td>
<td>Constraint</td>
<td>Default Value</td>
</tr>
<tr>
<td>PersonId</td>
<td>Integer</td>
<td>PRIMARY KEY</td>
<td> </td>
</tr>
<tr>
<td>FirstName</td>
<td>Text</td>
<td>NOT NULL</td>
<td> </td>
</tr>
<tr>
<td>LastName</td>
<td>Text</td>
<td>NOT NULL</td>
<td> </td>
</tr>
<tr>
<td>Age</td>
<td>Ineger</td>
<td>NULL</td>
<td> </td>
</tr>
<tr>
<td>CreatedAt</td>
<td>Text</td>
<td>NOT NULL</td>
<td>Now</td>
</tr>
</tbody>
</table>
</figure>
<p>Here is an example of why you need to know the <strong><em>Data Definition Language</em></strong> of your database. The following information was taken from<a href="https://www.sqlite.org/datatype3.html"> https://www.sqlite.org/datatype3.html</a></p>
<hr class="wp-block-separator"/>
<p>“SQLite does not have a storage class set aside for storing dates and/or times. Instead, the built-in<a href="https://www.sqlite.org/lang_datefunc.html"> Date And Time Functions</a> of SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values:</p>
<ul>
<li><strong>TEXT</strong> as ISO8601 strings (<code>"YYYY-MM-DD HH:MM:SS.SSS"</code>).</li>
<li><strong>REAL</strong> as Julian day numbers, the number of days since noon in Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian calendar.</li>
<li><strong>INTEGER</strong> as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC.</li>
</ul>
<p>Applications can choose to store dates and times in any of these formats and freely convert between formats using the built-in<a href="https://www.sqlite.org/lang_datefunc.html"> date and time functions</a>.”</p>
<hr class="wp-block-separator"/>
<p>SQLite Create Table Data Definition Language for the PeopleInfo table:</p>
<pre class="EnlighterJSRAW" data-enlighter-language="generic" data-enlighter-theme="" data-enlighter-highlight="" data-enlighter-linenumbers="" data-enlighter-lineoffset="" data-enlighter-title="" data-enlighter-group="">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 );
</pre>
<p>Let’s dive into the steps to create a table in SQLite using pyodbc in Python.</p>
<h2>Step 1: Install the pyodbc Package</h2>
<p>Install the<a href="https://github.com/mkleehammer/pyodbc/wiki"> Pyodbc</a> package using the following command:</p>
<pre class="EnlighterJSRAW" data-enlighter-language="generic" data-enlighter-theme="" data-enlighter-highlight="" data-enlighter-linenumbers="" data-enlighter-lineoffset="" data-enlighter-title="" data-enlighter-group="">pip install pyodbc</pre>
<p>For Anaconda use the following command:</p>
<pre class="EnlighterJSRAW" data-enlighter-language="generic" data-enlighter-theme="" data-enlighter-highlight="" data-enlighter-linenumbers="" data-enlighter-lineoffset="" data-enlighter-title="" data-enlighter-group="">conda install -c anaconda pyodbc</pre>
<h2>Step 2: Connect Your Python Script to SQLite</h2>
<p>Next, you will need to connect your script to SQLite.</p>
<p>You may use this template to perform the connection:</p>
<p>Remember to import the <code>pyodbc</code> package first. Connect to the database using the connect method with the ODBC connection string for SQLite.</p>
<pre class="EnlighterJSRAW" data-enlighter-language="generic" data-enlighter-theme="" data-enlighter-highlight="" data-enlighter-linenumbers="" data-enlighter-lineoffset="" data-enlighter-title="" data-enlighter-group="">import pyodbc
conn = pyodbc.connect('Driver={SQLite3 ODBC Driver};' 'Server=server_name;' 'Database=database_name;' 'Trusted_Connection=yes;')
</pre>
<h2>Step 3: Create Cursor from Your Connection</h2>
<p>The cursor object created by <code>cursor()</code> allows you to execute queries.</p>
<pre class="EnlighterJSRAW" data-enlighter-language="generic" data-enlighter-theme="" data-enlighter-highlight="" data-enlighter-linenumbers="" data-enlighter-lineoffset="" data-enlighter-title="" data-enlighter-group="">cursor = conn.cursor()</pre>
<h2>Step 4: Create the Table in SQLite</h2>
<p>Now you will be able to create your table in SQLite</p>
<p>For our example, here is the code that I used to create the table in SQL Server using Python: </p>
<pre class="EnlighterJSRAW" data-enlighter-language="generic" data-enlighter-theme="" data-enlighter-highlight="" data-enlighter-linenumbers="" data-enlighter-lineoffset="" data-enlighter-title="" data-enlighter-group="">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 ); ''')
</pre>
<h2>Step 5: Commit the Transaction</h2>
<pre class="EnlighterJSRAW" data-enlighter-language="generic" data-enlighter-theme="" data-enlighter-highlight="" data-enlighter-linenumbers="" data-enlighter-lineoffset="" data-enlighter-title="" data-enlighter-group="">conn.commit()</pre>
<p>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.</p>
<p> Other options to consider are:</p>
<p> 1 – Add the autocommit parameter to connect. This will not require a manual commit. For example:</p>
<pre class="EnlighterJSRAW" data-enlighter-language="generic" data-enlighter-theme="" data-enlighter-highlight="" data-enlighter-linenumbers="" data-enlighter-lineoffset="" data-enlighter-title="" data-enlighter-group="">con = pyodbc.connect(your_connection_string, autocommit = True)</pre>
<p>2 – Use a <code><a href="https://blog.finxter.com/python-one-line-with-statement/" title="Python One Line With Statement">with</a></code> block and anything will be committed before the connection is terminated at the end of the <code>with</code> block. For example:</p>
<pre class="EnlighterJSRAW" data-enlighter-language="generic" data-enlighter-theme="" data-enlighter-highlight="" data-enlighter-linenumbers="" data-enlighter-lineoffset="" data-enlighter-title="" data-enlighter-group="">with pyodbc.connect(your_connection_string) as con: CREATE_TABLE_CODE</pre>
</p>
<h2>Step 6: Insert Records to Verify Your Table is Configured Correctly.</h2>
<pre class="EnlighterJSRAW" data-enlighter-language="generic" data-enlighter-theme="" data-enlighter-highlight="" data-enlighter-linenumbers="" data-enlighter-lineoffset="" data-enlighter-title="" data-enlighter-group="">cursor.execute(''' INSERT INTO PeopleInfo (PersonId, FirstName, LastName, Age) VALUES (1,'Bob','Smith', 55), (2, 'Jenny','Smith', 66) ''')
conn.commit()
</pre>
<h2>Step 7: Run a SELECT Query to Retrieve the Records.</h2>
<pre class="EnlighterJSRAW" data-enlighter-language="generic" data-enlighter-theme="" data-enlighter-highlight="" data-enlighter-linenumbers="" data-enlighter-lineoffset="" data-enlighter-title="" data-enlighter-group="">cursor.execute('SELECT * FROM PeopleInfo')
for row in cursor: print(row)
</pre>
<h2>Step 8: Close Your Connection If It Isn’t Needed.</h2>
<pre class="EnlighterJSRAW" data-enlighter-language="generic" data-enlighter-theme="" data-enlighter-highlight="" data-enlighter-linenumbers="" data-enlighter-lineoffset="" data-enlighter-title="" data-enlighter-group="">conn.close()</pre>
<p>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.</p>
<p><strong>Complete code:</strong></p>
<pre class="EnlighterJSRAW" data-enlighter-language="generic" data-enlighter-theme="" data-enlighter-highlight="" data-enlighter-linenumbers="" data-enlighter-lineoffset="" data-enlighter-title="" data-enlighter-group="">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()
</pre>
<p><strong>Output:</strong></p>
<pre class="EnlighterJSRAW" data-enlighter-language="generic" data-enlighter-theme="" data-enlighter-highlight="" data-enlighter-linenumbers="" data-enlighter-lineoffset="" data-enlighter-title="" data-enlighter-group="">(1, 'Bob', 'Smith', 55, '2020-08-01 20:37:04')
(2, 'Jenny', 'Smith', 66, '2020-08-01 20:37:04')</pre>
<h2>References</h2>
<p>1 – Pyodbc Github repo: <a href="https://github.com/mkleehammer/pyodbc" target="_blank" rel="noreferrer noopener">https://github.com/mkleehammer/pyodbc</a></p>
<p>2 – Pyodbc Getting Started: <a href="https://code.google.com/archive/p/pyodbc/wikis/GettingStarted.wiki" target="_blank" rel="noreferrer noopener">https://code.google.com/archive/p/pyodbc/wikis/GettingStarted.wiki</a></p>
<p>3 – Create tables with SQLite: <a href="https://www.sqlitetutorial.net/sqlite-create-table/" target="_blank" rel="noreferrer noopener">https://www.sqlitetutorial.net/sqlite-create-table/</a></p>
</div>
https://www.sickgaming.net/blog/2020/08/02/how-to-create-a-database-table-with-pyodbc-in-python/