Python SQLite3 database operations

It was not a long time to get familiar with Python. Recently, a project needs to analyze data. Therefore, we chose Python as the programming language. In addition to the language features, we mainly focus on Python's excellent support capabilities for the SQLite3 database, because a large amount of intermediate data needs to be processed flexibly.

At the beginning, I was still happy to write SQL statements in some modules. Later I got tired of it. I thought back to the previous time when I used reflection to build an SQL query constructor until I found out that I was writing SQL statements, so I gave up this plan. Of course, Microsoft later launched the Entity Framework. These are all post-scripts, and now I am not very interested in Microsoft's things, the text below continues.

By the way, excellent blog program Drupal also uses a similar query constructor for database queries to avoid writing SQL statements directly. In addition, the advantage of doing so is, it can block platform relevance to some extent, which is helpful for database migration.

However, the database-assisted query constructor I introduced today is very simple, and it is even limited to SQLite databases. If you are interested in it, you can improve it, currently, I only need to operate SQLite smoothly. For large database applications, I can directly use ORM.

First look at the Code:

Copy codeThe Code is as follows:
Import sqlite3

#*************************************** ************
#*
# * Description: Python-assisted class for SQLite3 database operations (query constructor)
# * Author: wangye
#*
#*************************************** ************

Def _ wrap_value (value ):
Return repr (value)

Def _ wrap_values (values ):
Return list (map (_ wrap_value, values ))

Def _ wrap_fields (fields ):
For key, value in fields. items ():
Fields [key] = _ wrap_value (value)
Return fields

Def _ concat_keys (keys ):
Return "[" + "], [". join (keys) + "]"

Def _ concat_values (values ):
Return ",". join (values)

Def _ concat_fields (fields, operator = (None ,",")):
If operator:
Unit_operator, group_operator = operator
# Fields = _ wrap_fields (fields)
Compiled = []
For key, value in fields. items ():
Compiled. append ("[" + key + "]")
If unit_operator:
Compiled. append (unit_operator)
Compiled. append (value)
Compiled. append (group_operator)
Compiled. pop () # pop last group_operator
Return "". join (compiled)

Class DataCondition (object ):
"""
This class is used to operate the Condition Statement section of the SQL constructor auxiliary class.

For example:
DataCondition ("=", "AND"), id = 26)
DataCondition ("=", "AND"), True, id = 26)
"""

Def _ init _ (self, operator = ("=", "AND"), ingroup = True, ** kwargs ):
"""
Constructor
Parameters:
Operator operators, divided into (expression operators, conditional operators)
Whether or not the ingroup group is set. If the group is set, it is enclosed in parentheses.
Kwargs key-value tuples, including the names and values of database tables
Note that the equal sign here is not equal to the actually generated SQL statement symbol
The actual symbols are controlled by operator [0 ].
For example:
DataCondition ("=", "AND"), id = 26)
(Id = 26)
DataCondition (">", "OR"), id = 26, age = 35)
(Id> 26 OR age> 35)
DataCondition ("LIKE", "OR"), False, name = "John", company = "Google ")
Name LIKE 'john' OR company LIKE "Google"
"""
Self. ingroup = ingroup
Self. fields = kwargs
Self. operator = operator

Def _ unicode _ (self ):
Self. fields = _ wrap_fields (self. fields)
Result = _ concat_fields (self. fields, self. operator)
If self. ingroup:
Return "(" + result + ")"
Return result

Def _ str _ (self ):
Return self. _ unicode __()

Def toString (self ):
Return self. _ unicode __()

Class DataHelper (object ):

"""
SQLite3 Data Query helper class
"""

Def _ init _ (self, filename ):
"""
Constructor
Parameter: filename is the name of the SQLite3 database file
"""
Self. file_name = filename

Def open (self ):
"""
Open the database and set the cursor
"""
Self. connection = sqlite3.connect (self. file_name)
Self. cursor = self. connection. cursor ()
Return self

Def close (self ):
"""
Close the database. Note that if this method is not explicitly called,
It will also be called when the class is recycled
"""
If hasattr (self, "connection") and self. connection:
Self. connection. close ()

Def _ del _ (self ):
"""
Structure Method for cleanup
"""
Self. close ()

Def commit (self ):
"""
Commit transactions
The SELECT statement does not require this operation. The default execute Method
If commit_at_once is set to True, this method is called implicitly,
Otherwise, the call method must be displayed.
"""
Self. connection. commit ()

Def execute (self, SQL = None, commit_at_once = True ):
"""
Execute SQL statements
Parameters:
The SQL statement to be executed by the SQL statement. If it is None, the SQL statement generated by the constructor is called.
Commit_at_once: whether to submit the transaction immediately. If not,
For non-query operations, you need to call commit to submit explicitly.
"""
If not SQL:
SQL = self. SQL
Self.cursor.exe cute (SQL)
If commit_at_once:
Self. commit ()

Def fetchone (self, SQL = None ):
"""
Retrieve a record
"""
Self.exe cute (SQL, False)
Return self. cursor. fetchone ()

Def fetchall (self, SQL = None ):
"""
Retrieve all records
"""
Self.exe cute (SQL, False)
Return self. cursor. fetchall ()

Def _ concat_keys (self, keys ):
Return _ concat_keys (keys)

Def _ concat_values (self, values ):
Return _ concat_values (values)

Def table (self, * args ):
"""
Sets the table to be queried. Multiple table names are separated by commas (,).
"""
Self. tables = args
Self. tables_snippet = self. _ concat_keys (self. tables)
Return self

Def _ wrap_value (self, value ):
Return _ wrap_value (value)

Def _ wrap_values (self, values ):
Return _ wrap_values (values)

Def _ wrap_fields (self, fields ):
Return _ wrap_fields (fields)

Def _ where (self ):
# Self. condition_snippet
If hasattr (self, "condition_snippet "):
Self. where_snippet = "WHERE" + self. condition_snippet

Def _ select (self ):
Template = "SELECT % (keys) s FROM % (tables) s"
Body_snippet_fields = {
"Tables": self. tables_snippet,
"Keys": self. _ concat_keys (self. body_keys ),
}
Self. SQL = template % body_snippet_fields

Def _ insert (self ):
Template = "insert into % (tables) s (% (keys) s) VALUES (% (values) s )"
Body_snippet_fields = {
"Tables": self. tables_snippet,
"Keys": self. _ concat_keys (list (self. body_fields.keys ())),
"Values": self. _ concat_values (list (self. body_fields.values ()))
}
Self. SQL = template % body_snippet_fields

Def _ update (self ):
Template = "UPDATE % (tables) s SET % (fields) s"
Body_snippet_fields = {
"Tables": self. tables_snippet,
"Fields": _ concat_fields (self. body_fields, ("= ",","))
}
Self. SQL = template % body_snippet_fields

Def _ delete (self ):
Template = "delete from % (tables) s"
Body_snippet_fields = {
"Tables": self. tables_snippet
}
Self. SQL = template % body_snippet_fields

Def _ build (self ):
{
"SELECT": self. _ select,
"INSERT": self. _ insert,
"UPDATE": self. _ update,
"DELETE": self. _ delete
} [Self. current_token] ()

Def _ unicode _ (self ):
Return self. SQL

Def _ str _ (self ):
Return self. _ unicode __()

Def select (self, * args ):
Self. current_token = "SELECT"
Self. body_keys = args
Self. _ build ()
Return self

Def insert (self, ** kwargs ):
Self. current_token = "INSERT"
Self. body_fields = self. _ wrap_fields (kwargs)
Self. _ build ()
Return self

Def update (self, ** kwargs ):
Self. current_token = "UPDATE"
Self. body_fields = self. _ wrap_fields (kwargs)
Self. _ build ()
Return self

Def delete (self, * conditions ):
Self. current_token = "DELETE"
Self. _ build ()
# If * conditions:
Self. where (* conditions)
Return self

Def where (self, * conditions ):
Conditions = list (map (str, conditions ))
Self. condition_snippet = "AND". join (conditions)
Self. _ where ()
If hasattr (self, "where_snippet "):
Self. SQL + = self. where_snippet
Return self

Here are a few examples for your reference:
Copy codeThe Code is as follows:
Db = DataHelper ("/home/wangye/sample. db3 ")
Db. open () # open a database
Db.exe cute ("""
Create table [staffs] (
[Staff_id] integer primary key autoincrement,
[Staff_name] text not null,
[Staff_cardnum] text not null,
[Staff_reserved] INTEGER NOT NULL
)
") # Run the SQL statement directly. Note that the default value of commit_at_once is True.

Db. table ("staffs"). insert (staff_name = "John", staff_cardnum = "1001", staff_reserved = 0)
# Insert a record

Rs = db. table ("staffs"). select ("staff_id", "staff_name"). fetchall ()
# Retrieve all staff_id and staff_name directly

Rs = db. table ("staffs "). select ("staff_name "). where (DataCondition ("=", "AND"), id = 1 )). fetchone ()
# Obtain a staff_name with staff_id as 1.

Rs = db. table ("staffs "). select ("staff_name "). where (DataCondition ("<", "AND"), id = 100), DataCondition ("=", "AND"), staff_reserved = 1 )). fetchone ()
# Take a staff_name record whose id is less than 100 and staff_reserved is 1

Db. close () # close the database

The asterisk (*) operator is not supported yet. In addition, it is not very well processed in the operations of multiple tables with the same name. This operation is only used for simple script operations. It is best not to use it in the production environment, because there may be unknown issues.