This article is participating in Python Theme Month. See the link for details

Hello everyone, today I found another interesting gadget. I’m an old poet of fun gadgets.

I believe that many of the computer students have taken the database course. Never mind, as the old poem tells you, databases are important for back-end development. So you need to know more or less.

Know the SQLite

SQLite is an embedded database whose database is a file. Because SQLite itself is written in C and is small, it is often integrated into a variety of applications, even iOS and Android apps.

SQLite3 is built into Python, so you don’t need to install anything to use SQLite in Python.

Write concrete code

1. Import the packages import OS and sqlite3 first. One is the system package and the other is the database package.

2 Delete the test.db file if it exists

db_file = os.path.join(os.path.dirname(__file__), 'test.db')
if os.path.isfile(db_file):
    os.remove(db_file)
Copy the code

3 Connecting a Database

conn = sqlite3.connect(db_file)
Copy the code

4 create a table

cursor = conn.cursor()
cursor.execute('create table user(id varchar(20) primary key, name varchar(20), score int)')
Copy the code

5 Insert contents into the table

cursor.execute(r"insert into user values ('A-001', 'Adam', 95)")
cursor.execute(r"insert into user values ('A-002', 'Bart', 62)")
cursor.execute(r"insert into user values ('A-003', 'Lisa', 78)")
Copy the code

6 Submit and close the connection

cursor.close()
conn.commit()
conn.close()
Copy the code

When using Python’s DB-API, you can use it safely as long as you know the Connection and Cursor objects and remember to close them when you open them.

Rowcount returns the number of rows affected by the result of an INSERT, update, or DELETE statement.

Fetchall () fetchall() fetchall() fetchall() fetchall() fetchall() The result set is a list, where each element is a tuple, corresponding to a row of records.

Complete source code

# -*- coding: utf-8 -*- import os, sqlite3 db_file = os.path.join(os.path.dirname(__file__), 'test.db') if os.path.isfile(db_file): Os.remove (db_file) conn = sqlite3.connect(db_file) cursor = conn.cursor() cursor.execute('create table user(id varchar(20) primary key, name varchar(20), score int)') cursor.execute(r"insert into user values ('A-001', 'Adam', 95)") cursor.execute(r"insert into user values ('A-002', 'Bart', 62)") cursor.execute(r"insert into user values ('A-003', 'Lisa', 78)") cursor.close() conn.commit() conn.close() def get_score_in(low, high): 'Returns the name of the specified score range, sorted by score from lowest to highest' ---- pass ---- # test: assert get_score_in(80, 95) == ['Adam'], get_score_in(80, 95) assert get_score_in(60, 80) == ['Bart', 'Lisa'], get_score_in(60, 80) assert get_score_in(60, 100) == ['Bart', 'Lisa', 'Adam'], get_score_in(60, 100) print('Pass')Copy the code

The database is just a file. We don’t care how the data is stored in it. This is a very lightweight database that you can use to write homework, or to write systems in Python that are not very large.

For a more systematic look at Python, go to code poetry. Now that we’re in. Just give me a “like” and leave.