Learn the SQLite in Python tutorial

The Python SQLite database is a very small embedded open source database software with no independent maintenance process and all maintenance is from the program itself. It uses a file to store the entire database, which is very convenient to operate.

The following are important SQLITE3 module programs that you can use to meet the needs of your SQLite database in a Python program. If you need more details, check out the official documentation for the Python sqlite3 module.

Serial Number	API & Description
1	Sqlite3.connect (database [, timeout, other optional arguments]) The API opens a link to the SQLite database file. You can use ": Memory:" To open a database connection in RAM instead of on disk. If the database opens successfully, a connection object is returned. When a database is accessed by multiple connections, and one of them modifies the database, the SQLite database is locked until the transaction commits. The timeout parameter indicates the duration of the connection waiting for a lock until an abnormally disconnected connection occurs. The timeout parameter defaults to 5.0 (5 seconds). If the given database name filename does not exist, the call will create a database. If you do not want to create a database in the current directory, you can specify a file name with a path so that you can create the database anywhere.
2	Connection.cursor ([Cursorclass]) The routine creates a cursorthat will be used in Python database programming. The method accepts a single optional parameter, Cursorclass. If this argument is provided, it must be an extended self-sqlite3. The cursor's custom cursor class.
3	Cursor.execute (SQL [, optional parameters]) The routine executes an SQL statement. The SQL statement can be parameterized (that is, using placeholders instead of SQL text). The Sqlite3 module supports two types of placeholders: Greetings and named placeholders (named Styles). Example: Cursor.execute ("INSERT into people values (?,?)", (who, age))
4	Connection.Execute (SQL [, optional parameters]) The routine is a shortcut to the method provided by the cursor object above, which creates an intermediate cursor object by invoking the cursor method, and then invokes the Execute method of the cursor with the given parameters.
5	Cursor.executemany (SQL, seq_of_parameters) The routine executes an SQL command on all parameters or mappings in the seq_of_parameters.
6	Connection.executemany (sql[, parameters]) The routine is a shortcut to the middle cursor object created by the call cursor method and then invokes the cursor's Executemany method with the given parameters.
7	Cursor.executescript (Sql_script) Once the routine receives the script, it executes multiple SQL statements. It executes the COMMIT statement first, and then executes the SQL script passed in as a parameter. All SQL statements should be separated by semicolons (;).
8	Connection.executescript (Sql_script) The routine is a shortcut to the middle cursor object created by the call cursor method and then invokes the cursor's Executescript method with the given parameters.
9	Connection.total_changes () The routine returns the total number of database rows that have been modified, inserted, or deleted since the database connection was opened.
10	Connection.commit () The method submits the current food. If you do not call this method, any actions you have made since you last called commit () are not visible to other database connections.
11	Connection.rollback () The method rolls back the changes made to the database since the last call to commit ().
12	Connection.close () This method closes the database connection. Note that this does not automatically invoke commit (). If you have not called the commit () method before, close the database connection directly, and all changes you have made will be lost!
13	Cursor.fetchone () The method gets the next row in the query result set, returns a single sequence, and returns none when no more data is available.
14	Cursor.fetchmany ([size=cursor.arraysize]) This method gets the next row of groups in the query result set, returning a list. When there are no more rows available, an empty list is returned. The method attempts to get as many rows as are specified by the size parameter.
15	Cursor.fetchall () The routine gets all (the remaining) rows in the query result set, returning a list. When no rows are available, an empty list is returned.

Python's database module has a unified interface standard, so database operations have a unified pattern, basically the following steps (assuming the database module named db):

1) Create a database connection with Db.connect , assuming the connection object is conn
2) If the database operation does not need to return results, directly with Conn.execute query , depending on the database transaction isolation level, may modify the database needs Conn.commit
3) If you need to return query results, use Conn.cursor to create the cursor object cur, cur.execute query the database, and return the query results with Cur.fetchall/cur.fetchone/cur.fetchmany. depending on the database transaction isolation level, it is possible to modify the database to Conn.commit
4) Close cur, conn

Let's introduce the use of SQLite in the steps described above.

1) Import the Sqlite3 module , such as:

>>> Import Sqlite3

2) Create DATABASE/Open Database

Create or open the data as follows:

>>> Import sqlite3 as Sqlite>>> conn = Sqlite.connect ("d:/test.db")

The above command opens the D:/test.db database file, opens the database directly if the specified database exists, if it does not exist, creates a new one again, and returns the database connection object Conn, which mainly has the following actions:

• Commit (): Transaction commit
• Rollback (): Transaction rollback
  
• Close (): Close a database connection
  
• Cursor (): Create a cursor
  

The complete form of Connect is as follows:

Connect (database[, timeout, Isolation_level, Detect_types, Factory])

where the commit () method is used to commit the transaction, the rollback () method is used to roll back to the place where the commit () method was last called. The transaction isolation level can be defined through connection.isolation_level, and when the property is set to none, it commits the transaction automatically and does not require the commit () method to be explicitly called.

In addition to specifying the database files directly, there is another way to create a database in memory. The method is to pass ": Memory:" As a parameter to the Sqlite.connect () function:

conn = Sqlite3.connect (": Memory:")

If you do not need to return results, you can use Conn.execute () directly for data manipulation, as follows, creating a database table User:

Conn.execute ("CREATE table user (UID int primary key NOT NULL, name text NOT NULL, password text NOT NULL, age int)")

If you need to return the result, continue looking at the following cursor object.

3) Creating a Cursor object

>>> cur = conn.cursor ()

The cursor object has the following actions:

• Execute ()--Execute SQL statement
• executemany--executing multiple SQL statements
• Close ()--close cursor
• Fetchone ()--Takes a record from the result and points the cursor to the next record
• Fetchmany ()--take multiple records from the results
• Fetchall ()--Remove all records from the results

If you are inserting an operation:

>>> cur.execute("insert into user(uid,name, password,age)values(1,'John','123',12)")
<sqlite3.Cursor object at 0x02A8A0A0>
>>> cur.execute("insert into user(uid,name, password,age)values(2,'Kate','123',12)")

Now the query looks at the data you just inserted:

>>> cur.execute("select* from user")
<sqlite3.Cursor object at 0x02A8A0A0>
>>> print(cur.fetchone())
(1, u'John', u'123', 12)
>>> print(cur.fetchall())
[(2, u'Kate', u'123', 12)]
>>> print(cur.fetchall())
[]
>>> cur.execute("select* from user")
<sqlite3.Cursor object at 0x02A8A0A0>
>>> print(cur.fetchall())
[(1, u'John', u'123', 12), (2, u'Kate', u'123', 12)]
>>> 

As you can see from the code above, Fetchone () takes out one piece of data at a time and points to the next data, while Fetchall () pulls all the data and points to the end. Of course, there are changes to the operation:

>>> cur.execute("update user set age =15 where uid =1")
<sqlite3.Cursor object at 0x02A8A0A0>
>>> cur.execute("select* from user where uid =1")
<sqlite3.Cursor object at 0x02A8A0A0>
>>> print(cur.fetchall())
[(1, u'John', u'123', 15)]
>>> 

Delete operation:

>>> cur.execute("delete from user where uid =1")
<sqlite3.Cursor object at 0x02A8A0A0>
>>> 

In addition to the Cur=conn.curson shown above ()Create right Objectobject, you can actually directly conn.ececute () the cursor object that is returned, such as:

>>> cur = conn.execute("select* from user")
>>> print(cur.fetchall())
[(1, u'test', u'124', 16)]
>>> 

Cursor object fetching a lot of records Fetchmany ():

>>> cur = conn.execute("select* from user")
>>> print(cur.fetchmany(3))
[(1, u'test', u'124', 16), (2, u'test2', u'124', 18), (3, u'test3', u'124', 20)]
>>> 

Below, we encapsulate the sqlite operation in Python into a class, as follows:

class sqlitehelper:
    def __init__(self, db):
        self.db = db
        try:
            self.conn = sqlite.connect(db)
            self.cur = self.conn.cursor()
        except sqlite.Error,e:
            print("connect "+db+" failed:"+e.reason)
            return          
    def __del__(self):
        self.conn.close()

    def execute(self,sql):
        self.cur.execute(sql)
        return self.cur

    def select(self, sql):
        self.cur.execute(sql)
        return self.cur

    def insert(self, sql):
        self.cur.execute(sql)
        return self.cur

    def delete(self, sql):
        self.cur.execute(sql)
        return self.cur

    def update(self, sql):
        self.cur.execute(sql)
        return self.cur

Resources:

1, the sqlite3 use of Python detailed

2, Sqlite3-db-api 2.0 interface for SQLite databases

3. Python sqlite3 Module API

Learn the SQLite in Python tutorial