Python encapsulates dbutils and Pymysql instances

Source: Internet
Author: User
Before a Python package dbutils and Pymysql wrote a basedao.py, in recent days and re-organized the next thoughts, optimized the next basedao.py, the current support methods are not many, follow-up will be improved, add.

Main functions:

1. Querying a single object:

Required Parameters: Table name, filter criteria

2. Querying multiple objects:
Required Parameters: Table name, filter criteria

3. Press the primary key to query:
Required Parameters: Table name, value

4. Paging query:
Required Parameters: Table name, page number, number of records per page, filter criteria

The specific code is as follows:  

  1 import JSON, OS, SYS, TIME 2 3 import pymysql 4 from dbutils import POOLEDDB 5 6 class Basedao (object): 7  "" "8 Simple database Operation base Class 9" "" __config = {} # Database connection configuration one by one __conn = None # Database connection __cursor = none # database Cursor __database = none # for temporary sonjuku query database __tablen           ame = None # used to temporarily store query table name __fields = [] # field list for temporary storage of query Tables __primarykey_dict = {} # PRIMARY key for all tables in the database stored in the configuration. Def __init__ (self, creator=pymysql, host= "localhost", User=none, password= "", Da Tabase=none, port=3306, charset= "UTF8"): If host is none:20 raise Exception ("Parameter [host] is N One. ")              If user is none:22 raise Exception ("Parameter [user] is None.") If password is none:24 Raise Exception ("Parameter [password] is None"), if database is none:26 raise Excepti On ("Parameter [daTabase] is None. ") If Port is none:28 raise Exception ("Parameter [port] is None.") Self.__config = Dict ({ "Creator": Creator, "CharSet": CharSet, "host": Host, "Port":p ort, "user": Use         R, "password":p assword, "database":d atabase) Self.__conn = Pooleddb.connect (**self.__config) 35 Self.__cursor = Self.__conn.cursor () self.__database = self.__config["Database"] Notoginseng self.__init_p Rimarykey () get_time print (), "database connection initialization succeeded." ") __del__ def (self): 41 ' Overriding the method called when the class is cleared ' if self.__cursor:43 self.__curs             Or.close () + print (Get_time (), "cursor off") if self.__conn:46 self.__conn.close () 47 Print (Get_time (), "Connection off") Select_one def (self, Tablename=none, filters={}): 50 "51 queries Single object @tableName table name @filters filter conditions @returnReturns a collection of dictionaries, with table fields as key, field values as value, "Self.__check_params" (tableName), and sql = Self.__query_ut Il (filters) self.__cursor.execute (sql) result = Self.__cursor.fetchone () return SELF.__PA         Rse_result (Result) select_pk def (self, Tablename=none, Primarykey=none): 63 "64 Press primary key 65 @tableName table name @primaryKey primary key value "Self.__check_params" (tableName) filters = {} filters.setdefault (str (self.__primarykey_dict[tablename]), primaryKey) (= Self.__query_util ( filters) Self.__cursor.execute (sql), result = Self.__cursor.fetchone (), return Self.__parse         _result (Result) Select_all def (self, Tablename=none, filters={}): 77 "78 Query all 79         @tableName Table name @filters filter conditions Bayi @return Returns a dictionary collection with table fields as key and field values as value 82 "' 83 Self.__check_params (tablENAME) + sql = Self.__query_util (filters) self.__cursor.execute (SQL) results = Self.__curso          R.fetchall () Self.__parse_results return (results) (Tablename=none): 90 "91 Statistics record number of "Self.__check_params" (tableName) 94 sql = "SELECT COUNT (*) from%s"% (self._  _tablename) Self.__cursor.execute (sql), result = Self.__cursor.fetchone (), return result[0]         98 Select_page (self, tablename=none, pagenum=1, limit=10, filters={}): 100 "101 Paging 102 @tableName Table name 103 @return Returns a Dictionary collection with table fields as key and field values as value104 ' Self.__check_params (tablen AME) 106 TotalCount = Self.count () 107 If Totalcount/limit = 0:108 Totalpage = totalcount/             limit109 else:110 totalpage = totalcount//limit + 1111 if pagenum > totalpage:112 Print ("Max pages%d"%totAlpage) 113 Pagenum = totalPage114 elif pagenum < 1:115 print ("Number of pages cannot be less than 1") 116         Pagenum = 1117 Beginindex = (pageNum-1) * limit118 filters.setdefault ("_limit_", (Beginindex, limit)) 119 sql = Self.__query_util (filters) self.__cursor.execute (SQL) 121 results = Self.__cursor.fetchall ( ) 122 return Self.__parse_results (results) 123 124 def __parse_result (self, result): 125 ' for parsing a single query result, returning a dictionary Object ' 126 obj = {}127 for k,v in zip (self.__fields, result): $ obj[k] = v129 return obj1  131 def __parse_results (self, results): 132 ' To parse multiple query results, return dictionary List object ' 133 OBJS = []134 for result In results:135 obj = Self.__parse_result (Result) 136 objs.append (obj) 137 return objs138 13 9 def __init_primarykey (self): 140 ' reads the primary key collection for all tables in the database according to the database in the configuration ' 141 sql = ' "" "SELECT table_name, Column_nam E142 FROM information_schema.columns143 WHERE column_key= ' PRI ' and table_schema= '%s ' "" "% (self.__database) 144             Self.__cursor.execute (SQL) 145 results = Self.__cursor.fetchall () 146 for result in results:147         Self.__primarykey_dict[result[0]] = result[1]148 149 def __query_fields (self, Tablename=none, Database=none): 150 ' Query the field list of the table, save the Queried field list in __fields ' 151 sql = "" "Select column_name152 from Information_sch ema.columns153 WHERE table_name = '%s ' and table_schema= '%s ' "" "% (tableName, database) 154 SELF.__CU Rsor.execute (SQL) 155 fields_tuple = Self.__cursor.fetchall () 156 self.__fields = [Fields[0] for fields in F ields_tuple]157 158 def __query_util (self, Filters=none): 159 "" "SQL Statement Stitching Method 161 @filters Filter Bar         Piece 162 "" "163 sql = R ' select #{fields} from #{table_name} WHERE 1=1 #{filters} ' 164 # Stitching query Table 165 sql = Sql.replace ("#{tAble_name} ", self.__tablename) 166 # Stitching query field 167 self.__query_fields (Self.__tablename, Self.__database) 168 Fields = ' 169 for field ' in self.__fields:170 fields + = field + ', ' 171 fields = fields[ 0:len (fields) -2]172 sql = Sql.replace ("#{fields}", fields) 173 # Stitching query condition (to be optimized) 174 if filters is None : 175 sql = Sql.replace ("#{filters}", "") 176 else:177 FILTERS = "" 178 If Not I Sinstance (Filters, dict): 179 Raise Exception ("Parameter [filters] must be dict type.")                 Ispage = False181 If Filters.get ("_limit_"): 182 ispage = True183 Beginindex, limit = Filters.get ("_limit_") 184 for K, V in Filters.items (): 185 If K.startswi Th ("_in_"): # stitching in186 FILTERS + = "and%s in ("% (k[4:]) 187 values = V.split (",") 188 for VAlue in values:189 FILTERS + = "%s,"%value190 FILTERS = Filters[0:len (FILTERS)-  1] + ")" 191 elif K.startswith ("_nein_"): # Stitching not in192 FILTERS + = "and%s                         Not in ("% (k[4:]) 193 values = V.split (", ") 194 for value in values:195                 FILTERS + = "%s,"%value196 FILTERS = Filters[0:len (FILTERS)-1] + ")" 197                  Elif K.startswith ("_like_"): # stitching like198 FILTERS + = "and%s like '%%%s%% '"% (k[6:], v) 199 Elif K.startswith ("_ne_"): # stitching is not equal to FILTERS + = "and%s! = '%s '"% (k[4 :], v) 201 elif k.startswith ("_lt_"): # stitching less than 202 FILTERS + = "and%s < '% S ' "% (k[4:], v) 203 elif k.startswith (" _le_ "): # Stitching less than equals 204 FILTERS + =" an D%s <= '%s ' "% (k[4:], V) 205 elif k.startswith ("_gt_"): # Stitching greater than 206 FILTERS + = "and%s > '%s  '% (k[4:], v) 207 elif k.startswith ("_ge_"): # Stitching greater than equals 208 FILTERS + = "and %s >= '%s '% (k[4:], v) 209 elif K in self.__fields: # stitching equals FILT                 ERS + = "and%s = '%s '"% (k, v) 211 sql = Sql.replace ("#{filters}", FILTERS) 212 if ispage:213     SQL + = "Limit%d,%d"% (beginindex, limit) 214 215 print (Get_time (), SQL) 216 return sql217 218             def __check_params (self, TableName): 219 "220 Check parameters 221" "222 if tablename:223 Self.__tablename = tableName224 else:225 If Self.__tablename is none:226 raise Except Ion ("Parameter [TableName] is None.") 227 228 def get_time (): 229 return Time.strftime ("%y-%m-%d%h:%m:%s", Time.localtime ()) 231 if __name__ = = "__main__": 232 config = {233 # "creator": pymysql,234 # "host": "127.0.0.1", 235 "user": "Root", 236 "password": "Root", 237 "database": "Test", 238 # "Port": 3306,239 # "chars Et ": ' UTF8 '}241 base = Basedao (**config) 242 ############################################################# ########## #243 # user = Base.select_one ("user") 244 # Print (user) 245 ########################################## ############################# #246 # users = Base.select_all ("user") 247 # Print (users) 248 ##################### ################################################## #249 # filter1 = {2 # "sex": 0,251 # "_in_id": "1,, 3,4,5 ", 252 #" _like_name ":" Zhang ", 253 #" _ne_name ":" Wangwu "254 #}255 # user_filters = Base.select _all (tablename= "user", Filters=filter1), page # print (user_filters) 257 ############################################## ######################### #258 # menu = Base.select_one (tablename= "menu") 259 # Print (menu) 260 ######################################### ############################## #261 # user_pk = BASE.SELECT_PK ("User", 2) 262 # Print (USER_PK) 263 ##############     ######################################################### #264 # filter2 = {265 # "_in_id": "1,2,3,4", 266 # "_like_name": "Test" 267 #}268 # user_limit = base.select_page ("User", 2, Filter2) #未实现269 # print (use R_limit) 270 ########################################################################
View Code

  There are a few specific examples in the code that you can refer to for use.

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.