Python Study, SQLite3

SQLite3 is already included in your python, so you do not need install.

__author__ = 'mare'


import sqlite3

con = sqlite3.connect("test.db")
con2 = sqlite3.connect(":memory:") #create db in memory space, it is faster but all clear after program turn off

#sql excute
cur = con.cursor()
cur.execute("DROP TABLE phoneBook;")
cur.execute("CREATE TABLE PhoneBook(Name text, PhoneNum text);")
cur.execute("INSERT INTO PhoneBook VALUES('Derick', '010-1234-5678');")
cur.execute("INSERT INTO PhoneBook VALUES('Merry', '010-5678-0000');")


name = "SangJung"
phoneNumber = '010-5670-2343'
cur.execute("INSERT INTO PhoneBook VALUES(?, ?);",(name, phoneNumber))



cur.execute("INSERT INTO PhoneBook VALUES(:inputName, :inputNum);",
 {"inputNum":phoneNumber, "inputName":name})



#datalist = (('Tom', '010-543-5432'), ('DSP', '010-123-1234'))
#cur.execute("INSERT INTO PhoneBook VALUES(?, ?);", datalist)


#view reccord
cur.execute("SELECT * FROM PhoneBook;")
for row in cur:
 print(row)
#('Derick', '010-1234-5678')
#('SangJung', '010-5670-2343')
#('SangJung', '010-5670-2343')

cur.execute("SELECT * FROM PhoneBook;")
print( cur.fetchone() )
print( cur.fetchmany(2))
#('Derick', '010-1234-5678')
#[('SangJung', '010-5670-2343'), ('SangJung', '010-5670-2343')]


cur.execute("SELECT * FROM PhoneBook;")
print( cur.fetchone() )
print( cur.fetchall() )
#('Derick', '010-1234-5678')
#[('SangJung', '010-5670-2343'), ('SangJung', '010-5670-2343')]


con.commit()  #you have to run commit function to udpate db contents.
con.isolation_level = None #auto commit mode


#record ordering
cur.execute("SELECT * FROM PhoneBook ORDER By Name")
print( [r for r in cur])
cur.execute("SELECT * FROM PhoneBook ORDER By Name DESC")
print( [r for r in cur])
#[('Derick', '010-1234-5678'), ('Merry', '010-5678-0000'), ('SangJung', '010-5670-2343'), ('SangJung', '010-5670-2343')]
#[('SangJung', '010-5670-2343'), ('SangJung', '010-5670-2343'), ('Merry', '010-5678-0000'), ('Derick', '010-1234-5678')]

cur.execute("INSERT INTO PhoneBook VALUES('apple', '010-369-3639');")
cur.execute("SELECT * FROM PhoneBook ORDER By Name")
print( [r[0] for r in cur] )
#['Derick', 'Merry', 'SangJung', 'SangJung', 'apple']



#user define condition function
def OrderFunc(str1, str2):
 s1 = str1.upper()
 s2 = str2.upper()
 return (s1 > s2 ) - (s1 < s2 )

con.create_collation('myordering', OrderFunc)
cur.execute("SELECT Name FROM PhoneBook ORDER By Name COLLATE myordering")
print( [r[0] for r in cur])
#['apple', 'Derick', 'Merry', 'SangJung', 'SangJung']




#new table creation
cur.execute("DROP TABLE PhoneBook2")
cur.execute("CREATE TABLE PhoneBook2(Name text, Age ineger);")
list = (('Tom', 24), ('Derick',30), ('Peter',53), ('Jane',29))
cur.executemany("INSERT INTO PhoneBook2 VALUES (?,?);", list)
#cur.execute("INSERT INTO PhoneBook2 VALUES(?,?);", list[0])
#cur.execute("INSERT INTO PhoneBook2 VALUES(?,?);", list[1])
#cur.execute("INSERT INTO PhoneBook2 VALUES(?,?);", list[2])

cur.execute("SELECT length(Name), upper(Name), lower(Name) FROM PhoneBook")
print(" == length(), upper(), lower() ==")
print( [r for r in cur] )
#== length(), upper(), lower() ==
#[(6, 'DERICK', 'derick'), (5, 'MERRY', 'merry'), (8, 'SANGJUNG', 'sangjung'), (8, 'SANGJUNG', 'sangjung'), (5, 'APPLE', 'apple')]


#User-defined aggregate functions
class Average:
 def __init__(self):
  self.sum = 0
  self.cnt = 0

 def step(self, value):
  self.sum += value
  self.cnt += 1

 def finalize(self):
  return self.sum / self.cnt

con.create_aggregate("avg", 1, Average)
cur.execute("SELECT avg(Age) FROM PhoneBook2")
print( cur.fetchone() )
#34.0


#Type of variables in SQLite3
#Python -> SQLite3
#None -> NULL
#int -> INTEGER
#float -> REAL
#str, bytes -> TEXT
#buffer -> BLOB

#db dump
for l in con.iterdump():
 print(l)
"""
BEGIN TRANSACTION;
CREATE TABLE PhoneBook(Name text, PhoneNum text);
INSERT INTO "PhoneBook" VALUES('Derick','010-1234-5678');
INSERT INTO "PhoneBook" VALUES('Merry','010-5678-0000');
INSERT INTO "PhoneBook" VALUES('SangJung','010-5670-2343');
INSERT INTO "PhoneBook" VALUES('SangJung','010-5670-2343');
INSERT INTO "PhoneBook" VALUES('apple','010-369-3639');
CREATE TABLE PhoneBook2(Name text, Age ineger);
INSERT INTO "PhoneBook2" VALUES('Tom',24);
INSERT INTO "PhoneBook2" VALUES('Derick',30);
INSERT INTO "PhoneBook2" VALUES('Peter',53);
INSERT INTO "PhoneBook2" VALUES('Jane',29);
COMMIT;
"""