About Python development SQLite3 database related operations detailed

This article mainly introduced the Python development SQLite3 database related operation, combined with the instance form more detailed analysis of Python operation SQLite3 database connection, query, insert, UPDATE, delete, close and other related operation skills, need friends can refer to the next

The examples in this article describe Python development SQLite3 database related operations. Share to everyone for your reference, as follows:

'' 'SQLite database is a very compact embedded open source database software, which means
There is no independent maintenance process, all maintenance comes from the program itself.
In python, use sqlite3 to create a database connection when the database file we specified does not exist
The connection object will automatically create the database file; if the database file already exists, the connection object will not be created again
Database file, instead open the database file directly.
  The connection object can be a database file on the hard disk, or it can be a database built in memory and in memory.
  After performing any operation, there is no need to commit the transaction (commit)
  Created on the hard disk: conn = sqlite3.connect ('c: \\ test \\ test.db')
  Created in memory: conn = sqlite3.connect ('"memory:')
  Let's create a database file on the hard disk as an example to explain it in detail:
  conn = sqlite3.connect ('c: \\ test \\ hongten.db')
  The conn object is a database link object, and for a database link object, it has the following operations:
    commit ()-transaction commit
    rollback ()-transaction rollback
    close ()-close a database link
    cursor ()-create a cursor
  cu = conn.cursor ()
  So we have created a cursor object: cu
  In sqlite3, the execution of all SQL statements must be completed with the participation of the cursor object
  For the cursor object cu, the following specific operations are performed:
    execute ()-execute a SQL statement
    executemany ()-execute multiple SQL statements
    close ()-cursor closed
    fetchone ()-Fetch a record from the result
    fetchmany ()-fetch multiple records from the result
    fetchall ()-Fetch all records from the result
    scroll ()-cursor scroll
'' '

Here is my demo, in the demo, I made a very detailed comment and function of the demo, the details are as follows:

When show_sql = False :

Python 3.3.2 (v3.3.2: d047928ae3f6, May 16 2013, 00:03:43) [MSC v.1600 32 bit (Intel)] on win32
Type "copyright", "credits" or "license ()" for more information.
>>> ================================== RESTART ============== ===================
>>>
show_sql: False
Delete database table test ...
On the hard disk: [c: \ test \ hongten.db]
Delete database table [student] succeeded!
Create database table test ...
On the hard disk: [c: \ test \ hongten.db]
Create database table [student] succeeded!
Save data test ...
On the hard disk: [c: \ test \ hongten.db]
Query all data ...
On the hard disk: [c: \ test \ hongten.db]
(1, 'Hongten', 'Male', 20, 'Guangzhou City, Guangdong Province', '13423 **** 62')
(2, 'Tom', 'Male', 22, 'San Francisco, USA', '15423 **** 63')
(3, 'Jake', 'Female', 18, 'Guangzhou, Guangdong', '18823 **** 87')
(4, 'Cate', 'Female', 21, 'Guangzhou, Guangdong', '14323 **** 32')
###################################################
Query a piece of data ...
On the hard disk: [c: \ test \ hongten.db]
(1, 'Hongten', 'Male', 20, 'Guangzhou City, Guangdong Province', '13423 **** 62')
############################### ###################
update data...
On the hard disk: [c: \ test \ hongten.db]
Query all data ...
On the hard disk: [c: \ test \ hongten.db]
(1, 'HongtenAA', 'Male', 20, 'Guangzhou, Guangdong', '13423 **** 62')
(2, 'HongtenBB', 'Male', 22, 'San Francisco, USA', '15423 **** 63')
(3, 'HongtenCC', 'Female', 18, 'Guangzhou, Guangdong', '18823 **** 87')
(4, 'HongtenDD', 'Female', 21, 'Guangzhou City, Guangdong Province', '14323 **** 32')
###################################################
delete data...
On the hard disk: [c: \ test \ hongten.db]
Query all data ...
On the hard disk: [c: \ test \ hongten.db]
(2, 'HongtenBB', 'Male', 22, 'San Francisco, USA', '15423 **** 63')
(4, 'HongtenDD', 'Female', 21, 'Guangzhou City, Guangdong Province', '14323 **** 32')
>>>

When show_sql = True :

Python 3.3.2 (v3.3.2: d047928ae3f6, May 16 2013, 00:03:43) [MSC v.1600 32 bit (Intel)] on win32
Type "copyright", "credits" or "license ()" for more information.
>>> ================================== RESTART ============== ===================
>>>
show_sql: True
Delete database table test ...
On the hard disk: [c: \ test \ hongten.db]
Execute sql: [DROP TABLE IF EXISTS student]
Delete database table [student] succeeded!
Create database table test ...
On the hard disk: [c: \ test \ hongten.db]
Execute sql: [CREATE TABLE `student` (
             `id` int (11) NOT NULL,
             `name` varchar (20) NOT NULL,
             `gender` varchar (4) DEFAULT NULL,
             `age` int (11) DEFAULT NULL,
             `address` varchar (200) DEFAULT NULL,
             `phone` varchar (20) DEFAULT NULL,
              PRIMARY KEY (`id`)
            )]
Create database table [student] succeeded!
Save data test ...
On the hard disk: [c: \ test \ hongten.db]
Execute sql: [INSERT INTO student values (?,?,?,?,?,?)], Parameters: [(1, 'Hongten', 'Male', 20, 'Guangzhou City, Guangdong Province', '13423 ** ** 62 ')]
Execute sql: [INSERT INTO student values (?,?,?,?,?,?)], Parameters: [(2, 'Tom', 'Male', 22, 'San Francisco, USA', '15423 **** 63 ')]
Execute sql: [INSERT INTO student values (?,?,?,?,?,?)], Parameters: [(3, 'Jake', 'Female', 18, 'Guangzhou City, Guangdong Province', '18823 ** ** 87 ')]
Execute sql: [INSERT INTO student values (?,?,?,?,?,?)], Parameters: [(4, 'Cate', 'Female', 21, 'Guangzhou City, Guangdong Province', '14323 ** ** 32 ')]
Query all data ...
On the hard disk: [c: \ test \ hongten.db]
Execute sql: [SELECT * FROM student]
(1, 'Hongten', 'Male', 20, 'Guangzhou City, Guangdong Province', '13423 **** 62')
(2, 'Tom', 'Male', 22, 'San Francisco, USA', '15423 **** 63')
(3, 'Jake', 'Female', 18, 'Guangzhou, Guangdong', '18823 **** 87')
(4, 'Cate', 'Female', 21, 'Guangzhou, Guangdong', '14323 **** 32')
###################################################
Query a piece of data ...
On the hard disk: [c: \ test \ hongten.db]
Execute sql: [SELECT * FROM student WHERE ID =?], Parameter: [1]
(1, 'Hongten', 'Male', 20, 'Guangzhou City, Guangdong Province', '13423 **** 62')
###################################################
update data...
On the hard disk: [c: \ test \ hongten.db]
Execute sql: [UPDATE student SET name =? WHERE ID =?], Parameter: [('HongtenAA', 1)]
Execute sql: [UPDATE student SET name =? WHERE ID =?], Parameter: [('HongtenBB', 2)]
Execute sql: [UPDATE student SET name =? WHERE ID =?], Parameter: [('HongtenCC', 3)]
Execute sql: [UPDATE student SET name =? WHERE ID =?], Parameter: [('HongtenDD', 4)]
Query all data ...
On the hard disk: [c: \ test \ hongten.db]
Execute sql: [SELECT * FROM student]
(1, 'HongtenAA', 'Male', 20, 'Guangzhou, Guangdong', '13423 **** 62')
(2, 'HongtenBB', 'Male', 22, 'San Francisco, USA', '15423 **** 63')
(3, 'HongtenCC', 'Female', 18, 'Guangzhou, Guangdong', '18823 **** 87')
(4, 'HongtenDD', 'Female', 21, 'Guangzhou City, Guangdong Province', '14323 **** 32')
###################################################
delete data...
On the hard disk: [c: \ test \ hongten.db]
Execute sql: [DELETE FROM student WHERE NAME =? AND ID =?], Parameter: [('HongtenAA', 1)]
Execute sql: [DELETE FROM student WHERE NAME =? AND ID =?], Parameter: [('HongtenCC', 3)]
Query all data ...
On the hard disk: [c: \ test \ hongten.db]
Execute sql: [SELECT * FROM student]
(2, 'HongtenBB', 'Male', 22, 'San Francisco, USA', '15423 **** 63')
(4, 'HongtenDD', 'Female', 21, 'Guangzhou City, Guangdong Province', '14323 **** 32')
>>>

Specific code:

#python sqlite
#Author: Hongten
#Create: 2013-08-09
#Version: 1.0
# DB-API 2.0 interface for SQLite databases
import sqlite3
import os
'' 'SQLite database is a very compact embedded open source database software, which means
There is no independent maintenance process, all maintenance comes from the program itself.
In python, use sqlite3 to create a database connection when the database file we specified does not exist
The connection object will automatically create the database file; if the database file already exists, the connection object will not be created again
Database file, instead open the database file directly.
  The connection object can be a database file on the hard disk, or it can be a database built in memory and in memory.
  After performing any operation, there is no need to commit the transaction (commit)
  Created on the hard disk: conn = sqlite3.connect ('c: \\ test \\ test.db')
  Created in memory: conn = sqlite3.connect ('"memory:')
  Let's create a database file on the hard disk as an example to explain it in detail:
  conn = sqlite3.connect ('c: \\ test \\ hongten.db')
  The conn object is a database link object, and for a database link object, it has the following operations:
    commit ()-transaction commit
    rollback ()-transaction rollback
    close ()-close a database link
    cursor ()-create a cursor
  cu = conn.cursor ()
  So we have created a cursor object: cu
  In sqlite3, the execution of all SQL statements must be completed with the participation of the cursor object
  For the cursor object cu, the following specific operations are performed:
    execute ()-execute a SQL statement
    executemany ()-execute multiple SQL statements
    close ()-cursor closed
    fetchone ()-Fetch a record from the result
    fetchmany ()-fetch multiple records from the result
    fetchall ()-Fetch all records from the result
    scroll ()-cursor scroll
'' '
#global var
#Database file absolute sentence path
DB_FILE_PATH = ''
# 表 名称
TABLE_NAME = ''
#Whether to print sql
SHOW_SQL = True
def get_conn (path):
  '' 'Get the connection object to the database, the parameter is the absolute path of the database file
  If the parameter passed is a file, it is returned to the hard disk and changed.
  The connection object of the database file under the path; otherwise, returns the data connection in memory
  Connection object '' '
  conn = sqlite3.connect (path)
  if os.path.exists (path) and os.path.isfile (path):
    print ('On hard disk: (())'. format (path))
    return conn
  else:
    conn = None
    print ('On memory: [: memory:]')
    return sqlite3.connect (': memory:')
def get_cursor (conn):
  '' 'The method is to obtain the cursor object of the database, and the parameter is the connection object of the database
  If the database connection object is not None, the database connection object created is returned
  Cursor object created; otherwise a cursor object is returned, which is data in memory
  Cursor object '' 'created by library connection object
  if conn is not None:
    return conn.cursor ()
  else:
    return get_conn (''). cursor ()
################################################### ###############
#### Create | Delete Table Operation START
################################################### ###############
def drop_table (conn, table):
  '' 'If the table exists, delete the table, if there is data in the table, use this
  Use it with caution! '' '
  if table is not None and table! = '':
    sql = 'DROP TABLE IF EXISTS' + table
    if SHOW_SQL:
      print ('execute sql: [(}]'. format (sql))
    cu = get_cursor (conn)
    cu.execute (sql)
    conn.commit ()
    print ('Delete database table [(}] successful!'. format (table))
    close_all (conn, cu)
  else:
    print ('the [(}] is empty or equal None!'. format (sql))
def create_table (conn, sql):
  '' 'Create database table: student' ''
  if sql is not None and sql! = '':
    cu = get_cursor (conn)
    if SHOW_SQL:
      print ('execute sql: [(}]'. format (sql))
    cu.execute (sql)
    conn.commit ()
    print ('Success in creating database table [student]!')
    close_all (conn, cu)
  else:
    print ('the [(}] is empty or equal None!'. format (sql))
################################################### ###############
#### Create | Delete Table Operation END
############################### ################### ###############
def close_all (conn, cu):
  '' 'Close database cursor object and database connection object' ''
  try:
    if cu is not None:
      cu.close ()
  finally:
    if cu is not None:
      cu.close ()
############################### ################### ###############
#### Database operation CRUD START
############################### ################### ###############
def save (conn, sql, data):
  '' 'Insert data' ''
  if sql is not None and sql! = '':
    if data is not None:
      cu = get_cursor (conn)
      for d in data:
        if SHOW_SQL:
          print ('Execute sql: [(}], parameter: [(}]'. format (sql, d))
        cu.execute (sql, d)
        conn.commit ()
      close_all (conn, cu)
  else:
    print ('the [(}] is empty or equal None!'. format (sql))
def fetchall (conn, sql):
  '' 'Query all data' ''
  if sql is not None and sql! = '':
    cu = get_cursor (conn)
    if SHOW_SQL:
      print ('execute sql: [(}]'. format (sql))
    cu.execute (sql)
    r = cu.fetchall ()
    if len (r)> 0:
      for e in range (len (r)):
        print (r [e])
  else:
    print ('the [(}] is empty or equal None!'. format (sql))
def fetchone (conn, sql, data):
  '' 'Query a piece of data' ''
  if sql is not None and sql! = '':
    if data is not None:
      #Do this instead
      d = (data,)
      cu = get_cursor (conn)
      if SHOW_SQL:
        print ('execute sql: [(}], parameter: [(}]'. format (sql, data))
      cu.execute (sql, d)
      r = cu.fetchall ()
      if len (r)> 0:
        for e in range (len (r)):
          print (r [e])
    else:
      print ('the [(}] equal None!'. format (data))
  else:
    print ('the [(}] is empty or equal None!'. format (sql))
def update (conn, sql, data):
  '''update data'''
  if sql is not None and sql! = '':
    if data is not None:
      cu = get_cursor (conn)
      for d in data:
        if SHOW_SQL:
          print ('Execute sql: [(}], parameter: [(}]'. format (sql, d))
        cu.execute (sql, d)
        conn.commit ()
      close_all (conn, cu)
  else:
    print ('the [(}] is empty or equal None!'. format (sql))
def delete (conn, sql, data):
  '''delete data'''
  if sql is not None and sql! = '':
    if data is not None:
      cu = get_cursor (conn)
      for d in data:
        if SHOW_SQL:
          print ('Execute sql: [(}], parameter: [(}]'. format (sql, d))
        cu.execute (sql, d)
        conn.commit ()
      close_all (conn, cu)
  else:
    print ('the [(}] is empty or equal None!'. format (sql))
###################################################
###############
#### Database operations CRUD END
############################### ################### ###############
############################### ################### ###############
#### Test operation START
############################### ################### ###############
def drop_table_test ():
  '' 'Delete database table test' ''
  print ('Delete database table test ...')
  conn = get_conn (DB_FILE_PATH)
  drop_table (conn, TABLE_NAME)
def create_table_test ():
  '' 'Create database table test' ''
  print ('Create database table test ...')
  create_table_sql = '' 'CREATE TABLE `student` (
             `id` int (11) NOT NULL,
             `name` varchar (20) NOT NULL,
             `gender` varchar (4) DEFAULT NULL,
             `age` int (11) DEFAULT NULL,
             `address` varchar (200) DEFAULT NULL,
             `phone` varchar (20) DEFAULT NULL,
              PRIMARY KEY (`id`)
            ) '' '
  conn = get_conn (DB_FILE_PATH)
  create_table (conn, create_table_sql)
def save_test ():
  '' 'Save data test ...' ''
  print ('Save data test ...')
  save_sql = '' 'INSERT INTO student values (?,?,?,?,?,?)' ''
  data = [(1, 'Hongten', 'Male', 20, 'Guangzhou, Guangdong', '13423 **** 62'),
      (2, 'Tom', 'Male', 22, 'San Francisco, USA', '15423 **** 63'),
      (3, 'Jake', 'Female', 18, 'Guangzhou, Guangdong', '18823 **** 87'),
      (4, 'Cate', 'Female', 21, 'Guangzhou, Guangdong', '14323 **** 32')]
  conn = get_conn (DB_FILE_PATH)
  save (conn, save_sql, data)
def fetchall_test ():
  '' 'Query all data ...' ''
  print ('Query all data ...')
  fetchall_sql = '' 'SELECT * FROM student' ''
  conn = get_conn (DB_FILE_PATH)
  fetchall (conn, fetchall_sql)
def fetchone_test ():
  '' 'Query a piece of data ...' ''
  print ('Query a piece of data ...')
  fetchone_sql = 'SELECT * FROM student WHERE ID =?'
  data = 1
  conn = get_conn (DB_FILE_PATH)
  fetchone (conn, fetchone_sql, data)
def update_test ():
  '''update data...'''
  print ('Update data ...')
  update_sql = 'UPDATE student SET name =? WHERE ID =?'
  data = [('HongtenAA', 1),
      ('HongtenBB', 2),
      ('HongtenCC', 3),
      ('HongtenDD', 4)]
  conn = get_conn (DB_FILE_PATH)
  update (conn, update_sql, data)
def delete_test ():
  '''delete data...'''
  print ('Delete data ...')
  delete_sql = 'DELETE FROM student WHERE NAME =? AND ID =?'
  data = [('HongtenAA', 1),
      ('HongtenCC', 3)]
  conn = get_conn (DB_FILE_PATH)
  delete (conn, delete_sql, data)
############################### ################### ###############
#### Test operation END
############################### ################### ###############
def init ():
  '' 'Init method' ''
  #Database file absolute sentence path
  global DB_FILE_PATH
  DB_FILE_PATH = 'c: \\ test \\ hongten.db'
  #Database table name
  global TABLE_NAME
  TABLE_NAME = 'student'
  #Whether to print sql
  global SHOW_SQL
  SHOW_SQL = True
  print ('show_sql: ()'. format (SHOW_SQL))
  #If a database table exists, delete the table
  drop_table_test ()
  #Create database table student
  create_table_test ()
  #Insert data into the database table
  save_test ()
def main ():
  init ()
  fetchall_test ()
  print ('#' * 50)
  fetchone_test ()
  print ('#' * 50)
  update_test ()
  fetchall_test ()
  print ('#' * 50)
  delete_test ()
  fetchall_test ()
if __name__ == '__main__':
  main ()