def search_list_one(user_id): #Назначим кнопки markup = types.InlineKeyboardMarkup() markup.add( types.InlineKeyboardButton( text=u'\U0001F30D Искать каждый товар отдельно', callback_data='locallist_one:'), ) #Проверим что в списке есть товары db_config = read_db_config() conn = MySQLConnection(**db_config) cursor = conn.cursor() SQL = 'select count(distinct(product_id)) from user_product_list where chat_id = %s' cursor.execute(SQL, (user_id, )) products = cursor.fetchone() if products[0] == 0: bot.send_message(user_id, 'Сначала добавьте товары в список для поиска') cursor.close() conn.close() else: #Ищем аптеки с поответствием по списку товара db_config = read_db_config() conn = MySQLConnection(**db_config) cursor = conn.cursor() SQL = """\ select r.name, r.producer, p3.name, p3.address, p3.mode, p3.latitude, p3.longitude, p3.phone, t.way, t.price from user_product_list p inner join product r on r.nommodif = p.product_id inner join users u on u.chat_id = p.chat_id inner join store p3 on p3.city = u.city and r.company = p3.company inner join ( select distinct(pl.product_id) product_id, p2.price, min(get_way(p3.latitude ,p3.longitude,u.latitude,u.longitude)) way from user_product_list pl inner join users u on u.chat_id = pl.chat_id inner join stock p2 on p2.product_id = pl.product_id inner join store p3 on p3.company = p2.company and p3.name = p2.store and p3.city = u.city where pl.chat_id = %s group by pl.product_id, p2.price ) t where p.chat_id = %s and get_way(p3.latitude ,p3.longitude,u.latitude,u.longitude)=t.way and r.nommodif = t.product_id group by r.name, r.producer, p3.name, p3.address, p3.mode, p3.latitude, p3.longitude, p3.phone, t.way, t.price """ cursor.execute(SQL, ( user_id, user_id, )) stores = cursor.fetchall() for store in stores: try: bot.send_venue(user_id, store[5], store[6], store[2] + ' (' + str(store[8]) + ' м.)', store[3]) bot.send_message( user_id, '*' + store[0] + '*\n' + store[1] + '\n' + 'Цена: ' + str(store[9]) + ' тенге\n\n' + store[4] + '\n' + 'Тел: ' + store[7], parse_mode='markdown', ) except Exception as e: print(e) cursor.close() conn.close()
j = json.load(open(f, 'r')) jsons.append(j) ############# ### MYSQL ### ############# # Connection config = { 'user': '******', 'password': '******', 'host': 'localhost', 'raise_on_warnings': True, } try: cnx = MySQLConnection(**config) except Error as e: print "[ERROR] Could not connect to MySQL database." exit(1) ctrl = Controller(cnx) # Instantiate controller # Create database ctrl.get_db('ocervell') # Create and populate table with each json file for f in files: name = basename(f).lower().replace('.json','') args = ("`doc` json DEFAULT NULL", "`updated` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP" )
def update_table(table_name): db_config = read_db_config() data_dir = '{0}/save-on-data'.format(cwd) valid_name = table_name.replace(',', '') files = os.listdir('{0}/{1}/'.format(data_dir, table_name)) if not files: print('{0} directory is empty...'.format(table_name)) return query = """ DROP TABLE IF EXISTS `{0}`; CREATE TABLE {0} (brand VARCHAR(40), name VARCHAR(50), category VARCHAR (200), current_price VARCHAR(20), regular_price VARCHAR(20), size VARCHAR(20), current_unit_price VARCHAR(20), description VARCHAR(200), sku VARCHAR(20)); INSERT INTO {0} VALUES """.format(valid_name) for file in files: file_name = '{0}/{1}/{2}'.format(data_dir, table_name, file) with open(file_name) as r: table_data = json.load(r) for pe in table_data: query += '("{0}", "{1}", "{2}", "{3}", "{4}", "{5}", "{6}", "{7}", "{8}"),'.format( pe['brand'].replace('\'', '').replace('\"', ''), pe['name'].replace('\'', '').replace('\"', ''), pe['category'].replace('\'', '').replace('\"', ''), pe['current_price'].replace('\'', '').replace('\"', ''), pe['regular_price'].replace('\'', '').replace('\"', ''), pe['size'].replace('\'', '').replace('\"', ''), pe['current_unit_price'].replace('\'', '').replace('\"', ''), pe['description'].replace('\'', '').replace('\"', ''), pe['sku'].replace('\'', '').replace('\"', '')) try: query = query[:-1] conn = MySQLConnection(**db_config) cursor = conn.cursor() results = cursor.execute(query, multi=True) i = 1 for result in results: if i % 3 == 0: print('Inserting into saveon_db.`{0}`'.format(valid_name)) i += 1 conn.commit() except Error as error: print(error) finally: cursor.close() conn.close()
def connect(): # """Gets AOL Data and writes them to a MySQL table""" db = "mysql_sl" api = "tm" # Connect To DB: db_config = read_db_config(db) try: print('Connecting to database...') conn = MySQLConnection(**db_config) if conn.is_connected(): print('connection established.') cursor = conn.cursor() # calls get_access_token function and starts script logintoken = aol_api.get_access_token(api) print(logintoken) result = aol_api.run_existing_report(logintoken, "190143") #print(result) info = json.loads(result) #print(info) for x in json.loads(result)['data']: inventory_source = x['row'][0] media = x['row'][1] ad_opportunities = x['row'][2] market_opportunities = x['row'][3] ad_attempts = x['row'][4] ad_impressions = x['row'][5] ad_errors = x['row'][6] ad_revenue = x['row'][7] aol_cost = x['row'][7] epiphany_gross_revenue = x['row'][7] tm_revenue = x['row'][7] clicks = x['row'][8] iab_viewability_measurable_ad_impressions = x['row'][9] iab_viewable_ad_impressions = x['row'][10] platform = '5' list =(inventory_source, media, ad_opportunities, market_opportunities, ad_attempts, ad_impressions, \ ad_errors, ad_revenue, aol_cost, epiphany_gross_revenue, tm_revenue, clicks, \ iab_viewability_measurable_ad_impressions, iab_viewable_ad_impressions, platform) # print(list) sql = """INSERT INTO tm_core_today_media VALUES ("%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s"*.20, \ "%s"*.64, "%s"*.24, "%s", "%s", "%s", "%s")""" % (inventory_source, media, ad_opportunities, \ market_opportunities, ad_attempts, ad_impressions, ad_errors, ad_revenue, aol_cost, \ epiphany_gross_revenue, tm_revenue, clicks, iab_viewability_measurable_ad_impressions, \ iab_viewable_ad_impressions, platform) cursor.execute(sql) cursor.execute('commit') else: print('Connection failed') except Error as error: print(error) finally: conn.close() print('Connection Closed')
def __init__(self): self.configPath = "./util/config/db_config_inner.ini" self.dbconfig = cu.read_db_config(self.configPath) self.connector = MySQLConnection(charset='utf8', **self.dbconfig)
def getUsers(self): connect = MySQLConnection(**config) cursor = connect.cursor() cursor.execute("select * from users") return cursor.fetchall()
def connect(): # """Gets AOL Data and writes them to a MySQL table""" db = "mysql_sl" # Connect To DB: db_config = read_db_config(db) try: print('Connecting to database...') conn = MySQLConnection(**db_config) if conn.is_connected(): print('Connection established.') cursor = conn.cursor() sql = "DROP TABLE IF EXISTS v3_core_yesterday_media" cursor.execute(sql) sql = "CREATE TABLE v3_core_yesterday_media (date varchar(25), inventory_source varchar(255), media varchar(255) \ CHARACTER SET 'utf8',ad_opportunities bigint, market_opportunities bigint, ad_attempts bigint, \ ad_impressions bigint, ad_errors bigint, ad_revenue decimal(15, 5), clicks int, \ iab_viewability_measurable_ad_impressions bigint, iab_viewable_ad_impressions bigint)" cursor.execute(sql) # calls get_access_token function and starts script logintoken = aol_api.get_access_token( "daf5fa63-56c4-4279-842e-639c9af75750", "C5eBl8aErmCMO2+U85LGpw") print logintoken # Make Calls And Write To DB: result = aol_api.run_existing_report(logintoken, "143992") #print(result) info = json.loads(result) #print(info) # Make Calls And Write To DB: for x in json.loads(result)['data']: date = x['row'][0] inventory_source = x['row'][1] media = x['row'][2].replace('"', " ") ad_opportunities = x['row'][3] market_opportunities = x['row'][4] ad_attempts = x['row'][5] ad_impressions = x['row'][6] ad_errors = x['row'][7] ad_revenue = x['row'][8] clicks = x['row'][9] iab_viewability_measurable_ad_impressions = x['row'][10] iab_viewable_ad_impressions = x['row'][11] list = (date, inventory_source, media, ad_opportunities, market_opportunities, ad_attempts, ad_impressions, \ ad_errors, ad_revenue, clicks, iab_viewability_measurable_ad_impressions, iab_viewable_ad_impressions) #print(list) sql = """INSERT INTO v3_core_yesterday_media VALUES ("%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", \ "%s", "%s", "%s")""" % (date, inventory_source, media, ad_opportunities, market_opportunities, \ ad_attempts, ad_impressions, ad_errors, ad_revenue, clicks, iab_viewability_measurable_ad_impressions, \ iab_viewable_ad_impressions) cursor.execute(sql) cursor.execute('commit') else: print('Connection failed') except Error as error: print(error) finally: conn.close() print('Connection closed.')
def searchhn() : global aa7 aaa = aa7.get() if aaa.isdigit(): print("is number") else: print("is not number") sv1 = StringVar(root,value="HN ต้องใส่ตัวเลขเท่านั้น") aa=Entry(root,textvariable=sv1) #creating entry box aa.grid(row=5,column=6) initkri() return khn2 = "" try: print('Connecting to MySQL database...') print(db_config) conn = MySQLConnection(**db_config) # conn = MySQLdb.connect (charset='utf8',host = "",user = "",passwd =,db = "") print('Connection Successful!!!') cursor = conn.cursor () curex ="SELECT hn,name,surname,xn,brdate,id13 FROM krieng where trim(xn)<>'old' and hn="+aaa.strip()+";" cursor.execute (curex) acount = 0 while True: rows = cursor.fetchmany () if not rows: cursor.close () conn.close () if acount == 0: print("ไม่พบid13 นี้") sv1 = StringVar(root,value="ไม่พบid13 นี้") aa=Entry(root,textvariable=sv1) #creating entry box aa.grid(row=5,column=6) initkri() if acount >= 2: print("มีid13 จำนวน"+str(acount)) sv1 = StringVar(root,value="มีid13 จำนวน"+str(acount)) aa=Entry(root,textvariable=sv1) #creating entry box aa.grid(row=5,column=6) break for row in rows: khn = row[0] khn2 = khn2+","+str(khn) print("HN =:", row[0]) sv1 = StringVar(root,value=khn) aa=Entry(root,textvariable=sv1) #creating entry box aa.grid(row=5,column=6) # Label(root,text=row[5]).grid(row=5,column=4) #Creating label Label(root,text=" "+row[5].strip()+" ").grid(row=12,column=6) #Creating label print(row[5]) sv3 = StringVar(root,value=khn2) aa3=Entry(root,textvariable=sv3) #creating entry box aa3.grid(row=6,column=6) sv4 = StringVar(root,value=row[1]+" "+row[2]) aa4=Entry(root,textvariable=sv4) #creating entry box aa4.grid(row=7,column=1) sv5 = StringVar(root,value='XN:'+row[3]) aa5=Entry(root,textvariable=sv5) #creating entry box aa5.grid(row=7,column=4) sv6 = StringVar(root,value=str(row[4])) aa6=Entry(root,textvariable=sv6) #creating entry box aa6.grid(row=7,column=6) print(khn) print(row[1]+" "+row[2]) acount += 1 print(acount) # except MySQLdb.Error as e: except Exception as e: print (e) sv1 = StringVar(root,value="ไม่สามารถเชื่อม mysql ได้") aa=Entry(root,textvariable=sv1) #creating entry box aa.grid(row=5,column=6) print("ไม่สามารถเชื่อม mysql ได้") except : print("Unknow error occured")
def searchnamebirth(): khn2 = "" try: print('Connecting to MySQL database...') print(db_config) conn = MySQLConnection(**db_config) print('Connection Successful!!!') cursor = conn.cursor () myfile = open('../Dropbox/krifoxone/kriid.csv','r') data=myfile.readline() list=data.split(",") kid13=list[0] print(kid13) name = str(list[8],"tis-620") # name1 = list[8].encode('tis-620') surname = str(list[9],"tis-620") # surname1 = list[9].encode('tis-620') year1 = str(int(list[2][0:4])-543) birth = year1+"-"+list[2][4:6]+"-"+list[2][6:8] print(name) print(surname) print(birth) cursor.execute ("SELECT hn,name,surname,xn,brdate,id13 FROM krieng where trim(xn)<>'old' and name='"+name.strip()+"' and surname='"+surname.strip()+"' and brdate='"+birth+"';") acount = 0 while True: rows = cursor.fetchmany () if not rows: cursor.close () conn.close () if acount == 0: print("ไม่พบชื่อ นี้") sv1 = StringVar(root,value="ไม่พบชื่อ นี้") aa=Entry(root,textvariable=sv1) #creating entry box aa.grid(row=5,column=6) if acount >= 2: print("มีid13 จำนวน"+str(acount)) sv1 = StringVar(root,value="มีชื่อ จำนวน"+str(acount)) aa=Entry(root,textvariable=sv1) #creating entry box aa.grid(row=5,column=6) sv = StringVar(root,value='ค้นชื่อ เรียบร้อย') a=Entry(root,textvariable=sv) #creating entry box a.grid(row=5,column=1) print("Find name Ready") break for row in rows: # if row is None and acount>0: # row ==None khn = row[0] khn2 = khn2+","+str(khn) print("HN =:", row[0]) sv1 = StringVar(root,value=khn) aa=Entry(root,textvariable=sv1) #HN aa.grid(row=5,column=6) Label(root,text=kid13).grid(row=5,column=4) #id13 # sv2 = StringVar(root,value=kid13) # aa2=Entry(root,textvariable=sv2) #creating entry box # aa2.grid(row=6,column=1) sv3 = StringVar(root,value=khn2) aa3=Entry(root,textvariable=sv3) #HN2 aa3.grid(row=6,column=6) sv4 = StringVar(root,value=row[1]+" "+row[2]) aa4=Entry(root,textvariable=sv4) #name aa4.grid(row=7,column=1) sv5 = StringVar(root,value='XN:'+row[3]) aa5=Entry(root,textvariable=sv5) #XN aa5.grid(row=7,column=4) sv6 = StringVar(root,value=str(row[4])) aa6=Entry(root,textvariable=sv6) #brdate aa6.grid(row=7,column=6) # sv8 = StringVar(root,value=name+' '+surname) # aa8=Entry(root,textvariable=sv8) # aa8.grid(row=9,column=1) print(khn) print(row[1]+" "+row[2]) # +" "+row[2] acount += 1 except Exception as e: print (e) sv1 = StringVar(root,value="ไม่สามารถเชื่อม mysql ได้") aa=Entry(root,textvariable=sv1) #creating entry box aa.grid(row=5,column=6) print("ไม่สามารถเชื่อม mysql ได้") except : print("Unknow error occured")
def searchid13(): global aa2 aaa9 = aa2.get() if aaa9.find('?;600764') > 0: aaa = aaa9[aaa9.find('?;600764')+8:aaa9.find('?;600764')+21] print(aaa) aa2.delete(0,250) # aa8.destroy() to delete block aa8 aa2.insert(0,aaa) else: aaa = aaa9 print(aaa) if aaa.isdigit(): print("is number") else: print("is not number") sv1 = StringVar(root,value="ต้องใส่ตัวเลขเท่านั้น") aa=Entry(root,textvariable=sv1) #creating entry box aa.grid(row=5,column=6) initkri() return if len(aaa)!= 13: print("ใส่เลขไม่ครบ 13 หลัก") sv1 = StringVar(root,value="ใส่เลขไม่ครบ 13 หลัก") aa=Entry(root,textvariable=sv1) #creating entry box aa.grid(row=5,column=6) return khn2 = "" try: print(db_config) print('Connecting to MySQL database...') conn = MySQLConnection(**db_config) #conn = MySQLdb.connect(charset='utf8', init_command='SET NAMES UTF8') print('Connection Successful!!!') cursor = conn.cursor () cursor.execute ("SELECT hn,name,surname,xn,brdate,id13 FROM krieng where trim(xn)<>'old' and id13='"+aaa.strip()+"';") acount = 0 while True: rows = cursor.fetchmany () if not rows: cursor.close () conn.close () if acount == 0: print("ไม่พบid13 นี้") sv1 = StringVar(root,value="ไม่พบid13 นี้") aa=Entry(root,textvariable=sv1) #creating entry box aa.grid(row=5,column=6) initkri() if acount >= 2: print("มีid13 จำนวน"+str(acount)) sv1 = StringVar(root,value="มีid13 จำนวน"+str(acount)) aa=Entry(root,textvariable=sv1) #creating entry box aa.grid(row=5,column=6) messagebox.showinfo("Information","มี id13 ซ้ำกันจำนวน "+str(acount)+" ให้แก้ไขด้วย") # An information box break for row in rows: khn = row[0] khn2 = khn2+","+str(khn) print("HN =:", row[0]) sv1 = StringVar(root,value=khn) aa=Entry(root,textvariable=sv1) #creating entry box aa.grid(row=5,column=6) # Label(root,text=aaa.strip()).grid(row=5,column=4) #Creating label Label(root,text=row[5]).grid(row=12,column=6) #Creating label sv3 = StringVar(root,value=khn2) aa3=Entry(root,textvariable=sv3) #creating entry box aa3.grid(row=6,column=6) sv4 = StringVar(root,value=row[1]+" "+row[2]) aa4=Entry(root,textvariable=sv4) #creating entry box aa4.grid(row=7,column=1) sv5 = StringVar(root,value='XN:'+row[3]) aa5=Entry(root,textvariable=sv5) #creating entry box aa5.grid(row=7,column=4) sv6 = StringVar(root,value=str(row[4])) aa6=Entry(root,textvariable=sv6) #creating entry box aa6.grid(row=7,column=6) print(khn) print(row[1]+" "+row[2]) acount += 1 print(acount) except Exception as e: print (e) sv1 = StringVar(root,value="ไม่สามารถเชื่อม mysql ได้") aa=Entry(root,textvariable=sv1) #creating entry box aa.grid(row=5,column=6) print("ไม่สามารถเชื่อม mysql ได้") except : print("Unknow error occured")
def copyandfindid13(): khn2 = "" try: print('Connecting to MySQL database...') print(db_config) conn = MySQLConnection(**db_config) print('Connection Successful!!!') cursor = conn.cursor () myfile = open('./kriid.txt','r') data=myfile.readline() list=data.split(",") kid13=list[0] print(kid13) cursor.execute ("SELECT hn,name,surname,xn,brdate,id13 FROM krieng where id13='"+kid13+"';") acount = 0 while True: # row = cursor.fetchone () rows = cursor.fetchmany () if not rows: cursor.close () conn.close () if acount == 0: print("ไม่พบid:"+kid13+" นี้") sv1 = StringVar(root,value="ไม่พบid:"+kid13+" นี้") aa=Entry(root,textvariable=sv1) #creating entry box aa.grid(row=5,column=6) if acount >= 2: print("มีid13 จำนวน"+str(acount)) sv1 = StringVar(root,value="มีid13 จำนวน"+str(acount)) aa=Entry(root,textvariable=sv1) #creating entry box aa.grid(row=5,column=6) sv = StringVar(root,value='copy เรียบร้อย') a=Entry(root,textvariable=sv) #creating entry box a.grid(row=5,column=1) print("Copy Ready") break for row in rows: # if row is None and acount>0: # row ==None khn = row[0] khn2 = khn2+","+str(khn) print("HN =:", row[0]) sv1 = StringVar(root,value=khn) aa=Entry(root,textvariable=sv1) #creating entry box aa.grid(row=5,column=6) Label(root,text=kid13).grid(row=5,column=4) #Creating label # sv2 = StringVar(root,value=kid13) # aa2=Entry(root,textvariable=sv2) #creating entry box # aa2.grid(row=6,column=1) sv3 = StringVar(root,value=khn2) aa3=Entry(root,textvariable=sv3) #creating entry box aa3.grid(row=6,column=6) sv4 = StringVar(root,value=row[1]+" "+row[2]) aa4=Entry(root,textvariable=sv4) #creating entry box aa4.grid(row=7,column=1) sv5 = StringVar(root,value='XN:'+row[3]) aa5=Entry(root,textvariable=sv5) #creating entry box aa5.grid(row=7,column=4) sv6 = StringVar(root,value=str(row[4])) aa6=Entry(root,textvariable=sv6) #creating entry box aa6.grid(row=7,column=6) print(khn) print(row[1]+" "+row[2]) # +" "+row[2] acount += 1 except Exception as e: print (e) sv1 = StringVar(root,value="ไม่สามารถเชื่อม mysql ได้") aa=Entry(root,textvariable=sv1) #creating entry box aa.grid(row=5,column=6) print("ไม่สามารถเชื่อม mysql ได้") except : print("Unknow error occured")
def searchname() : global aa8 aaa = aa8.get() kname = aaa.split() print(kname[0]) print(kname[1]) khn2 = "" try: print(db_config) print('Connecting to MySQL database...') # conn = MySQLConnection(**db_config) conn = MySQLConnection(**db_config) #conn = MySQLdb.connect(charset='utf8', init_command='SET NAMES UTF8') print('Connection Successful!!!') cursor = conn.cursor () # cursor.execute ("SELECT hn,name,surname,xn,brdate,id13 FROM krieng where trim(xn)<>'old' and name='"+kname[0].strip()+"';") cursor.execute ("SELECT hn,name,surname,xn,brdate,id13 FROM krieng where trim(xn)<>'old' and name='"+kname[0].strip()+"' and surname='"+kname[1].strip()+"';") acount = 0 while True: rows = cursor.fetchmany () if not rows: cursor.close () conn.close () if acount == 0: print("ไม่พบชื่อ นี้") sv1 = StringVar(root,value="ไม่พบชื่อ นี้") aa=Entry(root,textvariable=sv1) #creating entry box aa.grid(row=5,column=6) initkri() if acount >= 2: print("มีชื่อ จำนวน"+str(acount)) sv1 = StringVar(root,value="มีชื่อ จำนวน"+str(acount)) aa=Entry(root,textvariable=sv1) #creating entry box aa.grid(row=5,column=6) break for row in rows: khn = row[0] khn2 = khn2+","+str(khn) print("HN =:", row[0]) sv9 = StringVar(root,value=khn) aa9=Entry(root,textvariable=sv9) #HN aa9.grid(row=9,column=6) iiiddd = 'ID13:'+str(row[5]) Label(root,text=iiiddd).grid(row=10,column=6) #id13 sv10 = StringVar(root,value=khn2) aa10=Entry(root,textvariable=sv10) #HN2 aa10.grid(row=9,column=8) # sv4 = StringVar(root,value=row[1]+" "+row[2]) # aa4=Entry(root,textvariable=sv4) #creating entry box # aa4.grid(row=7,column=1) # sv5 = StringVar(root,value='XN:'+row[3]) # aa5=Entry(root,textvariable=sv5) #XN # aa5.grid(row=7,column=4) Label(root,text='XN:'+row[3]).grid(row=10,column=4) #id13 bbbrrr = str(row[4]) Label(root,text='birth:'+bbbrrr).grid(row=10,column=8) # birth print(khn) print(row[1]+" "+row[2]) acount += 1 print(acount) # sv6 = StringVar(root,value=str(row[4])) # aa6=Entry(root,textvariable=sv6) #Brith # aa6.grid(row=7,column=6) except Exception as e: print (e) sv1 = StringVar(root,value="ไม่สามารถเชื่อม mysql ได้") aa=Entry(root,textvariable=sv1) #creating entry box aa.grid(row=5,column=6) print("ไม่สามารถเชื่อม mysql ได้") except : print("Unknow error occured")
from splinter import Browser import time import re from mysql.connector import MySQLConnection, Error from splinter.exceptions import ElementDoesNotExist import sys browser = Browser('phantomjs') time.sleep(1) try: conn = MySQLConnection(host='localhost',database='gujarat_test',user='******',password='******') cursor = conn.cursor() cursor1 = conn.cursor() cursor2 = conn.cursor() cursor3 = conn.cursor() cursor4 = conn.cursor() cursor5 = conn.cursor() cursor6 = conn.cursor() except Error as e: print(e) t="" # 4 for loops to hierarchically accessing state -> district -> tehsil -> village, if execution stops at a certain point due to some # error replace 0 in range of i with current state id, 0 of x with current district id, 0 of y with current tehsil id and so on for z and village id # to resume execution but reset them after on going tehsil or district is done to access next tehsil or district from the first element. i = 11 district_id=1 village_id=1 tehsil_id=1 FLAGprime = True
def connect(): #"""Gets Springserve data and writes to MySQL table""" db = "mysql_sl" api = "springs" page = 1 db_updated = False # Connect to DB: db_config = read_db_config(db) try: #print('Connecting to database...') conn = MySQLConnection(**db_config) if conn.is_connected(): #print('Connection established.') cursor = conn.cursor() # call to get logintoken logintoken = springs_api.get_logintoken(api) #print(logintoken) while True: jsoninfo = { "date_range": "Yesterday", "interval": "hour", "timezone": "America/Los_Angeles", "dimensions": ["supply_tag_id"], #"start_date": Today, #"endDate": Today, #"sort": [{ "field": "ad_revenue", "order": "desc"}] "page": str(page) } result = springs_api.get_data(logintoken, jsoninfo) #print(result.text) info = json.loads(result.text) if len(info) == 0: break if len(info) >= 1: if db_updated == False: sql = "DROP TABLE IF EXISTS springserve_core_yesterday" cursor.execute(sql) sql = "CREATE TABLE springserve_core_yesterday (date varchar(25), hour int(2), \ source_id varchar(10), supply_source varchar(255), total_requests bigint, \ usable_requests bigint, opportunities bigint, ad_impressions bigint, \ revenue decimal(15, 5), clicks bigint)" cursor.execute(sql) db_updated = True print( str(todaytime) + " Running springserve_core_yesterday. Page # " + str(page) + " Count " + str(len(info))) # use default to populate null data default = '0' for x in info: date1 = x['date'] date = date1[:10] time1 = x['date'] time2 = time1[11:-8].replace("00", "*0").lstrip("0") hour = time2.replace("*0", "0") source_id = x['supply_tag_id'] supply_source = x['supply_tag_name'] total_requests = x['total_requests'] usable_requests = x['usable_requests'] opportunities = x['opportunities'] ad_impressions = x['total_impressions'] revenue = x['revenue'] clicks = x['clicks'] list = (date, hour, source_id, supply_source, total_requests, \ usable_requests, opportunities, ad_impressions, revenue, clicks) #print(list) sql = """INSERT INTO springserve_core_yesterday VALUES ("%s", "%s", "%s", \ "%s", "%s", "%s", "%s", "%s", "%s", "%s")""" % (date, hour, source_id, \ supply_source, total_requests, usable_requests, opportunities, \ ad_impressions, revenue, clicks) cursor.execute(sql) cursor.execute('commit') page += 1 else: print('Connection failed.') except Error as Error: print(error) finally: conn.close()
from mysql.connector import MySQLConnection, Error from util import xstr cnx = MySQLConnection(user='******', password='******', database='project') cursor = cnx.cursor() def view_groups(user): print("-----------------Viewing Your Groups------------------") cursor.callproc("get_groups", [user]) for result in cursor.stored_results(): row = result.fetchone() if row is None: print("You are not a member of any groups") else: while row is not None: print("group-id: " + xstr(row[0])) print("group-name: " + row[1]) print("Description: " + row[2]) print("---------------------------------------------------") row = result.fetchone() print("You are not a member of any other groups") print("------------------------------------------------") break while True: print( "Options:\n1.Go back to home\n2.View group page\n3.Suggest groups\n4.Create a group" ) option = raw_input("Enter a number:\n") if option == '1': return
def connect(): # """Gets AOL Data and writes them to a MySQL table""" db = "mysql_sl" # Connect To DB: db_config = read_db_config(db) try: print('Connecting to database...') conn = MySQLConnection(**db_config) if conn.is_connected(): print('connection established.') cursor = conn.cursor() sql = "DROP TABLE IF EXISTS dc_core_today_media" cursor.execute(sql) sql = "CREATE TABLE dc_core_today_media (inventory_source varchar(255), media varchar(255), ad_opportunities bigint, \ market_opportunities bigint, ad_attempts bigint, ad_impressions bigint, ad_errors bigint, \ ad_revenue decimal(15, 5), aol_cost decimal(15, 5), epiphany_gross_revenue decimal(15, 5), dc_revenue decimal(15, 5), \ clicks int, iab_viewability_measurable_ad_impressions bigint, iab_viewable_ad_impressions bigint, platform int)" cursor.execute(sql) # calls get_access_token function and starts script logintoken = aol_api.get_access_token( "0e30062d-6746-4a9b-882a-3f61185479c7", "9O7SnFq/yDbNK+4M2bkSqg") print(logintoken) result = aol_api.run_existing_report(logintoken, "143816") #print(result) info = json.loads(result) #print(info) for x in json.loads(result)['data']: inventory_source = x['row'][0] media = x['row'][1] ad_opportunities = x['row'][2] market_opportunities = x['row'][3] ad_attempts = x['row'][4] ad_impressions = x['row'][5] ad_errors = x['row'][6] ad_revenue = x['row'][7] aol_cost = x['row'][7] epiphany_gross_revenue = x['row'][7] dc_revenue = x['row'][7] clicks = x['row'][8] iab_viewability_measurable_ad_impressions = x['row'][9] iab_viewable_ad_impressions = x['row'][10] platform = '2' list =(inventory_source, media, ad_opportunities, market_opportunities, ad_attempts, ad_impressions, \ ad_errors, ad_revenue, aol_cost, epiphany_gross_revenue, dc_revenue, clicks, \ iab_viewability_measurable_ad_impressions, iab_viewable_ad_impressions, platform) #print(list) sql = """INSERT INTO dc_core_today_media VALUES ("%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s"*.20, \ "%s"*.56, "%s"*.24, "%s", "%s", "%s", "%s")""" % (inventory_source, media, ad_opportunities, \ market_opportunities, ad_attempts, ad_impressions, ad_errors, ad_revenue, aol_cost, \ epiphany_gross_revenue, dc_revenue, clicks, iab_viewability_measurable_ad_impressions, \ iab_viewable_ad_impressions, platform) cursor.execute(sql) cursor.execute('commit') else: print('Connection failed') except Error as error: print(error) finally: conn.close() print('Connection Closed')
def run_execute(self, sorgu): connection = MySQLConnection(**config) cursor = connection.cursor(dictionary=True) cursor.execute(sorgu) connection.commit() connection.close()
def removeSala(): error = None if request.method == 'GET': Funcionario = [] g.db = MySQLConnection(**db_config) cursor = g.db.cursor() query = "SELECT ID,Nome,Sobrenome,CPF,RG,DataNascimento,Endereco,Sexo,data_admissao FROM Funcionario" cursor.execute(query) for (ID, Nome, Sobrenome, CPF, RG, DataNascimento, Endereco, Sexo, data_admissao) in cursor: print(Nome) Funcionario.append({ 'ID': ID, 'Nome': Nome, 'Sobrenome': Sobrenome, 'CPF': CPF, 'RG': RG, 'DataNascimento': DataNascimento, 'Endereco': Endereco, 'Sexo': Sexo, 'data_admissao': data_admissao }) cursor.close() g.db.close() session['Funcionario'] = Funcionario if request.method == 'POST': ID = request.form['ID'] Nome = request.form['Nome'] dados_demissao = (ID, Nome) try: g.db = MySQLConnection(**db_config) cursor = g.db.cursor() consulta1 = "SELECT * FROM Sala WHERE ID=(%s) AND Nome=(%s)" cursor.execute(consulta1, (ID, Nome)) rows = cursor.fetchall() for r in rows: print(r) if cursor: try: cursor = g.db.cursor(prepared=True) consulta = "UPDATE Sala SET Ativo:='0' WHERE ID=(%s) AND Nome=(%s)" cursor.execute(consulta, (ID, Nome)) g.db.commit() cursor.close() g.db.close() return render_template('Sala.html') except: return render_template('removeSala.html', title='removeSala') else: cursor.close() g.db.close() return render_template('removeSala.html', title='removeSala') except Exception as e: # return (str(e)) return render_template('removeSala.html', title='removeSala', error=e) return render_template('removeSala.html', title='removeSala')
def connect(): # """Gets AOL Data and writes them to a MySQL table""" db = "mysql_dp" api = "adsym" # Connect To DB: db_config = read_db_config(db) try: print('Connecting to database...') conn = MySQLConnection(**db_config) if conn.is_connected(): print('Connection established.') cursor = conn.cursor() sql = "DROP TABLE IF EXISTS adsym_market_public" cursor.execute(sql) sql = "CREATE TABLE adsym_market_public (date varchar(25), inventory_source varchar(255), geo_country varchar(50), \ buyer_organization varchar(100), ad_opportunities varchar(2), ad_attempts bigint, ad_impressions bigint, \ ad_revenue decimal(15, 5), ecpm decimal(6, 4), media_spend decimal(15, 5), completed_views int, clicks int, \ platform int)" cursor.execute(sql) # calls get_access_token function and starts script logintoken = aol_api_R.get_access_token(api) print(logintoken) result = aol_api_R.run_existing_report(logintoken, "161182") #print(result) info = json.loads(result) #print(info) for x in json.loads(result)['data']: date = x['row'][0] inventory_source = x['row'][1].replace("'", " -") geo_country = x['row'][2].replace(",", " -") buyer_organization = x['row'][3].replace('"', " ") ad_opportunities = '0' ad_attempts = x['row'][5] ad_impressions = x['row'][6] ad_revenue = x['row'][7] ecpm = x['row'][8] media_spend = x['row'][9] completed_views = x['row'][10] clicks = x['row'][11].replace(" ", "0") platform = '4' list = (date, inventory_source, geo_country, buyer_organization, ad_opportunities, ad_attempts, ad_impressions, \ ad_revenue, ecpm, media_spend, completed_views, clicks, platform) #print(list) sql = """INSERT INTO adsym_market_public VALUES ("%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", \ "%s", "%s", "%s")""" % (date, inventory_source, geo_country, buyer_organization, ad_opportunities, \ ad_attempts, ad_impressions, ad_revenue, ecpm, media_spend, completed_views, clicks, platform) cursor.execute(sql) cursor.execute('commit') else: print('Connection failed.') except Error as error: print(error) finally: conn.close() print('Connection closed.')
def connect(): # """Gets AOL Data and writes them to a MySQL table""" db = "mysql_dp" report_type = "market_private" p_name = sys.argv[1] p_id = platforms[p_name]["id"] m_id = platforms[p_name]["market_id"]["private"] db_updated = False # Connect To DB: db_config = read_db_config(db) try: #print('Connecting to database...') conn = MySQLConnection(**db_config) if conn.is_connected(): #print('Connected established.') cursor = conn.cursor() # calls get_access_token function and starts script logintoken = aol_api.get_access_token(p_name) #print(logintoken) for report in report_book[report_type][p_name]: result = aol_api.run_existing_report(logintoken, str(report)) #print(result) if len(result) == 0: break if len(result) >= 1: if db_updated == False: sql = "DROP TABLE IF EXISTS " + p_name + "_market_private" cursor.execute(sql) sql = "CREATE TABLE " + p_name + "_market_private (date varchar(25), inventory_source varchar(255), \ geo_country varchar(50), buyer_organization varchar(100), ad_opportunities varchar(2), \ ad_attempts bigint, ad_impressions bigint, ad_revenue decimal(15, 5), media_spend decimal(15, 5), \ ecpm decimal(6, 4), completed_views int, clicks int, platform int, market_id int)" cursor.execute(sql) db_updated = True print( str(todaysdate) + " Running " + p_name + "_market_private id_" + str(p_id) + " with report # " + str(report)) for x in json.loads(result)['data']: date = x['row'][0] inventory_source = x['row'][1].replace("'", " -") geo_country = x['row'][2].replace(",", " -") buyer_organization = x['row'][3].replace('"', " ") ad_opportunities = '0' ad_attempts = x['row'][5] ad_impressions = x['row'][6] ad_revenue = x['row'][7] media_spend = x['row'][8] ecpm = x['row'][9] completed_views = x['row'][10] clicks = x['row'][11].replace(" ", "0") platform = str(p_id) market_id = str(m_id) list = (date, inventory_source, geo_country, buyer_organization, ad_opportunities, ad_attempts, \ ad_impressions, ad_revenue, media_spend, ecpm, completed_views, clicks, platform, market_id) #print(list) sql = """INSERT INTO """ + p_name + """_market_private VALUES ("%s", "%s", "%s", "%s", "%s", "%s", "%s", \ "%s", "%s", "%s", "%s", "%s", "%s", "%s")""" % (date, inventory_source, geo_country, \ buyer_organization, ad_opportunities, ad_attempts, ad_impressions, ad_revenue, media_spend, \ ecpm, completed_views, clicks, platform, market_id) cursor.execute(sql) cursor.execute('commit') else: print('Connection failed.') except Error as error: print(error) finally: conn.close()
def connect(): # """Gets AOL Data and writes them to a MySQL table""" db = "mysql_sl" api = "dc" # Connect To DB: db_config = read_db_config(db) try: print('Connecting to database...') conn = MySQLConnection(**db_config) if conn.is_connected(): print('Connection established.') cursor = conn.cursor() sql = "DROP TABLE IF EXISTS dc_market_yesterday" cursor.execute(sql) sql = "CREATE TABLE dc_market_yesterday (date varchar(25), hour int, buyer_organization varchar(255), \ inventory_source varchar(255), market_opportunities bigint, ad_attempts bigint, ad_impressions bigint, \ ad_errors bigint, ad_revenue decimal(15, 5), aol_cost decimal(15, 5), epiphany_gross_revenue decimal(15, 5), \ dc_revenue decimal(15, 5), total_clicks int, iab_viewability_measurable_ad_impressions bigint, \ iab_viewable_ad_impressions bigint, platform int)" cursor.execute(sql) # calls get_access_token function and starts script logintoken = aol_api.get_access_token(api) print logintoken result = aol_api.run_existing_report(logintoken, "143986") #print(result) info = json.loads(result) #print(info) for x in json.loads(result)['data']: date = x['row'][0] hour = x['row'][1] buyer_organization = x['row'][2] inventory_source = x['row'][3] market_opportunities = x['row'][4] ad_attempts = x['row'][5] ad_impressions = x['row'][6] ad_errors = x['row'][7] ad_revenue = x['row'][8] aol_cost = x['row'][8] epiphany_gross_revenue = x['row'][8] dc_revenue = x['row'][8] total_clicks = x['row'][9] iab_viewability_measurable_ad_impressions = x['row'][10] iab_viewable_ad_impressions = x['row'][11] platform = '2' list = (date, hour, buyer_organization, inventory_source, market_opportunities, ad_attempts, ad_impressions, \ ad_errors, ad_revenue, aol_cost, epiphany_gross_revenue, dc_revenue, total_clicks, \ iab_viewability_measurable_ad_impressions, iab_viewable_ad_impressions, platform) #print(list) sql = """INSERT INTO dc_market_yesterday VALUES ("%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s"*.20, \ "%s"*.56, "%s"*.24, "%s", "%s", "%s", "%s")""" % (date, hour, buyer_organization, inventory_source, \ market_opportunities, ad_attempts, ad_impressions, ad_errors, ad_revenue, aol_cost, \ epiphany_gross_revenue, dc_revenue, total_clicks, iab_viewability_measurable_ad_impressions, \ iab_viewable_ad_impressions, platform) cursor.execute(sql) cursor.execute('commit') else: print('Connection failed.') except Error as error: print(error) finally: conn.close() print('Connection closed.')
users = [] while(count!=0): count-=1 user = "" user += (names[random.randint(0,len(names)-1)][1]+ " " + surnames[random.randint(0,len(surnames)-1)][1]+";") tmp = random.randint(0,len(addresses)-1) user+=(addresses[tmp][4]+";") user+=(addresses[tmp][5]+" "+addresses[tmp][3]+" "+ addresses[tmp][2]+" "+addresses[tmp][1]+";") user+=phones[random.randint(0,len(phones)-1)][1] users.append(user) return users region = sys.argv[1] eps = int(sys.argv[3]) db = MySQLConnection(host='localhost', user='******', passwd='1773002153904il', db='task1') cursor = db.cursor() tables = formTables(region) symbols = [] for i in range(32,58): symbols.append(chr(i)) for i in range(60,128): symbols.append(chr(i)) users = [] if(region == "ru" or region == "by"): users = generateByRu(cursor,tables,int(sys.argv[2])) else: users = generateUSA(cursor,tables,int(sys.argv[2])) db.close() new_users = [] if(eps == 0):
def connect(): # """Gets AOL Data and writes them to a MySQL table""" db = "mysql_dl" api = "adsym" # Connect To DB: db_config = read_db_config(db) try: print('Connecting to database...') conn = MySQLConnection(**db_config) if conn.is_connected(): print('Connection established.') cursor = conn.cursor() # calls get_access_token function and starts script logintoken = aol_api.get_access_token(api) print(logintoken) result = aol_api.run_existing_report(logintoken, "190596") #print(result) info = json.loads(result) #print(info) for x in json.loads(result)['data']: rownum = '' date = x['row'][0] inventory_source = x['row'][1].replace("'", " -").replace('"', "") geo_country = x['row'][2].replace("'", "") media = x['row'][3].replace('"', "").replace("'", "") ad_opportunities = x['row'][4] ad_attempts = x['row'][5] ad_impressions = x['row'][6] ad_revenue = x['row'][7] ecpm = x['row'][8] ad_errors = x['row'][9] iab_viewability_measurable_ad_impressions = x['row'][10] iab_viewable_ad_impressions = x['row'][11] market_ops = x['row'][12] clicks = x['row'][13].replace(" ", "0") list = (rownum, date, inventory_source, geo_country, media, ad_opportunities, ad_attempts, ad_impressions, \ ad_revenue, ecpm, ad_errors, iab_viewability_measurable_ad_impressions, iab_viewable_ad_impressions, market_ops, clicks) #print(list) sql = """INSERT INTO adsym_InventorySources_v2 VALUES ("%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", \ "%s", "%s", "%s", "%s")""" % (rownum, date, inventory_source, geo_country, media, ad_opportunities, ad_attempts, ad_impressions, \ ad_revenue, ecpm, ad_errors, iab_viewability_measurable_ad_impressions, iab_viewable_ad_impressions, market_ops, clicks) cursor.execute(sql) cursor.execute('commit') else: print('Connection failed.') except Error as error: print(error) finally: conn.close() print('Connection closed.')
def connect(): # """Gets AOL Data and writes them to a MySQL table""" db = "mysql_dp" report_type = "inventoryreport" p_name = sys.argv[1] p_id = platforms[p_name]["id"] db_updated = False # Connect To DB: db_config = read_db_config(db) try: #print('Connecting to database...') conn = MySQLConnection(**db_config) if conn.is_connected(): #print('Connection established.') cursor = conn.cursor() # calls get_access_token function and starts script logintoken = aol_api.get_access_token(p_name) #print(logintoken) for report in report_book[report_type][p_name]: result = aol_api.run_existing_report(logintoken, str(report)) #print(result) if len(result) == 0: break if len(result) >= 1: if db_updated == False: sql = "DROP TABLE IF EXISTS " + p_name + "_inventoryreport" cursor.execute(sql) sql = "CREATE TABLE " + p_name + "_inventoryreport (date varchar(50), inventory_source varchar(255), \ geo_country varchar(50), ad_opportunities bigint, ad_attempts bigint, ad_impressions bigint, \ ad_revenue decimal(15, 5), ecpm decimal(15, 5), platform int)" cursor.execute(sql) db_updated = True print( str(todaysdate) + " Running " + p_name + "_inventoryreport id_" + str(p_id) + " with report # " + str(report)) for x in json.loads(result)['data']: date = x['row'][0] inventory_source = x['row'][1] geo_country = x['row'][2] ad_opportunities = x['row'][3] ad_attempts = x['row'][4] ad_impressions = x['row'][5] ad_revenue = x['row'][6] ecpm = x['row'][7] platform = str(p_id) list = (date, inventory_source, geo_country, ad_opportunities, ad_attempts, ad_impressions, ad_revenue, \ ecpm, platform) #print(list) sql = """INSERT INTO """ + p_name + """_inventoryreport VALUES ("%s", "%s", "%s", "%s", "%s", "%s", "%s", \ "%s", "%s")""" % (date, inventory_source, geo_country, ad_opportunities, ad_attempts, \ ad_impressions, ad_revenue, ecpm, platform) cursor.execute(sql) cursor.execute('commit') else: print('Connection failed.') except Error as error: print(error) finally: conn.close()
def connect(): # """Gets AOL Data and writes them to a MySQL table""" db = "mysql_eom" # Connect To DB: db_config = read_db_config(db) try: print('Connecting to database...') conn = MySQLConnection(**db_config) if conn.is_connected(): print('Connection established') cursor = conn.cursor() sql = "DROP TABLE IF EXISTS tm_inventoryreport_EOM" cursor.execute(sql) sql = "CREATE TABLE tm_inventoryreport_EOM (date varchar(50), inventory_source varchar(255), geo_country varchar(50), \ ad_opportunities bigint, ad_attempts bigint, ad_impressions bigint, \ ad_revenue decimal(15, 5), ecpm decimal(15, 5), platform int)" cursor.execute(sql) # calls get_access_token function and starts script logintoken = aol_api.get_access_token( "9fadb507-01e9-4a2a-b9c5-c9d65d93396e", "y0QxY3DC7vbczeW7nBOsZg") print(logintoken) result = aol_api.run_existing_report(logintoken, "193899") #print(result) info = json.loads(result) #print(info) for x in json.loads(result)['data']: date = x['row'][0] inventory_source = x['row'][1] geo_country = x['row'][2] ad_opportunities = x['row'][3] ad_attempts = x['row'][4] ad_impressions = x['row'][5] ad_revenue = x['row'][6] ecpm = x['row'][7] platform = '5' list = (date, inventory_source, geo_country, ad_opportunities, ad_attempts, ad_impressions, ad_revenue, ecpm, \ platform) #print(list) sql = """INSERT INTO tm_inventoryreport_EOM VALUES ("%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s")""" % \ (date, inventory_source, geo_country, ad_opportunities, ad_attempts, ad_impressions, ad_revenue, ecpm, \ platform) cursor.execute(sql) cursor.execute('commit') else: print('Connection failed.') except Error as error: print(error) finally: conn.close() print('Connection closed.')
def find_part(part_num): dbconfig = read_db_config() try: conn = MySQLConnection(**dbconfig) cursor = conn.cursor() bean = False if (part_num[:6]) in resistors: quality = "Resistance" variant = "Resistance Tolerance" bean = True if (part_num[:6]) in capacitors: quality = "Capacitance" variant = "Dielectric Characteristic" bean = True if (bean): component = part_num.split('_') if (len(component)) <= 2: print( "Insufficient parameters (Needs 3 or 4) e.g. R_0805_100K(_±5%)" ) return ("0") c_case = component[1] c_value = convert_units(component[2]) if (len(component)) == 4: c_characteristics = component[3] # A fully specified 'bean' sql = """SELECT P.name, P.description, P.stockLevel, P.minStockLevel, P.averagePrice, P.internalPartNumber, S.name, P.id, M.name FROM Part P JOIN PartParameter R ON R.part_id = P.id JOIN StorageLocation S ON S.id = P.storageLocation_id LEFT JOIN PartManufacturer PM on PM.part_id = P.id LEFT JOIN Manufacturer M on M.id = PM.manufacturer_id WHERE (R.name = 'Case/Package' AND R.stringValue='{}') OR (R.name = '{}' AND R.normalizedValue = '{}') OR (R.name = '{}' AND R.stringValue = '%{}') GROUP BY P.id, M.id, S.id HAVING COUNT(DISTINCT R.name)=3""".format( c_case, quality, c_value, variant, c_characteristics) else: # A partially specified 'bean' sql = """SELECT P.name, P.description, P.stockLevel, P.minStockLevel, P.averagePrice, P.internalPartNumber, S.name, P.id, M.name FROM Part P JOIN PartParameter R ON R.part_id = P.id JOIN StorageLocation S ON S.id = P.storageLocation_id LEFT JOIN PartManufacturer PM on PM.part_id = P.id LEFT JOIN Manufacturer M on M.id = PM.manufacturer_id WHERE (R.name = 'Case/Package' AND R.stringValue='{}') OR (R.name = '{}' AND R.normalizedValue = '{}') GROUP BY P.id, M.id, S.id HAVING COUNT(DISTINCT R.name)=2""".format( c_case, quality, c_value) else: sql = """SELECT P.name, P.description, P.stockLevel, P.minStockLevel, P.averagePrice, P.internalPartNumber, S.name, P.id, M.name FROM Part P JOIN StorageLocation S ON S.id = P.storageLocation_id LEFT JOIN PartManufacturer PM on PM.part_id = P.id LEFT JOIN Manufacturer M on M.id = PM.manufacturer_id WHERE P.name LIKE '%{}%'""".format(part_num) cursor.execute(sql) components = cursor.fetchall() return (components, bean) except UnicodeEncodeError as err: print(err) finally: cursor.close() conn.close()
def connect(): # """Gets AOL Data and writes them to a MySQL table""" db = "mysql_sl" report_type = "core_yesterday" p_name = sys.argv[1] p_id = platforms[p_name]["id"] gross_rev = platforms[p_name]["fee"] r = fees["aol_platform"] a_cost = fees["aol_cost"] platform_rev = p_name + "_revenue" db_updated = False # Connect To DB: db_config = read_db_config(db) try: #print('Connecting to database...') conn = MySQLConnection(**db_config) if conn.is_connected(): #print('Connection established') cursor = conn.cursor() # calls get_access_token function and starts script logintoken = aol_api.get_access_token(p_name) #print logintoken for report in report_book[report_type][p_name]: result = aol_api.run_existing_report(logintoken, str(report)) #print(result) if len(result) == 0: break row_count_value = json.loads(result)['row_count'] if int(row_count_value) >= 1: if db_updated == False: sql = "DROP TABLE IF EXISTS " + p_name + "_core_yesterday" cursor.execute(sql) sql = "CREATE TABLE " + p_name + "_core_yesterday (date varchar(25), hour int, inventory_source varchar(255), ad_opportunities bigint, \ market_opportunities bigint, ad_attempts bigint, ad_impressions bigint, ad_errors bigint, ad_revenue decimal(15, 5), \ aol_cost decimal(15, 5), epiphany_gross_revenue decimal(15, 5), " + p_name + "_revenue decimal(15, 5), total_clicks int, \ iab_viewability_measurable_ad_impressions bigint, iab_viewable_ad_impressions bigint, platform int)" cursor.execute(sql) db_updated = True print( str(todaysdate) + " Running " + p_name + "_core_yesterday report # " + str(report)) for x in json.loads(result)['data']: date = x['row'][0] hour = x['row'][1] inventory_source = x['row'][2] ad_opportunities = x['row'][3] market_opportunities = x['row'][4] ad_attempts = x['row'][5] ad_impressions = x['row'][6] ad_errors = x['row'][7] ad_revenue = x['row'][8] aol_cos = x['row'][8] epiphany_gross_rev = x['row'][8] platform_rev = x['row'][8] total_clicks = x['row'][9] iab_viewability_measurable_ad_impressions = x['row'][10] iab_viewable_ad_impressions = x['row'][11] platform = str(p_id) list = (date, hour, inventory_source, ad_opportunities, market_opportunities, ad_attempts, ad_impressions, \ ad_errors, ad_revenue, aol_cos, epiphany_gross_rev, platform_rev, total_clicks, \ iab_viewability_measurable_ad_impressions, iab_viewable_ad_impressions, platform) #print(list) if p_name == 'dna': aol_cost = '0' epiphany_gross_revenue = '0' platform_revenue = '0' else: aol_cost = float(float(aol_cos) * float(a_cost)) epiphany_gross_revenue = float( float(epiphany_gross_rev) * float(gross_rev)) platform_revenue = float( float(platform_rev) * float(r)) sql = """INSERT INTO """ + p_name + """_core_yesterday VALUES ("%s", "%s", "%s", "%s", "%s", "%s", "%s", \ "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s")""" % (date, hour, inventory_source, \ ad_opportunities, market_opportunities, ad_attempts, ad_impressions, ad_errors, ad_revenue, \ aol_cost, epiphany_gross_revenue, platform_revenue, total_clicks, \ iab_viewability_measurable_ad_impressions, iab_viewable_ad_impressions, platform) cursor.execute(sql) cursor.execute('commit') else: print('connection failed.') except Error as error: print(error) finally: conn.close()
mode='w') handler.setFormatter( logging.Formatter('%(asctime)s:%(levelname)s:%(name)s: %(message)s')) logger.addHandler(handler) # Set up Intents intents = discord.Intents.all() # Load the bot's Discord Token load_dotenv() TOKEN = os.getenv('DISCORD_TOKEN') print(f'{TOKEN} has connected to Discord!') # Setup a mysql DB Connection dbconfig = read_db_config() conn = MySQLConnection(**dbconfig) if conn.is_connected(): print("Database Connection Successful.") else: print("Database Connection Failed.") exit conn.autocommit = True cursor = conn.cursor() # Setup Command Prefix bot = commands.Bot(command_prefix='!sb ', intents=intents) @bot.command(name='echo', help='Echos the message back to a user') async def echo(ctx, *args): # Echo the message back to the user
def connect(): # """Gets AOL Data and writes them to a MySQL table""" db = "mysql_sl" api = "aol" # Connect To DB: db_config = read_db_config(db) try: print('Connecting to database...') conn = MySQLConnection(**db_config) if conn.is_connected(): print('Connection established.') cursor = conn.cursor() # Create Table: sql = "DROP TABLE IF EXISTS v3_core_today" cursor.execute(sql) sql = "CREATE TABLE v3_core_today (hour int, inventory_source varchar(255), ad_opportunities bigint, \ market_opportunities bigint, ad_attempts bigint, ad_impressions bigint, ad_errors bigint, ad_revenue decimal(15, 5), \ total_clicks int, iab_viewability_measurable_ad_impressions bigint, iab_viewable_ad_impressions bigint)" cursor.execute(sql) # calls get_access_token function and starts script logintoken = aol_api.get_access_token(api) print(logintoken) result = aol_api.run_existing_report(logintoken, "142557") #print(result) info = json.loads(result) #print(info) for x in json.loads(result)['data']: hour = x['row'][0] inventory_source = x['row'][1] ad_opportunities = x['row'][2] market_opportunities = x['row'][3] ad_attempts = x['row'][4] ad_impressions = x['row'][5] ad_errors = x['row'][6] ad_revenue = x['row'][7] total_clicks = x['row'][8] iab_viewability_measurable_ad_impressions = x['row'][9] iab_viewable_ad_impressions = x['row'][10] list = (hour, inventory_source, ad_opportunities, market_opportunities, ad_attempts, ad_impressions, \ ad_errors, ad_revenue, total_clicks, iab_viewability_measurable_ad_impressions, iab_viewable_ad_impressions) # print(list) sql = """INSERT INTO v3_core_today VALUES ("%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", \ "%s", "%s", "%s")""" % (hour, inventory_source, ad_opportunities, market_opportunities, \ ad_attempts, ad_impressions, ad_errors, ad_revenue, total_clicks, \ iab_viewability_measurable_ad_impressions, iab_viewable_ad_impressions) cursor.execute(sql) cursor.execute('commit') else: print('Connection failed.') except Error as error: print(error) finally: conn.close() print('Connection closed')
def search_list(user_id): #Назначим кнопки markup = types.InlineKeyboardMarkup() markup.add( types.InlineKeyboardButton( text=u'\U0001F30D Искать каждый товар отдельно', callback_data='locallist_one:'), ) markup.add( types.InlineKeyboardButton(text=u'\U0001F50D Продолжить поиск', switch_inline_query_current_chat=""), ) #Проверим что в списке есть товары db_config = read_db_config() conn = MySQLConnection(**db_config) cursor = conn.cursor() SQL = 'select count(distinct(product_id)) from user_product_list where chat_id = %s' cursor.execute(SQL, (user_id, )) products = cursor.fetchone() if products[0] == 0: bot.send_message(user_id, 'Сначала добавьте товары в список для поиска') cursor.close() conn.close() else: #Ищем аптеки с поответствием по списку товара db_config = read_db_config() conn = MySQLConnection(**db_config) cursor = conn.cursor() SQL = """\ SELECT s.name, s.address, s.mode, s.phone, s.latitude ,s.longitude, t.way FROM ( SELECT count(p2.product_id) kol, p1.name, get_way(p1.latitude ,p1.longitude,u.latitude,u.longitude) way FROM users u inner join store p1 on p1.city = u.city inner join stock p2 on p2.company = p1.company and p1.name = p2.store WHERE u.chat_id = %s and p2.product_id in (select distinct(product_id) from user_product_list where chat_id = %s) group by p1.name, p1.latitude ,p1.longitude,u.latitude,u.longitude having count(p2.product_id)=(select count(distinct(product_id)) from user_product_list where chat_id = %s) ) t inner join store s on s.name = t.name order by t.way asc LIMIT 3 """ cursor.execute(SQL, ( user_id, user_id, user_id, )) stores = cursor.fetchall() for store in stores: try: bot.send_venue(user_id, store[4], store[5], store[0] + ' (' + str(store[6]) + ' м.)', store[1]) bot.send_message( user_id, store[2] + '\n' + 'Тел: ' + store[3] + '\nЕсть все по списку', parse_mode='markdown', ) except Exception as e: print(e) cursor.close() conn.close() bot.send_message( user_id, 'Если вас не устроили эти аптеки, вы можете поискать отдельно каждый товар из списка в ближайших аптеках', parse_mode='markdown', reply_markup=markup, )