Use Python to operate on the SQLite database and pythonsqlite Database

Source: Internet
Author: User

Use Python to operate on the SQLite database and pythonsqlite Database

SQLite is an embedded database, and its database is a file. Since SQLite is written in C and small, it is often integrated into various applications, and can be integrated in IOS and Android apps.

Python has built-in SQLite3. Therefore, you can directly use SQLite in Python without installing anything.

Before using SQLite, we need to clarify several concepts:

A table is a collection of relational data stored in a database. A database usually contains multiple tables, such as a student table, a class table, and a school table. A table is associated with a table by a foreign key.

To operate a relational database, you must first connect to the database. A database Connection is called Connection.

After connecting to the database, you need to open the Cursor, called Cursor, and run the SQL statement through Cursor to obtain the execution result.

1. Connect to the database

Import sqlite3 # Database Name db_name = "test. db" # table name table_name = "catalog" conn = sqlite3.connect (db_name)

Ii. Open the cursor

rs = conn.cursor()

3. Create a table

SQL = 'create table' + table_name + '(id varchar (20) primary key, pid integer, name varchar (10) 'try: rs.exe cute (SQL) print ("table created successfully") failed T: print ("table created failed ")

4. add, delete, modify, and query operations

# Add: add three records: SQL = "Insert into" + table_name + "values ('001', 1, 'zhang san')" try: rs.exe cute (SQL) # commit transaction conn. commit () print ("inserted successfully") failed T: print ("inserted failed") SQL = "Insert into" + table_name + "values ('002 ', 2, 'Li si') "try: rs.exe cute (SQL) # submit the transaction conn. commit () print ("inserted successfully") failed T: print ("inserted failed") SQL = "Insert into" + table_name + "values ('003 ', 3, 'wang wu') "try: rs.exe cute (SQL) # submit the transaction conn. commit () print ("inserted successfully") failed T: print ("inserted failed") # delete: delete SQL = "Delete from" + table_name + "where pid = 3" try: rs.exe cute (SQL) conn. commit () print ("deleted successfully") failed T: print ("failed to delete") # modify: change the pid of the record whose pid is equal to 2 to 1sql = "Update" + table_name + "set pid = 1 where pid = 2" try: rs.exe cute (SQL) conn. commit () print ("modified successfully") failed T: print ("failed to modify ") # query all table names in the database SQL = "Select name From sqlite_master where type = 'table'" res = rs.exe cute (SQL) print (res. fetchall () # query all records in the table SQL = "Select * from" + table_nametry: res = rs.exe cute (SQL) print (res. fetchall () before T: print ([])

5. Close the cursor

rs.close()

6. Close database connection

conn.close()

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.