A concise tutorial on Python operation SQLite

I. Introduction of SQLite

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

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

Ii. creating and opening a 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 the Code code as follows:

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

If the specified database exists, the database will be opened directly or the new one will be opened repeatedly.
You can also provide a dedicated name: Memory: To set up the database in RAM.

Third, database Connection object

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

Copy the 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 doing with it.
# Just is sure any changes has been committed or they would be lost.
Conn.close ()

The saved data is persistent and can be used later in the visit.

Iv. Increased deletion and investigation

1. Build (Create) Table
Copy the Code code as follows:

C.execute ("CREATE TABLE catalog (ID integer primary key,pid integer,name varchar (ten) 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 is null.

2. Delete table (drop), empty table (TRUNCATE)
Copy the Code code as follows:

C.execute ("drop table Catalog")

The above statement deletes the catalog table.

In addition, SQLite does not clear the table operation, using the following way instead:
Copy the Code code as follows:

C.execute ("Delete from Catalog")

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

Python variables are typically used as values (value) in SQL statements. It is not recommended to use Python string operations directly to construct query statements, because this is unsafe and makes 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. As a placeholder, then provide a tuple of values (value) as the second parameter of the Execute () method in the cursor (cursor). (Other database modules may use other placeholders, such as '%s ' or ': 1 ')
Copy the Code code as follows:

# 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 stated earlier, the use of tuples is advocated.
Copy the Code code as follows:

# 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. Deleting (delete) data
Copy the Code code as follows:

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

