Pyton implements SQLHelper and pyton implements sqlhelper

Pyton implements SQLHelper and pyton implements sqlhelper

After reading Liao's tutorial, I realized this module and implemented it according to my own ideas. The code is included.

1 import MySQLdb, logging, threading, time, uuid 2 logging. basicConfig (level = logging. INFO, format = '% (asctime) s % (filename) s [line: % (lineno) d] % (levelname) s % (message) s ', datefmt = '% a, % d % B % Y % H: % M: % s') 3 4 def next_id (t = None ): 5 ''' 6 Return next id as 50-char string. 7 8 Args: 9 t: unix timestamp, default to None and using time. time (). 10''' 11 if t is None: 12 t = time. time () 13 return '% 015d % s000' % (Int (t * 1000), uuid. uuid4 (). hex) 14 15 class Dict (dict): 16 def _ init _ (self, names = (), values = (), ** args): 17 super (Dict, self ). _ init _ (** args) 18 for k, v in zip (names, values): 19 self [k] = v 20 def _ getattr _ (self, item): 21 try: 22 return self [item] 23 random t KeyError: 24 raise AttributeError ('It has no attribute named % s' % (str (item ),)) 25 def _ setattr _ (self, key, value): 26 self [key] = value 27 28 class dbcontext (threading. local): 29 def _ init _ (self): 30 self. db = MySQLdb. connect (host = "localhost", port = 3306, passwd = 'toor', user = 'root', db = 'xdyweb') 31 def getdb (self ): 32 return self. db 33 34 class conntection (object): 35 def _ init _ (self): 36 self. dbctx = dbcontext () 37 self. db = self. dbctx. getdb () 38 self. cursor = self. db. cursor () 39 def getcursor (self): 40 return self. cursor 41 def close (self): 42 self. db. close () 43 self. cursor = None 44 def _ select (SQL, first, * args): 45 conn = conntection () 46 csr = conn. getcursor () 47 SQL = SQL. replace ('? ',' % S') 48 values=csr.exe cute (SQL, * args) 49 try: 50 if csr. description: 51 names = [x [0] for x in csr. description] 52 if first: 53 values = csr. fetchone () 54 if values is None: 55 return None 56 return Dict (names, values) 57 return [Dict (names, x) for x in csr. fetchall ()] 58 finally: 59 conn. close () 60 61 def select (SQL, first, * args): 62 return _ select (SQL, first, * args) 63 def select_one (SQL, pk): 64 return _ Select (SQL, True, pk) 65 66 def update (SQL, * args): 67 r'''68 Execute update SQL. 69 70 >>> u1 = dict (id = 1000, name = 'Michael ', email = 'Michael @ test.org', passwd = '000000', last_modified = time. time () 71 >>> insert ('user', ** u1) 72 1 73 >>> u2 = select_one ('select * from user where id =? ', 1000) 74 >>> u2.email 75 U' michael @ test.org '76 >>> u2.passwd 77 u'000000' 78 >>> update ('Update user set email = ?, Passwd =? Where id =? ', 'Michael @ example.org', '000000', 654321) 79 1 80 >>> u3 = select_one ('select * from user where id =? ', 1000) 81 >>> u3.email 82 U' michael @ example.org '83 >>> u3.passwd 84 u'000000' 85 >>> update ('Update user set passwd =? Where id =? ',' *** ', '2017 \' or id = \ '000000') 86 0 87 ''' 88 conn = conntection () 89 csr = conn. getcursor () 90 SQL = SQL. replace ('? ',' % S') 91 92 try: 93 csr.exe cute (SQL, args) 94 return csr. rowcount 95 finally: 96 conn. close () 97 def insert (table, ** kw): 98 '''99 Execute insert SQL .100 101 >>> u1 = dict (id = 2000, name = 'bob ', email = 'Bob @ test.org ', passwd = 'bobob', last_modified = time. time () 102 >>> insert ('user', ** u1) 103 1104 >>>> u2 = select_one ('select * from user where id =? ', 2000) 105 >>>> u2.name106 u'bob' 107 >>> insert ('user', ** u2) 108 Traceback (most recent call last): 109... 110 IntegrityError: 1062 (23000): Duplicate entry '000000' for key 'Primary '000000' '2000 cols, args = zip (* kw. iteritems () 113 SQL = 'insert into '% s' (% s) values (% s)' % (table ,','. join ([''% s' % col for col in cols]), ','. join (['? 'For I in range (len (cols)]) 114 logging.info (SQL) 115 return update (SQL, * args) 116 117 def main (): 118 # db = MySQLdb. connect (host = "localhost", port = 3306, passwd = 'toor', user = 'root', db = 'xdyweb') 119 120 # conn = conntection () 121 # c = conn. getcursor () 122 # r=c.exe cute ("select * from users where email = % s", ('sss @ sss. ss',) 123 # logging. warning (r) 124 125 # f = _ select ('select * from users where email =? ', False, ('sss @ sss. sss',) 126 # logging.info (f) 127 # pass128 129 u1 = dict (id = 2000, name = 'bob', email = 'Bob @ test.org ', passwd = 'bobob', last_modified = time. time () 130 r = insert ('user', ** u1) 131 logging.info (r) 132 if _ name __= = "_ main __": 133 main ()View Code