pymsq mysql python_python mysql

# -*- coding: utf-8 -*-

import pymssql

from utils.log import logger

DB_CHARSET = 'utf8'

class BaseDatabase():

def __init__(self, db_host, db_user, db_pwd, db_port, db_name):

self._logger = logger

try:

self._db_host = db_host

self._db_user = db_user

self._db_pwd = db_pwd

self._db_port = db_port

self._db_name = db_name

self._db_charset = DB_CHARSET

self._conn = self.conn_mysql()

except Exception as err:

self._logger.info("initial database failed, %s" % err)

if self._conn:

self._cursor = self._conn.cursor()

def conn_mysql(self):

"""连接数据库"""

conn = False

try:

conn = pymssql.connect(host=self._db_host,

user=self._db_user,

password=self._db_pwd,

database=self._db_name,

port=self._db_port,

as_dict=False,

charset=self._db_charset,

)

except Exception as err:

self._logger.info("connect database failed, %s" % err)

conn = False

return conn

def query(self, sql):

""""查询数据"""

res = ''

if self._conn:

try:

self._cursor.execute(sql)

res = self._cursor.fetchall()

except Exception as err:

res = False

self._logger.info("query database exception, %s" % err)

raise err

return res

def update(self, sql):

"""更新数据"""

flag = False

if self._conn:

try:

self._cursor.execute(sql)

self._conn.commit()

flag = True

except Exception as err:

flag = False

self.rollback()

self._logger.info("update database exception, %s" % err)

return flag

def insert(self, sql):

"""插入数据"""

flag = False

if self._conn:

try:

self._cursor.execute(sql)

self._conn.commit()

flag = True

except Exception as err:

flag = False

self.rollback()

self._logger.info("insert database exception, %s" % err)

return flag

def rollback(self):

return self._conn.rollback()

def close(self):

"""关闭数据库连接"""

if self._conn:

try:

if type(self._cursor) == 'object':

self._cursor.close()

if type(self._conn) == 'object':

self._conn.close()

except Exception as err:

self._logger.info("close database exception, %s,%s,%s"

% (err, type(self._cursor), type(self._conn)))

def do_sql(db_host, db_user, db_pwd, db_port, db_name, sql):

"""

执行查询数据库语句

"""

conn_sql = BaseDatabase(db_host, db_user, db_pwd, db_port, db_name)

ret = conn_sql.query(sql=sql)

conn_sql.close()

return ret