Sick Gaming
[Tut] How to Create a Database Table with pyodbc in Python - Printable Version

+- 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>&nbsp;</td>
</tr>
<tr>
<td>FirstName</td>
<td>Text</td>
<td>NOT NULL</td>
<td>&nbsp;</td>
</tr>
<tr>
<td>LastName</td>
<td>Text</td>
<td>NOT NULL</td>
<td>&nbsp;</td>
</tr>
<tr>
<td>Age</td>
<td>Ineger</td>
<td>NULL</td>
<td>&nbsp;</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.&nbsp; The statement above explicitly commits the transaction.&nbsp; If you do not commit the transaction, the database will not create the table.&nbsp; The transaction will be rolled back.</p>
<p>&nbsp;Other options to consider are:</p>
<p>&nbsp;1 – Add the autocommit parameter to connect.&nbsp; This will not require a manual commit.&nbsp; 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.&nbsp; 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/