One sqlite corresponds to the type of Python
Two examples
Import Sqlite3
Def sqlite_basic ():
# Connect to DB
conn = Sqlite3.connect (' test.db ')
# Create cursor
c = Conn.cursor ()
# Create Table
C.execute ("'
CREATE TABLE if not EXISTS stocks
(date text, trans text, symbol text,
Qty Real, Price real)
‘‘‘
)
# Insert a row of data
C.execute ("'
INSERT INTO stocks
VALUES (' 2006-01-05 ', ' BUY ', ' reht ', 100,35.14)
‘‘‘
)
# Query the table
rows = C.execute ("SELECT * from Stocks")
# Print the table
For row in rows:
Print (ROW)
# Delete the row
C.execute ("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.execute ("'
CREATE table if not EXISTS employee
(ID text, name text, age inteage)
‘‘‘)
# insert Many rows
For t in [(' 1 ', ' Itech ', 10),
(' 2 ', ' Jason ', 10),
(' 3 ', ' Jack ', 30),
]:
C.execute (' INSERT into employee values (?,?,?) ', T)
# CREATE INDEX
Create_index = ' CREATE index IF not EXISTS idx_id on employee (ID); '
C.execute (Create_index)
# More secure
t = (' Jason ',)
C.execute (' 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.executescript ("'
CREATE TABLE book (
Title
Author
Published
);
Insert into book (title, author, published)
VALUES (
' AAA book ',
' Douglas Adams ',
1987
);
‘‘‘)
rows = Cur.execute ("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.execute ("CREATE TABLE Test (d date, ts timestamp)")
Today = Datetime.date.today ()
now = Datetime.datetime.now ()
Cur.execute ("INSERT into Test (d, TS) VALUES (?,?)", (Today, now))
Cur.execute ("Select D, ts from test")
row = Cur.fetchone ()
Print today, "= =", row[0], type (row[0])
Print now, "= =", row[1], type (row[1])
Cur.execute (' 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 ()
Finish!
