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 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 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 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 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 dataDiri(user): pswd = getpass.getpass('Masukan password : '******'Sukses mendaftar') else: print("Last insert id not found") conn.commit() except Error as e: print(e) finally: cursor.close() conn.close
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 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 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 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 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 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 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 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 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 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 register_data(request): try: username = request.POST.get('username','') email = request.POST.get('email', '') phone = request.POST.get('phone', '') Error_dict = {} if(username == '' or email == "" or phone == "" ) : Error_dict['Invalid'] = "Enter Correct Values" else: dbconfig = {'password': '******', 'host': 'localhost', 'user': '******', 'database': 'login_database'} conn = MySQLConnection(**dbconfig) cursor = conn.cursor() cursor.execute("select COUNT(*) from client_info where phone_no = " + str(phone) + " ") count = cursor.fetchone() count = count[0] if(count > 0): Error_dict['user_exist'] = "User Already Exist" else: cursor.execute("insert into client_info (Name , email, phone_no) values ('%s', '%s', '%s')" % (username, email, phone)) conn.commit() return HttpResponseRedirect('register_success') except Error as e: print(e) return render_to_response('register_form.html', Error_dict)
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 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 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 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_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 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 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 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 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 query_with_fetchone(request): try: month_var = request.GET['month'] year_var = request.GET['year'] context_dict = {} context_dict['month'] = month_var context_dict['year'] = year_var dbconfig = {'password': '******', 'host': 'localhost', 'user': '******', 'database': 'stats'} conn = MySQLConnection(**dbconfig) # print month_var, year_var cursor = conn.cursor() cursor.execute("select COUNT(*) from clientBookings where MONTH(CheckInDate) = " + str(month_var) + " AND YEAR(CheckInDate) = " + str(year_var) + " AND status = 'A'") accept = cursor.fetchone() # print(accept) context_dict['accept'] = accept[0] cursor1 = conn.cursor() cursor1.execute("select COUNT(*) from clientBookings where MONTH(CheckInDate) = " + str(month_var) + " AND YEAR(CheckInDate) = " + str(year_var) + " AND status = 'X'") cancel = cursor1.fetchone() # print(cancel) context_dict['cancel'] = cancel[0] cursor2 = conn.cursor() cursor2.execute("select COUNT(*) from clientBookings where MONTH(CheckInDate) = " + str(month_var) + " AND YEAR(CheckInDate) = " + str(year_var) + " AND status = 'p'") wait = cursor2.fetchone() # print(wait) context_dict['wait'] = wait[0] cursor3 = conn.cursor() cursor3.execute("select COUNT(*) from clientBookings where MONTH(CheckInDate) = " + str(month_var) + " AND YEAR(CheckInDate) = " + str(year_var) + " AND status = 'R'") req = cursor3.fetchone() # print(req) context_dict['req'] = req[0] except Error as e: print(e) finally: cursor.close() conn.close() return render_to_response('gchart.html' , context_dict)
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 getData(): try: dbconfig = read_db_config() conn = MySQLConnection(**dbconfig) cursor = conn.cursor() cursor.execute((""" select table.row from table """)) rows = cursor.fetchall() return rows except Error as e: print(e) finally: cursor.close() conn.close()
def dumpToDataBase(csv_name, selection, neighbors, results): query = "INSERT INTO data(csv_name,selection,neighbors,results)"\ "VALUES(%s,%s,%s,%s)" 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 sql_insert(sql_query, database): try: db_config = read_db_config(filename='mysql_client/config.ini', section=database) conn = MySQLConnection(**db_config) cursor = conn.cursor() cursor.execute(sql_query) 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 findWordByAbbreviation(word): try: db_config = read_db_config() # db_config['database'] = "travel" conn = MySQLConnection(**db_config) cursor = conn.cursor() command = "SELECT word FROM abbreviation_dictionary WHERE abbreviation = '" + word + "'" cursor.execute(command) rows = cursor.fetchall() if rows: row = rows[0] return row[0] return '' except Error as error: print error finally: cursor.close() conn.close()
def getCuisineFromRestaurantId(db_name, rest_id, restaurant_cuisine): try: db_config = read_db_config() db_config['database'] = db_name conn = MySQLConnection(**db_config) cursor = conn.cursor() cursor.execute( "SELECT cuisine_name FROM cuisine_restaurant WHERE restaurant_id = '" + str(rest_id) + "'") rows = cursor.fetchall() for row in rows: restaurant_cuisine.append(row[0]) except Error as error: print "Error: getCuisineFromRestaurantId" print error finally: cursor.close() conn.close()
def query_items_for_build(god_id, category): try: dbconfig = read_db_config() conn = MySQLConnection(**dbconfig) cursor = conn.cursor() cursor.execute(f""" SELECT god_name, item_name FROM {CATEGORIES[category]} JOIN god ON {CATEGORIES[category]}.god_id=god.god_id JOIN item ON {CATEGORIES[category]}.item_id=item.item_id WHERE god.god_id={god_id} """) rows = cursor.fetchall() items = [row[1] for row in rows] # print('Total Row(s):', cursor.rowcount) except Error as e: print(e) finally: cursor.close() conn.close() return items
def update_table_with_csv_data(conn: mysql.MySQLConnection): current_file = read_csv() date_of_dataset = format_date_to_sql(current_file.get(0)[TOP100.TIME]) if dataset_already_in_database(conn, date_of_dataset): print('Dataset already in database.') return cursor = conn.cursor() for row in current_file: if "Downloaded from" in current_file.get(row)[TOP100.SYMBOL]: break column_names, values = get_column_names_and_values(current_file[row]) sql = f'INSERT INTO {TABLE_NAME} {column_names} VALUES {values}' cursor.execute(sql) conn.commit() print( f'Dataset from "{date_of_dataset} was sucessfully added to database.')
class SaveToDBPipeline(object): 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 process_item(self, item, spider): fun_name = 'process_' + item['pipeline_type'] method = getattr(self, fun_name) if not method: raise NotImplementedError("Method %s not implemented" % fun_name) method(item, spider) def process_IPItem(self, item, spider): if item: cursor = self.connector.cursor() sql_query = 'insert into ip_address (ip,port,address,ip_type) values (%s,%s,%s,%s)' cursor.execute( sql_query, (item['ip'], item['port'], item['address'], item['ip_type'])) cursor.close() self.connector.commit()
def modulo_rastreador(modulo, fabricante, gateway): import ipdb ipdb.set_trace() sql_modulo_rastreador = """ SELECT MODULO_RASTREADOR.ID_MODULO_RASTREADOR, MODULO_RASTREADOR.ID_MODELO_RASTREADOR, GATEWAY.ID_GATEWAY FROM MODULO_RASTREADOR, GATEWAY WHERE MODULO_RASTREADOR.ID_ORIGINAL = {MODULO} AND MODULO_RASTREADOR.ID_FABRICANTE = {FABRICANTE} AND GATEWAY.ID_MODELO_MODULO_RASTREAMENTO = MODULO_RASTREADOR.ID_MODELO_RASTREADOR AND GATEWAY.CODIGO_GATEWAY_FISICO = {GATEWAY}""".format( MODULO=modulo, FABRICANTE=fabricante, GATEWAY=gateway) try: dbconfig = read_db_config() conn = MySQLConnection(**dbconfig) cursor = conn.cursor() cursor.execute(sql_modulo_rastreador) rows = cursor.fetchall() if not rows: e = "Sem registros para este item no módulo rastreador" #log_erros(item, e, 'MODULO_RASTREADOR') print(e) for row in rows: id_modulo_rastreador = row[0] return id_modulo_rastreador except Error as e: #log_erros(item, e, 'MODULO_RASTREADOR') print(e) finally: cursor.close() conn.close()
def query_title_matching_records(): try: dbconfig = read_db_config() conn = MySQLConnection(**dbconfig) cursor = conn.cursor() cursor.execute( "select sourceid,sourcetitlename,sourcereleasedate,year(sourcereleasedate) as releaseyear from " + TABLE + " where SourceLookup like '%magic_id%'") magic_titles = list() for b in cursor: c = dict() c['movie_id'] = b[0] c['movie_name'] = b[1] c['release_date'] = b[2] c['release_year'] = b[3] magic_titles.append(c) except Error as e: print(e) finally: cursor.close() conn.close() return magic_titles
def delete_president(president_id): db_config = read_db_config() query = "DELETE FROM presidents WHERE id = %s" try: # connect to the database server conn = MySQLConnection(**db_config) # execute the query cursor = conn.cursor() cursor.execute(query, (president_id, )) # accept the change conn.commit() except Error as error: print(error) finally: cursor.close() conn.close()
def create_index(): """ create index in the table""" commands = ("ALTER TABLE word ADD INDEX idx_word (word, count);", "ALTER TABLE url ADD INDEX idx_url (url);") conn = None try: # read the connection parameters params = config(section='mysql') # connect to the MySQL server conn = MySQLConnection(**params) # create table one by one cursor = conn.cursor() # create tables index for command in commands: cursor.execute(command) cursor.close() conn.commit() print('Create index successfully.') except Error as e: print('Error:', e) finally: conn.close()
def get_word(word, req_ip, request): """ query matched words of specified word from word table """ sql = "SELECT DISTINCT word FROM word WHERE word LIKE {0} ORDER BY word DESC LIMIT 10;".format( "'{0}%'".format(word)) conn = None try: # read the connection parameters params = config(section='mysql') # connect to the MySQL server conn = MySQLConnection(**params) # create table one by one cursor = conn.cursor() cursor.execute(sql) rows = cursor.fetchall() cursor.close() insert_request(req_ip, word, request) # If no row return [] return rows except Error as e: print('Error:', e) finally: conn.close()
def insertOneCuisine(cuisine_name): query = "INSERT INTO cuisine(cuisine_name) " \ "VALUES(%s)" args = (cuisine_name) 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 get_user_enrollments(user_id): try: dbconfig = read_db_config() conn = MySQLConnection(**dbconfig) cursor = conn.cursor() query = "SELECT * FROM enrollments WHERE tg_id =" + str(user_id) cursor.execute(query) row = cursor.fetchone() events = [] while row is not None: events.append(row) print(row) row = cursor.fetchone() except Error as e: print(e) finally: cursor.close() conn.close() return(events)
def get_user_id(name, phone_number): try: connector = MySQLConnection(user='******', password='******', host='127.0.0.1', database="project") cursor = connector.cursor() except Error as e: print("connection error {}".format(e)) sys.exit("unable to connect to db") try: # check the values in the database insert_values = (str(name.split()[0]), str(name.split()[1]), phone_number) insert_command = "SELECT user_id FROM user WHERE (user_first_name=%s and user_last_name=%s and user_phone_number=%s)" cursor.execute(insert_command, insert_values) connector.commit() user_id = cursor.fetchone()[0] return user_id except: print("cannot find user ID sorry cannot enter the Comments") return None
def fetch_all(TestType): try: dbconfig = read_db_config() conn = MySQLConnection(**dbconfig) cursor = conn.cursor() sql0 = "select COLUMN_NAME from information_schema.COLUMNS where table_name = '" + TestType + "';" cursor.execute(sql0) columns_temp = cursor.fetchall() columns = '' for c in columns_temp: c = str(c).strip('()') c = c.strip("',") columns = columns + ',' + c columns = columns.strip(',') sql = "select * from " + TestType cursor.execute(sql) results = cursor.fetchall() #print(columns) #print(columns,results) return columns, results except Error as e: print(e)
def get_data_from(table): '''conecting to db and get average value from table_avg ''' try: conn = MySQLConnection(host='localhost', database='pars_db', user='******', password='******') if conn.is_connected(): print('Connected to MySQL database') cursor = conn.cursor() sql_select_query = "SELECT * FROM {} ".format(table) cursor.execute(sql_select_query) records = cursor.fetchall() except Error as e: print('Error:', e) finally: cursor.close() conn.close() return records
def partStatus(partID, parameter): dbconfig = read_db_config() try: conn = MySQLConnection(**dbconfig) cursor = conn.cursor() sql = "SELECT R.stringValue FROM PartParameter R WHERE (R.name = '{}') AND (R.part_id = {})".format( parameter, partID) cursor.execute(sql) partStatus = cursor.fetchall() if partStatus == []: part = "Unknown" else: part = str(partStatus[0])[2:-3] return (part) except UnicodeEncodeError as err: print(err) 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 None: print(row) row = cursor.fetchone() except Error as e: print(e) finally: cursor.close() conn.close()
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 insert_profile(sy, gr, fa, du, ha, na): query = 'update plant_usda set _group = %s, family=%s, duration=%s, growth_habit=%s, native_status=%s WHERE symbol = %s' args = (gr, fa, du, ha, na, sy) 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 delete2(self, x): dbconfig = read_db_config() conn = MySQLConnection(**dbconfig) cursor = conn.cursor() cursor.execute("SELECT * FROM product") a = [] b = [] c = [] d = [] for row in cursor: a.append(row[0]) b.append(row[1]) c.append(row[2]) d.append(row[3]) if len(a) == 0: return "no" for i in range(len(a)): cursor.execute("DELETE FROM product WHERE p_id LIKE '%s'" % (x)) conn.commit() print "deleted" cursor.close() conn.close()
def insert_pdf_data(field, value, sy): query = 'update plant_usda set %s = \'%s\' WHERE symbol = \'%s\'' % ( field, value, sy) try: db_config = read_db_config() conn = MySQLConnection(**db_config) cursor = conn.cursor() cursor.execute(query) 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 sendup(): stime = time.time() query = "INSERT INTO uptime(datetime, also) " \ "VALUES(now(), 20)" args = (stime) try: dbconfig = read_db_config() conn = MySQLConnection(**dbconfig) cursor = conn.cursor() cursor.execute(query) row = cursor.fetchone() return (row) print(row) except Error as e: print(e) finally: cursor.close() conn.close()
def insert_symbol(sy, _id): query = " insert into plant_usda(symbol,id) values(%s,%s)" args = (sy, _id) 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_factsheet(fa, _id): query = 'update plant_usda set fact_sheet = %s WHERE id = %s' args = (fa, _id) 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 insertOneCity(city_id, name, data_resource): query = "INSERT INTO city(city_id, name, data_resource) " \ "VALUES(%s, %s, %s)" args = (city_id, name, 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 "CITY DATA INSERTED!!!"
def get_urls_from_word(word, req_ip, request): """ get multiple urls, titles and counts of the specified word from the joined table """ sql = "SELECT url, title, CAST(SUM(count) AS UNSIGNED) AS count FROM url INNER JOIN word ON url.url_id = word.wurl_id WHERE word LIKE {0} GROUP BY wurl_id ORDER BY count DESC;".format( "'%{0}%'".format(word)) conn = None try: # read the connection parameters params = config(section='mysql') # connect to the MySQL server conn = MySQLConnection(**params) # create table one by one cursor = conn.cursor() cursor.execute(sql) rows = cursor.fetchall() cursor.close() insert_request(req_ip, word, request) # If no row return [] return rows except Error as e: print('Error:', e) finally: conn.close()
def getCuisineIdFromName(cuisine_name): try: db_config = read_db_config() db_config['database'] = "warehouse" conn = MySQLConnection(**db_config) cursor = conn.cursor() query = "SELECT cuisine_id from cuisine WHERE cuisine_name = '" + cuisine_name + "'" cursor.execute(query) row = cursor.fetchone() if row == None: return -1 return str(row[0]) except Error as error: print "Error: getCuisineIdFromName" print error finally: cursor.close() conn.close()