The sqlite3 of Python

Python sqlite3 Database is a very small built-in module, it uses a file to store the entire database, operation is very convenient, compared to other large databases, there are some gaps. But in performance is not inferior, little, perfectly formed, Sqlite3 realized how many sql-92 standards, such as transaction, trigger and complex query.

Describe

Python's database module has a unified interface standard, so database operations have a unified schema (assuming that the database module is named db):

1. Create a database connection with Db.connect, assuming the connection object is conn

2. If the database operation does not need to return results, use the Conn.execute query directly, depending on the isolation level of the database things, it is possible to modify the database Conn.commit

3. If you need to return query results using Conn.cursor to create a cursor object cur, through Cur.execute query database, the cursor method has Fetchall, Fetchone, Fetchmany return query results, according to the database things isolation level is different , you may need to modify the database Coon.commit

4. Close Cur.close

SQLITE3 Basic Operational Use cases

#Coding=utf-8ImportSqlite3conn= Sqlite3.connect ("sqlite.db")#Create a sqlite.db databasePrint("Open Database Success") Conn.execute ("drop table IF EXISTS student") Query="""CREATE TABLE IF not EXISTS student (customer VARCHAR, produce varchar (+), amount FLOAT, date date) ;"""conn.execute (query)Print("Table created successfully")#inserting data into a table" "Method 1" "#data = "INSERT into student (customer,produce,amount,date) \#VALUES ("Zhangsan", "notepad", 999, "2017-01-02") "#conn.execute (data)#data = "INSERT into student (customer,produce,amount,date) \#VALUES ("Lishi", "binder", 3.45, "2017-04-05") "#conn.execute (data)#Conn.commit ()" "Method 2" "Statement="INSERT into student VALUES (?,?,?,?)"Data= [("Zhangsan","Notepad", 999,"2017-01-02"),("Lishi","Binder", 3.45,"2017-04-05")]conn.executemany (statement, data) Conn.commit () Curson= Conn.execute ("SELECT * FROM Student") Conn.commit ()Print(Curson) rows=Curson.fetchall ()Print(rows) conn.close ()

Sqlite3 Csv->db->csv

" "import CSV data into a database" "ImportSYSImportCSVImportSqlite3#Parse CSV filedefParsecsvfile (filepath): Header=None Data=[] with open (filepath,'R') as Csvfile:filereader=Csv.reader (csvfile) header=Next (FileReader)#print (header)         forRowinchfilereader:data.append (Row)#print (data)    returnHeader,data#write a database using Sqlite3defInitdb (header, data): Conn= Sqlite3.connect ("sqlite.db")    Print("Connect Database Success") Conn.execute ("drop table IF EXISTS student") conn.commit () query=" "CREATE TABLE IF not EXISTS student (Supplier Name varchar (+), Invoice number varchar (+), part N Umber varchar (+), cost VARCHAR (+), Purchase date date);" "conn.execute (query) conn.commit () statement="INSERT into student VALUES (?,?,?,?,?)"Conn.executemany (statement, data) Conn.commit () Curson= Conn.execute ("SELECT * FROM Student") Conn.commit ()Print(Curson) rows=Curson.fetchall ()Print(rows) conn.close ()returnrows#Write a CSV file based on database contentdefwirtecsvfile (Writefilepath, header, data): With open (Writefilepath,'A +') as Writefile:writer= Csv.writer (WriteFile, delimiter=",") Writer.writerow (header) forRowinchdata:writer.writerow (Row)if __name__=="__main__": Readfilepath= Sys.argv[1] Writefilepath= Sys.argv[2] Header,data=parsecsvfile (readfilepath) rows=Initdb (header, data) wirtecsvfile (Writefilepath, header, rows)

The sqlite3 of Python