def selectOne(self, sql, parameter=None):
'''查询单条记录，如果查询结果返回多条，也只会取第一条结果返回
Args:
sql (str): 要执行的SQL语句
parameter (tuple): 拼接在SQL语句中的参数元组，可以不传.
Returns (tuple):
结果的元组，2个元素，1为查询结果的count数量，2为结果的dict.
'''
try:
if parameter is None:
count = self._cursor.execute(sql)
else:
count = self._cursor.execute(sql, parameter)
if count > 0:
result = self._cursor.fetchone()
else:
result = False
return (count, result)
except Exception as e:
print('Error : {}'.format(e))
self._conn.rollback()
raise DataBaseException(message=(
"select one data from database error, error sql:\n {}.".format(
sql)))
def selectAll(self, sql, parameter=None):
'''
@description:批量查询所有符合条件的内容。
@param {string} sql:要执行的SQL语句
@param {tuple} parameter:要替换到SQL语句中的占位符中的参数，会按索引进行替换
@return: {tuple}: (查询的总条目数, 查询的结果，如果查询的结果集为空，则返回False)
'''
try:
if parameter is None:
count = self._cursor.execute(sql)
else:
count = self._cursor.execute(sql, parameter)
if count > 0:
result = self._cursor.fetchall()
else:
result = False
return (count, result)
except Exception as e:
print('Error : {}'.format(e))
self._conn.rollback()
raise DataBaseException(message=(
"select all data from database error, error sql:\n %s." %
(sql)))
def selectMany(self, sql, number, parameter=None):
'''
@description:查询指定数量的数据集结果
@param {string} sql:要执行的SQL语句
@param {int} number:要查询的条数
@param {tuple} parameter:要替换到SQL语句中的占位符中的参数，会按索引进行替换
@return: {tuple}: (查询的总条目数, 查询的结果，如果查询的结果集为空，则返回False)
'''
try:
if parameter is None:
count = self._cursor.execute(sql)
else:
count = self._cursor.execute(sql, parameter)
if count > 0:
result = self._cursor.fetchmany(number)
else:
result = False
return (count, result)
except Exception as e:
print('Error : {}'.format(e))
self._conn.rollback()
raise DataBaseException(message=(
"insert one data to database error, error sql:\n %s." % (sql)))
def close_transaction_insert_many(self, sql, values):
try:
result = self._cursor.executemany(sql, values)
return result
except DatabaseError as e:
# print ('Error : {}'.format(e))
self._conn.rollback()
raise DataBaseException(message=(
"insert many data to database error, error sql:\n %s." %
(sql)))
def insertOne(self, sql, value):
'''
@description:单条插入数据
@param {string} sql:插入的SQL语句
@param {tuple} value：插入语句的值
@return:数据库的变化结果数量
'''
try:
result = self._cursor.execute(sql, value)
self._conn.commit()
return result
except Exception as e:
print('Error : {}'.format(e))
self._conn.rollback()
raise DataBaseException(message=(
"insert one data to database error, error sql:\n %s." % (sql)))
def __query(self, sql, auto_commit, parameter=None):
'''
@description:执行非select的语句
@param {string} sql:需要执行的SQL
@param {tuple} parameter:要替换到SQL语句中的占位符中的参数，会按索引进行替换
@return:数据库的变化结果数量
'''
count = 0
try:
if parameter is None:
count = self._cursor.execute(sql)
else:
count = self._cursor.execute(sql, parameter)
if auto_commit:
self._conn.commit()
return count
except Exception as e:
print('Error : {}'.format(e))
self._conn.rollback()
raise DataBaseException(
message=("execute sql error, error message, error sql:\n %s." %
(sql)))
def insertMany(self, sql, values):
'''
@description:批量插入数据
c.executemany("""INSERT INTO breakfast (name, spam, eggs, sausage, price) VALUES (%s, %s, %s, %s, %s)""",
[
("Spam and Sausage Lover's Plate", 5, 1, 8, 7.95 ),
("Not So Much Spam Plate", 3, 2, 0, 3.95 ),
("Don't Wany ANY SPAM! Plate", 0, 4, 3, 5.95 )
])
@param {string} sql:插入的SQL语句
@param {array} values：数组，每个元素为tuple,tuple的每个元素为要插入的值
@return:数据库的变化结果数量
'''
try:
result = self._cursor.executemany(sql, values)
self._conn.commit()
return result
except DatabaseError as e:
# print ('Error : {}'.format(e))
self._conn.rollback()
raise DataBaseException(message=(
"insert many data to database error, error sql:\n %s." %
(sql)))