Backend Development
Python Tutorial
Detailed explanation of Python database sqlite3 application method
Detailed explanation of Python database sqlite3 application method
Python comes with a lightweight relational database SQLite. This database uses SQL language. As a back-end database, SQLite can be used with Python to build websites or create tools that require data storage. SQLite is also widely used in other fields, such as HTML5 and mobile terminals. sqlite3 in the Python standard library provides an interface to this database.
I will create a simple relational database to store book categories and prices for a bookstore. The database contains two tables: category is used to record classification, and book is used to record information about a certain book. A book belongs to a certain category, so book has a foreign key (foreign key) that points to the primary key id of the catogory table. 
Create database
I first create the database and the tables in the database. After using connect() to connect to the database, I can execute the SQL command by positioning the pointer cursor:
# By Vamei
import sqlite3
# test.db is a file in the working directory.
conn = sqlite3.connect("test.db")
c = conn.cursor()
# create tables
c.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 changes
conn.commit()
# close the connection with the database
conn.close()
The SQLite database is a file on the disk , such as test.db above, so the entire database can be easily moved or copied. test.db does not exist to begin with, 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 from the database.
Insert data
The database and tables are created above, and the abstract structure of the database is established. The following will insert data into the same database:
# By Vamei
import sqlite3
conn = 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, 'kitchen')")
# using the placeholder
c.execute("INSERT INTO category VALUES (?, ?, ?)", [(2, 2, 'computer')])
# execute multiple commands
c.executemany('INSERT INTO book VALUES (?, ?, ?, ?, ?)', books)
conn.commit()
conn.close()
Inserting data can also use execute() to execute a complete SQL statement. For parameters in SQL statements, use "?" as a replacement symbol, and give specific values in subsequent parameters. You cannot use Python's formatted string , such as "%s", because this usage is vulnerable to SQL injection attacks.
I can also use the executemany() method to perform multiple insertions and add multiple records. Each record is an element in the table, such as the elements in the books table above.
Query
After executing the query statement, Python will return a looper containing multiple records obtained by the query. You can read in a loop or use the fetchone() and fetchall() methods provided by sqlite3 to read records:
# By Vamei import sqlite3 conn = sqlite3.connect('test.db') c = conn.cursor() # retrieve one record c.execute('SELECT name FROM category ORDER BY sort') print(c.fetchone()) print(c.fetchone()) # retrieve all records as a list c.execute('SELECT * FROM book WHERE book.category=1') print(c.fetchall()) # iterate through the records for row in c.execute('SELECT name, price FROM book ORDER BY sort'): print(row)
Update and Delete
You can update a certain record, or delete a record:
# By Vamei
conn = 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 test.db is deleted, the entire database will be deleted.
Summary
sqlite3 is just an interface to SQLite. If you want to use SQLite database proficiently, you need to learn more knowledge about relational databases.
The above is the detailed content of Detailed explanation of Python database sqlite3 application method. For more information, please follow other related articles on the PHP Chinese website!
Hot AI Tools
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Undress AI Tool
Undress images for free
Clothoff.io
AI clothes remover
Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!
Hot Article
Hot Tools
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
Hot Topics
1668
14
1428
52
1329
25
1273
29
1256
24
Python: Games, GUIs, and More
Apr 13, 2025 am 12:14 AM
Python excels in gaming and GUI development. 1) Game development uses Pygame, providing drawing, audio and other functions, which are suitable for creating 2D games. 2) GUI development can choose Tkinter or PyQt. Tkinter is simple and easy to use, PyQt has rich functions and is suitable for professional development.
Python vs. C : Learning Curves and Ease of Use
Apr 19, 2025 am 12:20 AM
Python is easier to learn and use, while C is more powerful but complex. 1. Python syntax is concise and suitable for beginners. Dynamic typing and automatic memory management make it easy to use, but may cause runtime errors. 2.C provides low-level control and advanced features, suitable for high-performance applications, but has a high learning threshold and requires manual memory and type safety management.
Python and Time: Making the Most of Your Study Time
Apr 14, 2025 am 12:02 AM
To maximize the efficiency of learning Python in a limited time, you can use Python's datetime, time, and schedule modules. 1. The datetime module is used to record and plan learning time. 2. The time module helps to set study and rest time. 3. The schedule module automatically arranges weekly learning tasks.
Python vs. C : Exploring Performance and Efficiency
Apr 18, 2025 am 12:20 AM
Python is better than C in development efficiency, but C is higher in execution performance. 1. Python's concise syntax and rich libraries improve development efficiency. 2.C's compilation-type characteristics and hardware control improve execution performance. When making a choice, you need to weigh the development speed and execution efficiency based on project needs.
Which is part of the Python standard library: lists or arrays?
Apr 27, 2025 am 12:03 AM
Pythonlistsarepartofthestandardlibrary,whilearraysarenot.Listsarebuilt-in,versatile,andusedforstoringcollections,whereasarraysareprovidedbythearraymoduleandlesscommonlyusedduetolimitedfunctionality.
Learning Python: Is 2 Hours of Daily Study Sufficient?
Apr 18, 2025 am 12:22 AM
Is it enough to learn Python for two hours a day? It depends on your goals and learning methods. 1) Develop a clear learning plan, 2) Select appropriate learning resources and methods, 3) Practice and review and consolidate hands-on practice and review and consolidate, and you can gradually master the basic knowledge and advanced functions of Python during this period.
Python: Automation, Scripting, and Task Management
Apr 16, 2025 am 12:14 AM
Python excels in automation, scripting, and task management. 1) Automation: File backup is realized through standard libraries such as os and shutil. 2) Script writing: Use the psutil library to monitor system resources. 3) Task management: Use the schedule library to schedule tasks. Python's ease of use and rich library support makes it the preferred tool in these areas.
Python vs. C : Understanding the Key Differences
Apr 21, 2025 am 12:18 AM
Python and C each have their own advantages, and the choice should be based on project requirements. 1) Python is suitable for rapid development and data processing due to its concise syntax and dynamic typing. 2)C is suitable for high performance and system programming due to its static typing and manual memory management.
