Python uses database (SQLite)

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

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

Before using SQLite, we need to understand several concepts:

A table is a collection of relational data in a database. A database usually contains multiple tables, such as student tables, class tables, school tables, and so on. Tables are associated with each other through foreign keys.

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

After connecting to the database, you need to open the Cursor, which is called Cursor. Execute the SQL statement through Cursor, and then get the execution result.

Python defines a set of API interface to operate the database. To connect any database to python, you only need to provide a database driver that conforms to Python standard.

Because SQLite driver is built into Python standard library, we can operate SQLite database directly.

# Import module
import sqlite3

# ##### Before using the database
# Database file path
db_path = r'D:\test.db' # Physics(disk)storage(raw Character string)
# Create database connection
conn = sqlite3.connect(db_path)

def dict_factory(cursor, row):
    '''Modify query format'''
    d = {}
    for idx, col in enumerate(cursor.description):
        d[col[0]] = row[idx]
    return d
# conn.row_factory = dict_factory

# Create cursors
cur = conn.cursor()

# ##### Database operations
sql = '''
create table if not exists tb_test(
    id integer primary key autoincrement,
    name varchar(20) not null,
    remark text
)
'''
# implement SQL Sentence
cur.execute(sql)
sql = '''
    drop table tb_test
'''
# cur.execute(sql)
# ### insert
sql = '''
INSERT INTO tb_test(name, remark)
VALUES('zhangsan', 'Study Python')
'''
# cur.execute(sql)
sql = '''
INSERT INTO tb_test(name, remark)
VALUES(?, ?)
'''
# ?, Parameter formatting, avoiding SQL injection(security)
# Do not use str.format Or string splicing SQL Sentence(Unsafe)
stuents = [
    ('Liz', 'Unified writing, Xiaozhuan'),
    ('Tang San', 'Continent of'),
    ('Wang Xiao er', 'Mr. marvel of China')
]
# Insert multiple data
# cur.executemany(sql, stuents)
# commit, Submit changes
conn.commit()

# ### select
sql = '''
SELECT
    COUNT(1) AS total,
    'test' AS test
FROM tb_test
'''
# Query single data
cur.execute(sql)
result_one = cur.fetchone()
print(result_one)

sql = '''
SELECT *
FROM tb_test
WHERE name = ?
OR id < 3
'''
params = ('Tang San',)
# Query single data
cur.execute(sql, params)
result_one = cur.fetchone()
print(result_one)
# Query multiple data
cur.execute(sql, params)
page_count = 2 # Number of data in a query
result_many = cur.fetchmany(page_count)
print(result_many)
# Query all data
cur.execute(sql, params)
result_all = cur.fetchall()
print(result_all)
"""
"""
# ### update
# ### delete


# ##### After using the database
# Close cursor
cur.close()
# Close database connection
conn.close()

For the first time, please take care

Tags: Python Database SQL SQLite

Posted on Fri, 31 Jan 2020 04:31:09 -0500 by littlejay