1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980818283848586878889909192 |
- #!/usr/bin/python
- # -*- coding:utf-8 -*-
- import sys
- import os
- sys.path.append(os.path.abspath('..'))
- from util.config import config
- import pymysql
- from sqlalchemy import create_engine
- class MysqlConfig(object):
- HOST = config.get('mysql', 'host')
- USER = config.get('mysql', 'user')
- PASSWORD = config.get('mysql', 'password')
- DB = config.get('mysql', 'db')
- engine = create_engine('mysql+pymysql://{}:{}@{}/{}?charset=utf8'.format(
- MysqlConfig.USER,
- MysqlConfig.PASSWORD,
- MysqlConfig.HOST,
- MysqlConfig.DB,
- ))
- class Mysql(object):
- def __init__(self):
- # 数据库构造函数,从连接池中取出连接,并生成操作游标
- # self._conn = Mysql.__GetConnect()
- self.host = MysqlConfig.HOST
- self.user = MysqlConfig.USER
- self.pwd = MysqlConfig.PASSWORD
- self.db = MysqlConfig.DB
- __pool = None
- # @staticmethod
- def __GetConnect(self):
- """
- @summary: 静态方法,从连接池中取出连接
- @return MySQLdb.connection
- """
- if not self.db:
- raise (NameError, "没有设置数据库信息")
- self.conn = pymysql.connect(host=self.host, user=self.user, password=self.pwd, database=self.db)
- db= self.conn
- if not db:
- raise (NameError, "连接数据库失败")
- else:
- return db
- def insert_batch(self, sql, data):
- db = self.__GetConnect()
- cur = db.cursor()
- try:
- cur.executemany(sql, data)
- db.commit()
- except Exception as e:
- db.rollback()
- print(e)
- finally:
- cur.close()
- db.close()
- def select_list(self, sql):
- db = self.__GetConnect()
- cur = db.cursor(cursor=pymysql.cursors.DictCursor)
- results = []
- try:
- cur.execute(sql)
- results = cur.fetchall()
- db.commit()
- # print(results)
- except Exception as e:
- print(e)
- finally:
- cur.close()
- db.close()
- return results
- if __name__ == '__main__':
- sql = 'select name from `ai-callcenter-dev`.callcenter_ai_terminology limit 10'
- mysql = Mysql()
- result = mysql.select_list(sql)
- for item in result:
- print(item['name'])
|