Python class library 26 [SQLite]

A. SQLite corresponds to the Python type

 

 

Example 2

Import Sqlite3

Def Sqlite_basic ():
# Connect to DB
Conn = sqlite3.connect ( ' Test. DB ' )
# Create cursor
C = conn. cursor ()
# Create Table
C.exe cute ( '''
Create Table if not exists stocks
(Date text, Trans text, symbol text,
Qty real, price real)
'''
)
# Insert a row of data
C.exe cute ( '''
Insert into stocks
Values ('2017-01-05 ', 'buy', 'reht', 2006, 100)
'''
)
# Query the table
Rows = c.exe cute (" Select * from stocks " )
# Print the table
For Row In Rows:
Print (ROW)
# Delete the row
C.exe cute ( " Delete from stocks where symbol = 'reht' " )
# Save (COMMIT) The changes
Conn. Commit ()
# Close the connection
Conn. Close ()

Def Sqlite_adv ():
Conn = sqlite3.connect ( ' Test2.db ' )
C = conn. cursor ()
C.exe cute ( '''
Create Table if not exists employee
(ID text, Name text, age inteage)
''' )
# Insert into rows
For T In [( ' 1 ' , ' Itech ' , 10 ),
( ' 2 ' , ' Jason ' , 10 ),
( ' 3 ' , ' Jack ' , 30 ),
]:
C.exe cute ( ' Insert into employee values (?,?,?) ' , T)
# Create Index
Create_index = ' Create index if not exists idx_id on employee (ID ); '
C.exe cute (create_index)
# More secure
T = ( ' Jason ' ,)
C.exe cute ( ' Select * from employee where name =? ' , T)
# Fetch Query Result
For Row In C. fetchall ():
Print (ROW)
Conn. Commit ()
Conn. Close ()

Def Sqlite_adv2 ():
# Memory DB
Con = sqlite3.connect ( " : Memory: " )
Cur = con. cursor ()
# Execute SQL
Cur.exe cutescript ('''
Create Table Book (
Title,
Author,
Published
);
Insert into book (title, author, published)
Values (
'Aaa Book ',
'Douglas Adams ',
1987
);
''' )
Rows = cur.exe cute ( " Select * from book " )
For Row In Rows:
Print ( " Title: " + Row [0])
Print ( " Author: " + Row [1])
Print ( " Published: " + STR (row [2])

Def Sqlite_adv3 ():
Import Datetime

# converting SQLite values to custom Python types
# default adapters and Converters for datetime and timestamp
con = sqlite3.connect (" : Memory: " , detect_types = sqlite3.parse _ decltypes | sqlite3.parse _ colnames)
cur = con. cursor ()
cur.exe cute ( " Create Table Test (d date, TS timestamp) " )

today = datetime. Date. Today ()
now = datetime. datetime. Now ()

cur.exe cute ( " insert into test (D, TS) values (?, ?) ", (today, now ))
cur.exe cute ( " select D, TS from test ")
ROW = cur. fetchone ()
Print today, " => " , row [0], type (row [0])
Print now, " => " , row [1], type (row [1])

cur.exe cute ( ' select current_date as" d [date] ", current_timestamp as" ts [timestamp] "from test ')
ROW = cur. fetchone ()
Print " current_date " , row [0], type (row [0])
Print " current_timestamp " , row [1], type (row [1])

#Sqlite_basic ()
#Sqlite_adv ()
#Sqlite_adv2 ()
# Sqlite_adv3 ()

 

 

Complete!