Chapter 17 database operation

17.1 sqlite3 database

SQLite is a C language library. It can provide a lightweight disk based database. This database does not need an independent server process, but also allows access to it using a non-standard SQL query language. Some applications can use SQLite as an internal data store. You can use it to create an application prototype and then migrate to a larger database, such as PostgreSQL or Oracle.

The built-in sqlite3 module of Python 3 is used to operate the database. You only need to import the module directly.

17.1.1 sqlite3 module

1. Built in constant

   • sqlite3.version records the version number of the module as a string.
   • sqlite3.sqlite_version records the version number of the SQLite database as a string.
   • sqlite3.isolation_level is used to configure the isolation level.
   • sqlite3.in_transaction Boolean, activity status flag.

2. connection object

   • sqlite3.connect(database, timeout, *args) can read or create database files and return connection objects.

     • ": memory:" when working as a database, you can open a database connection in RAM instead of on disk.
     • If a database is accessed by multiple connections, a user's transaction will be locked until the transaction is committed.
     • The parameter timeout indicates the duration of the connection waiting for locking until an abnormal disconnection occurs.
     • If the database name database does not exist, it will be created automatically. You can use the name with path to specify the creation location.

   • The connection object contains many methods to operate the database. For details, find the document.

3. cursor object

   • This object contains methods such as execute() that can be used to execute SQL statements.
   • The connection.cursor(cursorClass) method of the connection object can create and return the cursor object.
     • This method has only one optional parameter and can only be a self defined subclass of sqlite3.Cursor class.

4. cursor.execute(sql, optionalParam) method:

   • You can execute an SQL statement in the form of placeholder parameters, similar to the PreparedStatement interface of java.

   import sqlite3                                       # Introduction module
   conn = sqlite3.connect(":memory:")                   # Establish the database in memory and obtain the connection
   curs = conn.cursor()                                 # Get cursor object from connection object
   curs.execute("create table t_student(Sname, Sage)")  # Execute SQL statement with cursor object to create table
   name = "Jarjack"
   age = 18
   # 1. Execute SQL statement with "?" as placeholder
   curs.execute("insert into t_student values (?, ?)",(name,age))
   # 2. Execute SQL statements as placeholders in the form of parameter names
   curs.execute("select * from t_student where Sname= :name",{"name":name})
   # 3. Grab a record output from the result set.
   print(curs.fetchone())                               
   

5. Cursor.executenamy (SQL, seq_of_param) method:

   • All parameters in the seq_of_param sequence can be automatically executed into one SQL in turn.

   import sqlite3                                       # Introduction module
   conn = sqlite3.connect(":memory:")                   # Establish the database in memory and obtain the connection
   curs = conn.cursor()                                 # Get cursor object from connection object
   curs.execute("create table t_student(Sname, Sage)")  # Execute SQL statement with cursor object to create table
   # 1. Define the sequence of parameter table
   students_param = [["Xue You Zhang",24],["Lau Andy",25],["Guo Fucheng",19],["dawn",28]]
   # 2. Use "?" as placeholder and the sequence of parameter table as parameter to insert data for each parameter table in turn.
   curs.executemany("insert into t_student values (?,?)", students_param)
   # 3. Execute the query and return the result set to the cursor object.
   curs.execute("select * from t_student")
   print(curs.fetchall())                               # Grab a record output from the result set.
   

6. cursor.executescript(sql, optional_param) method:

   • Execute multiple SQL statements as scripts. First execute the commit statement, and then execute the input script. Each statement is separated by ".
   • The query executed in executescript() will not return the result set to the cursor object. You should use execute() to query.

   import sqlite3                                       # Introduction module
   conn = sqlite3.connect(":memory:")                   # Establish the database in memory and obtain the connection
   curs = conn.cursor()                                 # Get cursor object from connection object
   # 1. Execute multiple SQL statements
   curs.executescript("""
           create table t_person(name, age, sex);
           insert into t_person values("Jarjack",18,"male");
           insert into t_person values("Leon",25,"male");
           """)
   curs.execute("select * from t_person")  # Another query to execute
   print(curs.fetchall())
   

7. The execute() method can be called in the connection object, which is equivalent to a shortcut. It will first establish a temporary cursor object and then execute it.

   • connection.execute()
   • connection.executemany()
   • connection.executescript()

8. There are also many database operation methods in the connection object and cursor object, such as commit, rollback, create function, permission management, etc. find the document for details.

17.1.2 data type of SQLite3

• sqlite3 only supports five types: null, integer, real, text and blob, which correspond to none, int, float, STR and bytes in Python respectively.

• If you need to deal with multiple types in practice, you need to use type extension technology, one is object adaptation, the other is forced type conversion.

17.2 MySQL database

1. Install the MySQL driver module PyMySQL in Python. Download address: https://github.com/PyMySQL/PyMySQL

   • You can use the management tool pip install: pip install PyMySQL

2. Connect and operate MySQL database in Python:

   # 1. Introduce MySQL driver module
   import pymysql
   # 2. Connect to the database to obtain the connection object
   conn = pymysql.connect(host="localhost",user="root",password="123456",database="db_practice")
   # 3. Get the cursor object in the connection object for executing SQL
   curs = conn.cursor()
   try:
       # 4. Insert data
       curs.execute("insert into t_dpmt values(50,'manufacture','Los Angle')")
       # 5. Update data (type placeholder precompiled!)
       curs.execute("update t_dpmt set locate='%s' where name='%s'" % ('guangzhou','manufacture'))
       # 6. Delete data (type placeholder precompiled!)
       curs.execute("delete from t_dpmt where locate='%s'" % "guangzhou")
       # 7. Commit the transaction to make the operation persistent to the database (without commit(), the data will not be saved and will only be displayed in the panel).
       conn.commit()
       # 8. Query data
       curs.execute("select * from t_dpmt") # The cursor object saves the result set
       resultset = curs.fetchall()          # Get result set
       for row in resultset:                # Parse result set by row
           id = row[0]
           name = row[1]
           locate = row[2]
           print(id, name, locate)          # Output line
   except:
       print("Incorrect statement!")
       conn.rollback()                      # Rollback in case of operation exception
   finally:
       conn.close()                         # Finally, close the connection
   

17.3 MariaDB database

1. Install the driver module PIP install MySQL connector. Others are omitted.