Python Operations SQLite Database Simple Tutorial

SQLite Introduction

SQLite, is a lightweight database, is to comply with the ACID relational database management system, its design goal is embedded, and has been used in many embedded products, it occupies very low resources, in embedded devices, may only need hundreds of K of memory is enough.

It can support Windows/linux/unix and so on the mainstream operating system, at the same time can be combined with a lot of program language, such as TCL, C #, PHP, Java, and so on, as well as ODBC interface.

Compared to MySQL, PostgreSQL, the two open source world's most famous database management system, it processing faster than they.

The first alpha version of SQLite was born in May 2000. So far there have been 14 years, SQLite also ushered in a version of SQLite 3 has been released.

Unlike the common client-server paradigm, the SQLite engine is not a stand-alone process that the program communicates with, but is connected to the program as a major part of it. So the main communication protocol is the direct API call within the programming language.

This has a positive effect on total consumption, delay time and overall simplicity.

The entire database (definition, table, index, and data itself) is stored in a single file on the host host. Its simple design is accomplished by locking the entire data file at the start of a transaction.

Use Tips

1, download a sqlite from www.sqlite.org, it is an embedded database, no server concept, Windows version is an EXE, put it into a suitable directory, and then put this directory into the system's path variable.

To establish a database:
XP Version: Sqlite3.exe test.db
Linux version:./sqlite3.bin test.db

2, and then to find a pysqlite, this is the Python Access SQLite interface, address here: Http://initd.org/tracker/pysqlite
Currently for different Python versions, Pysqlite has two versions: 2.3 and 2.4, please choose from your Python version.

3, then you can open your favorite editor, write a test code.

4, the Chinese processing should note that the SQLite default to UTF-8 encoded storage.

5, also note that SQLite only support file locks, in other words, it is not good for concurrent processing, not recommended in the network environment, suitable for stand-alone environment;

manipulating SQLite databases with Python

Import Pysqlite2.dbapi2 as SQLite
Def runtest ():
CX = Sqlite.connect (test.db)
CU = Cx.cursor ()
#create
Cu.execute (CREATE TABLE catalog (
ID Integer PRIMARY KEY,
PID Integer,
Name varchar Unique
))

#insert
Cu.execute (INSERT into catalog values (0,0, "www.ttlsa.com")
Cu.execute (INSERT into catalog values (1,0, "Hello"))
Cx.commit ()

#select
Cu.execute (SELECT * from catalog)
Print 1:,
Print Cu.rowcount
rs = Cu.fetchmany (1)
Print 2:,
Print RS
rs = Cu.fetchall ()
Print 3:,
Print RS

#delete
Cu.execute (delete from catalog where id = 1)
Cx.commit ()

Cu.execute (SELECT * from catalog)
rs = Cu.fetchall ()
Print 4:,
Print RS

#select Count
Cu.execute ("SELECT count (*) from Catalog")
rs = Cu.fetchone ()
Print 5:,
Print RS
Cu.execute ("SELECT * from Catalog")
Cu.execute (drop table catalog)

if __name__ = = __main__:
Runtest ()