Shulou(Shulou.com)06/03 Report--

This article introduces the knowledge of "the usage of SQLite, a lightweight relational database that comes with Python". Many people will encounter this dilemma in the operation of actual cases, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Catalogue

1. Create a database

2. Insert data

3. Query

4. Update and delete

Python comes with a lightweight relational database, SQLite. This database uses SQL language. As a back-end database, SQLite can be used to build websites with Python, or to create tools with data storage needs. SQLite also has a wide range of applications in other fields, such as HTML5 and mobile. Sqlite3 in the Python standard library provides an interface to the database.

I will create a simple relational database to store the classification and price of books for a bookstore. The database contains two tables: category for recording categories and book for recording information about a book. A book belongs to a category, so book has a foreign key that points to id, the primary key of the catogory table.

1. Create a database

I'll start by creating the database and the tables in the database. After connecting to the database using connect (), I can execute the SQL command by locating the pointer cursor:

# By Vameiimport sqlite3# test.db is a file in the working directory.conn = sqlite3.connect ("test.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 ()

SQLite's database is a file on disk, such as the test.db above, so the entire database can be easily moved or copied. Test.db does not exist at first, 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 the creation is complete, save and disconnect the database.

2. Insert data

The database and tables are created above, and the abstract structure of the database is established. The following data will be inserted in the same database:

# By Vameiimport sqlite3conn = sqlite3.connect ("test.db") c = conn.cursor () books = [(1,1, 'Cook Recipe', 3.12,1), (2,3,' Python Intro', 17.5,2), (3,2,'OS Intro', 13.6,2),] # execute "INSERT" c.execute ("INSERT INTO category VALUES (1,1)" ) # using the placeholderc.execute ("INSERT INTO category VALUES (), [(2,2, 'computer')]) # execute multiple commandsc.executemany (' INSERT INTO book VALUES (,)', books) conn.commit () conn.close ()

You can also use execute () to execute the complete SQL statement when inserting data. Parameters in the SQL statement, using "?" As an alternative symbol, and the specific value is given in the following parameters. You cannot use Python's format string, such as "% s", because this usage is vulnerable to SQL injection attacks.

I can also use the executemany () method to perform multiple inserts, adding multiple records. Each record is an element in the table, such as the element in the books table above.

3. Query

After the query statement is executed, Python returns a circulator containing multiple records obtained by the query. You can read through the loop, or you can use the fetchone () and fetchall () methods provided by sqlite3 to read the record:

# By Vameiimport 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) 4, update and delete

You can update a record or delete a record:

# By Vameiconn = 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 delete the entire table directly:

C.execute ('DROP TABLE book')

If you delete test.db, the entire database will be deleted.

Summary:

Sqlite3 is just an interface to SQLite. If you want to be proficient in using SQLite databases, you need to learn more about relational databases.

This is the end of the introduction to "the usage of SQLite, a lightweight relational database that comes with Python". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.