pymysql的使用
pymysql和MySqlDB的用法,大同小异,官方文档也只是给个简单的例子,看一看很容易明白:
CREATE TABLE `users` ( `id` int(11) NOT NULL AUTO_INCREMENT, `email` varchar(255) COLLATE utf8_bin NOT NULL, `password` varchar(255) COLLATE utf8_bin NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1 ; import pymysql.cursors # Connect to the database connection = pymysql.connect(host='localhost', user='user', password='passwd', db='db', charset='utf8mb4', cursorclass=pymysql.cursors.DictCursor) try: with connection.cursor() as cursor: # Create a new record sql = "INSERT INTO `users` (`email`, `password`) VALUES (%s, %s)" cursor.execute(sql, ('webmaster@python.org', 'very-secret')) # connection is not autocommit by default. So you must commit to save # your changes. connection.commit() with connection.cursor() as cursor: # Read a single record sql = "SELECT `id`, `password` FROM `users` WHERE `email`=%s" cursor.execute(sql, ('webmaster@python.org',)) result = cursor.fetchone() print(result) finally: connection.close()
我自己总结了一段代码,可以在工程中使用:
#!/usr/bin/python # -*- coding: utf-8 -*- import pymysql.cursors from six import itervalues class mysqlCs(object): def __init__(self): try: self.conn = pymysql.connect( host='localhost', user='spider', password='spiderPass', port=5636, db='spiderdatas', charset='utf8mb4', cursorclass=pymysql.cursors.DictCursor ) self.cur = self.conn.cursor() except Exception as ex: exit(ex) def run(self, sql): try: effect_rows = self.cur.execute(sql) print("effect rows :", effect_rows) return self.cur.fetchall() except Exception as ex: print(ex) def tabExists(self, table): sql = "SELECT COLUMN_NAME from information_schema.`COLUMNS` where TABLE_NAME='%s'"% table try: effect_rows = self.cur.execute(sql) if effect_rows > 0: return self.cur.fetchall() return False except: return False def insertstr(self, table, data, seprater=","): if type(data) != str: exit("Must be str ...") try: sql = "INSERT INTO %s VALUES (%s)" % (table, ','.join(['%s']*(data.count(seprater) + 1))) effect_rows = self.cur.execute(sql, data.split(seprater)) self.conn.commit() return effect_rows except Exception as e: print(e) return False def insertdic(self, table, dic): if type(dic) != dict: exit("Must be dict ...") try: __key = ','.join([i for i in dic.keys()]) __cols = ','.join(['%s']*len(dic)) sql = "INSERT INTO %s(%s) VALUES (%s)" % (table, __key, __cols) effect_rows = self.cur.execute(sql, list(itervalues(dic))) self.conn.commit() return effect_rows except Exception as e: print(e) return False def insertlist(self, table, datas): if type(datas) != list: exit("Must be list...") ret = self.tabExists(table) if ret: cols = [i['COLUMN_NAME'] for i in ret] if type(datas[0]) == list: try: sql = "INSERT INTO %s(%s) VALUES (%s)" % (table, ','.join(cols), ','.join(['%s']*len(cols))) effect_rows = self.cur.executemany(sql, datas) self.conn.commit() return effect_rows except Exception as ex: self.conn.rollback() print(ex) return False elif type(datas[0]) == dict: for d in datas: self.insertdic(table, d) else: try: sql = "INSERT INTO %s VALUES (%s)" % (table, ','.join(['%s']*len(datas))) effect_rows = self.cur.execute(sql, datas) self.conn.commit() return effect_rows except Exception as ex: print(ex) return False else: exit("table %s not exists ..." % table) p = mysqlClient()