Python Study Notes 21: Database Operations (sqlite3), pythonsqlite3

Python Study Notes 21: Database Operations (sqlite3), pythonsqlite3
Python comes with a lightweight relational database SQLite. This database uses the SQL language.
As a back-end database, SQLite can be used with Python to build websites or create tools with data storage needs.
SQLite is also widely used in other fields, such as HTML5 and mobile terminals. Sqlite3 In the Python Standard Library provides interfaces for this database.


A database design I will create a simple relational database to classify and store books for a bookstore.
The database contains two tables: category is used to record the category, and book is used to record the information of a book.
A book belongs to a certain category. Therefore, the book has a foreign key pointing to the primary key id of the catogory table.

【category】id int,sort int,name text【book】id int,sort int,name text,price real,category int

2. Create a database first. Create a database and tables in the database.
After connecting to the database using connect (), you can locate the pointer cursor to execute the SQL statement.

import sqlite3# data.db is a db file in the working directory.conn = sqlite3.connect("data.db")c = conn.cursor()# create tablesc.execute("'CREATE TABLE category      (id int primary key,        sort int,        name text)'")c.execute("'CREATE TABLE book      (id int primary key,        sort int,        name text,        price real,        category int,       FOREIGN KEY (category) REFERENCES category(id))'")# save the changesconn.commit()# close the connection with the databaseconn.close()

The SQLite database is a file on a disk, as shown in data. db above. Therefore, the entire database can be conveniently moved or copied.
Data. db does not exist at the beginning, so SQLite will automatically create a new file.
Using the execute () command, I executed two SQL commands to create two tables in the database. After creation, save and disconnect the database.


3. The database and table are created on the data inserted, and the abstract structure of the database is established. Insert data in the same database as follows:

Import sqlite3conn = sqlite3.connect ("data. db ") c = conn. cursor () books = [(1, 1, 'cook REE E', 3.12, 1), (2, 3, 'python intro', 17.5, 2), (3, 2, 'OS intro', 13.6, 2),] # execute "INSERT" c.exe cute ("INSERT INTO category VALUES (1, 1, 'kitchen ')") # using the placeholderc.exe cute ("insert into category VALUES (?, ?, ?) ", (2, 2, 'computer ') # The second parameter is a tuples, not a list # execute multiple commandsc.exe cute.pdf ('insert INTO book VALUES (?, ?, ?, ?, ?) ', Books) conn. commit () conn. close ()

You can also use execute () to insert data to execute the complete SQL statement.
For parameters in SQL statements, use "? "As an alternative symbol, and the specific value is given in the following parameters.
The format string of Python, such as "% s", cannot be used here because it is vulnerable to SQL injection attacks.
I can also use the executeplugin () method to execute multiple inserts and add multiple records.
Each record is an element in the table.


4. After a query statement is executed, Python returns a iterator containing multiple records obtained from the query.
You can also use the fetchone () and fetchall () methods provided by sqlite3 to read records cyclically:

import sqlite3conn = sqlite3.connect('test.db')c = conn.cursor()# retrieve one recordc.execute('SELECT name FROM category ORDER BY sort')print(c.fetchone())print(c.fetchone())# retrieve all records as a listc.execute('SELECT * FROM book WHERE book.category=1')print(c.fetchall())# iterate through the recordsfor row in c.execute('SELECT name, price FROM book ORDER BY sort'):    print(row)

5. Update and delete records

You can update or delete a record:

import sqlite3conn = sqlite3.connect("test.db")c = conn.cursor()c.execute('UPDATE book SET price=? WHERE id=?',(1000, 1))c.execute('DELETE FROM book WHERE id=2')conn.commit()conn.close()

You can also directly Delete the entire table:

c.execute('DROP TABLE book')

If data. db is deleted, the entire database is deleted.


How does sqlite3 select the database to operate?

Sqlite3 can only operate on database files. It does not have the concept of a database like mysql. A file is a database, and a database is a file.
So after you open the database file, the next step is to operate the "table", no "Database"

How to operate the database sqlite3 in C/C ++ in Linux

1. here we assume that you have compiled the sqlite library file libsqlite3.a libsqlite3.la libsqlite3.so libsqlite3.so. 0 libsqlite3.so. 0.8.6 pkgconfig and the executable file: sqlite3. Let's assume that the installation directory of your sqlite3 is in the/usr/local/sqlite3 directory. If not, we can copy your installation file to the/usr/local/sqlite3 directory. In this way, we can unify the following operations to reduce the probability of errors, for example: [root @ localhost home] # cp-rf sqlite-3.3.8-ix86 // usr/local/sqlite3 here suppose/home/sqlite-3.3.8-ix86/is your installation directory, that is to say, after your sqlite was installed here, the Directory of the sqlite3 library file is:/usr/local/sqlite3/lib Executable File sqlite3 is: the Directory of the/usr/local/sqlite3/bin header file sqlite3.h is/usr/local/sqlite3/include. Now we start our sqlite3 programming journey in Linux. 2. Now we have a test. Now let's write a C/C ++ program to call the sqlite API function. The following example shows how to use the C/C ++ interface of sqlite. the database name is obtained by the first parameter and the second or more parameters are SQL Execution statements. this function calls sqlite3_open () to open the database in line 16, and sqlite3_close () to close the database connection in line 25. [Root @ localhost temp] # vi opendbsqlite. c. Press the I key to switch to the input mode and enter the following code: // name: opendbsqlite. c // This prog is used to test C/C ++ API for sqlite3.It is very simple, ha! // Author: zieckey All rights reserved. // data: 2006/11/13 # include <stdio. h ># include <sqlite3.h> int main (void) {sqlite3 * db = NULL; char * zErrMsg = 0; int rc; // open the specified database file, if not, a database file rc = sqlite3_open ("zieckey. db ", & db); if (rc) {fprintf (stderr," Can't open database: % s ", sqlite3_errmsg (db); sqlite3_close (db ); exit (1);} else printf ("You have opened a sqlite3 database ...... remaining full text>