Python Data Persistence - Creating Table

Python Data Persistence – Creating Table

We shall now add a table in our newly created ‘mydb.sqlite’ database. In the following script, the first two steps are as illustrated above – setting up connection and cursor objects. Next, we call execute () method of cursor object, giving it a string with CREATE TABLE statement inside. We shall use the same ‘Products’ table that we created in the previous chapter. Save the following script as ‘createqry.py’ and execute it.

Example

import sqlite3
conn=sqlite3.connect('mydb.sqlite')
cur=conn.cursor()
qry=' ' '
CREATE TABLE Products (
ProductID INTEGER PRIMARY KEY AUTOINCREMENT,
Name     TEXT (20) ,
Price    INTEGER
) ;
' ' '
try:
        cur.execute(qry)
        print ('Table created successfully')
except:
        print ('error in creating table')
conn.close ( )

Products table will be created in our database. We can verify by listing out tables in this database in the SQLite console, as we did in the previous chapter.

sqlite> .open mydb.sqlite 
sqlite> .tables 
Products

Let us also create ‘Customers’ and ‘Invoices’ tables with the same structure as used in the previous chapter. Here, we use a convenience method to execute the script ( ) that is defined in the cursor class. With its help, it is possible to execute multiple execute statements at once.

Example

import sqlite3
conn=sqlite3.connect('mydb.sqlite')
cur=conn.cursor( )
qry= ' ' '
CREATE TABLE Customers (
          CustID INTEGER PRIMARY KEY AUTOINCREMENT,
          Name           TEXT (20),
          GSTIN           TEXT (15)
   ) ;
CREATE TABLE Invoices (
         InvID             INTEGER             PRIMARY KEY
AUTOINCREMENT,
       CustID               TEXT                REFERENCES Customers
(CustID),
      ProductID        INTEGER            REFERENCES Products
(ProductID),
     Quantity         INTEGER (5)
) ;
' ' '
try:
      cur.executescript(qry)
      print ('Table created successfully')
except:
     print ('error in creating table')
conn.close( )

You can go back to the SQLite console and refresh the table list to confirm that all three tables are created in mydb.sqlite database.

Python Data Persistence – Creating Table

Related