def create_user():
print ('create')
data = request.json
browser = data['browser']
ip = data['ip']
query = "SELECT COUNT(*) FROM users WHERE browser = %s AND ip = %s"
try:
dbconfig = read_db_config()
conn = MySQLConnection(**dbconfig)
cursor = conn.cursor()
cursor.execute(query, (browser, ip),)
count = cursor.fetchone()
count = count[0]
except Error as e:
print(e)
finally:
cursor.close()
conn.close()
if count != 0:
query = "SELECT id FROM users WHERE browser = %s AND ip = %s"
try:
dbconfig = read_db_config()
conn = MySQLConnection(**dbconfig)
cursor = conn.cursor()
cursor.execute(query, (browser, ip),)
id = cursor.fetchone()
except Error as e:
print(e)
finally:
cursor.close()
conn.close()
#if user already exist return his id
return jsonify({'state': 'fail', 'index' : id})
#if user is not exist add him to db
query = "INSERT INTO users (date, browser, ip) VALUES (%s, %s, %s)"
date = datetime.now()
try:
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
cursor.execute(query, (date, browser, ip, ),)
conn.commit()
except Error as error:
print(error)
finally:
cursor.close()
conn.close()
return jsonify({'state': 'OK'})
def login_page():
error = ''
gc.collect()
try:
dbconfig = read_db_config()
conn = MySQLConnection(**dbconfig)
cur = conn.cursor()
if request.method == "POST":
query = ("""SELECT * FROM users WHERE username = %s""")
cur.execute(query, (request.form['username'],))
userpw = cur.fetchone()[2]
if sha256_crypt.verify(request.form['password'], userpw):
session['logged_in'] = True
session['username'] = request.form['username']
session['user-ip'] = request.remote_addr
if session['username'] == 'admin':
flash(Markup('The Dark Knight <span class="glyphicon glyphicon-knight"></span>'))
else:
flash('Logged In')
return redirect(url_for('blog'))
else:
error = "Invalid Credentials. Please try again."
gc.collect()
return render_template('login.html', error = error)
except Exception as e:
error = 'Invalid Credentials. Please try again.'
return render_template('login.html', error = error)
def insert_book(title, isbn):
query = "INSERT INTO books(title, isbn) " "VALUES(%s, %s)"
args = (title, isbn)
try:
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
cursor.execute(query, args)
if cursor.lastrowid:
print ("last insert id", cursor.lastrowid)
else:
print ("last insert id not found")
conn.commit()
except Error as error:
print error
finally:
cursor.close()
conn.close()
def insert_user(name, password, email):
query = "INSERT INTO users(username, password, email) " \
"VALUES(%s,%s,%s)"
args = (name, password, email)
try:
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
cursor.execute(query, args)
if cursor.lastrowid:
print('last insert id', cursor.lastrowid)
else:
print('last insert id not found')
conn.commit()
except Error as error:
print(error)
finally:
cursor.close()
conn.close()
def update_book(book_id, title):
# read database configuration
db_config = read_db_config()
# prepare query and data
query = """ UPDATE books
SET title = %s
WHERE id = %s """
data = (title, book_id)
try:
conn = MySQLConnection(**db_config)
# update book title
cursor = conn.cursor()
cursor.execute(query, data)
# accept the changes
conn.commit()
except Error as error:
print(error)
finally:
cursor.close()
conn.close()
def insert_name(name, gender):
query = "INSERT INTO babynames(babyName, gender) " \
"VALUES(%s,%s)"
args = (name, gender)
try:
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
cursor.execute(query, args)
if cursor.lastrowid:
print('last insert id', cursor.lastrowid)
else:
print('last insert id not found')
conn.commit()
except Error as error:
print(error)
finally:
cursor.close()
conn.close()
def insert_imei(query, args):
try:
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
cursor.execute(query, args)
if cursor.lastrowid:
mensaje = ('Last insert id: %s' % (cursor.lastrowid,) )
print(mensaje)
else:
mensaje = 'Last insert id not found'
conn.commit()
except Error as error:
print(error)
finally:
cursor.close()
conn.close()
return mensaje
def insert_character(char_id, name, realname, gender, origin, image, siteurl, deck):
query = "INSERT INTO characters(id, name, realname, gender, origin, image, siteurl, deck) " \
"VALUES(%s, %s, %s, %s, %s, %s, %s, %s)"
args = (char_id, name, realname, gender, origin, image, siteurl, deck)
try:
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
cursor.execute(query, args)
if cursor.lastrowid:
print('last insert id', cursor.lastrowid)
else:
print('last insert id not found')
conn.commit()
except Error as error:
print error
finally:
cursor.close()
conn.close()
def input_data():
for x in range(len(gabungan)):
gabungan[x]
query = "INSERT INTO data_test "
nilai = "VALUES (\"\"," + "\"" + file + "\"" + ","
for x in range(len(gabungan)):
nilai = nilai + str(gabungan[x])
if x < len(gabungan)-1 :
nilai = nilai + ", "
query = query + nilai + ")"
# print query
try:
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
cursor.execute(query)
conn.commit()
except Error as e:
print('Error:', e)
finally:
cursor.close()
conn.close()
def __init__(self):
self.current_job_id = 0
self.current_job_count = 0
self.job_list = dict()
self.node_list = dict()
self.section_list = dict()
self.acc_type_list = dict() # <acc_name:acc_bw>
self.node_list = dict() # <node_ip: PowerNode>
self.job_list = dict()
self.dbconfig = read_db_config()
self.conn = MySQLConnection(**self.dbconfig)
def read_db():
try:
dbconfig = read_db_config()
conn = MySQLConnection(**dbconfig)
cursor = conn.cursor()
cursor.execute("SELECT * FROM temperature;")
row = cursor.fetchone()
while row is not None:
print(row)
row = cursor.fetchone()
finally:
cursor.close()
conn.close()
def post(self):
try:
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
cursor.callproc('sp_insertmember')
except Error as e :
return {'error': str(e)}
finally:
cursor.close()
conn.close()
def __init__(self, fpga_node_num, total_node_num):
self.dbconfig = read_db_config()
self.conn = MySQLConnection(**self.dbconfig)
self.acc_type_list = dict()
self.total_node_num = total_node_num
self.fpga_node_num = fpga_node_num
self.each_section_num = 4
self.acc_type_list[0] = ('1','acc0','EC','1200','1')
self.acc_type_list[1] = ('2','acc1','AES','1700','1')
self.acc_type_list[2] = ('3','acc2','FFT','1200','1')
self.acc_type_list[3] = ('4','acc3','DTW','1200','1')
self.acc_type_list[4] = ('5','acc4','SHA','150','0')
self.acc_type_num = len(self.acc_type_list)
def search():
try:
dbconfig = read_db_config()
conn = MySQLConnection(**dbconfig)
cursor = conn.cursor()
cursor.execute("SELECT * FROM data_train")
#row_train = cursor.fetchone()
data_train = list(cursor)
cursor = conn.cursor()
cursor.execute("SELECT * FROM data_test")
data_test = list(cursor)
index1 = 0
for x in range(len(data_train)) :
canbera = 0
index2 = 2
for y in range(82):
temp1 = (data_test[0][index2] - data_train[index1][index2])
temp2 = (data_test[0][index2] + data_train[index1][index2])
#print "temp1 "+str(temp1)
#print "temp2 "+str(temp2)
if temp2 == 0 :
hasil_temp = abs(temp1)
else :
hasil_temp = float(abs(temp1)) / float(temp2)
#print "hasil temp = " + str(hasil_temp)
canbera = float(canbera) + float(hasil_temp)
index2 += 1
index1+=1
print canbera
#for x in range(len(gabungan)):
#row_train[x]
except Error as e:
print(e)
finally:
cursor.close()
conn.close()
def insert_many(entry):
query = "insert into test(name, class, skills) values(%s, %s, %s)"
try:
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
cursor.executemany(query, entry)
conn.commit()
except Error as e:
print "[SOMETHING BAD HAPPEND!]",e
finally:
cursor.close()
conn.close()
def __init__(self, scheduling_algorithm):
self.scheduling_algorithm = scheduling_algorithm
self.current_job_count = 0
self.job_list = dict()
self.node_list = dict()
self.section_list = dict()
self.acc_type_list = dict() # <acc_name:acc_bw>
self.node_list = dict() # <node_ip: PowerNode>
self.job_list = dict()
self.job_waiting_list = dict()#<job_id: weight> could be arrival time, execution time, etc;
self.epoch_time = time.time()
self.dbconfig = read_db_config()
self.conn = MySQLConnection(**self.dbconfig)
def get_users_one_month():
count = 0
try:
dbconfig = read_db_config()
conn = MySQLConnection(**dbconfig)
cursor = conn.cursor()
cursor.execute("SELECT COUNT(*) FROM users")
count = cursor.fetchone()
except Error as e:
print(e)
finally:
cursor.close()
conn.close()
count = count[0]
return jsonify({'count': count})
def query_with_fetchmany():
print "FetchMany...........\n"
try:
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
cursor.execute('select * from test')
for row in iter_rows(cursor, 10):
print row
except Error as e:
print "[SOMETHING BAD HAPPEND ]", e
finally:
conn.close()
cursor.close()
def __init__(self, mean):
self.dbconfig = read_db_config()
self.conn = MySQLConnection(**self.dbconfig)
self.acc_type_list = ['acc0','acc1','acc2','acc3','acc4']
self.job_size_list = ['1','1','0.3','0.5','2']
self.job_node_list = list()
self.fpga_available_node_list = list()
self.fpga_non_available_node_list = list()
self.acc_bw_list = dict()
self.poisson_interval_mean = mean
self.exponential_interval_mean = mean
for acc_id in self.acc_type_list:
self.acc_bw_list[acc_id] = '1.2'
if acc_id == 'acc4':
self.acc_bw_list[acc_id] = '0.15'
def update_user(user_id):
db_config = read_db_config()
query = "UPDATE users SET date = %s WHERE id = %s"
date = datetime.now()
try:
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
cursor.execute(query, (date, user_id,),)
conn.commit()
except Error as error:
print(error)
finally:
cursor.close()
conn.close()
return jsonify({'update': 'OK'})
def query_with_fetchmany():
try:
dbconfig=read_db_config()
conn=MySQLConnection(**dbconfig)
cursor=conn.cursor()
cursor.execute("Select * from books")
for row in iter_row(cursor,10):
print row
except Error as e:
print e
finally:
cursor.close()
conn.close()
def connect():
db_config = read_db_config()
try:
print 'Connecting to database: {0}...'.format(db_config['database'])
conn = MySQLConnection(**db_config)
if conn.is_connected():
print 'Connection established.'
return conn
else:
print 'Connection failed.'
except Error as e:
print e.message
def update_blob(id, data):
query = "UPDATE Puzzles " "SET puzzledata = %s " "WHERE puzzleid = %s"
args = (data, id)
db_config = read_db_config()
try:
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
cursor.execute(query, args)
conn.commit()
except Error as e:
print(e)
finally:
cursor.close()
conn.close()
def query(q):
try:
dbconfig = read_db_config()
conn = MySQLConnection(**dbconfig)
cursor = conn.cursor()
cursor.execute(q)
rows = cursor.fetchall()
return rows
except Error as e:
print(e)
finally:
cursor.close()
conn.close()
def register_page():
try:
form = RegistrationForm(request.form)
if request.method == "POST" and form.validate():
username = form.username.data
email = form.email.data
password = sha256_crypt.encrypt((str(form.password.data)))
dbconfig = read_db_config()
conn = MySQLConnection(**dbconfig)
cur = conn.cursor()
query = ("""SELECT * FROM users WHERE username = %s""")
cur.execute(query, (username,))
row = cur.fetchone()
if row:
flash("That username has already been used. Please try another")
return render_template ('register.html', form = form)
else:
try:
url = '/about/'
query = ("INSERT INTO users(username, email, password, tracking)"
"VALUES (%s,%s,%s,%s)")
cur.execute(query,(username, email, password, url))
conn.commit()
cur.close()
conn.close()
gc.collect()
session['logged_in'] = True
session['username'] = username
flash('Success')
return redirect(url_for('blog'))
except Exception as e:
return render_template('error.html', error = 'Error occured. Please refresh')
return render_template("register.html", form=form)
except Exception as e:
return render_template('error.html', error = 'An error occured. Please refresh.')
def connect(): ''' Connect to MySql Database''' db_config = read_db_config() print db_config try: conn = MySQLConnection(**db_config) print "Connecting to MySql database..." if conn.is_connected(): print "Connection establish" else: print "Connection Failed" except Error as e: print "[SOMETHING BAD HAPPEND...]",e finally: conn.close() print 'Connection Closed'
def query_with_fetchall():
print "FetchAll..........."
try:
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
cursor.execute('select * from test')
rows = cursor.fetchall()
for row in rows:
print row
except Error as e:
print "[SOMETHING BAD HAPPEND ]", e
finally:
conn.close()
cursor.close()
def findRestaurantById(id):
try:
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
cursor.execute("SELECT * FROM restaurants where restaurant_id = " + id)
row = cursor.fetchone()
print row
except Error as error:
print error
finally:
cursor.close()
conn.close()
def query_with_fetchall():
try:
dbconfig = read_db_config()
conn = MySQLConnection(**dbconfig)
cursor = conn.cursor()
cursor.execute("SELECT * FROM books")
rows = cursor.fetchall()
print('Total Row(s):', cursor.rowcount)
for row in rows:
print(row)
except Error as e:
print(e)
finally:
cursor.close()
conn.close()
def query_with_fetchone():
print "FetchOne..........."
try:
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
cursor.execute('select * from test')
row = cursor.fetchone()
while row is not None:
print row
row = cursor.fetchone()
except Error as e:
print "[SOMETHING BAD HAPPEND ]", e
finally:
conn.close()
cursor.close()
def get_event_organizer(event_id):
try:
dbconfig = read_db_config()
conn = MySQLConnection(**dbconfig)
cursor = conn.cursor()
query = "SELECT tg_id FROM organizer WHERE event_id =" + str(event_id)
cursor.execute(query)
row = cursor.fetchone()
while row is not None:
cursor.close()
conn.close()
return (row)[0]
# row = cursor.fetchone()
# except Error as e:
# print(e)
finally:
cursor.close()
conn.close()
# return(event)
def get_event_id_by_name(tg_name):
try:
dbconfig = read_db_config()
conn = MySQLConnection(**dbconfig)
cursor = conn.cursor()
query = "SELECT event_id FROM event WHERE tg_name ='" + str(tg_name) + "'"
cursor.execute(query)
row = cursor.fetchone()
while row is not None:
cursor.close()
conn.close()
print(row)
return(row)[0]
# row = cursor.fetchone()
# except Error as e:
# print(e)
finally:
cursor.close()
conn.close()
def query_with_fetchone():
try:
dbconfig = read_db_config()
conn = MySQLConnection(**dbconfig)
cursor = conn.cursor()
cursor.execute("Select * from books;")
row = cursor.fetchone()
while row is not Null:
print row
row = cursor.fetchone()
except Error as e:
print e
finally:
cursor.close()
conn.close()
def connection_test():
db_config = read_db_config()
try:
print 'Connecting to database: {0}...'.format(db_config['database'])
conn = MySQLConnection(**db_config)
if conn.is_connected():
print 'Connection established.'
else:
print 'Connection failed.'
except Error as e:
print e.message
finally:
conn.close()
print 'Connection closed.'
def update_records(name, classNM):
db_config = read_db_config()
query = "update test set class = %s where name=%s "
data = (classNM, name)
try:
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
cursor.execute(query, data)
conn.commit()
except Error as e:
print "[SOMETHING BAD HAPPEND]", e
finally:
cursor.close()
conn.close()
def query_with_fetchone():
try:
dbconfig = read_db_config()
conn = MySQLConnection(**dbconfig)
cursor = conn.cursor()
cursor.execute("SELECT * FROM insert_name")
row = cursor.fetchone()
while row is not None:
print(row)
row = cursor.fetchone()
except Error as e:
print(e)
finally:
cursor.close()
conn.close()
def existeUsuarios(co):
query=" SELECT nombre FROM usuarios WHERE correo = %s "
dbconfig = read_db_config()
try:
conn = MySQLConnection(**dbconfig)
cursor = conn.cursor()
print(co)
cursor.execute(query, (co,))
row=cursor.fetchone()
if row is not None:
encontrado='si'
else:
encontrado='no'
except Error as error:
print(error)
finally:
cursor.close()
conn.close()
return encontrado
def turnosjuadorAgendaTenis(jugador, fec):
turnosjugador = []
query = " SELECT * FROM agenda_tenis WHERE fecha = %s AND jugador = %s "
data = (fec, jugador)
dbconfig = read_db_config()
try:
conn = MySQLConnection(**dbconfig)
cursor = conn.cursor()
cursor.execute(query, data)
row = cursor.fetchone()
while row is not None:
turnosjugador.append(row)
row = cursor.fetchone()
except Error as error:
print(error)
finally:
cursor.close()
conn.close()
return turnosjugador
def consultadatoAgendaGolf(clu, cam, fec, tur, dato):
q1 = " SELECT "
q2 = " FROM agenda_golf WHERE club=%s AND campo=%s AND fecha = %s AND turno=%s "
query = q1 + dato + q2
data = (clu, cam, fec, tur)
dbconfig = read_db_config()
hora: ''
try:
conn = MySQLConnection(**dbconfig)
cursor = conn.cursor()
cursor.execute(query, data)
row = cursor.fetchone()
hora = row[0]
except Error as error:
print(error)
finally:
cursor.close()
conn.close()
return hora
def query_with_fetchall():
try:
dbconfig = read_db_config()
conn = MySQLConnection(**dbconfig)
cursor = conn.cursor()
cursor.execute("SELECT * FROM parking_lot")
rows = cursor.fetchall()
# print('Total Row(s):', cursor.rowcount)
# for row in rows:
# print(row)
except Error as e:
print(e)
finally:
cursor.close()
conn.close()
return rows
def newuser(idi):
query = ("INSERT INTO users SET `chatid`='12345'")
try:
dbconfig = read_db_config()
conn = MySQLConnection(**dbconfig)
cursor = conn.cursor()
cursor.execute(query)
row = cursor.fetchone()
print(row)
except Error as e:
print(e)
return (e)
finally:
cursor.close()
conn.close()
def connect():
""" Connect to MySQL database """
db_config = read_db_config()
try:
print('Connecting to MySQL database...')
conn = MySQLConnection(**db_config)
if conn.is_connected():
print('Connection established.')
else:
print('Connection failed.')
except Error as error:
print(error)
finally:
conn.close()
print('Connection closed.')
def events_tomorrow():
try:
dbconfig = read_db_config()
conn = MySQLConnection(**dbconfig)
cursor = conn.cursor()
date = datetime.date.today() + datetime.timedelta(days=1)
query = "SELECT event_id FROM event WHERE start_date BETWEEN '" + str(datetime.date.today()) + "' AND '" + str(date) + "'"
cursor.execute(query)
row = cursor.fetchone()
events = []
while row is not None:
events.append(row[0])
row = cursor.fetchone()
except Error as e:
print(e)
finally:
cursor.close()
conn.close()
return (events)
def getRoomType():
try:
dbconfig = read_db_config()
conn = MySQLConnection(**dbconfig)
cursor = conn.cursor()
cursor.execute("""SELECT
bot_table.roomType
FROM botdb.bot_table""")
for row in iter_row(cursor, 10):
print(row)
except Error as e:
print(e)
finally:
cursor.close()
conn.close()
def createNewTable(tableName):
try:
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
query = "CREATE TABLE " + tableName + "(Name VARCHAR(50) NOT NULL PRIMARY KEY, date_entered TIMESTAMP, Original_Price numeric(6,2) NOT NULL, Discount_Price numeric(6,2) NOT NULL);"
cursor.execute(query)
conn.commit
except Error as e:
print('Error', e)
finally:
cursor.close()
conn.close()
def __init__(self, parent=None):
super(winlevel1, self).__init__(parent)
loadUi('level1.ui', self)
self.btn_exit.clicked.connect(self.exitwinlevel1)
self.btn_sendValue.clicked.connect(self.sendValue)
#Combobox where we are going to pass the value of id from this class to another
try:
dbconfig = read_db_config()
conn = MySQLConnection(**dbconfig)
#Read the products name and id
cursor = conn.cursor()
cursor.execute("SELECT * FROM product")
result_set = cursor.fetchall()
except Error as error:
logger.exception(error)
for products in result_set:
self.combo_products.addItem(products[1], int(products[0]))
cursor.close()
def read_blob(author_id, filename):
query = "SELECT photo FROM authors WHERE id = %s"
db_config = read_db_config()
try:
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
cursor.execute(query, (author_id, ))
photo = cursor.fetchone()
write_file(photo, filename)
except Error as e:
print(e)
finally:
cursor.close()
conn.close()
def insert_characters(character_info):
query = "INSERT INTO characters(id, name, realname, gender, origin, image, siteurl, deck) " \
"VALUES(%s, %s, %s, %s, %s, %s, %s, %s)"
try:
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
cursor.executemany(query, character_info)
conn.commit()
except Error as error:
print error
finally:
cursor.close()
conn.close()
def getordercost(orderid):
string = "select cost from orderheader where orderID =" + str(
orderid) + ";"
connectionstring = read_db_config()
con = mdb.connect(connectionstring.get('host'),
connectionstring.get('user'),
connectionstring.get('password'),
connectionstring.get('database'))
# With will close the connection after the code is done,
# regardless of how the code exists. Use as an alternative to 'finally' statement
with con:
cur = con.cursor()
cur.execute(string)
# Get all of the MySQL return at once
# Returns a tuple of tuples, with each inner tupple being one row
result = cur.fetchall()
con.close()
return result[0][0]
def insert_powers(power_info):
query = "INSERT INTO powers(character_id, name) " \
"VALUES(%s, %s)"
try:
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
cursor.executemany(query, power_info)
conn.commit()
except Error as error:
print error
finally:
cursor.close()
conn.close()
def insert_orderer(name, tel, email):
if name == "":
messagebox.showerror(
'Ошибка',
'Отсутствуют необходимые параметры.\nЗаполните все поля формы и попробуйте снова'
)
return 0
if tel == "":
messagebox.showerror(
'Ошибка',
'Отсутствуют необходимые параметры.\nЗаполните все поля формы и попробуйте снова'
)
return 0
if email == "":
messagebox.showerror(
'Ошибка',
'Отсутствуют необходимые параметры.\nЗаполните все поля формы и попробуйте снова'
)
return 0
else:
query = "INSERT INTO orderers(name,telephone,email) VALUES(%s,%s,%s)"
args = (name, tel, email)
try:
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
cursor.execute(query, args)
if cursor.lastrowid:
print('last insert id', cursor.lastrowid)
messagebox.showinfo('Сообщение', 'запись добавлена в базу')
else:
print('last insert id not found')
conn.commit()
except Error as error:
print(error)
finally:
cursor.close()
conn.close()
def getinv():
string = "Select recordnum,itemid, onhand, location From inventory;"
connectionstring = read_db_config()
con = mdb.connect(connectionstring.get('host'),
connectionstring.get('user'),
connectionstring.get('password'),
connectionstring.get('database'))
# With will close the connection after the code is done,
# regardless of how the code exists. Use as an alternative to 'finally' statement
with con:
cur = con.cursor()
cur.execute(string)
# Get all of the MySQL return at once
# Returns a tuple of tuples, with each inner tupple being one row
result = cur.fetchall()
return result
con.close()
def check_email(msg_received):
email = msg_received["email"]
dbconfig = read_db_config()
conn = MySQLConnection(**dbconfig)
cursor = conn.cursor()
cursor.execute("SELECT * FROM `user` WHERE `email`='" + email + "';")
row = cursor.fetchall()
if len(row) != 0:
conn.close()
cursor.close()
return json.dumps({'email': '1'})
else:
conn.close()
cursor.close()
return json.dumps({'email': '0'})
def getvendors():
query = "SELECT vendorID FROM mastervendor"
connectionstring = read_db_config()
con = mdb.connect(connectionstring.get('host'), connectionstring.get('user'), connectionstring.get('password'),
connectionstring.get('database'))
# With will close the connection after the code is done,
# regardless of how the code exists. Use as an alternative to 'finally' statement
with con:
cur = con.cursor()
cur.execute(query)
# Get all of the MySQL return at once
# Returns a tuple of tuples, with each inner tupple being one row
result = cur.fetchall()
return result
con.close()
def findDistinctCuisineNames():
try:
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
cursor.execute("SELECT DISTINCT cuisine_name FROM cuisine_restaurant")
rows = cursor.fetchall()
# for row in rows:
# print row
return rows
except Error as error:
print error
finally:
cursor.close()
conn.close()
def findAllCuisines():
try:
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
cursor.execute("SELECT * FROM cuisine")
rows = cursor.fetchall()
# for row in rows:
# print row
return rows
except Error as error:
print error
finally:
cursor.close()
conn.close()
def get_id_by_name(customer_first_name, customer_second_name):
try:
dbconfig = read_db_config()
conn = MySQLConnection(**dbconfig)
cursor = conn.cursor()
#print(customer_first_name, customer_second_name)
query = "SELECT ID_Customer FROM customer WHERE First_Name_Customer = '" + customer_first_name + "' " \
"AND Second_Name_Customer = '" + customer_second_name + "'"
# print(query)
cursor.execute(query)
row = cursor.fetchone()
while row is not None:
cursor.close()
conn.close()
#print(row)
return(row)[0]
finally:
cursor.close()
conn.close()
def query_with_fetchone():
try:
dbconfig = read_db_config()
conn = MySQLConnection(**dbconfig)
cursor = conn.cursor()
cursor.execute("SELECT id, reg_date FROM count_passengers")
row = cursor.fetchone()
while row is not None:
print(row)
row = cursor.fetchone()
except Error as e:
print(e)
finally:
cursor.close()
conn.close()
def fetchAll(header):
user_id = tokens.getID(header)
if user_id == "Error expired token" or user_id == "Error invalid token":
return json.dumps({'Error': 'login in again'})
else:
dbconfig = read_db_config()
conn = MySQLConnection(**dbconfig)
cursor = conn.cursor()
cursor.execute("SELECT * FROM `profile_photo` WHERE user_id='" +
str(user_id) + "';")
profile_pic = cursor.fetchall()
if len(profile_pic) == 1:
for info in profile_pic:
result = collection.find({"posted_by": user_id})
data = []
for i in result:
posts = {
'post_id': i['post_id'],
'post_details': i['post_details'],
'user': {
'userName': i['userName'],
'fullName': i['fullName'],
'posted_by': i['posted_by'],
'profile_photo': info[2]
},
'uniqueID': info[3],
'timestamp': i['timestamp'],
'images': i['images'],
'audio': i['audio'],
'video': i['video'],
'post_likes': i['post_likes']
}
data.append(posts)
return json.dumps(data)
def getUnitData():
try:
dbconfig = read_db_config()
conn = MySQLConnection(**dbconfig)
cursor = conn.cursor()
cursor.execute("SELECT * FROM addresses")
row = cursor.fetchone()
while row is not None:
print(row)
row = cursor.fetchone()
except Error as e:
print(e)
finally:
cursor.close()
conn.close()