A concise tutorial on Python operation SQLite

I. introduction to SQLite

SQLite is a lightweight database included in the C library. It does not require a separate maintenance process and allows access to the database using SQL queries from nonstandard variant. Some applications use SQLite to store internal data. It can also be used while building application prototypes for later migration to larger databases such as PostgreSQL or Oracle.

Sqlite3 module by Gerhard Hä Ring is written to provide an SQL interface that is designed to follow the db-api 2.0 specification described by PEP 249.

Create and open a database

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

import sqlite3

conn = sqlite3.connect('example.db')

If the specified database exists, it is opened directly, otherwise it is opened again and again.
You can also provide a special name :memory: to create a database in memory.

Three, database connection object

Once you have a Connection object, you can create a Cursor object and call its execute() method to execute the SQL statement:

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 done with it. 

 # Just be sure any changes have been committed or they will be lost. 

 conn.close()

 

The saved data is persistent and available for later access.

Four, add, delete, change, check

1. Build (create) table

c.execute("create table catalog (id integer primary key,pid integer,name varchar(10) UNIQUE,nickname text NULL)")

The above statement creates a table called catalog, which has a primary key id, a pid, and a name, whose name cannot be repeated, and a nickname that defaults to NULL.

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

c.execute("drop table catalog")

The above statement deletes the catalog table.

In addition, there is no operation to clear the table in SQLite, and the following method is used instead:

c.execute("delete from catalog")

3. Insert data, change (uptate) data

Python variables are usually used as values in SQL statements. It is not recommended to use python string operations directly to construct queries, as this is not safe and leaves your program vulnerable to SQL injection attacks.

You can use the parameter substitution provided by db-api. Place a '? where you want to use the value; 'ACTS as a placeholder, and then provides a tuple of values as a second argument to the execute() method in cursor. (other database modules may use other placeholders, such as '%s' or ':1')

# Larger example that inserts many records at 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, the use of tuples for operations is encouraged.

# Never do this -- insecure!

symbol = 'RHAT'

c.execute("SELECT * FROM stocks WHERE symbol = '%s'" % symbol)
# Do this instead

t = ('RHAT',)

c.execute('SELECT * FROM stocks WHERE symbol=?', t)

print c.fetchone()

5. Delete data

t=('RHAT')

c.execute("DELETE * FROM stocks WHERE symbol=?", t)