Python Learning-writing Your own ORM (2)

The previous article simply implemented the ORM (Object Relational model), this article mainly implements the simple MySQL database operation.

To operate the database, first establish a database connection. The following defines a function that creates a database connection, and gets a connection called the engine.

def create_engine (user,password,database,host= ' 127.0.0.1 ', port=3306,**kw):    import mysql.connector    Global Engine    If engine isn't None:        raise Dberror (' engine is already initialized. ')    params = Dict (user=user,password=password,database=database,host=host,port=port)    defaults = Dict (use_unicode= true,charset= ' UTF8 ', collation= ' utf8_general_ci ', Autocommit=false)    #print ('%s%s%s%s ')% (User,password, Database,host,port) for    k,v in Defaults.iteritems ():        params[k] = Kw.pop (k,v)    params.update (kw)    params[' buffered ' = True    engine = Mysql.connector.connect (**params)    cursor = Engine.cursor ()

With the connection, you can manipulate the database. Several functions are written below to query and insert the database.

def _select (Sql,first,*args): cursor = None sql = sql.replace ('? ', '%s ') global engine try:cursor = ENG Ine.cursor () Cursor.execute (Sql,args) If cursor.description:names = [x[0] for x in CURSOR.DESCRI            Ption] If first:values = Cursor.fetchone () if not Values:return None            Return Dict (names,values) return [Dict (names,x) for x in Cursor.fetchall ()] finally:if cursor: Cursor.close () def select_one (Sql,*args): Return _select (Sql,true,*args) def select (Sql,*args): Return _select (S Ql,false,*args) def _update (Sql,*args): cursor = None global engine sql = Sql.replace ('? ', '%s ') print SQL TR        Y:cursor = Engine.cursor () cursor.execute (Sql,args) R = Cursor.rowcount Engine.commit () return r finally:if cursor:cursor.close () def insert (table,**kw): cols, args = Zip (*kw.iteritem s ()) sql = ' Insert inTo%s (%s) values (%s) '% (table, ', '. Join (['%s '% col for Col in Cols]), ' ". Join (['? ' For I in Range (len (cols))]) print (' SQL%s args%s '% (SQL, str (args)) ') return _update (Sql,*args)

Here, the basic database operation has been completed. But, according to Liaoche's tutorial, this is far from enough.

• If you want to implement multiple operations in a database connection, the code above is inefficient, and you will need to reassign a connection if you do not play one statement at a time.
• It is equally inefficient to perform multiple operations in a single transaction.
• If the server allocates a thread for different user database requests, the connection is available for use in the process. This is the problem, causing the database operation to be abnormal.

For the third problem, you should make each connection owned by each thread, and other threads cannot access it, using threading.local. First define a class to hold the context of the database:

Class _dbctx (threading.local):    def __init__ (self):        self.connection = None        self.transactions = 0    def Is_init (self):        return not Self.connection was None    def init (self):        self.connection = engine # Create a database connection        self.transactions = 0    def cleanup (self):        self.connection.cleanup ()        self.connection = None    def Cursor (self):        return Self.connection.cursor ()

The above code has an error. Because Python's assignment statement simply passes a reference to an object to a variable, as in the code above, self.connection = engine in the init function. Indicates that both self.connection and engine point to a database connection object. If the self.connection is given to cleanup, then the object that the engine points to is also cleanup. is an example:

A is a reference to the class Foo instance, after executing b=a, when executing B.clean (), it should be only B's V value is changed to 0, but execution A.V finds that the value of V also becomes 0.

The following is the final code, just encapsulates the bottom-level database operations, the code is also written very much, although the code is modeled Liaoche.

#-*-Coding:utf-8-*-import time, uuid, Functools, threading, Loggingclass Dict (Dict): "Simple Dict    Access as x.y style. "Def __init__ (Self, names= (), values= (), **kw): Super (Dict, self). __init__ (**kw) for K, V in zip (names , values): self[k] = v def __getattr__ (self, key): Try:return Self[key] except key         Error:raise Attributeerror (r "' Dict ' object have no attribute '%s '"% key) def __setattr__ (self, Key, value): Self[key] = Valueclass dberror (Exception): Passclass multicolumnserror (Exception): Passengine = Noneclass _d Bctx (threading.local): def __init__ (self): self.connection = None self.transactions = 0 def is_init (SE  LF): Return not Self.connection was None def init (self): self.connection = Engine self.transactions = 0 def cleanup (self): self.connection = None def cursor (self): return Self.connection.cursor () dEF create_engine (user,password,database,host= ' 127.0.0.1 ', port=3306,**kw): Import mysql.connector Global engine if    Engine is not a none:raise dberror (' engine is already initialized. ') params = dict (user=user,password=password,database=database,host=host,port=port) defaults = Dict (Use_unicode=true, charset= ' UTF8 ', collation= ' utf8_general_ci ', Autocommit=false) #print ('%s%s%s%s ')% (User,password,database,host,     Port) for k,v in Defaults.iteritems (): params[k] = Kw.pop (k,v) params.update (kw) params[' buffered '] = True  Engine = Mysql.connector.connect (**params) print type (engine) _db_ctx = _dbctx () class _connectionctx (object): Def            __enter__ (self): Self.should_cleanuo = False if not _db_ctx.is_init (): cursor = Engine.cursor () _db_ctx.init () Self.should_cleanup = True return self def __exit__ (self,exctype,excvalue,t Raceback): If Self.should_cleanup: _db_ctx.cleanup () def With_connection (func): @functools. Wraps (func) def _wrapper (*args,**kw): With _connectionctx (): RE    Turn func (*args, **kw) return _wrapperdef _select (sql,first,*args): cursor = None sql = sql.replace ('? ', '%s ')            Global _db_ctx try:cursor = _db_ctx.cursor () cursor.execute (Sql,args) if cursor.description:  names = [x[0] for x in cursor.description] if first:values = Cursor.fetchone () if not Values:return None return Dict (names,values) return [Dict (names,x) for x in Cursor.fetc Hall ()] Finally:if cursor:cursor.close () @with_connectiondef Select_one (Sql,*args): Return _selec  T (Sql,true,*args) @with_connectiondef select_int (Sql,*args): D = _select (Sql,true,*args) If Len (d)! = 1:raise    Multocolumnserror (' Except only one column. ') return d.values () [0] @with_connectiondef Select (Sql,*args): Global engine Print type (engine)    Return _select (Sql,false,*args) @with_connectiondef _update (Sql,*args): cursor = None global _db_ctx sql = sq L.replace ('? ', '%s ') print SQL Try:cursor = _db_ctx.cursor () cursor.execute (Sql,args) R = Curso R.rowcount Engine.commit () return R finally:if cursor:cursor.close () def insert (table, **KW): cols, args = Zip (*kw.iteritems ()) sql = ' insert into%s (%s) ' Values (%s) '% (table, ', '. "Join (['%s '% col for Co L in cols]), ', '. Join (['? ' For I in Range (len (cols)])) ' Print (' sql%s args%s '% (SQL, str) ') Return _update (sq L,*args) Create_engine (user= ' root ', password= ' z5201314 ', database= ' test ') u1 = Select_one (' select * from user where id=? ') , 1) print ' U1 ' Print u1print ' start selet () ... ' U2 = Select (' select * from user ') for item in U2:print ('%s%s '% (item.na me,item.id)) print ' name:%s ID:%s '% (u1.name,u1.id)

Python Learning-writing Your own ORM (2)