A concise tutorial on Python operations SQLite _python

Introduction of SQLite

SQLite is a lightweight database that is included in the C library. It does not require a separate maintenance process and allows access to the database using nonstandard Variant SQL query statements. Some applications use SQLite to save internal data. It can also be used when building an application prototype to facilitate later transfer to larger databases, such as PostgreSQL or Oracle.

The Sqlite3 module, written by Gerhard Häring, provides a SQL interface designed to follow the DB-API 2.0 specification described by PEP 249.

Second, create and open the database

In order to use this module, you must first create a connection (Connection) object to represent the database. In the following example, the data will be saved in the Example.db file:

Copy Code code as follows:

Import Sqlite3
conn = Sqlite3.connect (' example.db ')

If the specified database exists, the database is opened directly or the new one is opened repeatedly.
You can also provide a private name: Memory: To build a database in memory.

Third, database connection objects

Once you have a connection (Connection) object, you can create a cursor (Cursor) object and invoke his execute () method to execute the SQL statement:

Copy Code code as follows:

c = Conn.cursor ()

# Create Table
C.execute (' CREATE TABLE stocks
(date text, trans text, symbol text, qty Real, price real)

# Insert a row of data
C.execute ("INSERT into stocks VALUES (' 2006-01-05 ', ' buy ', ' rhat ', 100,35.14)")

# Save (commit) The changes
Conn.commit ()

# We can also close the connection if We are do with it.
# Just Be sure any changes have been committed or they'll be lost.
Conn.close ()

The saved data is persistent and can be available for later access.

Iv. increase of the screening

1. Building (Create) Table

Copy Code code as follows:

C.execute ("CREATE TABLE catalog (ID integer primary key,pid integer,name varchar () unique,nickname text NULL)")

The above statement creates a table called catalog, which has a primary key ID, a PID, and a name,name that cannot be duplicated, and a nickname default of NULL.

2. Delete table (drop), empty table (TRUNCATE)

Copy Code code as follows:

C.execute ("drop table Catalog")

The above statement deletes the catalog table.

In addition, there is no emptying table in the SQLite, using the following alternative:

Copy Code code as follows:

C.execute ("Delete from Catalog")

3. Inserting (insert) data, changing (uptate) data

Python variables are usually used as values (value) in SQL statements. It is not recommended to construct query statements directly using Python string operations, because this is unsafe and makes your program susceptible to SQL injection attacks.

You can use the parameter substitution provided by DB-API. Place a '? ' where you want to use the value. As a placeholder, and then provides a tuple of value (value) as the second parameter of the Execute () method in the cursor (cursor). (Other placeholders may be used by other database modules, such as '%s ' or ': 1 ')

Copy Code code as follows:

# Larger example that inserts many records in a time
purchases = [(' 2006-03-28 ', ' buy ', ' IBM ', 1000, 45.00),
(' 2006-04-05 ', ' buy ', ' MSFT ', 1000, 72.00),
(' 2006-04-06 ', ' SELL ', ' IBM ', 500, 53.00),
]
C.executemany (' INSERT into stocks VALUES (?,?,?,?,?) ', purchases)

C.execute ("UPDATE catalog SET trans= ' SELL ' WHERE symbol = ' IBM '")

4. Query (SELECT) data

As mentioned earlier, it is advocated to operate with tuples.

Copy Code code as follows:

# Never do this--insecure!
Symbol = ' Rhat '
C.execute ("SELECT * from stocks WHERE symbol = '%s '"%)

# do this instead
t = (' Rhat ',)
C.execute (' SELECT * from stocks WHERE symbol=? ', T)
Print C.fetchone ()

5. Deleting data (delete)

Copy Code code as follows:

t= (' Rhat ')
C.execute ("DELETE * from stocks WHERE symbol=?", T)