Touch python time is not very long, recently a project needs to analyze data, and then choose Python as the programming language, in addition to language features, mainly focus on Python for SQLite3 database good support capabilities, because the need to flexibly deal with a large number of intermediate data.
At the beginning of some modules I am also happy to write SQL statements, and then gradually tired, back to the previous time in C # when the use of reflection to build a SQL query constructor, until the discovery of LINQ, and then abandoned the plan, of course, Microsoft later launched the entity Framework, These are something, and now I am not interested in Microsoft things very much, well, a lot more, the following continues the text.
By the way, excellent blog program Drupal also used a similar query constructor for database query, to avoid directly write SQL statements, and do a little bit of the benefit is that can be a certain degree of shielding platform dependencies, for the database migration is helpful.
However, I introduced today's database-assisted class Query Builder is a very simple dongdong, even limited to SQLite database, if there are child shoes interested can be perfected, I currently as long as the operation of SQLite conveniently on it, for the larger database applications directly on the ORM bar.
First look at the code:
Copy Code code as follows:
Import Sqlite3
# ***************************************************
# *
# * Description:python Operation SQLite3 Database helper class (query builder)
# * 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 manipulate the conditional statement section of the SQL Constructor helper class
For example:
Datacondition (("=", "and"), id = 26)
Datacondition (("=", "and"), True, id = 26)
"""
def __init__ (self, operator = ("=", "and"), Ingroup = True, **kwargs):
"""
Construction method
Parameters:
operator operator, divided into (expression operator, conditional operator)
Ingroup are grouped, and if grouped, enclose in parentheses
Kwargs a key-value tuple that contains the column names and values of the database tables
Note that the equals number here is not equal to the actual generated SQL statement symbol
The actual symbol is controlled by operator[0]
For example:
Datacondition (("=", "and"), id = 26)
(id=26)
Datacondition ((">", "OR"), id = num, age = 35)
(id>26 OR age>35)
Datacondition ("Like", "OR"), False, name = "John", company = "Google")
Name like ' John ' OR Inc. 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 Auxiliary class
"""
def __init__ (self, filename):
"""
Construction method
Parameter: filename is SQLite3 database filename
"""
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, and note that if you do not explicitly call this method,
When the class is reclaimed, it also attempts to invoke the
"""
If Hasattr (self, "connection") and Self.connection:
Self.connection.close ()
def __del__ (self):
"""
Deconstruction method, do some cleanup work
"""
Self.close ()
def commit (self):
"""
Commit a transaction
The SELECT statement does not require this action, and the default Execute method's
Commit_at_once set to true implicitly calls this method,
Otherwise, you need to display the call to this method.
"""
Self.connection.commit ()
def execute (self, sql = None, commit_at_once = True):
"""
Execute SQL statement
Parameters:
The SQL statement to execute for SQL, or none, calls the constructor-generated SQL statement.
Commit_at_once whether to commit the transaction immediately, if not submitted immediately,
For non-query operations, you need to invoke commit explicit commits.
"""
If not sql:
sql = Self.sql
Self.cursor.execute (SQL)
If commit_at_once:
Self.commit ()
def fetchone (self, sql = None):
"""
Take a record
"""
Self.execute (SQL, False)
Return Self.cursor.fetchone ()
def fetchall (self, sql = None):
"""
Take All records
"""
Self.execute (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 for the query, with multiple table names 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 you to refer to it:
Copy Code code as follows:
db = Datahelper ("/home/wangye/sample.db3")
Db.open () # Open Database
Db.execute ("" "
CREATE TABLE [Staffs] (
[STAFF_ID] INTEGER PRIMARY KEY AutoIncrement,
[Staff_name] TEXT not NULL,
[Staff_cardnum] TEXT not NULL,
[Staff_reserved] INTEGER not NULL
)
"" ") # Execute SQL statement directly, note here Commit_at_once defaults to 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 ()
# Just take out all staff_id and Staff_name
rs = db.table ("Staffs"). Select ("Staff_name"). where (Datacondition (("=", "and"), id = 1). Fetchone ()
# Take a staff_name with a staff_id of 1
rs = db.table ("Staffs"). Select ("Staff_name"). WHERE (Datacondition ("<", "and"), id = m), datacondition (("=", "and" ), staff_reserved = 1). Fetchone ()
# take a staff_name record with an ID less than 100 and staff_reserved to 1
Db.close () # Close Database
The
does not currently have the asterisk (*) operator supported, and is not handled well in multiple tables with column operations, which is used only for day-to-day simple scripting operations, preferably not in a production environment, because there may be an unknown problem.
