|
|
|
(examples in this section are from Python LibRef)
conn = sqlite3.connect('/tmp/example')
c = conn.cursor()
# Create table
c.execute('''create table 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','RHAT',100,35.14)""")
# Save (commit) the changes
conn.commit()
# We can also close the cursor if we are done with it
c.close()
Always use database parameter substitution:
# Never do this -- insecure!
symbol = 'IBM'
c.execute("... where symbol = '%s'" % symbol)
# Do this instead
t = (symbol,)
c.execute('select * from stocks where symbol=?', t)
Fetch rows using cursor as an iterator:
c = conn.cursor()
c.execute('select * from stocks order by price')
for row in c:
print row
(u'2006-01-05', u'BUY', u'RHAT', 100, 35.140000000000001)
# ...
Cursor.executescript(sql_script)
con = sqlite3.connect(":memory:")
cur = con.cursor()
cur.executescript("""
create table person(
firstname,
lastname,
age
);
create table book(
title,
author,
published
);
insert into book(title, author, published)
values (
'Dirk Gently''s Holistic Detective Agency',
'Douglas Adams',
1987
);
""")
Cursor.fetchone(), Cursor.fetchmany([size=cursor.arraysize]), Cursor.fetchall()
c = conn.cursor()
c.execute('select * from stocks order by price')
print c.fetchone()
# (u'2006-01-05', u'BUY', u'RHAT', 100, 35.140000000000001)
print c.fetchmany(10)
# [ten rows will be printed]
Cursor.lastrowid - row id of last modified row
c = conn.cursor()
c.execute("""insert into stocks
values ('2006-01-05','BUY','RHAT',100,35.14)""")
c.lastrowid # [id of row we just inserted]
Cursor.executemany(command, parameter)
persons = [
("Hugo", "Boss"),
("Calvin", "Klein")
]
con = sqlite3.connect(":memory:")
# Create the table
con.execute("create table person(firstname, lastname)")
# Fill the table
con.executemany("insert into person(firstname, lastname) values (?, ?)",
persons)
Access row items by case-insensitive name instead of index:
con = sqlite3.connect("mydb")
con.row_factory = sqlite3.Row
cur = con.cursor()
cur.execute("select name_last, age from people")
for row in cur:
assert row[0] == row["name_last"]
assert row["name_last"] == row["nAmE_lAsT"]
assert row[1] == row["age"]
assert row[1] == row["AgE"]