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

Sqlite in python

Using Sqlite in python is very simple. Sqlite installed default with python 2.5+
I use SQLite Manager for sqlite
Lets goto the example:

import sqlite3 as sqlite
#Connect to the file 'pythonSqliteExample.sqlite'
connection = sqlite.connect("pythonSqliteExample.sqlite")
#Get the cursor
cur=connection.cursor()
cur.execute("select * from info")

for row in cur:
    print "name: ",row[0]," address ",row[1]," score",row[2]

cur.execute("insert into info values ('TJ','BD',50)")
#This is for save the chages
connection.commit()
print "After Commit"
cur.execute("select * from info")

for row in cur:
    print "name: ",row[0]," address ",row[1]," score",row[2]

connection.close()
 

Output

 
name:  tom  address  bangladesh  score 10
name:  jerry  address  bangladesh  score 5.5

After Insert
name:  tom  address  bangladesh  score 10
name:  jerry  address  bangladesh  score 5.5
name:  TJ  address  BD  score 50
 
 

I upload full code with database here You can use it.