Python SQLite3 Database Operations class sharing _python

Source: Internet
Author: User
Tags sqlite sqlite database

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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.