def test_columnnames_unicode(self):
"""Table names should unicode objects in cursor.description"""
exp = [('ham', 8, None, None, None, None, 0, 129)]
cursor = self.cnx.cursor()
cursor.execute("SELECT 1 as 'ham'")
cursor.fetchall()
self.assertEqual(exp, cursor.description)
def test_rawfetchall(self):
cursor = self.cnx.cursor(raw=True)
cursor.execute("SELECT 1")
try:
cursor.fetchall()
except errors.InterfaceError:
self.fail("fetchall() raises although result is available")
def deleterequest(cnx, cursor, id, placeid, which, results):
if which == 0:
while 1:
featid = input("Please input the feature id from the above list you would like to update:\n")
isafeat = 0
for result in results:
if result[4] == featid:
isafeat = 1
if isafeat:
break
else:
print("Not a valid feature id from the results. Try again")
delete = ("INSERT INTO change_requests "
"(userid, featureid, changetype, streetid) "
"VALUES (%s, %s, %s, %s)")
cursor.execute(delete, (id, featid, "delete", placeid))
cnx.commit()
print("Change request submitted")
elif which == 1:
while 1:
featid = "Please input the feature id from the above list you would like to update:"
isafeat = 0
for result in results:
if result[2] == featid:
isafeat = 1
if isafeat:
break
else:
print("Not a valid feature id from the results. Try again")
delete = ("INSERT INTO change_requests "
"(userid, featureid, changetype, intersectionid) "
"VALUES (%s, %s, %s, %s)")
cursor.execute(delete, (id, featid, "delete", placeid))
cnx.commit()
print("Change request submitted")
def get_book():
""" Connect to MySQL database """
conn = None
try:
conn = mysql.connector.connect(host='db',
port='3306',
database='bookflix',
user='******',
password='******')
category = request.values.get('category')
category_type = request.values.get('category_type')
sql_select_booking_query = """Select * from book where genre = %s"""
cursor = conn.cursor()
cursor.execute(sql_select_booking_query, (category_type, ))
record = cursor.fetchall()
conn.commit()
except Error as e:
print(e)
finally:
if conn is not None and conn.is_connected():
conn.close()
return json.dumps(record)
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 list_category():
""" Connect to MySQL database """
category_no = 0
conn = None
try:
conn = mysql.connector.connect(host='db',
port='3306',
database='bookflix',
user='******',
password='******')
category = request.values.get('category')
cursor = conn.cursor()
if category == "read":
category_no = 1
elif category == "started":
category_no = 2
elif category == "liked":
category_no = 3
elif category == "disliked":
category_no = 4
print(category_no)
sql_list_query = "Select title, author_name from user_client natural join book where list_id = %s"
cursor.execute(sql_list_query, (category_no, ))
record = cursor.fetchall()
except Error as e:
print(e)
finally:
if conn is not None and conn.is_connected():
conn.close()
return json.dumps(record)
def delete_book():
""" Connect to MySQL database """
conn = None
try:
conn = mysql.connector.connect(host='db',
port='3306',
database='bookflix',
user='******',
password='******')
book_id = request.values.get('book_id')
sql_select_booking_query = "Select * from book where book_id = %s"
cursor = conn.cursor()
cursor.execute(sql_select_booking_query, (book_id, ))
exits = cursor.fetchone()
if exits != "":
sql_Delete_query = "Delete from book where book_id = %s"
cursor = conn.cursor()
cursor.execute(sql_Delete_query, (book_id, ))
conn.commit()
except Error as e:
print(e)
finally:
if conn is not None and conn.is_connected():
conn.close()
return ""
def update(self, book):
cursor = self.db.cursor()
sql = "update books set title = %s, author = %s, price = %s where ISBN = %s"
values = [book['title'], book['author'], book['price'], book['ISBN']]
cursor.execute(sql, values)
self.db.commit()
return book
def create(self, book):
cursor = self.db.cursor()
sql = "insert into books (ISBN, title, author, price) values (%s,%s,%s,%s)"
values = [book['ISBN'], book['title'], book['author'], book['price']]
cursor.execute(sql, values)
self.db.commit()
return cursor.lastrowid
def findById(self, ISBN):
cursor = self.db.cursor()
sql = 'select * from books where ISBN = %s'
values = [ISBN]
cursor.execute(sql, values)
result = cursor.fetchone()
return self.convertToDict(result)
def findById(self, id):
cursor = self.db.cursor()
sql = "select * from coffeeconsumers where id = %s"
values = [id]
cursor.execute(sql, values)
result = cursor.fetchone()
return self.convertToDict(result)
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 test_columnnames_unicode(self):
"""Table names should unicode objects in cursor.description"""
exp = [("ham", 8, None, None, None, None, 0, 129)]
cursor = self.cnx.cursor()
cursor.execute("SELECT 1 as 'ham'")
cursor.fetchall()
self.assertEqual(exp, cursor.description)
def create(self, values):
cursor = self.db.cursor()
sql = "insert into dvds (title, director, price) values (%s,%s,%s)"
cursor.execute(sql, values)
self.db.commit()
return cursor.lastrowid
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 delete(self, ClientID):
cursor = self.db.cursor()
sql = 'delete from clients where ClientID = %s'
values = [ClientID]
cursor.execute(sql, values)
return {}
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 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')
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 read ():
db = mysql.connector.connect(
# host="localhost",
# user="******",
# password="",
# database = "mydatabase"
host="dz8959rne9lumkkw.chr7pe7iynqr.eu-west-1.rds.amazonaws.com",
user="******",
password="******",
database="a001ta7fuectz1bw"
)
cursor = db.cursor()
## defining the Query
query = "SELECT link FROM links"
## getting records from the table
cursor.execute(query)
## fetching all records from the 'cursor' object
records = cursor.fetchall()
## Showing the data
links = []
for record in records:
links.append(record[0])
return links
def update(self,table,data,params={},join='AND',commit=True,lock=True):
# 更新数据
try :
fields,values = self.__contact_fields(data)
if params :
where ,whereValues = self.__contact_where(params)
values.extend(whereValues) if whereValues else values
sqlWhere= ' WHERE '+where if where else ''
cursor = self.__getCursor()
if commit : self.begin()
if lock :
sqlSelect="SELECT %s From `%s` %s for update" % (','.join(tuple(list(params.keys()))),table,sqlWhere)
cursor.execute(sqlSelect,tuple(whereValues)) # 加行锁
sqlUpdate = "UPDATE `%s` SET %s "% (table,fields) + sqlWhere
self.__display_Debug_IO(sqlUpdate,tuple(values)) #DEBUG
cursor.execute(sqlUpdate,tuple(values))
if commit : self.commit()
return cursor.rowcount
except Exception as err:
try :
raise BaseError(705,err._full_msg)
except :
raise BaseError(705,err.args)
def tableDemonstracoesContaveis():
try:
db = mysql.connector.connect(
host='localhost',
user='******',
passwd='Teste3pswd!',
auth_plugin='mysql_native_password',
database='teste3'
) # o ideal seria por a senha em outro arquivo, mas tudo bem
cursor = db.cursor()
with db:
createquery = """
CREATE TABLE IF NOT EXISTS Demonstracoes_Contaveis(
id int AUTO_INCREMENT PRIMARY KEY,
DATA date,
REG_ANS int(10),
CD_CONTA_CONTABIL int,
DESCRICAO varchar(60),
VL_SALDO_FINAL float(9,2))
"""
datequery = '''SELECT DATE_FORMAT(DATA, "%d/%m/%Y") FROM Demonstracoes_Contaveis'''
cursor.execute(createquery)
cursor.execute(datequery)
except mysql.connector.Error as e:
print(e)
def deposit(request):
time1 = datetime.datetime.now()
time2 = str(time1)
today = time2[0:19]
currentBalance = getData(int(request.session.get('user_id', None)))
context = {
"message": "Connectez - vous s.v.p ",
"error": False,
"data": ""
}
cursor = connection.cursor()
amount = int(currentBalance['account_amount']) + 500
# Update the Amount
cursor.execute(
"""
UPDATE account
SET account_amount=%s WHERE account_user_id=%s
""", (amount, request.session.get('user_id', None)))
# Update the Transactions
cursor.execute(
"""
INSERT INTO `mytransaction`
SET mytransaction_user_id=%s, mytransaction_type=%s, mytransaction_amount=%s, mytransaction_description=%s, mytransaction_date=%s ,montant_restant=%s,mytransaction_montant_depot=%s
""", (request.session.get('user_id', None), "Credit", "500",
"Vous avez fait un depot de 500", today, amount, 500))
messages.add_message(request, messages.INFO,
"Ton Compte a ete credite de 500/-")
return redirect('/users/dashboard')
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 link_aadhar_card(request):
userId = request.session.get('user_id', None)
userDetails = user.objects.get(user_id=userId)
cursor = connection.cursor()
context = {"fn": "update", "userdetails": userDetails}
currentUserDetails = user.objects.get(user_id=userId)
context[
'sub_heading'] = "Update Details of " + currentUserDetails.user_name
# Message according Users Role #
if (request.method == "POST"):
try:
cursor.execute(
"""
UPDATE users_user
SET user_aadhar=%s WHERE user_id=%s
""", (request.POST['user_aadhar'], userId))
except Exception, e:
return HttpResponse('Something went wrong. Error Message : ' +
str(e))
context["userdetails"] = user.objects.get(user_id=userId)
messages.add_message(request, messages.INFO,
"Your Aadhar Card Updated Successfully !!!")
return render(request, 'aadhar.html', context)
def list_books():
""" Connect to MySQL database """
conn = None
try:
conn = mysql.connector.connect(host='db',
port='3306',
database='bookflix',
user='******',
password='******')
print("DA")
sql_list_query = "Select * from book"
cursor = conn.cursor()
cursor.execute(sql_list_query)
record = cursor.fetchall()
print("DA")
print(record)
except Error as e:
print(e)
finally:
if conn is not None and conn.is_connected():
conn.close()
return json.dumps(record)
def delete(self, ISBN):
cursor = self.db.cursor()
sql = 'delete from books where ISBN = %s'
values = [ISBN]
cursor.execute(sql, values)
return {}
def demonstracoes_to_sql():
try:
db = mysql.connector.connect(
host='localhost',
user='******',
passwd='Teste3pswd!',
auth_plugin='mysql_native_password',
database='teste3'
) # o ideal seria por a senha em outro arquivo, mas tudo bem
cursor = db.cursor()
for nn in range(19, 21):
nn = str(nn)
for i in range(1, 5):
i = str(i)
file = f'csvs_teste3/{i}T20{nn}.csv'
with db:
with open(file, newline='') as file:
csv_data = csv.reader(file, delimiter=';', strict=True)
skip = True
try:
for row in csv_data:
if skip:
skip = False
continue
query = f"INSERT INTO teste3.Demonstracoes_Contaveis(DATA, REG_ANS, CD_CONTA_CONTABIL, DESCRICAO, VL_SALDO_FINAL) VALUES({row[0]},{row[1]},{row[2]},{row[3]},{row[4]})",
cursor.execute(query)
except Exception:
print('demonstracoes_to_sql() Error')
except mysql.connector.Error as e:
print(e)
def count(self,table,params={},join='AND'):
# 根据条件统计行数
try :
sql = 'SELECT COUNT(*) FROM %s' % table
if params :
where ,whereValues = self.__contact_where(params)
sqlWhere= ' WHERE '+where if where else ''
sql+=sqlWhere
#sql = self.__joinWhere(sql,params,join)
cursor = self.__getCursor()
self.__display_Debug_IO(sql,tuple(whereValues)) #DEBUG
if self.DataName=='ORACLE':
cursor.execute(sql % tuple(whereValues))
else :
cursor.execute(sql,tuple(whereValues))
#cursor.execute(sql,tuple(params.values()))
result = cursor.fetchone();
return result[0] if result else 0
#except:
# raise BaseError(707)
except Exception as err:
try :
raise BaseError(707,err._full_msg)
except :
raise BaseError(707)
def create(self):
"""
Description
Function To insert value in database table stocks data
"""
try:
print('Enter your name:')
name = input()
print('Enter your address:')
address = input()
print(e)
cursor = self.db.cursor()
val = (name, address)
cursor.execute(
"INSERT INTO " + self.__table_name +
" (stockname, stockprice) VALUES (%s, %s)", val)
self.db.commit()
print(cursor.rowcount, "record inserted.")
except ValueError as e:
print(e)
def update(self):
"""
Description
Function To update value in database table stocks data
"""
try:
print("Enter id")
id = input()
print('Edit name:')
name = input()
print('Edit price:')
price = input()
cursor = self.db.cursor()
val = (name, price, id)
cursor.execute(
"update " + self.__table_name +
" set stockname=%s, stockprice=%s where stockid=%s", val)
self.db.commit()
print(cursor.rowcount, "record update.")
except ValueError as e:
print(e)
def test_rawfetchall(self):
cursor = self.cnx.cursor(raw=True)
cursor.execute("SELECT 1")
try:
cursor.fetchall()
except errors.InterfaceError:
self.fail("fetchall() raises although result is available")
def findByID(self, eeID):
cursor = self.db.cursor()
sql = "select * from employees where eeID = %s"
values = [eeID]
cursor.execute(sql, values)
result = cursor.fetchone()
return self.convertToDict(result)
def findById(self, codenr): # Find a whiskey by entered the codenr
cursor = self.db.cursor()
sql = 'select * from whiskey where codenr = %s'
values = [ codenr ]
cursor.execute(sql, values)
result = cursor.fetchone()
return self.convertToDict(result)
def delete(self, codenr): # Delete whiskey with selected codenr
cursor = self.db.cursor()
sql = 'delete from whiskey where codenr = %s'
values = [codenr]
cursor.execute(sql, values)
return {}
def insert(self, id, name, location, state, distance, max_group_size, type,
season):
#query mysql to insert into table
query = (
"INSERT INTO tour "
"(tid, name, location, state, distance, maxGroupSize, type, season)"
"VALUES (%s, %s, %s, %s, %s, %s, %s, %s)")
args = (id, name, location, state, distance, max_group_size, type,
season)
try:
self.db = mysql.connector.connect(user='******',
database='adventure',
password="******")
cursor = self.db.cursor()
cursor.execute(query, args)
if cursor.lastrowid:
print('last insert id', cursor.lastrowid)
else:
print("The following was added to the table: ")
print(args)
self.db.commit()
self.addTour(self.db, cursor)
except mysql.connector.Error as error:
print("\n" + "There was an error")
print(error)
print('\n' + "Please type valid input" + "\n")
self.usrInputForInsert()
def count(self, table, params={}, join='AND'):
# 根据条件统计行数
try:
sql = 'SELECT COUNT(*) FROM `%s`' % table
if params:
where, whereValues = self.__contact_where(params)
sqlWhere = ' WHERE ' + where if where else ''
sql += sqlWhere
#sql = self.__joinWhere(sql,params,join)
cursor = self.__getCursor()
self.__display_Debug_IO(sql, tuple(whereValues)) #DEBUG
cursor.execute(sql, tuple(whereValues))
#cursor.execute(sql,tuple(params.values()))
result = cursor.fetchone()
return result[0] if result else 0
#except:
# raise BaseError(707)
except Exception as err:
try:
raise BaseError(707, err._full_msg)
except:
raise BaseError(707)
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 log_error_to_table(err):
sql = 'insert into '+mysql_server_controller_schema+'.client_errors (cmd,error,time_stamp) values (\''+element_map['CID']+'\',\''+err.replace('\'','|')+'\',now())'
try:
cursor = cnx.cursor()
cursor.execute(sql)
cnx.commit()
except mysql.connector.Error as err:
print err
return
def logDbAction(userid,logAction,logTime):
cursor = cnx.cursor()
insert = (userid,logAction,logTime)
querry = ("insert into logs (userid, action, time) VALUES (%s,%s, %s)")
cursor.execute(querry, insert)
result = cursor.fetchall
print(cursor.statement + " " + str(cursor.rowcount))
cursor.close
cnx.commit()
def create_command_id():
command_id = 'CMD_'
sql = 'select count(*)+1 as count ,date_format(now(),\'%Y_%m_%d_%H_%i_%s\') as c_time from queue_messages'
cursor = cnx.cursor(dictionary=True)
cursor.execute(sql)
for row in cursor:
command_id = command_id + str(row['c_time'])+'_'+str(row['count'])
print ('Command ID - '+command_id)
return command_id
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)
def countBySql(self,sql,params = {},join = 'AND'):
# 自定义sql 统计影响行数
try:
cursor = self.__getCursor()
sql = self.__joinWhere(sql,params,join)
cursor.execute(sql,tuple(params.values()))
result = cursor.fetchone();
return result[0] if result else 0
except:
raise BaseError(707)
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 _test_callproc_cleanup(self, connection):
sp_names = ('myconnpy_sp_1', 'myconnpy_sp_2', 'myconnpy_sp_3')
stmt_drop = "DROP PROCEDURE IF EXISTS %s"
try:
cursor = connection.cursor()
for sp_name in sp_names:
cursor.execute(stmt_drop % sp_name)
except errors.Error, e:
self.fail("Failed cleaning up test stored routine; %s" % e)
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 (StandardError), e:
self.fail("Failed setting up test table; %s" % e)
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 view_sales_spec_year(self):
## get sql
screen = self._Menu_Node__tree._Menu_Tree__screen
screen.output("Enter Year")
while True:
screen.update()
ans = screen.input()
if not ans.isdecimal(): continue
year = int(ans)
break
cursor.execute(sql, (year, ))
interface.table.table(cursor, screen)
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 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 newrequest(cnx, cursor, id, placeid, which):
if which == 0:
start = input('If there is a start address, input it. Otherwise press enter\n')
end = input('If there is an end address, input it. Otherwise press enter\n')
desc = input('Please input the bike feature description:\n')
if start.isdigit():
if end.isdigit():
new = ("INSERT INTO change_requests "
"(userid, changetype, streetid, startaddress, endaddress, description) "
"VALUES (%s, %s, %s, %s, %s, %s)")
cursor.execute(new, (id, "new", placeid, start, end, desc))
else:
new = ("INSERT INTO change_requests "
"(userid, changetype, streetid, startaddress,description) "
"VALUES (%s, %s, %s, %s, %s)")
cursor.execute(new, (id, "new", placeid, start, desc))
cnx.commit()
else:
new = ("INSERT INTO change_requests "
"(userid, changetype, streetid, description) "
"VALUES (%s, %s, %s, %s)")
cursor.execute(new, (id, "new", placeid, desc))
cnx.commit()
print("Change request submitted")
elif which == 1:
desc = input('Please input the bike feature description:\n')
new = ("INSERT INTO change_requests "
"(userid, changetype, intersectionid, description) "
"VALUES (%s, %s, %s, %s)")
cursor.execute(new, (id, "new", placeid, desc))
cnx.commit()
print("Change request submitted")
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 deleteByAttr(self,table,params={},join='AND'):
# 删除数据
try :
fields = ','.join(k+'=%s' for k in params.keys())
sql = "DELETE FROM `%s` "% table
sql = self.__joinWhere(sql,params,join)
cursor = self.__getCursor()
cursor.execute(sql,tuple(params.values()))
self.__conn.commit()
return cursor.rowcount
#except:
# raise BaseError(704)
except Exception as err:
raise BaseError(704,err._full_msg)
def update_status(dct_update_status):
sql = "%s%s" % ('insert into '+mysql_server_controller_schema+'.client_status (row_recieved,successfully_processed,time_stamp, status_sent,CMD_ID)',
' values (\''+str(dct_update_status['row_count'])+'\',\''+str(dct_update_status['rows_processed'])+'\',now(),\'P\',\''+element_map['CID']+'\')')
print sql;
try:
cursor = cnx.cursor()
cursor.execute(sql)
cnx.commit()
except mysql.connector.Error as err:
print err
cid = element_map['CID']
update_origin_server(cid)
return 0
def view_sales_spec_month(self):
## get sql
screen = self._Menu_Node__tree._Menu_Tree__screen
screen.output("Enter Month and Year, Seperated By '/'")
while True:
screen.update()
ans = screen.input().split("/")
if len(ans) != 2: continue
if not ans[0].isdecimal(): continue
if not ans[1].isdecimal(): continue
if int(ans[0]) > 12: continue
month, year = int(ans[0]), int(ans[1])
break
cursor.execute(sql, (month, year))
interface.table.table(cursor, screen)
def findBySql(self,sql,params = {},limit = 0,join = 'AND',lock=False):
"""
自定义sql语句查找
limit = 是否需要返回多少行
params = dict(field=value)
join = 'AND | OR'
"""
try :
cursor = self.__getCursor()
sql = self.__joinWhere(sql,params,join)
cursor.execute(sql,tuple(params.values()))
rows = cursor.fetchmany(size=limit) if limit > 0 else cursor.fetchall()
result = [dict(zip(cursor.column_names,row)) for row in rows] if rows else None
return result
except:
raise BaseError(706)
def _execute(self, cursor, query, parameters, kwparameters):
try:
return cursor.execute(query, kwparameters or parameters)
except mysql.connector.errors.OperationalError:
logging.error("Error connecting to MySQL on %s", self.host)
self.close()
raise
def __query(self,table,criteria,all=False,isDict=True,join='AND'):
'''
table : 表名
criteria : 查询条件dict
all : 是否返回所有数据，默认为False只返回一条数据,当为真是返回所有数据
isDict : 返回格式是否为字典，默认为True ，即字典否则为数组
'''
try :
if all is not True:
criteria['limit'] = 1 # 只输出一条
sql,params = self.__contact_sql(table,criteria,join) #拼sql及params
'''
# 当Where为多个查询条件时，拼查询条件 key 的 valuse 值
if 'where' in criteria and 'dict' in str(type(criteria['where'])) :
params = criteria['where']
#params = tuple(params.values())
where ,whereValues = self.__contact_where(params)
sql+= ' WHERE '+where if where else ''
params=tuple(whereValues)
else :
params = None
'''
#__contact_where(params,join='AND')
cursor = self.__getCursor()
self.__display_Debug_IO(sql,params) #DEBUG
#if self.DataName=="ORACLE":
#sql="select * from(select * from(select t.*,row_number() over(order by %s) as rownumber from(%s) t) p where p.rownumber>%s) where rownum<=%s" % ()
#pass
cursor.execute(sql,params if params else ())
rows = cursor.fetchall() if all else cursor.fetchone()
if isDict :
result = [dict(zip(cursor.column_names,row)) for row in rows] if all else dict(zip(cursor.column_names,rows)) if rows else {}
else :
result = [row for row in rows] if all else rows if rows else []
return result
except Exception as err:
try :
raise BaseError(706,err._full_msg)
except :
raise BaseError(706)
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