A basic walkthrough of SQLite3 in Python

This article mainly introduces the SQLite3 basic operation method in the Python2.7 programming, involves the Python2.7 operation Sqlite3 Database's deletion and modification and the anti-injection and so on related skill, needs the friend can refer to the next

This paper describes the basic operation method of SQLite3 in Python2.7. Share to everyone for your reference, as follows:

1. Basic operation

#-*-Coding:utf-8-*-#!/usr/bin/env pythonimport sqlite3def mykey (x): Return X[3]conn=sqlite3.connect ("D:\\demo\\my_ Db.db ") sql =" CREATE table IF not EXISTS MYTB (a char, b int, c real, D DATE) "# a char, b int, c Real means that the table has three fields, # A is A string type, B is an integer type, and C is a real type. Conn.execute (SQL) cs = conn.cursor () #cs. Execute ("delete from MYTB WHERE a= ' Zhang San '") cs.execute ("Delete from MYTB") #删除所有记录 ' "' Cs.execute (" INSERT into MYTB (a,b,c,d) VALUES (' Zhang San ', ' +, ' 2014-03-04 ') ") Cs.execute (" INSERT INTO MYTB ( A,B,C,D) VALUES (' Wang Wu ', ' 2014-05-01 ') cs.execute ("INSERT into MYTB (a,b,c,d) VALUES (' Li Si ', 23, 130, ' 2 014-04-06 ') ' #批量注入, Batchdata is a list, each element in the table is a tuple batchdata=[(' Zhang San ', ' +, ' 2014-03-04 '), (' Wang Wu ', 24, 1 (' 2014-05-01 '), (' Li Si ', max., ' 2014-04-06 ')]cs.executemany (' INSERT into MYTB values (?,?,?,?) ', BATCHDATA) conn . Commit () #将加入的记录保存到磁盘, very important! Cs.execute ("Select name, sql from Sqlite_master WHERE type= ' table '") RECs = Cs.fetchall () print (RECs) Cs.execute ("SELECT * from MYTB") #打开数据表recs = Cs.fetchall () #取出所有记录print ("There is", Len (RECs), "notes.") Print Recsrecs.sort (key = MyKey) print recscs.close () conn.close ()

2, delete a record , using SQL string variable can be implemented with the deletion of parameters

Sql= "DELETE from my_table WHERE number= '" + my_num + "'" Cs.execute (SQL)

3. Query one or more records

If SQLite3 finds a database record that contains Chinese, the data is decode processed when it is fetched to python. At that time I went online to check when said to use GBK decoding, but my own decoding failed, replaced by Utf-8 decoding successfully displayed. In addition, if you query only one can use the Fetchone statement, or Fetchall and then find it through Python.

Sql= "SELECT name from my_table WHERE number = '" + my_num + "'" Cs.execute (SQL) the_name= (Cs.fetchall ()) [0][0].decode (' Utf-8 ')

4. Avoid repeated injections

Sometimes we have duplicate records, in order to avoid inserting the same records into the database multiple times, you can use the following statement:

Sql= "INSERT OR REPLACE into" +my_table+ "values (?,?,?,?,?,?,?) "#假设my_table有7项

5. Insert Chinese records to SQLite3

Python is Unicode encoded, but the database uses GBK encoding for Chinese, such as the Stock_name variable contains Chinese, then Unicode (name, "GBK") is required for processing

batch=[(Stock_num, Unicode (Stock_name, "GBK"))]cs.executemany ("INSERT OR REPLACE into my_table values (?,?)", batch)