def insert_products(sql, products):
try:
conn = ConnectionPool.get_instance().get_connection()
cursor = conn.cursor()
cursor.executemany(sql, products)
conn.commit()
except Error as e:
print('Error:', e)
finally:
cursor.close()
conn.close()
def query_with_fetchall2(sql):
try:
conn = ConnectionPool.get_instance().get_connection()
cursor = conn.cursor()
cursor.execute(sql)
return cursor.fetchall()
except Error as e:
print(e)
finally:
cursor.close()
conn.close()
def delete_product(sql, code):
try:
conn = ConnectionPool.get_instance().get_connection()
cursor = conn.cursor()
cursor.execute(sql, (code, ))
conn.commit()
except Error as error:
print(error)
finally:
cursor.close()
conn.close()
def insert_product(sql, code, name):
args = (code, name)
try:
conn = ConnectionPool.get_instance().get_connection()
cursor = conn.cursor()
cursor.execute(sql, args)
conn.commit()
except Error as e:
print(e)
finally:
cursor.close()
conn.close()
def query_with_fetchmany(sql):
try:
conn = ConnectionPool.get_instance().get_connection()
cursor = conn.cursor()
cursor.execute(sql)
for row in iter_row(cursor, 5):
print(type(row), " ", row)
except Error as e:
print(e)
finally:
cursor.close()
conn.close()
def update_product(sql, name, code):
args = (name, code)
try:
conn = ConnectionPool.get_instance().get_connection()
cursor = conn.cursor()
cursor.execute(sql, args)
conn.commit()
except Error as error:
print(error)
finally:
cursor.close()
conn.close()
def call_sale_stat_sp(query):
try:
conn = ConnectionPool.get_instance().get_connection()
cursor = conn.cursor()
cursor.callproc(query)
for result in cursor.stored_results():
res = result.fetchone()
print(res)
except Error as e:
print(e)
finally:
cursor.close()
conn.close()
def query_with_fetchone(sql):
try:
conn = ConnectionPool.get_instance().get_connection()
cursor = conn.cursor()
cursor.execute(sql)
row = cursor.fetchone()
while row is not None:
print(type(row), " : ", row)
row = cursor.fetchone()
except Error as e:
print(e)
finally:
cursor.close()
conn.close()
def query_with_fetchall(sql):
try:
conn = ConnectionPool.get_instance().get_connection()
cursor = conn.cursor()
cursor.execute(sql)
rows = cursor.fetchall()
print('Total Row(s):', cursor.rowcount)
for row in rows:
print(type(row), " ", row)
except Error as e:
print(e)
finally:
cursor.close()
conn.close()
def call_order_price_by_issale(query, isSale):
try:
conn = ConnectionPool.get_instance().get_connection()
cursor = conn.cursor()
args = [
isSale,
]
cursor.callproc(query, args)
for result in cursor.stored_results():
rows = result.fetchall()
for row in rows:
print(row)
except Error as e:
print(e)
finally:
cursor.close()
conn.close()
def create_table():
try:
conn = ConnectionPool.get_instance().get_connection()
cursor = conn.cursor()
cursor.execute(TABLE_SQL)
print("CREATE TABLE {}".format(TABLE_NAME))
except Error as err:
# print(err)
if err.errno == errorcode.ER_TABLE_EXISTS_ERROR:
cursor.execute("DROP TABLE {} ".format(TABLE_NAME))
print("DROP TABLE {}".format(TABLE_NAME))
cursor.execute(TABLE_SQL)
print("CREATE TABLE {}".format(TABLE_NAME))
else:
print(err.msg)
finally:
if conn.is_connected():
cursor.close()
conn.close()
def create_procedure():
try:
conn = ConnectionPool.get_instance().get_connection()
cursor = conn.cursor()
cursor.execute(PROCEDURE_SRC)
print("CREATE PROCEDURE {}".format(PROCEDURE_NAME))
except Error as err:
# print(err)
if err.errno == errorcode.ER_SP_ALREADY_EXISTS:
cursor.execute("DROP PROCEDURE {} ".format(PROCEDURE_NAME))
print("DROP PROCEDURE {}".format(PROCEDURE_NAME))
cursor.execute(PROCEDURE_SRC)
print("CREATE PROCEDURE {}".format(PROCEDURE_NAME))
else:
print(err.msg)
finally:
if conn.is_connected():
cursor.close()
conn.close()
def transaction_success():
try:
print('Connecting to MySQL database...')
conn = ConnectionPool.get_instance().get_connection()
print(type(conn))
conn.autocommit = False
cursor = conn.cursor()
insert_sql = "Insert into product values(%s, %s)"
cursor.execute(insert_sql, ('D001', '아메리카노 Set'))
cursor.execute(insert_sql, ('C005', '라떼5'))
print("Record 2 product successfully ")
conn.commit()
except Error as error:
print("Failed to update record to database rollback: {}".format(error))
conn.rollback()
finally:
if conn.is_connected():
conn.autocommit = True
cursor.close()
conn.close()
print("connection is closed")