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;
"""


 



Comments

Popular posts from this blog

OpenCV Stitching example (Stitcher class, Panorama)

(OpenCV Study) Background subtractor MOG, MOG2, GMG example source code (BackgroundSubtractorMOG, BackgroundSubtractorMOG2, BackgroundSubtractorGMG)

Example source code of extract HOG feature from images, save descriptor values to xml file, using opencv (using HOGDescriptor )

Real-time N camera stitching Class.

8 point algorithm (Matlab source code) / The method to get the Fundamental Matrix and the Essential matrix

Optical Flow sample source code using OpenCV

Video Stabilization example source code, (using cvFindHomography, cvWarpPerspective functions in openCV)

(OpenCV Study) calcOpticalFlowFarneback example source code ( dense optical flow )

yuv422(YUYV) to RGB and RGB to yuv422(YUYV), (Using OpenCV and TBB)

(C, C++) TinyXML , xml read & write