Contact Python time is not very long, recently there is a project to analyze data, so the use of Python as a programming language, in addition to the language characteristics of the main focus on Python for the SQLite3 database good support, because the need for flexible processing of large amounts of intermediate data.
Just started some modules I also bored to write SQL statements, and then gradually tired of, recall that in the past when the use of C # using reflection to build a SQL query constructor, until the discovery of LINQ, so abandoned the plan, of course, Microsoft later launched the entity Framework, These are something, and now my interest in Microsoft's things is not very big, well, much more, the following continue the text.
By the way, a good blogging program Drupal also uses a similar query constructor for database queries, to avoid writing SQL statements directly, in addition to do a little bit of the benefit is that a certain degree of shielding platform dependency, for the database migration is still helpful.
But today I introduced the database auxiliary class query constructor is a very simple thing, or even limited to SQLite database, if there are children shoes interested can be perfected under, I now as long as the operation of SQLite handy can, for the larger database application directly on the ORM bar.
Look at the code first:
Copy the 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 statements 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 whether grouping, if grouped, will be enclosed in parentheses
Kwargs key value tuple containing the column name and value of the database table
Note that the equals number here is not equal to the actual generation of the SQL statement symbol
The actual symbol is controlled by operator[0]
For example:
Datacondition (("=", "and"), id = 26)
(id=26)
Datacondition ((">", "OR"), id = +, 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):
"""
Construction method
Parameter: filename is SQLite3 database file name
"""
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 recycled, it also attempts to invoke the
"""
If Hasattr (self, "connection") and Self.connection:
Self.connection.close ()
def __del__ (self):
"""
destructor, do some cleanup work
"""
Self.close ()
def commit (self):
"""
Commit a transaction
The SELECT statement does not require this action, the default Execute method's
Commit_at_once is set to True to call this method implicitly,
Otherwise, this method needs to be displayed.
"""
Self.connection.commit ()
def execute (self, sql = None, commit_at_once = True):
"""
Execute SQL statement
Parameters:
The SQL statement that SQL executes, or none, invokes the SQL statement generated by the constructor.
Commit_at_once if the transaction is committed immediately, if it is not immediately committed,
For non-query operations, you need to invoke commit explicit commit.
"""
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):
"""
Set 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:
Copy the 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
)
"") # executes the SQL statement directly, note that 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 ()
# Remove all staff_id and staff_name directly
rs = db.table ("Staffs"). Select ("Staff_name"). where (Datacondition (("=", "and"), id = 1). Fetchone ()
# Take a staff_id of 1 staff_name
rs = db.table ("Staffs"). Select ("Staff_name"). where (Datacondition (("<", "and"), id = +), datacondition (("=", "and" ), staff_reserved = 1). Fetchone ()
# take a staff_name record with an ID of less than 100 and a staff_reserved of 1
Db.close () # Close the database
There is no support for the asterisk (*) operator at this time, and it is not very well handled in the multi-table same-name operation, which is only used for everyday simple script operations, preferably not in production environments, because there may be an unknown problem.
