Python Operation SQLite Database

"SQLite database is a very small embedded open source database software, which means
There is no independent maintenance process, and all maintenance is from the program itself.
In Python, use sqlite3 to create a connection to the database when the database file that we specified does not exist
The connection object automatically creates the database file, and if the database file already exists, the connection object is no longer created
Database file, but opens the database file directly.
The connection object can be a database file above the hard disk, or it can be built in memory, in-memory database
No commit transaction is required after any action has been performed (commit)

Create on hard disk: conn = sqlite3.connect (' e:\\test.db ')
Created on top of memory: conn = Sqlite3.connect (' Memory: ')

Below we create a hard drive above the database file as an example to specify:
conn = Sqlite3.connect (' e:\\test.db ')
Where the Conn object is a database link object, and for a database link object, you have the following actions:

Commit ()--Transaction commit
Rollback ()--Transaction rollback
Close ()--Close a database link
Cursor ()--Create a cursor

CU = Conn.cursor ()
So we've created a cursor object: CU
In Sqlite3, the execution of all SQL statements is done with the participation of the cursor object
For a Cursor object CU, you have the following specific actions:

Execute ()--executes an SQL statement
Executemany ()--Execute multiple SQL statements
Close ()--Cursor Close
Fetchone ()--Remove a record from the result
Fetchmany ()--remove multiple records from the result
Fetchall ()--Remove all records from the results
Scroll ()--cursor scrolling

‘‘‘

#-*-Coding:utf-8-*-
Import Sqlite3
Import OS

Class Sqlitedb:

#是否打印sql
Print_sql = True

#数据库连接
Sqlite_conn = None
def __init__ (Self,dbfile_path):
' Initialize database file path '
filepath = Unicode (dbfile_path, ' UTF8 ')
Self.sqlite_conn = Self.get_conn (filepath)

def get_conn (Self,dbfile_path):
"' Gets the connection object to the database, the parameter is the absolute path to the database file
If the passed parameter is present and is a file, then return to the hard disk to change
The connection object for the database file under the path, otherwise, the data in memory is returned
Connection Object "'
If Os.path.exists (Dbfile_path) and Os.path.isfile (Dbfile_path):
Print (' hard disk above: [{}] '. Format (Dbfile_path))
Return Sqlite3.connect (Dbfile_path)
Else
Print (' Ram above: [: Memory:] ')
Return Sqlite3.connect (': Memory: ')

def commit (self):
"Commit database Transaction"
If Self.sqlite_conn is not None:
Self.sqlite_Conn.commit ()

def get_cursor (self):
‘‘‘
The method is to get the database's cursor object, and the parameter is the connection object of the database
If the connection object for the database is not none, the database connection object that is created is returned
Cursor object, otherwise a cursor object that is an in-memory data
Cursor objects created by the Library connection object
‘‘‘
If Self.sqlite_conn is not None:
Return Self.sqlite_Conn.cursor ()
Else
Return Self.sqlite_Conn.cursor ()

def close_cursor (self,cursor):
"' Close database Cursor object and database connection object '
Try
If cursor is not None:
Cursor.close ()
Finally
If cursor is not None:
Cursor.close ()

################################################################
#创建表, delete table operations
################################################################
def create_table (self, strSQL):
"' Create database table: ' '
If strSQL is not None and strSQL! = ":
cursor = Self.get_cursor ()
If Self.print_sql:
Print (' Execute sql:[{}] '. Format (strSQL))
Cursor.execute (strSQL)
Self.commit ()
Print (' Create database table successfully! ')
Self.close_cursor (Cursor)
Else
Print (' The [{}] is empty or equal none! '. Format (strSQL))


def drop_table (self,table):
If the table exists, delete the table and, if there is data in the table, use the
Use caution when using the method! ‘‘‘
If table is not None and table! = ':
strSQL = ' DROP table IF EXISTS ' + table
If Self.print_sql:
Print (' Execute sql:[{}] '. Format (strSQL))
cursor = Self.get_cursor ()
Cursor.execute (strSQL)
Self.commit ()
Print (' Delete database table [{}] succeeded! '. Format (table))
Self.close_cursor (Cursor)
Else
Print (' The [{}] is empty or equal none! '. Format (strSQL))

#####################################################################

#数据库操作
#####################################################################
def insert_multidata (Self,strsql, data):
"Insert Data"
If strSQL is not None and strSQL! = ":
If data is not None:
cursor = Self.get_cursor ()
For D in data:
If Self.print_sql:
Print (' Execute sql:[{}], parameter: [{}] '. Format (strSQL, D))
Cursor.execute (strSQL, D)
Self.commit ()
Self.close_cursor (Cursor)
Else
Print (' The [{}] is empty or equal none! '. Format (strSQL))

def insert_data (Self,strsql):
"Insert Data"
If strSQL is not None and strSQL! = ":
cursor = Self.get_cursor ()
Print (' Execute sql:[{}] '. Format (strSQL))
Cursor.execute (strSQL)
Self.commit ()
Self.close_cursor (Cursor)

Else
Print (' The [{}] is empty or equal none! '. Format (strSQL))

def get_all_item (Self,strsql):
"Query all Data"
If strSQL is not None and strSQL! = ":
cursor = Self.get_cursor ()
If Self.print_sql:
Print (' Execute sql:[{}] '. Format (strSQL))
Cursor.execute (strSQL)
Listr = Cursor.fetchall ()

Self.close_cursor (Cursor)
Return LISTR

Else
Print (' The [{}] is empty or equal none! '. Format (strSQL))
Return None

def get_one_item (Self,strsql, data):
"Query a piece of data"
If strSQL is not None and strSQL! = ":
If data is not None:
#Do this instead
D = (data,)
cursor = Self.get_cursor ()
If Self.print_sql:
Print (' Execute sql:[{}], parameter: [{}] '. Format (strSQL, data))
Cursor.execute (strSQL, D)
R = Cursor.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 (strSQL))

def update_data (Self,strsql, Data):
"Update Data"
If strSQL is not None and strSQL! = ":
If data is not None:
cursor = Self.get_cursor ()
For D in data:
If Self.print_sql:
Print (' Execute sql:[{}], parameter: [{}] '. Format (strSQL, D))
Cursor.execute (strSQL, D)
Self.commit ()
Self.close_cursor (Cursor)
Else
Print (' The [{}] is empty or equal none! '. Format (strSQL))

def delete_multidata (Self,strsql, data):
"Delete more than one SQL data"
If strSQL is not None and strSQL! = ":
If data is not None:
cursor = Self.get_cursor ()
For D in data:
If Self.print_sql:
Print (' Execute sql:[{}], parameter: [{}] '. Format (strSQL, D))
Cursor.execute (strSQL, D)
Self.commit ()
Self.close_cursor (Cursor)
Else
Print (' The [{}] is empty or equal none! '. Format (strSQL))

def delete_data (Self,strsql):
"Delete a SQL data"
If strSQL is not None and strSQL! = ":
If Self.print_sql:
Print (' Execute sql:[{}] '. Format (strSQL))
cursor = Self.get_cursor ()
Cursor.execute (strSQL)
Self.commit ()
Self.close_cursor (Cursor)
Else
Print (' The [{}] is empty or equal none! '. Format (strSQL))

#########################################################################
#测试代码
#########################################################################

db = Sqlitedb (' e:\\test.db ')

#删除数据表
db.drop_table (' person ')

#创建数据库表
Create_table_sql = ' CREATE table ' person ' (
' id ' int (one) is not NULL,
' Name ' varchar (not NULL),
' Gender ' varchar (4) DEFAULT NULL,
' Age ' int (one) DEFAULT NULL,
' Address ' varchar (+) DEFAULT NULL,
PRIMARY KEY (' id ')
)‘‘‘

Db.create_table (Create_table_sql)

#删除数据,
Delsql= ' Delete from person '
Db.delete_data (Delsql)

#插入数据测试, insert a statement
Insert_sql = ' INSERT into ' VALUES (3, ' Xiaoli ', ' female ', 18, ' Shandong ') '
Db.insert_data (Insert_sql)

#插入数据测试, insert multiple statements

Insert_sql = ' INSERT into ' values (?,?,?,?,?) '
‘‘‘
data = [[1, ' Xiaowang ', U ' man ', ' u ', ' Guangdong '],
[2, ' Xiaozhang ', U ' man ', ' U ' Henan '],
[3, ' Xiaoli ', U ' m ', U ' Shandong '],
[4, ' Xiaoliu ', u ' female ', +, U ' Shanxi ']
‘‘‘

data = [[1, ' Xiaowang ', ' Male ', 20, ' Guangdong '],
[2, ' Xiaozhang ', ' Male ', 22, ' Henan '],
[3, ' Xiaoli ', ' Male ', 18, ' Shandong '],
[4, ' Xiaoliu ', ' female ', 21, ' Shanxi ']

For item in data:
ITEM[2] = Unicode (item[2], ' UTF8 ')
ITEM[4] = Unicode (item[4], ' UTF8 ')

Db.insert_multidata (Insert_sql,data)

#查询数据
Print Db.get_all_item (' select * from person ')

Python Operation SQLite Database