1 #!/usr/bin/env python
2 #-*- coding: utf-8 -*-
3
4 importpymysql5 from utils importNotImplementedError6
7 '''
8 本檔案是基于mysql實作的一個ORM架構9 '''
10
11 classMysqlConnector(object):12 '''Python與mysql的連接配接器'''
13
14 def __init__(self, host, port, username, password, db):15 conn = pymysql.connect(host=host, port=port, user=username,16 passwd=password, db=db, use_unicode=True, charset="utf8")17 self.conn =conn18 self.cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)19
20 defexecute(self, sql_msg):21 '''
22 執行sql語句23 :param sql_msg: sql語句，字元串格式24 :return:25 '''
26 ret =self.cursor.execute(sql_msg)27 self.conn.commit()28 returnret29
30 defclose(self):31 '''關閉連接配接器'''
32 self.cursor.close()33 self.conn.close()34
35 classBaseModel(object):36 '''
37 實作将Python語句轉換為sql語句，配合MysqlConnector實作表的建立以及資料的增删查改等操作。38 建立表時： 支援主鍵PRIMARY KEY，索引INDEX，唯一索引UNIQUE，自增AUTO INCREMENT 外鍵語句39 建立的表引擎指定為InnoDB，字元集為 utf-840 增删查改： 支援WHERE [LIKE] LIMIT語句41 其子類必須設定initialize方法，并在該方法中建立字段對象42 '''
43 def __new__(cls, *args, **kwargs):44 _instance = super().__new__(cls)45 _instance.initialize()46 return_instance47
48 def __init__(self, table_name, sql_connector):49 '''
50 :param table_name: 要建立的表名51 :param sql_connector: MysqlConnector執行個體對象52 '''
53 self.table_name =table_name54 self.fields =[]55 self.primary_key_field =None56 self.uniques_fields =[]57 self.index_fields =[]58 self.is_foreign_key_fields =[]59 self.sql_connector =sql_connector60 self._create_fields_list()61 self.create_table()62
63 definitialize(self):64 '''BaseModel的每個子類中必需包含該方法，且在該方法中定義字段'''
65 raise NotImplementedError("Method or function hasn't been implemented yet.")66
67 def_create_fields_list(self):68 '''建立list用來存儲表的字段對象'''
69 for k, v in self.__dict__.items():70 ifisinstance(v, BaseField):71 self.fields.append(v)72 v.full_column = '%s.%s' %(self.table_name, v.db_column)73 v.table_name =self.table_name74 for field inself.fields:75 iffield.primary_key:76 self.primary_key_field =field77 iffield.unique:78 self.uniques_fields.append(field)79 iffield.db_index:80 self.index_fields.append(field)81 iffield.is_foreign_key:82 self.is_foreign_key_fields.append(field)83
84 def_has_created(self):85 '''檢測表有沒有被建立'''
86 self.sql_connector.cursor.execute('SHOW TABLES;')87 ret =self.sql_connector.cursor.fetchall()88 for table inret:89 for k, v intable.items():90 if v ==self.table_name:91 returnTrue92
93 def_create_table(self):94 ret = 'CREATE TABLE %s (' %self.table_name95 for v inself.fields:96 ret +=v.generate_field_sql()97 ret = '%s%s%s%s%s' %(ret, self._generate_primary_key(),98 self._generate_unique(), self._generate_index(),99 self._generate_is_foreign_key())100 ret = ret[:-1] + ')ENGINE=InnoDB DEFAULT CHARSET=utf8;'
101 returnret102
103 defcreate_table(self):104 '''建立表'''
105 if notself._has_created():106 print('建立表：%s' %self.table_name)107 sql_msg =self._create_table()108 #print(sql_msg)
109 self.sql_connector.execute(sql_msg)110
111 def_generate_primary_key(self):112 '''生成sql語句中的 primary key 語句'''
113 ret = ''
114 ifself.primary_key_field:115 ret = 'PRIMARY KEY(%s),' %self.primary_key_field.db_column116 returnret117
118 def_generate_is_foreign_key(self):119 ret = ''
120 ifself.is_foreign_key_fields:121 for field inself.is_foreign_key_fields:122 ret += 'FOREIGN KEY(%s) REFERENCES %s(%s) ON DELETE %s ON UPDATE %s,' %(field.db_column,123 field.model_obj.table_name,124 field.model_obj.primary_key_field.db_column,125 field.on_delete,126 field.on_delete )127 returnret128
129 def_generate_unique(self):130 '''生成sql語句中的 unique 語句'''
131 ret = ''
132 ifself.uniques_fields:133 ret = 'UNIQUE ('
134 for field inself.uniques_fields:135 ret += '%s,' %field.db_column136 ret = ret[:-1]137 ret += '),'
138 returnret139
140 def_generate_index(self):141 index = ''
142 ifself.index_fields:143 index = 'INDEX ('
144 for field inself.index_fields:145 index += '%s,' %field.db_column146 index = index[:-1]147 index += '),'
148 returnindex149
150 def _generate_where(self, condition={}):151 '''
152 根據條件生成 where 語句153 :param condition: 一個dict，key是字段對象，value是條件(比如 'WHERE ID=3',那麼value就是'=3')154 :return:155 '''
156 where = ''
157 ifcondition:158 where = 'WHERE'
159 for k, v incondition.items():160 v =v.strip()161 offset = 1
162 if v.startswith('l'):163 offset = 4
164 if notk.is_str:165 where += '%s %s and' %(k.db_column, v)166 else:167 where += '%s %s "%s" and' %(k.db_column, v[:offset], v[offset:].strip())168 where = where[:-3]169 returnwhere170
171 def select_items(self, counts=0, select_fields=[], condition={}, join_conditions=[]):172 '''
173 根據condition 對表進行select，并 LIMIT counts174 :param counts:175 :param condition:176 :return:177 '''
178 join_length =len(join_conditions)179 counts_sql = ''
180 join_sql = ''
181 select_fields_sql = ''
182 where =self._generate_where(condition)183 ifcounts:184 counts_sql = 'LIMIT %s' %counts185 if notselect_fields:186 select_fields_sql = '*'
187 ifjoin_conditions:188 tables_order = list(list(zip(*join_conditions))[0])189 tables_order.insert(0, self)190 for i inselect_fields:191 select_fields_sql += '%s,' %i.full_column192 for n inrange(join_length):193 one_join_condition =join_conditions[n]194 if n ==0:195 base_table =tables_order[0].table_name196 else:197 base_table = ''
198 bracket_counts = join_length - n - 1
199 join_sql += '%s %s LEFT JOIN %s on %s=%s%s' %(200 bracket_counts*'(', base_table, tables_order[n+1].table_name,201 one_join_condition[1].full_column, one_join_condition[2].full_column,202 bracket_counts * ')', )203 else:204 for i inselect_fields:205 select_fields_sql += '%s,' %i.db_column206 join_sql =self.table_name207 select_fields_sql = select_fields_sql[:-1]208 select = 'SELECT %s FROM %s %s %s;' %(select_fields_sql, join_sql, where, counts_sql)209 #print('----------------', select)
210 self.sql_connector.execute(select)211 result =self.sql_connector.cursor.fetchall()212 returnresult213
214 def insert_item(self, data={}):215 '''
216 向表中插入一行217 :param data: 一個dict，key是字段對象，value則是值218 :return:219 '''
220 insert = 'INSERT INTO %s (' %self.table_name221 value = '('
222 ifdata:223 for k, v indata.items():224 insert += '%s,' %k.db_column225 ifk.is_str:226 value += '"%s",' %v227 else:228 value += '%s,' %v229 #print('value is ',value)
230 insert = insert[:-1] + ') VALUES'
231 value = value[:-1] + ');'
232 insert +=value233 #print('......',insert)
234 self.sql_connector.execute(insert)235
236 def delete_item(self, condition={}):237 '''删除符合condition的條目'''
238 delete = 'DELETE FROM %s' %self.table_name239 where =self._generate_where(condition)240 delete +=where241 #print(delete)
242 self.sql_connector.execute(delete)243
244 def update_item(self, data={}, condition={}):245 '''将符合condition的條目修改為data'''
246 update = 'UPDATE %s' %self.table_name247 data_statement = ''
248 ifdata:249 data_statement = 'SET'
250 for k, v indata.items():251 if notk.is_str:252 data_statement += '%s=%s,' %(k.db_column, v)253 else:254 data_statement += '%s="%s",' %(k.db_column, v)255 data_statement = data_statement[:-1]256 where =self._generate_where(condition)257 update += data_statement +where258 #print('---------',update)
259 self.sql_connector.execute(update)260
261 def get_field_value(self, field, condition={}):262 ret = self.select_items(condition=condition)263 #print(ret)
264 if len(ret) == 1:265 value =ret[0][field.db_column]266 elif len(ret) > 1:267 value =[]268 for i inret:269 value.append(i[field.db_column])270 else:271 value = ''
272 #print('value is ',value)
273 returnvalue274
275 classBaseField(object):276 def __init__(self, db_column, null=True, blank=None, choice={},277 db_index=False, default=None, primary_key=False,278 unique=False, max_length=0, auto_increment=False,279 ):280 '''
281
282 :param db_column: 資料庫中表的字段名283 :param null: 該字段是否可以為空284 :param blank: 如果該字段為空，存儲什麼值285 :param choice: 該字段的值隻能是choice的一個286 :param db_index: 是否為該字段設定索引287 :param default: 該字段的預設值288 :param primary_key: 是否為該字段設定主鍵289 :param unique: 該字段值是否可以重複290 :param max_length： 該字段的最大長度291 :param auto_increment: 是否自增292 '''
293 self.db_column =db_column294 self.null =null295 self.blank =blank296 self.choice =choice297 self.db_index =db_index298 self.default =default299 self.primary_key =primary_key300 ifself.primary_key:301 self.null =False302 self.unique =unique303 self.max_length =max_length304 self.auto_increment =auto_increment305 self.is_foreign_key =False306
307 defgenerate_field_sql(self):308 pass
309
310 def_generate_null(self):311 if notself.null:312 null = 'NOT NULL'
313 else:314 null = 'NULL'
315 returnnull316
317 def_generate_default(self):318 default = ''
319 if self.default is notNone:320 ifself.is_str:321 default = 'DEFAULT "%s"' %self.default322 else:323 default = 'DEFAULT %s' %self.default324 returndefault325
326 def_generate_auto_increment(self):327 ret = ''
328 ifself.auto_increment:329 ret = 'AUTO_INCREMENT'
330 returnret331
332 classCharField(BaseField):333 def __init__(self, *args, **kwargs):334 super().__init__(*args, **kwargs)335 kwargs['blank'] = ''
336 if notself.max_length:337 self.max_length = 128
338 if notself.default:339 self.default =self.blank340 self.is_str =True341 self.field_type = 'CHAR'
342
343 defgenerate_field_sql(self):344 null =self._generate_null()345 default =self._generate_default()346 return '%s CHAR(%s) %s %s,' %(self.db_column, self.max_length, null, default)347
348 classIntField(BaseField):349 def __init__(self, *args, **kwargs):350 super().__init__(*args, **kwargs)351 self.is_str =False352 self.field_type = 'INT'
353
354 defgenerate_field_sql(self):355 null =self._generate_null()356 default =self._generate_default()357 auto_increment =self._generate_auto_increment()358 return '%s INT %s %s %s,' %(self.db_column, null, default, auto_increment)359
360 classForeignKeyField(BaseField):361 def __init__(self, db_column, model_obj, null=True, default=None, on_delete='CASCADE'):362 self.db_column =db_column363 self.model_obj =model_obj364 self.null =null365 self.default =default366 self.is_str =model_obj.primary_key_field.is_str367 self.reference =model_obj.primary_key_field368 self.on_delete =on_delete369 self.is_foreign_key =True370 self.primary_key =False371 self.unique =False372 self.db_index =False373
374 defgenerate_field_sql(self):375 null =self._generate_null()376 default =self._generate_default()377 return '%s %s %s %s,' %(self.db_column, self.model_obj.primary_key_field.field_type, null, default)378
379
380 Connector = MysqlConnector('127.0.0.1', 3306, 'root', '', 'test1')381
382
383 if __name__ == '__main__':384 classUserModel(BaseModel):385 definitialize(self):386 self.uid = IntField('uid', primary_key=True, auto_increment=True)387 self.account = IntField('account', unique=True, null=False)388 self.password = CharField('password', null=False)389 self.name = CharField('name', null=False)390 self.class_name = CharField('class_name', null=False)391 self.profession = CharField('profession', null=False)392 self.out_date_counts = IntField('out_date_counts', default=0)393
394 u = UserModel('user', Connector)