sqllite3 _ in python

import sqlite3
conn = sqlite3.connect('example.db')

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()

 

select 옵션( ?  or %s )

# Never do this -- insecure!
symbol = 'RHAT'
c.execute("SELECT * FROM stocks WHERE symbol = '%s'" % symbol)

# Do this instead
t = (symbol,)
c.execute('SELECT * FROM stocks WHERE symbol=?', t)

# cur.execute("insert into people values (?, ?)", (who, age))
print c.fetchone()

# Larger example that inserts many records at a time
purchases = [('2006-03-28', 'BUY', 'IBM', 1000, 45.00),
                   ('2006-04-05', 'BUY', 'MSFT', 1000, 72.00),
                   ('2006-04-06', 'SELL', 'IBM', 500, 53.00),
                 ]
c.executemany('INSERT INTO stocks VALUES (?,?,?,?,?)', purchases)

 

c.execute   or fetchall()  <-- return  matching rows.

for row in c.execute('SELECT * FROM stocks ORDER BY price'):
        print row

(u'2006-01-05', u'BUY', u'RHAT', 100, 35.14)
(u'2006-03-28', u'BUY', u'IBM', 1000, 45.0)
(u'2006-04-06', u'SELL', u'IBM', 500, 53.0)
(u'2006-04-05', u'BUY', u'MSFT', 1000, 72.0)

 

Connection.create_function(name, num_params, func)¶

Creates a user-defined function that you can later use from within SQL statements under the function name name. num_params is the number of parameters the function accepts, and func is a Python callable that is called as the SQL function.

The function can return any of the types supported by SQLite: unicode, str, int, long, float, buffer and None.

import sqlite3
import md5

def md5sum(t):
    return md5.md5(t).hexdigest()

con = sqlite3.connect(":memory:")
con.create_function("md5", 1, md5sum)
cur = con.cursor()
cur.execute("select md5(?)", ("foo",))
print cur.fetchone()[0]

 

출처 : http://docs.python.org/library/sqlite3.html

디비구축.zip