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 main():
print('Put txt file in the same folder of exe!')
table_name = 'lst_' + (input('Please name your list (lst_ will be add as prefix automatically):').lower())
if table_name == 'lst_':
sys.exit(0)
exist_control = exist_list(table_name)
if exist_control is None:
create_table(table_name)
elif exist_control == 0:
drop_table(table_name)
create_table(table_name)
else:
print('The named table is exist and contain ' + str(exist_control) + ' Records')
ask = input('Do you want to drop ' + str(exist_control) + ' table? (yes/no)')
if ask.lower() == 'yes':
drop_table(table_name)
create_table(table_name)
else:
print('Exit without any change!')
sys.exit(0)
extension = str(input('Please Enter Your Extension(ir,co.ir ,...):'))
list_method=input('Choice your Method( 1:Auto Letter Generator 2:Import Text File ) :')
if list_method == '1':
letter_number = int(input('Number of Letters:'))
keywords = [''.join(i) for i in product(ascii_lowercase, repeat=letter_number)]
db_config = read_db_config()
conn = MySQLConnection(**db_config)
for line in range(0, len(keywords)):
cursor = conn.cursor()
Query = "insert into %s (site) values (concat('%s','.','%s'));" \
% (table_name, '{0}'.format(str(keywords[line])),extension)
cursor.execute(Query)
print(str(line+1),end='\r')
print(str(line+1),'Records Imported!')
elif list_method == '2':
dic_filename_mask = str(input('Whats the Text Dictionary Filename {without extension}:'))
filename = dic_filename_mask + '.txt'
dic_list = open(filename)
print('Total Count of Records: ' + str(sum(1 for _ in dic_list)))
dic_list.close()
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
load_text_file_query = "LOAD DATA LOCAL INFILE '%s' INTO TABLE %s LINES TERMINATED BY '\\r\\n' (SITE);" \
% (filename, table_name)
print('Transferring List...')
cursor.execute(load_text_file_query)
print('Add Extension to list...')
update_extension_query = "update %s set site=concat(site,'.','%s')" % (table_name, extension)
cursor.execute(update_extension_query)
conn.commit()
cursor.close()
conn.close()
else:
print('Wrong Choice,Bye!')
print("Finish")
def data_laundry(user,saldo_user):
kodeLau = randomTiket()
total = 0
status = ""
print '1. Cuci laundry'
print '2. Cuci laundry + setrika'
print '3. Cuci laundry + setrika + delivery'
pilLaundry = input('Masukan pilihan : ')
berat = input('Masukan berat pakaian : ')
if pilLaundry == 1 :
total = 5000 * berat
elif pilLaundry == 2:
total = 7000 * berat
elif pilLaundry == 3:
total = 7000 * berat + 3000
fix_update = saldo_user - total
if fix_update < 0:
status = "Lunas"
print "Saldo anda tidak mencukupi"
tambah_saldo = raw_input("Ingin menambah saldo anda ? ")
if tambah_saldo == 'y' or 'Y' or 'Ya' or 'YA' or 'ya':
saldoTambah(saldo_user,fix_update,user)
else:
status = "Lunas"
query1 = "INSERT INTO data_user (kodeLau,user,status,total)"\
"VALUES(%s,%s,%s,%s)"
args1 = (kodeLau,user,status,total)
try:
dbConfig = bacaConfig()
connection = MySQLConnection(**dbConfig)
query = connection.cursor()
query.execute(query1,args1)
if query.lastrowid:
print "Data laundry berhasil dimasukan"
query2 = "UPDATE user SET saldo = %s WHERE user = %s"
data2 = (fix_update,user)
try:
dbConfig = bacaConfig()
conn2 = MySQLConnection(**dbConfig)
cursor2 = conn2.cursor()
cursor2.execute(query2,data2)
conn2.commit()
except Error as e:
print (e)
finally:
cursor2.close()
conn2.close()
else:
print "Gagal menginput"
connection.commit()
except Error as e:
print (e)
finally:
query.close()
connection.close()
def mysql_fetchall():
'''Query a MySQL database using fetchall()'''
try:
db_config = read_db_config()
#Creating a new MySQLConnection object
conn = MySQLConnection(**db_config)
#Creating a new MySQLCursor object from the MySQLConnection object
cursor = conn.cursor()
#Selects all rows from the space table
cursor.execute("SELECT * from space")
#selects all the rows in the cursor result set
rows = cursor.fetchall()
print('Number of rows: %d' % cursor.rowcount)
#prints the rows out
for row in rows:
print(row)
except Error as error:
print(error)
finally:
cursor.close()
conn.close()
print('Connection closed.')
def mysql_fetchone():
'''Query a MySQL database using fetchone()'''
try:
db_config = read_db_config()
#Creating a new MySQLConnection object
conn = MySQLConnection(**db_config)
#Creating a new MySQLCursor object from the MySQLConnection object
cursor = conn.cursor()
#Selects all rows from the space table
cursor.execute("SELECT * from space")
#selects the next row in the cursor result set
row = cursor.fetchone()
#prints the row out and gets the next row
while row is not None:
print(row)
row = cursor.fetchone()
except Error as error:
print(error)
finally:
cursor.close()
conn.close()
print('Connection closed.')
def create_table(tbl_name_val):
query = "CREATE TABLE IF NOT EXISTS %s ( " \
"No int NOT NULL auto_increment," \
"Site varchar(100)," \
"Status varchar(30)," \
"Email varchar(100)," \
"Person varchar(100)," \
"Phone varchar(25)," \
"lockedby bigint, " \
"lockedat datetime," \
"Trycount int default 0 , " \
"Hostname varchar(50)," \
"HFlag char(1) DEFAULT NULL," \
"IP varchar(15) DEFAULT NULL," \
"PRIMARY KEY (No)" \
") ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;" % tbl_name_val
args = tbl_name_val
try:
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
cursor.execute(query)
print('New Table Created:' + str(tbl_name_val))
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 ads():
website = request.args.get('website', 'earthtravelers.com')
user = app.config['DB_LOGIN']
password = app.config['DB_PW']
host = app.config['DB_HOST']
database = app.config['DB_NAME']
conn = MySQLConnection(user=user, password=password, host=host, database=database)
conn.autocommit = True
cursor = conn.cursor()
args = (website,)
try:
cursor.callproc('AdMania.prc_GetAds', args)
except Error as e:
print e
# In order to handle multiple result sets being returned from a database call,
# mysql returns results as a list of lists.
# Therefore, even if there is only one result set, you still have to get it from the list of lists.
for result in cursor.stored_results():
row_set = result.fetchall()
result_set = []
for row in row_set:
result_set.append(row[0].decode().replace('##DOMAIN_ID##', '7782886'))
cursor.close()
conn.close()
return render_template('T1.html',resultsSET=result_set)
def query_with_fetchone(Lista1,v2):
try:
conn = MySQLConnection(host=DB_HOST,user=DB_USER,password=DB_PASS,database=DB_NAME)
cursor = conn.cursor()
cursor.execute("SELECT nombre FROM clientes")
row = cursor.fetchone()
Lista1.delete(0,END)
while row is not None:
d=""
for i in row:
if i== "("or i==")"or i=="'"or i ==",":
pass
else:
d = d+i
Lista1.insert(END,d)
row = cursor.fetchone()
except Error as e:
print(e)
finally:
Lista1.grid(column=1,row=2)
update(v2)
cursor.close()
conn.close()
def get_comments(self, article_id):
try:
conn = MySQLConnection(**self.db)
query = "SELECT * FROM comment WHERE article_id=%s"
cursor = conn.cursor()
cursor.execute(query, (article_id,))
list_of_comments = list()
row_comment = cursor.fetchone()
if row_comment is None:
return None
while row_comment is not None:
list_of_comments.append(row_comment)
row_comment = cursor.fetchone()
return list_of_comments
except Error as e:
print(e)
finally:
cursor.close()
conn.close()
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 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 insert_one(star_name, star_location):
'''Insert one row into a MySQL database'''
query = ("INSERT INTO space(star_name, star_location)"
"VALUES(%s, %s)")
args = (star_name, star_location)
try:
db_config = read_db_config()
#Creating a new MySQLConnection object
conn = MySQLConnection(**db_config)
#Creating a new MySQLCursor object from the MySQLConnection object
cursor = conn.cursor()
#Inserts one row into the database
cursor.execute(query, args)
if cursor.lastrowid:
print('Insert ID: ', cursor.lastrowid)
else:
print('Insert Failure')
conn.commit()
except Error as error:
print(error)
finally:
cursor.close()
conn.close()
print('Connection closed.')
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 query_row(table):
"""Returns the next row of a query result set or None
str -> tuple"""
try:
dbconfig = read_db_config() # returns a dict of connection parameters
print("Connecting " + dbconfig["user"] + " to " + dbconfig["database"] + "...")
conn = MySQLConnection(**dbconfig)
if conn.is_connected():
print("Connection established.")
else:
print("Connection failed")
cursor = conn.cursor(buffered=True)
sql_command = "SELECT * FROM " + table
print("Executed command: " + sql_command + ".")
cursor.execute(sql_command)
row = cursor.fetchone()
return row
# The fetchall method is similar but memory-consuming
# rows = cursor.fetchall()
# print('Total rows:', cursor.rowcount)
# return rows
except Error as e:
print(e)
finally:
cursor.close()
conn.close()
def delete_one(star_id):
'''Delete one row of a MySQL database'''
query = "DELETE FROM space WHERE id = %s"
try:
db_config = read_db_config()
#Creating a new MySQLConnection object
conn = MySQLConnection(**db_config)
#Creating a new MySQLCursor object from the MySQLConnection object
cursor = conn.cursor()
#Deletes one row in the database
cursor.execute(query, (star_id,))
conn.commit()
except Error as error:
print(error)
finally:
cursor.close()
conn.close()
print('Connection closed.')
def login_data(request):
try:
username = request.POST.get('username','')
phone = request.POST.get('phone', '')
Error_dict = {}
dbconfig = {'password': '******', 'host': 'localhost', 'user': '******', 'database': 'login_database'}
conn = MySQLConnection(**dbconfig)
cursor = conn.cursor()
username = "******" + username + "'"
cursor.execute("select COUNT(*) from client_info where Name = " + username + " AND phone_no = " + str(phone) + " ")
count = cursor.fetchone()
if(count[0] <= 0) :
Error_dict['Wrong_values'] = "Wrong Username or Phone no"
else :
context_dict = {}
context_dict['name'] = username
return render_to_response('logged_in_user.html', context_dict)
except Error as e:
print(e)
finally:
cursor.close()
conn.close()
return render_to_response('login_form.html', Error_dict)
def update_one(star_id, star_location):
'''Update a field in a MySQL database'''
query = ("UPDATE space"
"SET star_name = %s"
"WHERE id = %s")
args = (star_name, star_id)
try:
db_config = read_db_config()
#Creating a new MySQLConnection object
conn = MySQLConnection(**db_config)
#Creating a new MySQLCursor object from the MySQLConnection object
cursor = conn.cursor()
#Updates a field in the database
cursor.execute(query, args)
conn.commit()
except Error as error:
print(error)
finally:
cursor.close()
conn.close()
print('Connection closed.')
def query_with_fetchone():
try:
dbconfig = {'password': '******', 'host': 'localhost', 'user': '******', 'database': 'stats'}
conn = MySQLConnection(**dbconfig)
cursor = conn.cursor()
cursor.execute("select COUNT(*) from clientBookings where MONTH(CheckInDate) = '2' AND YEAR(CheckInDate) = '2005' AND status = 'A'")
accept = cursor.fetchone()
x = accept[0]
print(accept)
print x
cursor1 = conn.cursor()
cursor1.execute("select COUNT(*) from clientBookings where MONTH(CheckInDate) = '2' AND YEAR(CheckInDate) = '2005' AND status = 'X'")
cancel = cursor1.fetchone()
print(cancel)
cursor2 = conn.cursor()
cursor2.execute("select COUNT(*) from clientBookings where MONTH(CheckInDate) = '2' AND YEAR(CheckInDate) = '2005' AND status = 'p'")
wait = cursor2.fetchone()
print(wait)
cursor3 = conn.cursor()
cursor3.execute("select COUNT(*) from clientBookings where MONTH(CheckInDate) = '2' AND YEAR(CheckInDate) = '2005' AND status = 'R'")
request = cursor3.fetchone()
print(request)
except Error as e:
print(e)
finally:
cursor.close()
conn.close()
def insert_many(stars):
'''Insert multiple rows into a MySQL database'''
query = ("INSERT INTO space(star_name, star_location)"
"VALUES(%s, %s)")
try:
db_config = read_db_config()
#Creating a new MySQLConnection object
conn = MySQLConnection(**db_config)
#Creating a new MySQLCursor object from the MySQLConnection object
cursor = conn.cursor()
#Inserts many rows into the database
cursor.executemany(query, stars)
conn.commit()
except Error as error:
print(error)
finally:
cursor.close()
conn.close()
print('Connection closed.')
def insert_Face_Details(face_id, gender,age,emotion,emotion_percentage):
query = "INSERT INTO FaceData(face_id, gender,age,emotion,emotion_percentage) " \
"VALUES(%s,%s,%s,%s,%s)"
args = (face_id, gender,age,emotion,emotion_percentage)
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()
print('Face Data transfered into Database')
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_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 Connect():
kwargs = ReadingMySQLConfig()
MyConnection = MySQLConnection(**kwargs)
try:
if MyConnection.is_connected():
print("Connected")
except Error as e:
print(e)
finally:
MyConnection.close()
def database_update():
config=Config().config
user = config['DB_LOGIN']
password = config['DB_PW']
host = config['DB_HOST']
database = config['DB_NAME']
cjauth = config['CJ_AUTH']
cjurl = config['CJ_URL']
conn = MySQLConnection(user=user, password=password, host=host, database=database)
conn.autocommit = True
cursor = conn.cursor()
page_number = 0
records_per_page = 100 # this is the max number allowed by the affiliate api per call.
records_returned = records_per_page
headers = {'authorization': cjauth}
while records_returned == records_per_page:
page_number += 1
params = {'website-id': '7782886', 'link-type': 'banner', 'advertiser-ids': 'joined', 'page-number': page_number, 'records-per-page': records_per_page}
result = requests.get(cjurl, headers=headers, params=params)
result_xml = result.text
root = ET.fromstring(result_xml.encode('utf8'))
records_returned = int(root.find('links').get('records-returned'))
for link in root.iter('link'):
link_code_html = html.fromstring(link.find('link-code-html').text)
height = int(link_code_html.xpath('//img/@height')[0])
width = int(link_code_html.xpath('//img/@height')[0])
mysql_args = (
link.find('link-id').text,
link.find('advertiser-id').text,
link.find('advertiser-name').text,
link.find('category').text,
'None' if link.find('promotion-start-date').text == None else link.find('promotion-start-date').text,
'None' if link.find('promotion-end-date').text == None else link.find('promotion-end-date').text,
height,
width,
link.find('link-code-html').text)
try:
cursor.callproc('AdMania.prc_UpdateAd',mysql_args)
except Error as e:
print e
cursor.close()
conn.close()
def getAllUsers(self):
try:
conn = MySQLConnection(host='localhost', database='mydb', user='******', password='******')
cursor = conn.cursor()
cursor.execute("SELECT * FROM users")
rows = cursor.fetchall()
return rows
except Error as error:
print error
finally:
cursor.close()
conn.close()
def create_database(cursor):
try:
conn = MySQLConnection(**db_data)
cursor = conn.cursor()
cursor.execute(
"CREATE DATABASE {} DEFAULT CHARACTER SET 'utf8'".format(DB_NAME))
except Error as e:
print(e)
finally:
cursor.close()
conn.close()
def UpdateBooks(book_id, title):
kwargs = ReadingMySQLConfig()
data = (title, book_id)
query = "UPDATE books SET title = %s WHERE id = %s"
try:
MyConnection = MySQLConnection(**kwargs)
cursor = MyConnection.cursor()
cursor.execute(query, data)
MyConnection.commit()
except Error as e:
print(e)
finally:
MyConnection.close()
def ReadImage(author_id, filename):
kwargs = ReadingMySQLConfig()
query = 'SELECT photo FROM authors WHERE id = %s'
try:
MyConnection = MySQLConnection(**kwargs)
cursor = MyConnection.cursor()
cursor.execute(query, (author_id,))
photo = cursor.fetchone()[0]
WriteFile(photo, filename)
except Error as e:
print(e)
finally:
MyConnection.close()
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 getRangeIdentifier(word): rows = [] try: db_config = read_db_config() conn = MySQLConnection(**db_config) cursor = conn.cursor() if word: word = word + "%" command = "SELECT word, category FROM range_identifiers WHERE word LIKE '%" + word +"'" #print command cursor.execute(command) rows = cursor.fetchall() return rows except Error as error: print error finally: cursor.close() conn.close()
def get_all(self):
try:
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
cursor.callproc('getAllCustomerUserInfo')
all_customer_info = []
for result in cursor.stored_results():
customers = result.fetchall()
for x in customers:
currentinfo = CustomerInfo()
currentinfo.customer_id = x[0]
currentinfo.work_phone = x[1]
currentinfo.home_phone = x[2]
u = User()
u.id = x[3]
u.password = x[4]
u.last_login = x[5]
u.is_superuser = x[6]
u.username = x[7]
u.first_name = x[8]
u.last_name = x[9]
u.email = x[10]
u.is_staff = x[11]
u.is_active = x[12]
u.date_joined = x[13]
currentinfo.set_user(u)
all_customer_info.append(currentinfo)
cursor.close()
conn.close()
except Error as error:
print(error)
except Exception as e:
print(e)
return all_customer_info
def insertManyRestaurants(restaurants_info):
query = "INSERT INTO restaurants(restaurant_id,name,url,address,latitude,longitude,rating,country_id,phone,timings,average_cost_for_two,is_pure_veg,\
sports_bar_flag,has_bar,has_ac,has_dine_in,has_delivery,takeaway_flag,accepts_credit_cards,accepts_debit_cards,\
sheesha_flag,halal_flag,has_wifi,has_live_music,nightlife_flag,stag_entry_flag,entry_fee,has_online_delivery,\
min_order,average_delivery_time,delivery_charge,accepts_online_payment)" \
"VALUES(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s,%s)"
try:
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
cursor.executemany(query, restaurants_info)
conn.commit()
except Error as error:
print error
finally:
cursor.close()
conn.close()
def insert_Staff(Name, Pay, Surname, LibraryName):
query = "INSERT INTO Staff(Name,Pay,Surname ,LibraryName) VALUES( %s , %s , %s , %s)"
args = (Name, Pay, Surname, LibraryName)
try:
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
cursor.execute(query, args)
if cursor.lastrowid:
print('done')
else:
print('last insert id not found')
conn.commit()
except Error as error:
print(error)
finally:
cursor.close()
conn.close()
def insert_Publisher(Name, Date_of_Est, Address):
query = "INSERT INTO Publishers VALUES(%s,%s,%s)"
args = (Name, Address, Date_of_Est)
try:
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
cursor.execute(query, args)
if cursor.lastrowid:
print('done')
else:
print('last insert id not found')
conn.commit()
except Error as error:
print(error)
finally:
cursor.close()
conn.close()
def delete_book(book_id):
#read database configuration
db_config = read_db_config()
#prepare query and data
query = "DELETE FROM books WHERE id = %s"
try:
conn = MySQLConnection(**db_config)
#update book title
cursor = conn.cursor()
cursor.execute(query, (book_id, ))
#accept the changes
conn.commit()
except Error as error:
print(error)
finally:
cursor.close()
conn.close()
def invoice_range_update():
query = """ UPDATE invoice_range
SET use_flag = %s
WHERE use_flag = %s """
data = (2, 1)
try:
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
cursor.execute(query, data)
conn.commit()
except Error:
log.exception("MYSQL error occurred:")
finally:
cursor.close()
conn.close()
def query_student(self, student_id):
query = "SELECT id, name, email, picture FROM STUDENT " \
"WHERE STUDENT.id = %s"
args = (student_id, )
try:
db_config = self.read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
cursor.execute(query, args)
data = cursor.fetchone()
return data
except Error as error:
print(error)
finally:
cursor.close()
conn.close()
def post_code(code):
dbconfig = read_db_config()
conn = MySQLConnection(**dbconfig)
cursor = conn.cursor()
# print(code)
cursor.execute("SELECT * FROM `post_images` WHERE `key_name` ='" +
str(code) + "';")
profile_row = cursor.fetchall()
#cursor.execute("SELECT * FROM `posts` WHERE `uniqueID` ='" + str(code) + "';")
#cover_row = cursor.fetchall()
if len(profile_row) == 1:
conn.close()
cursor.close()
return wall_post_gen()
else:
conn.close()
cursor.close()
# print(code)
return code
def turnosjuadorAgendaGolf(usuario, fec):
turnosjugador = []
query = " SELECT * FROM agenda_golf WHERE fecha = %s "
data = (fec)
dbconfig = read_db_config()
try:
conn = MySQLConnection(**dbconfig)
cursor = conn.cursor()
cursor.execute(query, (data, ))
row = cursor.fetchone()
while row is not None:
for i in range(8, 12):
if row[i] == usuario:
turnosjugador.append(row)
print(row)
row = cursor.fetchone()
except Error as error:
print(error)
finally:
cursor.close()
conn.close()
return turnosjugador
def fetch_dashboard_sp(employee):
try:
conn = MySQLConnection(host=host,
database=database,
user=user,
password=password)
cursor = conn.cursor()
args = (employee)
cursor.callproc('DashboardValues', [args])
for result in cursor.stored_results():
column_names_list = [x[0] for x in result.description]
result_dicts = [
dict(zip(column_names_list, row)) for row in result.fetchall()
]
return result_dicts
except Error as e:
print(e)
finally:
cursor.close()
conn.close()
def connect():
""" Connect to MySQL database """
db_config = read_db_config("config.ini", "mysql")
table_config = read_db_config("config.ini", "tabledata")
try:
print('Connecting to MySQL database...')
conn = MySQLConnection(**db_config)
if conn.is_connected():
print('connection established.')
spotifyCall(conn, table_config)
else:
print('connection failed.')
except Error as error:
print(error)
finally:
conn.close()
print('Connection closed.')
def insertOneDish(menu,restaurant_id): query = "INSERT INTO menus(menu,restaurant_id) " \ "VALUES(%s, %s)" args = (menu,restaurant_id) try: db_config = read_db_config() conn = MySQLConnection(**db_config) cursor = conn.cursor() cursor.execute(query, args) conn.commit() except Error as error: print error finally: cursor.close() conn.close() print "DISHES DATA INSERTED!!!"
def getNERSemantics(ner): try: db_config = read_db_config() # db_config['database'] = "travel" conn = MySQLConnection(**db_config) cursor = conn.cursor() command = "SELECT minimum, maximum FROM ner_config WHERE category = '" + ner + "'" cursor.execute(command) rows = cursor.fetchall() if rows: row = rows[0] return row return '' except Error as error: print error finally: cursor.close() conn.close()
def add_user(email,
password,
phone_1,
ph,
ip,
mac,
verification_code,
is_confirmed=False):
query = 'INSERT INTO `account_temp` VALUE (%s, %s, PASSWORD(%s), %s, %s, %s, %s, %s)'
args = None, email, password, ip, mac, verification_code, is_confirmed, None
try:
conn = MySQLConnection(**config_eduwork_temp)
cursor = conn.cursor()
cursor.execute(query, args)
conn.commit()
except Error as err:
print(err)
else:
print(' Тимчасовий обліковий запис створено! '.center(80, '='))
finally:
cursor.close()
conn.close()
def insertOneLocality(locality_id, name, city_id, data_resource):
query = "INSERT INTO locality(locality_id, name, city_id, data_resource) " \
"VALUES(%s, %s, %s, %s)"
args = (locality_id, name, city_id, data_resource)
try:
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
cursor.execute(query, args)
conn.commit()
except Error as error:
print error
finally:
cursor.close()
conn.close()
print "LOCALITY DATA INSERTED!!!"
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.')
cursor=conn.cursor();
cursor.execute("create table reg_logs(car_number_plate varchar(20),time varchar(20),date varchar(20),description varchar(20))")
else:
print('connection failed.')
except Error as error:
print(error)
finally:
conn.close()
print('Connection closed.')
def fetchOne():
try:
# Connect to the Database
dbconfig = readDBConfig()
conn = MySQLConnection(**dbconfig)
# Create a "cursor" this objects helps retrieve the rows using buffering
cursor = conn.cursor()
cursor.execute("SELECT * FROM employees LIMIT 20")
row = cursor.fetchone()
# Retrieve each row
while row is not None:
print(row)
row = cursor.fetchone()
except Error as e:
print(e)
finally:
cursor.close()
conn.close()
def findDishByName(query):
rows = []
try:
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
if query:
query = query + "%"
cursor.execute("SELECT DISTINCT(dish_name) FROM menu WHERE dish_name LIKE '%s'" % query)
rows = cursor.fetchall()
# for row in rows:
# print row
except Error as error:
print error
finally:
cursor.close()
conn.close()
return rows
def insert_copy(Number, Books_ISBN, Position):
query = "INSERT INTO Copy VALUES (%s,%s,%s)"
args = (Books_ISBN, Number, Position)
try:
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
cursor.execute(query, args)
if cursor.lastrowid:
print('done')
else:
print('last insert id not found')
conn.commit()
except Error as error:
print(error)
finally:
cursor.close()
conn.close()
def db_log_history(endDT=datetime.now(), limit=100, \
periodTD=timedelta(days=1), db_result_list = [], db='mysql'):
try:
dbconfig = read_db_config(db=db)
conn = MySQLConnection(**dbconfig)
cursor = conn.cursor(buffered=True)
startDT = endDT - periodTD
startDTstr = startDT.strftime("%Y-%m-%d %H:%M:%S")
endDTstr = endDT.strftime("%Y-%m-%d %H:%M:%S")
query = "SELECT datetime, source, msg_type, message FROM message_log " \
"WHERE datetime BETWEEN %s and %s " \
"ORDER BY datetime DESC " \
"LIMIT %s "
args = (startDTstr, endDTstr, limit)
cursor.execute(query, args)
#text_result = ""
#db_result_list = []
for row in iter_row(cursor, 10):
#text_result = row[0].strftime("%H:%M:%S") + " " + row[1] + " " + row[2] + " " + row[3]
#db_result_list.append(text_result, )
db_result_list.append(row, )
db_result_list.reverse()
except Error as e:
dbMsg.appMessage("db_log_history()", dbMsg.EXCEPT, \
"Exception occurred in query execution")
print(e)
try:
cursor.close()
except Error as error:
dbMsg.appMessage("db_log_history()", dbMsg.EXCEPT, \
"Exception occurred closing cursor")
print(error)
try:
conn.close()
except Error as error:
dbMsg.appMessage("db_log_history()", dbMsg.EXCEPT, \
"Exception occurred closing connection")
print(error)
return db_result_list
def recuperaMarcasCampos(campo):
dbconfig = read_db_config()
query = "SELECT marcas FROM campos_colombia WHERE campo=%s "
data = (campo, )
marcas = []
try:
conn = MySQLConnection(**dbconfig)
cursor = conn.cursor()
cursor.execute(query, data)
try:
row = cursor.fetchall()
dato = row
except:
dato = 'errror'
except Error as error:
dato = 'errror'
finally:
cursor.close()
conn.close()
for lin in dato:
marcas.append(lin[0])
return marcas
def insert_list_book(list_book):
sql = """INSERT INTO list_book(id, ISBN, Name, page_num, pub_year) VALUES (%s, %s, %s, %s,%s)"""
try:
print('Connecting to MySQL database...')
conn = MySQLConnection(host='localhost',
database='billing_simple',
user='******',
password='******')
cursor = conn.cursor()
cursor.executemany(sql, list_book)
conn.commit()
#frame.to_sql(name='billing_simple', con=conn, if_exists = 'append', index=False)
except Error as error:
print(error)
finally:
cursor.close()
conn.close()
print('Connection closed.')
def insertOneDish(dish_id, restaurant_id, dish_name, type, cost, data_resource): query = "INSERT INTO menu(dish_id, restaurant_id, dish_name, cost, type, data_resource) " \ "VALUES(%s, %s, %s, %s, %s, %s)" args = (dish_id, restaurant_id, dish_name, cost, type, data_resource) try: db_config = read_db_config() conn = MySQLConnection(**db_config) cursor = conn.cursor() cursor.execute(query, args) conn.commit() except Error as error: print error finally: cursor.close() conn.close() print "DISHES DATA INSERTED!!!"
def insertOneCuisine(restaurant_id, cuisine_id):
query = "INSERT INTO cuisine_restaurant(restaurant_id, cuisine_id) " \
"VALUES(%s, %s)"
args = (restaurant_id, cuisine_id)
try:
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
cursor.execute(query, args)
conn.commit()
except Error as error:
print error
finally:
cursor.close()
conn.close()
print "CUISINE_RESTAURANT DATA INSERTED!!!"
def insertOneLocalityRestaurant(locality_id, restaurant_id):
query = "INSERT INTO locality_restaurant(locality_id, restaurant_id) " \
"VALUES(%s, %s)"
args = (locality_id, restaurant_id)
try:
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
cursor.execute(query, args)
conn.commit()
except Error as error:
print error
finally:
cursor.close()
conn.close()
print "LOCALITY_RESTAURANT DATA INSERTED!!!"
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 e:
print(e)
finally:
conn.close()
print 'Connection Closed'
def delete_book(book_id):
db_config = read_db_config()
query = "DELETE FROM books WHERE id = %s"
try:
# connect to the database server
conn = MySQLConnection(**db_config)
# execute the query
cursor = conn.cursor()
cursor.execute(query, (book_id, ))
# accept the change
conn.commit()
except Error as error:
print(error)
finally:
cursor.close()
conn.close()
def get_values():
query = "SELECT year, injuries FROM fourth_table"
try:
config = {
'user': '******',
'password': '******',
'host': 'localhost',
'database': 'bootcamp'
}
conn = MySQLConnection(**config)
cursor = conn.cursor()
cursor.execute(query)
# Create a container to hold these values
tuple_container = []
for (year, injuries) in cursor:
tuple_container.append((year, injuries))
print 'Original Container'
print tuple_container
print '\nSorter Container'
new_container = sorted(tuple_container, key=get_key, reverse=True)
print new_container
# Now just find the first year
print 'The year with the most injuries is: ', new_container[0][0]
except Error as error:
print(error)
finally:
cursor.close()
conn.close()
def event_create_insert_into_db(name, type, num_participants, startdatetime,
enddatetime, state):
query = "INSERT INTO event(name,type,num_participants,startdatetime,enddatetime,state) " \
"VALUES(%s,%s,%s,%s,%s,%s)"
args = (name, type, num_participants, startdatetime, enddatetime, state)
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')
last_event_id = cursor.lastrowid
sql_event_start = "CREATE EVENT " + name + "id" + str(
last_event_id
) + "start" + " ON SCHEDULE AT '" + startdatetime + "' DO UPDATE hems.event SET state = 'ready' WHERE id = " + str(
last_event_id) + ";"
sql_event_end = "CREATE EVENT " + name + "id" + str(
last_event_id
) + "end" + " ON SCHEDULE AT '" + enddatetime + "' DO UPDATE hems.event SET state = 'deprovison' WHERE id = " + str(
last_event_id) + ";"
cursor.execute(sql_event_start)
cursor.execute(sql_event_end)
conn.commit()
except Error as error:
print(error)
finally:
cursor.close()
conn.close()
