recently needed to write a simple address book with Python, but it was very sad for data storage. As you all know, it is unreliable to use lists and dictionaries in Python to store data, so it is thought that Python has a built-in database module. The

SQLite3 introduction

SQLite3 can be integrated with Python using the SQLite3 module. The SQLite3 module is written by Gerhard Haring. It provides a SQL interface that is compatible with the DB-API 2 specification described by PEP 249. You do not need to install this module individually, because the version of Python 2.5.x is the default module.
in order to use the SQLite3 module, you first have to create a connection object that represents the database. Then you can create cursor object selectively, which will help you execute all the SQL statements. What about

? It sounds good! Let's learn to use it soon. The Python code under the

connection database

shows how to connect to an existing database. If the database does not exist, it will be created and will eventually return to a database object.

 coding:utf-8 import SQLite3 #-*- -*- conn = sqlite3.connect ('mysql_person.db') print "Opened database successfully"; 

here, you can also copy the database name as the specific name: memory:, this will create a database in RAM. Now, let's run the program above and create our database mysql_person.db in the current directory. You can change the path according to your needs. Save the above code into the sqlite.py file and execute it as shown below. If the database is created successfully, it will display the following message shown: Python

 $chmod +x sqlite.py code $./sqlite.py Open database successfully 

to create the table below will be used to create a table in the previously created database:

 coding:utf-8 import SQLite3 #-*- -*- conn = sqlite3.connect ('mysql_person.db') print Opened database successfully; conn.execute ('''CREATE TABLE MT (ID INT PRIMARY KEY NOT NULL NAME TEXT, NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (50) SALARY, REAL);' print "Table created successfully"); conn.close (

)

of the program is executed, it will create the MT table in test.db. And the display shown in the following message:

Opened database successfully Table created successfully

INSERT 操作

下面的 Python 程序显示了如何在上面创建的 MT 表中创建记录:

 #-*- coding:utf-8 -*-  import sqlite3  conn = sqlite3.connect('mysql_person.db') print "Opened database successfully";  conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)     VALUES (1, 'Paul', 32, 'California', 20000.00 )");  conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)     VALUES (2, 'Allen', 25, 'Texas', 15000.00 )");  conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)     VALUES (3, 'Teddy', 23, 'Norway', 20000.00 )");  conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)    VALUES (4,'Mark', 25,'Rich-Mond, 65000) "); conn.commit (print) Records created successfully conn.close (

)"; the program is executed, it will create a record in the MT table, and will show the following two lines:

Opened database successfully Records created pre> successfully

SELECT the operation of

following Python program shows how to create the MT table from the front access and display the record:

 coding:utf-8 import SQLite3 #-*- -*- conn = sqlite3.connect ('mysql_person.db') print "Opened database successfully"; cursor = conn.execute ("SELECT ID, name, address, salary from MT for row in cursor: print") "ID" = row[0], NAME = "print" row[1], print "ADDRESS" row[2], print "= = SALARY "Row[3]," "n print" Operation done successfully "conn.close (

); the program is executed, it will produce the following results:

 Opened database successfully ID = 1 NAME = Paul ADDRESS = California SALARY = 20000 ID = 2 NAME = Allen ADDRESS = Texas SALARY ID = NAME = 3 = 15000 Teddy ADDRESS = Norway SALARY = 20000 ID = 4 NAME = Mark ADDRESS = Rich-Mond SALARY = 65000 Operation done successfully 

UPDATE

to operate the Python code below shows how to use the UPDATE statement to update any records, and then retrieve and display the updated records from the COMPANY table:

 coding:utf-8 import SQLite3 conn #-*- -*- = sqlite3.connect ('mysql_person.db') print" Opened database successfully "; C Onn.execute ("UPDATE COMPANY set SALARY = 25000 where ID=1) conn.commit (print) Total number of rows updated:" conn.total_changes cursor = conn.execute ("SELECT ID, name, address, salary from MT for row in cursor:) print" ID = row[0] "NAME = print", "row[1], print" ADDRESS = "row[2] print" SALARY = row[3] "," n "," print Operation done successfully conn.close (

) "; the program is executed, it will produce the following results:

 Opened database successfully Total number of rows updated: 1 ID = 1 NAME = Paul ADDRESS = California SALARY = 25000 ID = 2 NAME = Allen ADDRESS = Texas SALARY = 15000 ID = 3 NAME = Teddy ADDRESS = Norway SALARY = 20000 ID = 4 NAME = Mark ADDRESS = Rich-Mond SALARY = 65000 Operation done successfully 

DELETE

to operate the Python code below shows how to delete any records using the DELETE statement, and then acquire and display the remaining records from the COMPANY table:

 coding:utf-8 import SQLite3 #-*- -*- conn = sqlite3.connect ('mysql_person.db') print "Opened database successfully"; conn.execute ("DELETE from COMPANY where ID=2;" conn.commit) (print) "Total number of rows deleted:" conn.total_changes cursor = conn.execute ("SELECT ID, name, address, salary from MT for row in cursor:) print" ID = row[0] "NAME = print", "row[1] print," ADDRESS = row[2] ", print" SALARY = row[3] "." n print "," Operation done successfully "(conn.close 

); the execution of a program And it will produce the following results:

 Opened database successfully Total number of rows deleted: all 1 ID = 1 NAME = Paul ADDRESS = California SALARY = 20000 ID = 3 NAME = Teddy ADDRESS = Norway SALARY = 20000 ID = 4 NAME = Mark ADDRESS = Rich-Mond SALARY = Operation done successfully 

more than 65000

in this paper, I hope to help everyone to learn, I hope you will support a script.

This paper fixed link:http://www.script-home.com/a-brief-introduction-to-python-sqlite3.html | Script Home | +Copy Link

Article reprint please specify:A brief introduction to Python SQLite3 | Script Home

You may also be interested in these articles!