Python Tutorial

Wednesday, November 20, 2013

Friday, November 15, 2013

Python sqlite example: executemany, search

Previous example

Python sqlite insert many data in a single query.

import sqlite3

connection = sqlite3.connect("sqlite_sample.db")
cursor=connection.cursor()

# create new table
cursor.execute("CREATE TABLE IF NOT EXISTS student (id INT, name TEXT, score INT)")

data =[
    (1, "Joey", 25),
    (2, "Mac", 65),
    (3, "Fin", 85),
    (4, "Jac", 45),
]

cursor.executemany("INSERT INTO student VALUES (?,?,?)",data)
cursor.execute("INSERT INTO student VALUES (?,?,?)", (5, "Roy", 77) )

# commit the changes
connection.commit()

# Fetching data
cursor.execute("SELECT * FROM student")
for row in cursor:
    print "id: ",row[0]," name ",row[1]," score",row[2]

print "Search example: "
data = ("Mac", 20)
cursor.execute("SELECT  * FROM student WHERE name = ? AND score > ?", data)
for row in cursor:
    print "id: ",row[0]," name: ",row[1]," score: ",row[2]

connection.close()

Output:
id:  1  name  Joey  score 25
id:  2  name  Mac  score 65
id:  3  name  Fin  score 85
id:  4  name  Jac  score 45
id:  5  name  Roy  score 77
Search example:
id:  2  name:  Mac  score:  65