Translation: The Sqlite3 of the standard library manual

First, you want to connect and manipulate the SQLite database, you need to create a database connection object, after which the operation is based on this object.

Import sqlite3conn = Sqlite3.connect (' example.db ') #如果想在内存中构建sqlite对象, you can also use a special name: Memory: #conn = Sqllite3.connect (': Memory: ')

Once the connection is complete, you can create a pointer object cursor, execute the SQL statement using the Excute method

#创建指针: cu = Conn.cursor () #新建表 cu.execute ("CREATE TABLE stocks (date text, trans text, symbol text, qty Real, Price real) # Insert row inserts a row of Datacu.execute ("INSERT INTO stocks VALUES (' 2006-01-05 ', ' BUY ', ' rhat ', 100,35.14)") # Save (Commit (change save (commit) the Changesconn.commit () # We can also close the connection if We are doing with it.# Just is sure any C Hanges have been committed or they would be lost. #关闭连接conn. Close ()

Python variables are typically used by SQL operations in Python. But it's not safe to use string variables directly, which can make you vulnerable to SQL injection attacks. (Here's a comic http://xkcd.com/327/about injecting attacks)

Instead, you can use the DB-API parameter. By using the "?" placeholder, followed by a variable in the form of a tuple. To illustrate:

#绝对不建议的操作! Symbol = ' rhat ' Cu.execute ("SELECT * from stocks WHERE symbol = '%s '"% symbol) #正确的操作方式应该是t = (' Rhat ',) #这样定义变量cu. Execute ("s Elect * FROM stocks WHERE symbol =? "% t" #注意到区别没有? #批量操作数据的方法: #1定义一个包含多个元组的列表purchases = [(' 2006-03-28 ', ' buy ', ' IBM ', 45.00), (' 2006-04-05 ', ' buy ', ' MSFT ') , 72.00), (' 2006-04-06 ', ' SELL ', ' IBM ', $, 53.00),] #每个字段一个问号cu. Executemany (' INSERT into St Ocks VALUES (?,?,?,?,?) ', purchases

After executing the query statement, the query results can be obtained by cu.fetchone () or by using Cu.fetchall () to get the list or pass in the variable

For row in C.execute (' SELECT * from stocks ORDER by Price '): Print (Row) (' 2006-01-05 ', ' BUY ', ' rhat ', 100, 35.14) (' 2006- 03-28 ', ' buy ', ' IBM ', ' 45.0 ' (' 2006-04-06 ', ' SELL ', ' IBM ', ' $ ', ' 53.0 ') (' 2006-04-05 ', ' buy ', ' MSFT ', 1000, 72.0)

Translation: The Sqlite3 of the standard library manual