Pysqlite Confidential Tutorial
The main purpose of this article is to be an entry-level tutorial, teach you some of the basic statements of Sqite to operate Sqite, more detailed, and write the corresponding test programs. I hope this article helps you.
I used to blog sqlite a lightweight database
Pysqlite's home page address: http://pysqlite.sourceforge.net/ About using pysqlite documents
First, install
Go to the Pysqlite home page to download the installation package, there is a version of Windows, which is now supported by Python 2.2 and 2.3.
Second, create a database / open database
SQLite uses the file as the database, you can specify the location of the database file.
>>> Import SQLite >>> CX = SQLite.Connect ("D: / Test.db", Encoding = 'CP936')
Use SQLite's Connect to create a database file, I specify the path. When the database file does not exist, it will be created automatically. If this file already exists, open this file. Encoding Indicates the encoding used by saving the data, where the CP936 is the encoding from Python, in fact, GBK encoding. CX is a database connection object.
Third, the basic object of the operation database
3.1 Database Connection Object
The previous CX is a connection object of a database, which can have the following:
Commit () - Transaction Submit Rollback () - Transaction Rollback Close () - Close a database connection Cursor () - Create a cursor
3.2 Cursor object
The execution of all SQL statements must be performed under the cursor object.
Cu = cx.cursor ()
This defines a cursor. The cursor object has the following operations:
Execute () - Execute SQL Statement ExecuteMany - Execute Multi-SQL Statement Close () - Close Cursor Fetchone () - Take a Record FetchMany () from the Results - From the Results Multiple Record Fetchall () - - Remove multiple records of scroll () - cursor rolling from the results
About the object's method can go to the Python home page to view the DB API detailed documentation. However, Pysqlite supports the DB API to what program, I don't know. The operations I have listed are supported, but I don't have it.
Fourth, use example
4.1 Construction Library
The front is already there, no longer repeat. (These examples, if you are interested, you can try it directly in the interactive environment of Python)
4.2 Construction Table
>>> Cu = cx.cursor () >>> CU.EXECUTE ("" "Creger Primary Key, PID Integer, Name Varchar (10) Unique)" "" "
The above statement created a table called Catalog, which has a primary key ID, a PID, and a name, Name is not repeated.
About SQLite supported data type, which is described in the document on its homepage, you can refer to: Version 2 DataTypes
4.3 INSERT (insert)
>>> cu.execute ("INSERT INTO CATALOG VALUES") >>> CU.EXECUTE ("Insert Into Catalog Values") >>> CX .commit () If you like, you can always use the Cu cursor object. Note that a transaction statement must be used for data modifications: commit () or rollback (), and the object is a database connection object, here is CX.
4.4 SELECT (Select)
>>> cu.execute ("Select * from catalog") >>> cu.Fetchall () [(0, 0, 'Name2'), (1, 0, 'Hello')]
Fetchall () returns all the data in the result set, the result is a list of Tuples. Each tuple element is arranged in the field of fields. Note that the cursor is stateful, it can record the first few records that have been taken to the results, so you can only traverse the result set once. In the above case, if fetchone () will return empty. This requires attention when testing.
>>> cu.execute ("Select * from catalog where id = 1") >>> cu.Fetchone () (1, 0, 'Hello')
The statement that does not modify the database, does not need to perform a transaction statement after execution.
4.5 UPDATE (Modify)
>>> cu.execute ("Update Catalog Set Name = 'Name2' Where Id = 0") >>> cx.commit () >>> CU.Execute ("Select * from catalog" >>> cu.Fetchone () (0, 0, 'Name2')
4.6 Delete (delete)
>>> cu.execute ("delete from catalog where id = 1" >>> cx.commit () >>> cu.execute ("select * from catalog") >>> cu.Fetchall () [(0 , 0, 'Name2')]]]
The above is a simple example of how to operate SQLite using pysqlite.
Five, post
The above can be run in the interactive environment, interested can try it. Now SQLite has been upgraded to 3.0.2 (beta). When I wrote a blog, I downloaded or 2.8.13, and it turned very big. And its homepage has also been revised.
SQLITE Home: http://www.sqlite.org/
