def _test_callproc_setup(self, connection):
self._test_callproc_cleanup(connection)
stmt_create1 = (
"CREATE PROCEDURE myconnpy_sp_1"
"(IN pFac1 INT, IN pFac2 INT, OUT pProd INT) "
"BEGIN SET pProd := pFac1 * pFac2; END;")
stmt_create2 = (
"CREATE PROCEDURE myconnpy_sp_2"
"(IN pFac1 INT, IN pFac2 INT, OUT pProd INT) "
"BEGIN SELECT 'abc'; SELECT 'def'; SET pProd := pFac1 * pFac2; END;"
)
stmt_create3 = (
"CREATE PROCEDURE myconnpy_sp_3"
"(IN pStr1 VARCHAR(20), IN pStr2 VARCHAR(20), "
"OUT pConCat VARCHAR(100)) "
"BEGIN SET pConCat := CONCAT(pStr1, pStr2); END;")
stmt_create4 = (
"CREATE PROCEDURE myconnpy_sp_4"
"(IN pStr1 VARCHAR(20), INOUT pStr2 VARCHAR(20), "
"OUT pConCat VARCHAR(100)) "
"BEGIN SET pConCat := CONCAT(pStr1, pStr2); END;")
try:
cursor = connection.cursor()
cursor.execute(stmt_create1)
cursor.execute(stmt_create2)
cursor.execute(stmt_create3)
cursor.execute(stmt_create4)
except errors.Error as err:
self.fail("Failed setting up test stored routine; {0}".format(err))
cursor.close()
def updateIncomeExpenses(self, budget: dict, username: str):
# Example for insert in database
try: # Set wether expense or income, and check if key already exists.
dbConnector = self.__getConnector()
cursor = self.__getDictionaryCursor()
for item in budget.items():
key = item[0]
value = item[1]
if value > 0:
dice = "incomes"
else:
dice = "expenses"
search = self.ifCategoryExists(key, username)
if 1 in search:
updateQuery = None
if dice == "incomes":
updateQuery = """
UPDATE budget SET incomes = %s WHERE username = %s AND categories = %s
"""
else:
updateQuery = """
UPDATE budget SET expenses = %s WHERE username = %s AND categories = '%s
"""
insertTuple = (value, username, key)
cursor.execute(updateQuery, insertTuple)
else:
insertQuery = None
if dice == "incomes":
insertQuery = """
INSERT into budget (
username,
incomes,
categories
) VALUES (%s, %s, %s);
"""
else:
insertQuery = """
INSERT into budget (
username,
expenses,
categories
) VALUES (%s, %s, %s);
"""
insertTuple = (username, value, key)
cursor.execute(insertQuery, insertTuple)
self.__dbConnector.commit()
return True
except mysql.connector.IntegrityError as err:
rejectedInsert = {"Error": err, "Status": False}
return rejectedInsert
finally:
if dbConnector.is_connected:
cursor.close()
dbConnector.close()
def lock():
querry = ("select * from users where name = %s ")
try:
cursor=cnx.cursor()
cursor.execute(querry, (request.form['username'],))
result = cursor.fetchall()
logTime = datetime.now()
logUserId = result[0][0]
cursor.close()
if len(result) > 0:
if currentlyLocked[0] == True:
logAction = "Attempted to lock already locked lock"
logDbAction(logUserId,logAction,logTime)
return "Already Locked"
else:
logAction = "Locked the lock"
logDbAction(logUserId,logAction,logTime)
currentlyLocked[0] = True
return "locked"
else:
logAction = "tried to lock the lock but denied due to invalid credentials"
logDbAction(logUserId,logAction,logTime)
return 'denied'
cursor.close
except Exception, err:
print Exception,err
def update_replicated_database(dct_element_map):
dct_update_status = {}
row_count = 0
rows_processed = 0
cmd_id = dct_element_map['CID']
lst_replications = dct_element_map['replications']
for dct_replication in lst_replications:
table = dct_replication['table']
schema = dct_replication['schema']
cursor = None
lst_queries = dct_replication[schema+'.'+table]
try:
query = prepare_query(lst_queries,schema);
cursor = cnx.cursor()
cursor.execute(query)
# cnx.commit()
rows_processed+=1
except mysql.connector.Error as err:
print err
log_error_to_table(str(err)+'for query - '+query)
cursor.close()
row_count+=1
dct_update_status['row_count'] = row_count
dct_update_status['rows_processed'] = rows_processed
dct_update_status['cmd_id'] = cmd_id
return dct_update_status
def _test_callproc_setup(self,db,prc="myconnpy_callproc"):
self._test_callproc_cleanup(db)
stmt_create1 = """CREATE PROCEDURE myconnpy_sp_1
(IN pFac1 INT, IN pFac2 INT, OUT pProd INT)
BEGIN SET pProd := pFac1 * pFac2;
END;"""
stmt_create2 = """CREATE PROCEDURE myconnpy_sp_2
(IN pFac1 INT, IN pFac2 INT, OUT pProd INT)
BEGIN SELECT 'abc'; SELECT 'def'; SET pProd := pFac1 * pFac2;
END;"""
stmt_create3 = (
"CREATE PROCEDURE myconnpy_sp_3"
"(IN pStr1 VARCHAR(20), IN pStr2 VARCHAR(20), "
"OUT pConCat VARCHAR(100)) "
"BEGIN SET pConCat := CONCAT(pStr1, pStr2); END;")
try:
cursor = db.cursor()
cursor.execute(stmt_create1)
cursor.execute(stmt_create2)
cursor.execute(stmt_create3)
except errors.Error as e:
self.fail("Failed setting up test stored routine; %s" % e)
cursor.close()
def _test_callproc_setup(self, db, prc="myconnpy_callproc"):
self._test_callproc_cleanup(db)
stmt_create1 = """CREATE PROCEDURE myconnpy_sp_1
(IN pFac1 INT, IN pFac2 INT, OUT pProd INT)
BEGIN SET pProd := pFac1 * pFac2;
END;"""
stmt_create2 = """CREATE PROCEDURE myconnpy_sp_2
(IN pFac1 INT, IN pFac2 INT, OUT pProd INT)
BEGIN SELECT 'abc'; SELECT 'def'; SET pProd := pFac1 * pFac2;
END;"""
stmt_create3 = ("CREATE PROCEDURE myconnpy_sp_3"
"(IN pStr1 VARCHAR(20), IN pStr2 VARCHAR(20), "
"OUT pConCat VARCHAR(100)) "
"BEGIN SET pConCat := CONCAT(pStr1, pStr2); END;")
try:
cursor = db.cursor()
cursor.execute(stmt_create1)
cursor.execute(stmt_create2)
cursor.execute(stmt_create3)
except errors.Error as err:
self.fail("Failed setting up test stored routine; {0}".format(err))
cursor.close()
def insert_table():
try:
conn = connect()
cursor = conn.cursor()
#直接字符串插入
sql_insert1 = "insert into python1(id,name,passwd) values(1,'tom','strpass')"
cursor.execute(sql_insert1)
#元组连接插入方式
sql_insert2 = "insert into python1(id,name,passwd) values(%s,%s,%s)"
data = (2, 'jim', 'jimpasswd')
cursor.execute(sql_insert2, data)
#字典插入方式
sql_insert3 = "insert into python1 (id,name,passwd) values(%(id)s,%(name)s,%(passwd)s)"
data3 = {'id': 6, 'name': 'jak', 'passwd': 'passwdjsck'}
cursor.execute(sql_insert3, data3)
#MySQL Connector也支持多次插入
sql_insert4 = "insert into python1(id,name,passwd) values(%s,%s,%s)"
data4 = [(8, 'jin', 'jin'), (9, 'yin', 'yinpass'),
(10, 'hao', 'happass'), (11, '中文', 'pass123')]
cursor.executemany(sql_insert4, data4)
conn.commit()
except mysql.connector.Error as e:
print("execute fails{}".format(e))
finally:
cursor.close()
conn.close()
print("conn has chosed")
def open():
querry = "select * from users where name = %s "
try:
cursor = cnx.cursor()
cursor.execute(querry, (request.form["username"],))
result = cursor.fetchall()
logTime = datetime.now()
logUserId = result[0][0]
cursor.close()
if len(result) > 0:
if currentlyLocked[0] == True:
currentlyLocked[0] = False
logAction = "Opened the lock"
logDbAction(logUserId, logAction, logTime)
return "opend"
else:
logAction = "Tried to open already open lock"
logDbAction(logUserId, logAction, logTime)
return "Aleady Open"
else:
logAction = "tried to open the lock but denied due to invalid credentials"
logDbAction(logUserId, logAction, logTime)
return "denied"
except Exception, err:
print Exception, err
def delete(self, sql, params=None, cursor_mode=CursorMode.CURSOR_MODE):
if sql.upper().startswith('DROP') | sql.upper().startswith(
'CREATE') | sql.upper().startswith('INSERT') | sql.upper(
).startswith('SELECT') | sql.upper().startswith('UPDATE'):
raise DBError(key=DBErrorType.OPERATE_NOT_SUPPORT_ERROR,
message="Not support this operate")
conn = None
cursor = None
try:
delete_result = self.__execute(sql=sql,
params=params,
cursor_mode=cursor_mode)
conn = delete_result["conn"]
cursor = delete_result["cursor"]
self.__commit_connection()
return cursor.rowcount
except DBError as e:
raise e
except Exception as e:
reraise(DBError(e, sql=sql, params=params), sys.exc_info()[2])
finally:
if cursor:
cursor.close()
if conn:
self.__close_connection()
def get(self, table):
db = mysql.connector.connect(host="localhost",
user="******",
password="******",
database='project')
data = []
#query = 'select {0} from {1} where {0} like "{2}%"'
# this will fetch all data from given table
query = 'select distinct {0} from {1}'
val = request.args.get("query")
if (table == "disease"):
query = query.format("dName", table)
elif (table == "symptoms"):
query = query.format("sName", table)
elif (table == "medicine"):
query = query.format("mName", table)
elif (table == "chemicals"):
query = query.format("cName", table)
else:
return {"message": "an error in query parameter"}, 400
cursor = db.cursor()
try:
cursor.execute(query)
except:
print("error ocuured while executing sql")
cursor.close()
return {"message": "error occured"}, 500
result = cursor.fetchall()
for i in result:
data.append(i[0])
db.close()
return data
def get(self):
db = mysql.connector.connect(host="localhost",
user="******",
password="******",
database='project')
disease = request.args.get("disease")
print("disease names are = %s" % (disease))
query = """
select sName
from symptoms
where sId in (
select ds.sId
from diseaseSymptoms ds,disease d
where ds.dId = d.dId
and dName = "%s"
);
"""
cursor = db.cursor()
cursor.execute(query % (disease))
symptoms = cursor.fetchall()
cursor.close()
result = []
for i in symptoms:
result.append(i[0])
return result
def get(self):
db = mysql.connector.connect(host="localhost",
user="******",
password="******",
database='project')
query = """
select mName,modeOfAdministration
from medicine
where mId in (
select similar
from similarMedicine
where mId in ( select s.mId
from medicine m,similarMedicine s
where m.mId = s.similar
and m.mName = "%s"
)
);
"""
name = request.args.get("medicine")
cursor = db.cursor()
cursor.execute(query % (name))
result = cursor.fetchall()
cursor.close()
return result
def get(self):
db = mysql.connector.connect(host="localhost",
user="******",
password="******",
database='project')
symptoms = request.args.get("symptoms")
symptoms = symptoms.split(",")
symptoms = ",".join(map(str, symptoms))
print("disease are {}".format(symptoms))
cursor = db.cursor()
query = """
select dName,count(*) as frequency from
disease d,diseaseSymptoms ds,symptoms s
where d.dId = ds.dId
and ds.sId = s.sId
and sName in (%s)
group by dName
order by frequency desc;
"""
cursor.execute(query % (symptoms))
record = cursor.fetchall()
cursor.close()
print(record)
return record
def delete(self, id):
cursor = self.db.cursor()
sql = "delete from datasets where id= %s"
values = (id, )
cursor.execute(sql, values)
self.db.commit()
cursor.close()
def login_db_con() -> 'html':
u_id = request.form["user_id"]
u_pw = request.form["user_pw"]
dbconfig = {
'host': 'localhost',
'user': '******',
'password': '',
'database': 'member_db'
}
conn = mysql.connector.connect(**dbconfig)
cursor = conn.cursor()
SQL = "SELECT * FROM login_t WHERE id=%s and pw=%s"
cursor.execute(SQL, (u_id, u_pw))
alldata = cursor.fetchall()
cursor.close()
conn.close()
info = ''
if (len(alldata) >= 1):
if u_id == 'admin':
return redirect('/admin/')
print("logged in...")
session.clear()
session["user_id"] = u_id
return redirect('/')
else:
print("who are you...")
return render_template("admin_login.html", info='다시입력해주세요.')
def datasetSearch(self, params):
self.params = self.params + params
print(
f"Calling the open data portal using package_search action with query parameters {self.params}"
)
self.response = requests.get(self.url + self.action + self.params)
print(self.response)
data = self.response.json()
print(f"Success: {data['success']}")
print(f"count: {data['result']['count']}")
cursor = self.db.cursor()
for result in data["result"]["results"]:
resources = result['resources']
for resource in resources:
print(
f"name: {resource['name']}, \ndescription: {resource['description']}"
)
sql = "insert ignore into datasets (id, package_id, name, description, url, format, created) values (%s,%s,%s,%s,%s,%s,%s)"
values = [
resource['id'], resource['package_id'], resource['name'],
resource['description'], resource['url'],
resource['format'], resource['created']
]
cursor.execute(sql, values)
self.db.commit()
cursor.close()
def get_query_list_to_replicate(last_update):
dct_data_list = {}
lst_replications = []
time_stamp = ''
cursor = None
insert_sql = None
try:
select_sql = 'select `id`,`table`,`schema`,`query`,`type`,`time_stamp` from maticagent_replicate.replication_data where id > \''+str(last_update)+'\' order by `schema`, `table` '
print select_sql
cursor = cnx.cursor(dictionary=True)
cursor.execute(select_sql + "limit 10")
for row in cursor:
insert_sql = row['query']
schema = row['schema']
table = row['table']
dct_repliction = {}
dct_repliction['schema'] = schema
dct_repliction['table'] = table
dct_repliction['query'] = insert_sql
lst_replications.append(dct_repliction)
last_update = row['id']
time_stamp = row['time_stamp']
dct_data_list['queries'] =lst_replications
dct_data_list['last_update'] = last_update
dct_data_list['time_stamp'] = time_stamp
except mysql.connector.Error as err:
print(err)
cursor.close();
return dct_data_list
def deleteMessagebyID(self, message):
cursor = self.getCursor()
sql = "delete from message where id= %s"
values = [message['id']]
cursor.execute(sql, values)
self.db.commit()
cursor.close()
return message
def delete(self, id):
cursor = self.db.cursor()
sql = 'delete from harvest where id = %s'
values = [id]
cursor.execute(sql, values)
self.db.commit()
cursor.close()
return {}
def execute_rowcount(self, query, *parameters, **kwparameters):
"""Executes the given query, returning the rowcount from the query."""
cursor = self._cursor()
try:
self._execute(cursor, query, parameters, kwparameters)
return cursor.rowcount
finally:
cursor.close()
def execute_lastrowid(self, query, *parameters, **kwparameters):
"""Executes the given query, returning the lastrowid from the query."""
cursor = self._cursor()
try:
self._execute(cursor, query, parameters, kwparameters)
return cursor.lastrowid
finally:
cursor.close()
def updateMessagebyID(self, message):
cursor = self.getCursor()
sql = "update message set message = %s where id = %s"
values = [message['message'], message['id']]
cursor.execute(sql, values)
self.db.commit()
cursor.close()
return message
def updateUserbyID(self, user):
cursor = self.getCursor()
sql = "update user set name = %s where id = %s"
values = [user['name'], user['id']]
cursor.execute(sql, values)
self.db.commit()
cursor.close()
return user
def findById(self, id):
cursor = self.db.cursor()
sql = 'select * from patients where id = %s'
values = [id]
cursor.execute(sql, values)
result = cursor.fetchone()
cursor.close()
return self.convertToDict(result)
def getUserID(self, user):
cursor = self.getCursor()
sql = 'select * from user where id = %s'
values = [user]
cursor.execute(sql, values)
result = cursor.fetchone()
cursor.close()
return self.convertToIDDict(result)
def getUserbyID(self, user):
cursor = self.getCursor()
print("GET USER BY ID")
sql = 'select * from user where id = "%s"'
values = [user['id']]
cursor.execute(sql, values)
result = cursor.fetchone()
cursor.close()
return self.convertToUserDict(result)
def createUser(self, user):
cursor = self.getCursor()
sql = "insert into user (name,id) values (%s,%s)"
id = uuid.uuid1()
values = [user['name'], str(id)]
cursor.execute(sql, values)
self.db.commit()
cursor.close()
return cursor.lastrowid
def deleteUserID(self, user):
cursor = self.getCursor()
print("DELTER user id is", user)
sql = "delete from user where id= %s"
values = [user['id']]
cursor.execute(sql, values)
self.db.commit()
cursor.close()
return user
def getMessagebyID(self, message):
cursor = self.getCursor()
sql = 'select * from message where ID = %s'
values = [message['id']]
cursor.execute(sql, values)
result = cursor.fetchone()
print("result", result)
cursor.close()
return self.convertToMessageDict(result)
def checkUser(self, username, password):
cursor = self.db.cursor()
sql = "select * from users where username = %s and password = %s"
values = [username, password]
cursor.execute(sql, values)
# Fetch one record and return result
result = cursor.fetchone()
cursor.close()
return self.convertToDict2(result)
def update(
self,
supplier): # Update info of certain supplier with selected codenr
cursor = self.db.cursor()
sql = "update supplier set name = %s, country = %s where snr = %s"
values = (supplier["name"], supplier["country"], supplier["snr"])
cursor.execute(sql, values)
self.db.commit()
cursor.close()
return supplier
def _test_execute_cleanup(self, connection, tbl="myconnpy_cursor"):
stmt_drop = """DROP TABLE IF EXISTS %s""" % (tbl)
try:
cursor = connection.cursor()
cursor.execute(stmt_drop)
except (StandardError) as e:
self.fail("Failed cleaning up test table; %s" % e)
cursor.close()
def _test_execute_cleanup(self,db,tbl="myconnpy_cursor"):
stmt_drop = """DROP TABLE IF EXISTS %s""" % (tbl)
try:
cursor = db.cursor()
cursor.execute(stmt_drop)
except (Exception) as e:
self.fail("Failed cleaning up test table; %s" % e)
cursor.close()
def query(self, query, *parameters, **kwparameters):
"""Returns a row list for the given query and parameters."""
self._ensure_connected()
cursor = mysql.connector.cursor.MySQLCursorBuffered(self._db)
try:
self._execute(cursor, query, parameters, kwparameters)
column_names = [d[0] for d in cursor.description]
return [Row(zip(column_names, row)) for row in cursor]
finally:
cursor.close()
def get_data(self, query):
cursor = self.conn.cursor()
cursor.execute(query)
result = []
columns = tuple([i[0] for i in cursor.description])
for row in cursor:
result.append(dict(zip(columns, row)))
cursor.close()
return result
def getUsers(self):
cursor = self.getCursor()
sql = 'select * from user'
cursor.execute(sql)
results = cursor.fetchall()
returnArray = []
for result in results:
resultAsDict = self.convertToUserDict(result)
returnArray.append(resultAsDict)
cursor.close()
return returnArray
def create(self, harvest):
cursor = self.db.cursor()
sql = "insert into harvest (id, employeeName, fieldSection, variety, quantity) values (%s,%s,%s,%s,%s)"
values = [
harvest['id'], harvest['employeeName'], harvest['fieldSection'],
harvest['variety'], harvest['quantity']
]
cursor.execute(sql, values)
self.db.commit()
cursor.close()
return cursor.lastrowid
def update(self, patient):
cursor = self.db.cursor()
sql = "update patients set firstName = %s, lastName = %s, reasonForVisiting = %s where id = %s"
values = [
patient['firstName'], patient['lastName'],
patient['reasonForVisiting'], patient['id']
]
cursor.execute(sql, values)
self.db.commit()
cursor.close()
return patient
def executemany_rowcount(self, query, parameters):
"""Executes the given query against all the given param sequences.
We return the rowcount from the query.
"""
cursor = self._cursor()
try:
cursor.executemany(query, parameters)
return cursor.rowcount
finally:
cursor.close()
def create(self, patient):
cursor = self.db.cursor()
sql = "insert into patients (id,firstName, lastName, reasonForVisiting) values (%s,%s,%s,%s)"
values = [
patient['id'], patient['firstName'], patient['lastName'],
patient['reasonForVisiting']
]
cursor.execute(sql, values)
self.db.commit()
cursor.close()
return cursor.lastrowid
def update(self, harvest):
cursor = self.db.cursor()
sql = "update harvest set employeeName = %s, fieldSection = %s, variety = %s, quantity = %s where id = %s"
values = [
harvest['employeeName'], harvest['fieldSection'],
harvest['variety'], harvest['quantity'], harvest['id']
]
cursor.execute(sql, values)
self.db.commit()
cursor.close()
return harvest
def iter(self, query, *parameters, **kwparameters):
"""Returns an iterator for the given query and parameters."""
self._ensure_connected()
cursor = self._cursor()
try:
self._execute(cursor, query, parameters, kwparameters)
column_names = [d[0] for d in cursor.description]
for row in cursor:
yield Row(zip(column_names, row))
finally:
cursor.close()
def _test_callproc_cleanup(self, connection):
sp_names = ('myconnpy_sp_1', 'myconnpy_sp_2', 'myconnpy_sp_3')
stmt_drop = "DROP PROCEDURE IF EXISTS {procname}"
try:
cursor = connection.cursor()
for sp_name in sp_names:
cursor.execute(stmt_drop.format(procname=sp_name))
except errors.Error as err:
self.fail("Failed cleaning up test stored routine; {0}".format(err))
cursor.close()
def _test_callproc_cleanup(self,db,prc="myconnpy_callproc"):
sp_names = ('myconnpy_sp_1','myconnpy_sp_2')
stmt_drop = "DROP PROCEDURE IF EXISTS %s"
try:
cursor = db.cursor()
for sp_name in sp_names:
cursor.execute(stmt_drop % sp_name)
except errors.Error as e:
self.fail("Failed cleaning up test stored routine; %s" % e)
cursor.close()
def get_last_update_row_id():
last_update_row_id = 0
try:
select_sql = 'select row_id from maticagent_replicate.replication_controller where id in (select max(id) from maticagent_replicate.replication_controller)'
print select_sql
cursor = cnx.cursor()
cursor.execute(select_sql)
for row in cursor:
last_update_row_id = row[0]
except mysql.connector.Error as err:
print(err)
cursor.close();
return last_update_row_id
def _test_execute_setup(self,db,tbl="myconnpy_cursor",engine="MyISAM"):
self._test_execute_cleanup(db,tbl)
stmt_create = """CREATE TABLE %s
(col1 INT, col2 VARCHAR(30), PRIMARY KEY (col1))
ENGINE=%s""" % (tbl,engine)
try:
cursor = db.cursor()
cursor.execute(stmt_create)
except (Exception) as e:
self.fail("Failed setting up test table; %s" % e)
cursor.close()
def adduser():
try:
insert = (request.form['username'],request.form['type'])
cursor = cnx.cursor()
querry = ("insert into users (name, type) VALUES (%s,%s)")
cursor.execute(querry, insert)
result = cursor.fetchall
logTime = datetime.now()
logUserId = result[0][0]
print(cursor.statement + " " + str(cursor.rowcount))
cursor.close()
cnx.commit()
logAction = "User " + insert[0] + "added with type " + insert[1]
logDbAction(logUserId,logAction,logTime)
return "successful"
except Exception, err:
print Exception,err
def adduser():
try:
insert = (request.form["username"], request.form["type"])
cursor = cnx.cursor()
querry = "insert into users (name, type) VALUES (%s,%s)"
cursor.execute(querry, insert)
logTime = datetime.now()
logUserId = cursor.lastrowid
cursor.close()
cnx.commit()
logAction = "User " + insert[0] + " added with type " + insert[1]
logDbAction(logUserId, logAction, logTime)
return "successful"
except Exception, err:
print Exception, err
def test_cursor(self):
config = self.getMySQLConfig()
cnx = connection.MySQLConnection(**config)
# Create a cursor and close using close()-method
for i in range(0, self.sample_size):
cursor = cnx.cursor()
cursor.close()
self.samples[i] = len(gc.get_objects())
self._assert_flat_line(self.samples)
self._reset_samples()
# Create a cursor and rely on destructor to close
for i in range(0, self.sample_size):
cursor = cnx.cursor()
self.samples[i] = len(gc.get_objects())
self._assert_flat_line(self.samples)
def update_update_status_for_each_table(server_ip,last_update_row_id,command_id):
cursor = cnx.cursor()
# 'select id , tableID,row_id,time_stamp,status,base_column,base_column_name from maticagent_replicate.replication_controller where tableId = ' + str(table_id) + ''
try:
insert_replication_controller = 'insert into maticagent_replicate.replication_controller (ip_of_server,row_id,time_stamp) values (\''+server_ip+'\',\''+str(last_update_row_id)+'\' , now())'
insert_queue_messages = 'insert into maticagent_replicate.queue_messages (cmd_id,time_stamp,status,error_message) value (\''+command_id+'\', now(),\'SENT\',\'\')'
print insert_replication_controller
print insert_queue_messages
cursor.execute(insert_replication_controller,insert_queue_messages)
# cnx.commit()
except mysql.connector.Error as err:
print (err)
cursor.close()
return
def _test_callproc_setup(self,db,prc="myconnpy_callproc"):
self._test_callproc_cleanup(db)
stmt_create1 = """CREATE PROCEDURE myconnpy_sp_1
(IN pFac1 INT, IN pFac2 INT, OUT pProd INT)
BEGIN SET pProd := pFac1 * pFac2;
END;"""
stmt_create2 = """CREATE PROCEDURE myconnpy_sp_2
(IN pFac1 INT, IN pFac2 INT, OUT pProd INT)
BEGIN SELECT 'abc'; SELECT 'def'; SET pProd := pFac1 * pFac2;
END;"""
try:
cursor = db.cursor()
cursor.execute(stmt_create1)
cursor.execute(stmt_create2)
except errors.Error as e:
self.fail("Failed setting up test stored routine; %s" % e)
cursor.close()
cursor = connection.cursor()
cursor.execute(stmt_create)
except (StandardError), e:
self.fail("Failed setting up test table; %s" % e)
cursor.close()
def _test_execute_cleanup(self, connection, tbl="myconnpy_cursor"):
stmt_drop = """DROP TABLE IF EXISTS %s""" % (tbl)
try:
cursor = connection.cursor()
cursor.execute(stmt_drop)
except (StandardError), e:
self.fail("Failed cleaning up test table; %s" % e)
cursor.close()
class CursorModule(tests.MySQLConnectorTests):
"""
Tests for the cursor module functions and attributes
"""
def test_RE_SQL_INSERT_VALUES(self):
regex = cursor.RE_SQL_INSERT_VALUES
cases = [
("(%s, %s)",
"INSERT INTO t1 VALUES (%s, %s)"),
("( %s, \n %s )",
"INSERT INTO t1 VALUES ( %s, \n %s )"),
("(%(c1)s, %(c2)s)",
"INSERT INTO t1 VALUES (%(c1)s, %(c2)s)"),
