Working at the database level with SQLITE3 in Python 3.7

Using :Memory: as a way to tryout interaction with the SQL command in SQLITE3

Two Tables

• TESTSTATION
• TS_LOCATION

Key SQL used

• Create TABLE
• INSERT INTO
• SELECT  and with INNER JOIN(so data from two tables can be seen)
• DELETE FROM
• UPDATE

# -*- coding: utf-8 -*-
"""
Created on Mon Nov 25 16:53:17 2019

@author: aleja
"""

#Useful reference
#https://www.tutorialspoint.com/sqlite/sqlite_python.htm
#sqlite3 is already included (did not have to pip )

#https://stackoverflow.com/questions/26862809/operational-error-database-is-locked

import sqlite3

#conn=sqlite3.connect('teststation3.db')
#for testing in memory, note result of output may be different when working with a file
conn=sqlite3.connect(':memory:')

#if db file will now be in the same folder as this script

#create a table this should only be done one
try:
    conn.execute('''CREATE TABLE TESTSTATION
             (ID INT PRIMARY KEY NOT NULL,
              TS_NAME TEXT NOT NULL,
              TS_TYPE NOT NULL,
              TS_LOC_ID NOT NULL,
              TS_PN NOT NULL,
              TS_SN NOT NULL,
              TS_STATUS NOT NULL);''')
except sqlite3.OperationalError:
    print('\n***SORRY Table in this db already exist cannot create again\n but you can use it')

try:
    conn.execute('''CREATE TABLE TS_LOCATION
             (TS_LOC_ID INT PRIMARY KEY NOT NULL,
              TS_LOC_COUNTRY NOT NULL,
              TS_LOC_STATE NOT NULL,
              TS_LOC_CITY NOT NULL,
              TS_LOC_ADDR1 NOT NULL,
              TS_LOC_ADDR2 NOT NULL,
              TS_COMPANY NOT NULL);''')
except sqlite3.OperationalError:
    print('\n***SORRY Table in this db already exist cannot create again\n but you can use it')

#Locations

#check for IntegrityError: UNIQUE constraint failed: TESTSTATION.ID
try:
    conn.execute("INSERT INTO TS_LOCATION (TS_LOC_ID,TS_LOC_COUNTRY,TS_LOC_STATE,TS_LOC_CITY,TS_LOC_ADDR1,TS_LOC_ADDR2,TS_COMPANY) VALUES (1, 'Country1', 'State1', 'City1', 'Addr11', 'Addrs12', 'Company1')");
    conn.execute("INSERT INTO TS_LOCATION (TS_LOC_ID,TS_LOC_COUNTRY,TS_LOC_STATE,TS_LOC_CITY,TS_LOC_ADDR1,TS_LOC_ADDR2,TS_COMPANY) VALUES (2, 'Country1', 'State2', 'City2', 'Addr21', 'Addrs22', 'Company1')");
except sqlite3.IntegrityError:
    print('\n*** SORRY You cannot use the same primary key that is already in a database, but you can get info.')

cursor=conn.execute("SELECT TS_LOC_ID,TS_COMPANY,TS_LOC_STATE,TS_LOC_CITY from TS_LOCATION")
print("TS_LOC_ID,TS_COMPANY,TS_LOC_STATE,TS_LOC_CITY")
for row in cursor:
    print(row)

#check for IntegrityError: UNIQUE constraint failed: TESTSTATION.ID
try:
    conn.execute("INSERT INTO TESTSTATION (ID,TS_NAME,TS_TYPE,TS_LOC_ID,TS_PN,TS_SN,TS_STATUS) VALUES (1, 'OpticalTS', 'RnD', 1, '5R347', 'A01001','Active')");
    conn.execute("INSERT INTO TESTSTATION (ID,TS_NAME,TS_TYPE,TS_LOC_ID,TS_PN,TS_SN,TS_STATUS) VALUES (2, 'OpticalTS', 'Production', 2, '5R348', 'A01002','Active')");
    conn.execute("INSERT INTO TESTSTATION (ID,TS_NAME,TS_TYPE,TS_LOC_ID,TS_PN,TS_SN,TS_STATUS) VALUES (3, 'OpticalTS', 'Production', 2, '5R348', 'A01003','Active')");
except sqlite3.IntegrityError:
    print('\n*** SORRY You cannot use the same primary key that is already in a database, but you can get info.')

cursor=conn.execute("SELECT ID,TS_NAME,TS_PN,TS_SN from TESTSTATION")
print("ID,TS_NAME,TS_PN,TS_SN")
for row in cursor:
    print(row)

sqlfunc="DELETE from "
sqltbl="TESTSTATION where "
sqlcols="TS_PN='5R348' and TS_SN='A01002';"
sqldel=sqlfunc+sqltbl+sqlcols
print ("\n",sqldel)

cursor=conn.execute(sqldel)
Num_deleted=cursor.rowcount
print("Number of Deleted Row",Num_deleted)

print("\nLook at TESTSTATION Table")
cursor=conn.execute("SELECT ID,TS_NAME,TS_PN,TS_SN from TESTSTATION")
print("ID,TS_NAME,TS_PN,TS_SN")
for row in cursor:
    print(row)

print("\nLook at both TESTSTATION and TS_LOCATION Table")
cursor=conn.execute("SELECT ID,TS_NAME,TS_PN,TS_SN,TS_STATUS,TS_COMPANY,TS_LOC_STATE from TESTSTATION INNER JOIN TS_LOCATION ON TS_LOCATION.TS_LOC_ID=TESTSTATION.TS_LOC_ID")
print("ID,TS_NAME,TS_PN,TS_SN,TS_STATUS,TS_COMPAY,TS_LOC_STATE")
for row in cursor:
    print(row)

sqlfunc="UPDATE "
sqltbl="TESTSTATION "
sqlsetcols="SET TS_STATUS='OFFLINE' "
sqlwhere="WHERE TS_PN='5R348' and TS_SN='A01003';"
sqlupdate=sqlfunc+sqltbl+sqlsetcols+sqlwhere
print ("\n",sqlupdate)

cursor=conn.execute(sqlupdate)
Num_updates=cursor.rowcount
print("Number of Updated Row",Num_deleted)

print("\nLook at TESTSTATION Table")
cursor=conn.execute("SELECT ID,TS_NAME,TS_PN,TS_SN,TS_STATUS from TESTSTATION")
print("ID,TS_NAME,TS_PN,TS_SN,TS_STATUS")
for row in cursor:
    print(row)   

conn.commit()
conn.close()

Output from above (remember this is in :memory: mode database (output will be different with dbname.db because of rows being in table after 2nd run of code.

In memory mode it is like start new all the time but its great for checking syntax before loading database file.

TS_LOC_ID,TS_COMPANY,TS_LOC_STATE,TS_LOC_CITY
(1, 'Company1', 'State1', 'City1')
(2, 'Company1', 'State2', 'City2')
ID,TS_NAME,TS_PN,TS_SN
(1, 'OpticalTS', '5R347', 'A01001')
(2, 'OpticalTS', '5R348', 'A01002')
(3, 'OpticalTS', '5R348', 'A01003')

 DELETE from TESTSTATION where TS_PN='5R348' and TS_SN='A01002';
Number of Deleted Row 1

Look at TESTSTATION Table
ID,TS_NAME,TS_PN,TS_SN
(1, 'OpticalTS', '5R347', 'A01001')
(3, 'OpticalTS', '5R348', 'A01003')

Look at both TESTSTATION and TS_LOCATION Table
ID,TS_NAME,TS_PN,TS_SN,TS_STATUS,TS_COMPAY,TS_LOC_STATE
(1, 'OpticalTS', '5R347', 'A01001', 'Active', 'Company1', 'State1')
(3, 'OpticalTS', '5R348', 'A01003', 'Active', 'Company1', 'State2')

 UPDATE TESTSTATION SET TS_STATUS='OFFLINE' WHERE TS_PN='5R348' and TS_SN='A01003';
Number of Updated Row 1

Look at TESTSTATION Table
ID,TS_NAME,TS_PN,TS_SN,TS_STATUS
(1, 'OpticalTS', '5R347', 'A01001', 'Active')
(3, 'OpticalTS', '5R348', 'A01003', 'OFFLINE')