Python SQLite simple tutorial

This article mainly introduces a simple Python tutorial for operating SQLite, including examples of connection, table creation, addition, deletion, and repair. For more information, see I. INTRODUCTION to SQLite

SQLite is a lightweight database included in the C library. It does not require independent maintenance processes and allows SQL query statements of nonstandard variant to access the database. Some applications use SQLite to save internal data. It can also be used when building an application prototype for later migration to a larger database, such as PostgreSQL or Oracle.

The sqlite3 module, written by Gerhard Häring, provides an SQL interface designed to comply with the DB-API 249 specification described by PEP 2.0.

2. 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 is saved in the example. db file:

The code is as follows:

Import sqlite3
Conn = sqlite3.connect ('example. db ')

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

III. Database Connection objects

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

The code is as follows:

C = conn. cursor ()

# Create table
C.exe cute (''' create table stocks
(Date text, trans text, symbol text, qty real, price real )''')

# Insert a row of data
C.exe cute ("insert into stocks VALUES ('2017-01-05 ', 'buy', 'rhat', 2006, 100 )")

# 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 stored data is persistent and can be used for future access.

4. add, delete, modify, and query

1. create a table

The code is as follows:

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

The preceding statement creates a table named catalog, which has a primary key id, a pid, and a name and name that cannot be repeated, and a nickname is NULL by default.

2. DROP and TRUNCATE)

The code is as follows:

C.exe cute ("drop table catalog ")

The preceding statement deletes the catalog table.

In addition, SQLite does not clear the table, which is replaced by the following method:

The code is as follows:

C.exe cute ("delete from catalog ")

3. insert data and change (uptate) data

Python variables are usually used as values in SQL statements ). We do not recommend that you directly use python string operations to construct query statements, because this is insecure and your program is vulnerable to SQL injection attacks.

You can use the parameter substitution provided by the DB-API. Place '? 'As a placeholder, and then a tuples consisting of values are provided as the second parameter of the execute () method in the cursor (cursor. (Other database modules may use other placeholders, such as '% s' or': 1 ')

The code is as follows:

# Larger example that inserts cannot records at a time
Purchases = [('2014-03-28 ', 'buy', 'IBM', 2006, 1000 ),
('2014-04-05 ', 'buy', 'msft', 2006, 1000 ),
('2014-04-06 ', 'clerk', 'IBM', 2006, 500 ),
]
C.exe cutees ('Insert INTO stocks VALUES (?,?,?,?,?) ', Purchases)

C.exe cute ("UPDATE catalog SET trans = 'signature' WHERE symbol = 'IBM '")

4. query (select) data

As mentioned above, we advocate the use of tuples for operations.

The code is as follows:

# Never do this -- insecure!
Symbol = 'rhat'
C.exe cute ("SELECT * FROM stocks WHERE symbol = '% s'" % symbol)

# Do this instead
T = ('rhat ',)
C.exe cute ('select * FROM stocks WHERE symbol =? ', T)
Print c. fetchone ()

5. delete data

The code is as follows:

T = ('rhat ')
C.exe cute ("DELETE * FROM stocks WHERE symbol =? ", T)