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_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_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_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_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 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 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 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_book(title, isbn): query = "INSERT INTO books(title, isbn) " "VALUES(%s, %s)" args = (title, isbn) try: db_config = read_db_config() conn = MySQLConnection(**db_config) cursor = conn.cursor() cursor.execute(query, args) if cursor.lastrowid: print ("last insert id", cursor.lastrowid) else: print ("last insert id not found") conn.commit() except Error as error: print error finally: cursor.close() conn.close()
def insert_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 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 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 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 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 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 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 UpdateImage(author_id, filename): kwargs = ReadingMySQLConfig() data = ReadFile(filename) query = "UPDATE authors SET photo = %s WHERE id = %s" args = (data, author_id) try: MyConnection = MySQLConnection(**kwargs) cursor = MyConnection.cursor() cursor.execute(query, args) MyConnection.commit() except Error as e: print(e) finally: MyConnection.close()
def insert_many(entry): query = "insert into test(name, class, skills) values(%s, %s, %s)" try: db_config = read_db_config() conn = MySQLConnection(**db_config) cursor = conn.cursor() cursor.executemany(query, entry) conn.commit() except Error as e: print "[SOMETHING BAD HAPPEND!]",e finally: cursor.close() conn.close()
def update_user(user_id): db_config = read_db_config() query = "UPDATE users SET date = %s WHERE id = %s" date = datetime.now() try: conn = MySQLConnection(**db_config) cursor = conn.cursor() cursor.execute(query, (date, user_id,),) conn.commit() except Error as error: print(error) finally: cursor.close() conn.close() return jsonify({'update': 'OK'})
def update_blob(id, data): query = "UPDATE Puzzles " "SET puzzledata = %s " "WHERE puzzleid = %s" args = (data, id) db_config = read_db_config() try: conn = MySQLConnection(**db_config) cursor = conn.cursor() cursor.execute(query, args) conn.commit() except Error as e: print(e) finally: cursor.close() conn.close()
def insert_ppi(ppis, db_data): query = "INSERT INTO ppi(orfA,orfB) " \ "VALUES(%s,%s)" try: conn = MySQLConnection(**db_data) cursor = conn.cursor() cursor.executemany(query, ppis) conn.commit() except Error as e: print('Error:', e) finally: cursor.close() conn.close()
def insert_genes(genes, db_data): query = "INSERT INTO genes(orfid,geneid) " \ "VALUES(%s,%s)" try: conn = MySQLConnection(**db_data) cursor = conn.cursor() cursor.executemany(query, genes) conn.commit() except Error as e: print('Error:', e) finally: cursor.close() conn.close()
def register_page(): try: form = RegistrationForm(request.form) if request.method == "POST" and form.validate(): username = form.username.data email = form.email.data password = sha256_crypt.encrypt((str(form.password.data))) dbconfig = read_db_config() conn = MySQLConnection(**dbconfig) cur = conn.cursor() query = ("""SELECT * FROM users WHERE username = %s""") cur.execute(query, (username,)) row = cur.fetchone() if row: flash("That username has already been used. Please try another") return render_template ('register.html', form = form) else: try: url = '/about/' query = ("INSERT INTO users(username, email, password, tracking)" "VALUES (%s,%s,%s,%s)") cur.execute(query,(username, email, password, url)) conn.commit() cur.close() conn.close() gc.collect() session['logged_in'] = True session['username'] = username flash('Success') return redirect(url_for('blog')) except Exception as e: return render_template('error.html', error = 'Error occured. Please refresh') return render_template("register.html", form=form) except Exception as e: return render_template('error.html', error = 'An error occured. Please refresh.')
def delete_information(self, article_id): # At first I wnat to do it for comments delete_query_for_comments = "DELETE FROM comment where article_id = %s" delete_query_for_article = "DELETE FROM article where id = %s" try: conn = MySQLConnection(**self.db) cursor = conn.cursor() cursor.execute(delete_query_for_comments, (article_id,)) cursor.execute(delete_query_for_article, (article_id,)) conn.commit() except Error as error: print(error) finally: cursor.close() conn.close()
def insert_books(books): query = "INSERT INTO books(title,isbn) " \ "VALUES(%s,%s)" try: db_config = read_db_config() conn = MySQLConnection(**db_config) cursor = conn.cursor() cursor.executemany(query, books) conn.commit() except Error as e: print('Error:', e) finally: cursor.close() conn.close()
def addNewUser(self, user, password): query = "INSERT INTO users(name, password) " \ "VALUES(%s, %s)" args = (user, password) try: conn = MySQLConnection(host='localhost', database='mydb', user='******', password='******') cursor = conn.cursor() cursor.execute(query, args) conn.commit() except Error as error: print error finally: cursor.close() conn.close()
def insertManyLocalities(locality_info): query = "INSERT INTO locality(locality_id, name, city_id, data_resource) " "VALUES(%s, %s, %s, %s)" try: db_config = read_db_config() conn = MySQLConnection(**db_config) cursor = conn.cursor() cursor.executemany(query, locality_info) conn.commit() except Error as error: print error finally: cursor.close() conn.close() print "LOCALITY DATA INSERTED!!!"
def clear (): print ('clear') now = datetime.now() delta = timedelta(days=30) date = now - delta date_str = date.strftime("%Y-%m-%d %H:%M:%S") print (date_str) db_config = read_db_config() query = 'DELETE FROM users WHERE date < %s' try: conn = MySQLConnection(**db_config) cursor = conn.cursor() cursor.execute(query, (date_str, ), ) conn.commit() except Error as error: print(error) finally: cursor.close() conn.close()
def update_books (ISBN = '', Title = '', Pages = 0, Publication_Year = 0, Publisher = '', LibraryName = '', Author='',ISBN_KEY = '', condition = ''): coma_counter = [0]*8 if Author == '': AuthorName, AuthorSur = '' , '' else: AuthorName, AuthorSur = div_first_last(Author) attribute_list = ["ISBN = '", "Title = '", "Pages = ", "Publication_Year = ", "Publishers_Name = '", "LibraryName = '", "AuthorName = '", "AuthorSur = '"] i = 0 if Pages == '0': Pages = 0 if Publication_Year == '0': Publication_Year = 0 if ISBN != '': coma_counter[0] +=1 if Title != '': coma_counter[1] +=1 if Pages != 0: coma_counter[2] +=1 if Publication_Year != 0: coma_counter[3] +=1 if Publisher != '': coma_counter[4] +=1 if LibraryName != '': coma_counter[5] +=1 if AuthorSur != '': coma_counter[6] +=1 if AuthorSur != '': coma_counter[7] +=1 j = sum(coma_counter) List = ["UPDATE Books SET "] if ISBN != '' and j!=1: List += attribute_list[0] List += ISBN List += "'," j = j-1 elif ISBN != '' and j==1: List += attribute_list[0] List += ISBN List += "'" if Title != '' and j!=1: List += attribute_list[1] List += Title List += "'," j = j-1 elif Title != '' and j==1: List += attribute_list[1] List += Title List += "'" if Pages != 0 and j!=1: List += attribute_list[2] List += Pages List += "," j = j-1 elif Pages != 0 and j==1: List += attribute_list[2] List += Pages List += "" if Publication_Year != 0 and j!=1: List += attribute_list[3] List += Publication_Year List += "," j = j-1 elif Publication_Year != 0 and j==1: List += attribute_list[3] List += Publication_Year List += "" if Publisher != '' and j!=1: List += attribute_list[4] List += Publisher List += "'," j = j-1 elif Publisher != '' and j==1: List += attribute_list[4] List += Publisher List += "'" if LibraryName != '' and j!=1: List += attribute_list[5] List += Library List += "'" j = j-1 elif LibraryName != '' and j==1: List += attribute_list[5] List += Publisher List += "'" if AuthorName != '' and j!=1: List += attribute_list[6] List += AuthorName List += "'," j = j-1 elif AuthorName != '' and j==1: List += attribute_list[6] List += AuthorName List += "'" if AuthorSur != '' and j==1: List += attribute_list[7] List += AuthorSur List += "'" j = j-1 if ISBN_KEY!= '' or condition!='': List += " Where " if ISBN_KEY != '': List += " ISBN = '" List += ISBN_KEY List += "'" else: List += condition List += ";" query = ''.join(List) try: db_config = read_db_config() conn = MySQLConnection(**db_config) cursor = conn.cursor() cursor.execute(query) if cursor.lastrowid: print('done') else: print('last update id not found') conn.commit() except Error as error: print(error) finally: cursor.close() conn.close()
def update_Staff(StaffID = 0,Name = '',Pay = 0,Surname = '',LibraryName = '', StaffID_KEY = 0, condition = ''): coma_counter = [0]*5 attribute_list = ["StaffID = ", "Name = '", "Pay = ", "Surname = '", "LibraryName = '"] i = 0 if StaffID == '0': StaffID = 0 if StaffID != 0: coma_counter[0] +=1 if Name != '': coma_counter[1] +=1 if Pay != 0: coma_counter[2] +=1 if Surname != '': coma_counter[3] +=1 if LibraryName!='': coma_counter[4] +=1 j = sum(coma_counter) List = ["UPDATE Staff SET "] if StaffID != 0 and j!=1: List += attribute_list[0] List += str(StaffID) List += "," j = j-1 elif StaffID != 0 and j==1: List += attribute_list[0] List += str(StaffID) List += "" if Name != '' and j!=1: List += attribute_list[1] List += Name List += "'," j = j-1 elif Name != '' and j==1: List += attribute_list[1] List += Name List += "'" if Pay != 0 and j!=1: List += attribute_list[2] List += str(Pay) List += "," j = j-1 elif Pay != 0 and j==1: List += attribute_list[2] List += str(Pay) List += "" if Surname != '' and j!=1: List += attribute_list[3] List += Surname List += "'," j = j-1 elif Surname != '' and j==1: List += attribute_list[3] List += Surname List += "'" if LibraryName != '' and j!=1: List += attribute_list[4] List += LibraryName List += "'," j = j-1 elif LibraryName != '' and j==1: List += attribute_list[4] List += LibraryName List += "'" if StaffID_KEY != 0 or condition!='': List += " Where " if StaffID_KEY != '': List += "StaffID = " List += str(StaffID_KEY) List += "" else: List += condition List += ";" query = ''.join(List) try: db_config = read_db_config() conn = MySQLConnection(**db_config) cursor = conn.cursor() cursor.execute(query) if cursor.lastrowid: print('done') else: print('last update id not found') conn.commit() except Error as error: print(error) finally: cursor.close() conn.close()
def update_Authored(Authors_AuthorID = 0, Books_ISBN = '', Authors_AuthorID_KEY = 0, Books_ISBN_KEY = '', condition = ''): coma_counter = [0]*2 attribute_list = ["Authors_AuthorID = ", "Books_ISBN = '"] i = 0 if Authors_AuthorID == '0': Authors_AuthorID = 0 if Authors_AuthorID != 0: coma_counter[0] +=1 if Books_ISBN != '': coma_counter[1] +=1 j = sum(coma_counter) List = ["UPDATE Authored SET "] if Authors_AuthorID != 0 and j!=1: List += attribute_list[0] List += Authors_AuthorID List += "," j = j-1 elif Authors_AuthorID != 0 and j==1: List += attribute_list[0] List += Authors_AuthorID List += "" if Books_ISBN != '' and j!=1: List += attribute_list[1] List += Books_ISBN List += "'," j = j-1 elif Books_ISBN != '' and j==1: List += attribute_list[1] List += Books_ISBN List += "'" if (Authors_AuthorID_KEY != 0 and Books_ISBN_KEY!='') or condition!='': List += " Where " if Authors_AuthorID_KEY != 0 and Books_ISBN_KEY!='': List += "Authors_AuthorID = " List += Authors_AuthorID_KEY List += ", Books_ISBN = '" List += Books_ISBN_KEY List += "'" else: List += condition List += ";" query = ''.join(List) try: db_config = read_db_config() conn = MySQLConnection(**db_config) cursor = conn.cursor() cursor.execute(query) if cursor.lastrowid: print('done') else: print('last update id not found') conn.commit() except Error as error: print(error) finally: cursor.close() conn.close()
def getoutput(id): Results = [] #query to check whether or not the id requested appears in the database query = """ SELECT command_output, command_record, file_names, sshspray FROM victim_machines WHERE victim_id =%s """ data = (id, ) #https://www.mysqltutorial.org/python-mysql-update/ try: #connect to the mysql database connection = MySQLConnection(host='localhost', database='JOB_C2', user='******', password='******', auth_plugin='mysql_native_password') cursor = connection.cursor(buffered=True) cursor.execute(query, data) result = cursor.fetchall() # print(result[0][1]) if result: #get command output, decode it, output it, and set command_output to empty set decoded_output = decode(result[0][0]) decoded_record = decode(result[0][1]) decoded_filenames = decode(result[0][2]) decoded_sshspray = decode(result[0][3]) record_data = json.loads(decoded_record) output_data = json.loads(decoded_output) filename_data = json.loads(decoded_filenames) sshspray_data = json.loads(decoded_sshspray) newfilename = id + ".txt" file = open(newfilename, "a") print("command output written to file named %s.txt" % (id, )) #append all entries for traditional commands for key1, value1 in record_data["commands"].items(): for key2, value2 in output_data["commands"].items(): if key1 == key2: output = Result(key2, value1, value2) Results.append(output) #append all entries for keylogging #append all entries for downloaded files for key1, value1 in record_data["exfiltrate"].items(): for key2, value2 in filename_data[key1].items(): if value2 == "0": output_string = "There was an error in downloading \"" + str( value1) + "\" from the victim machine." if value2 == "1": output_string = str( value1 ) + " was downloaded from victim machine and stored locally at \"" + str( value2) + "\"" output = Result(key1, output_string, "") Results.append(output) #append all entries for uploaded files for key1, value1 in record_data["infiltrate"].items(): for key2, value2 in filename_data[key1].items(): if value2 == "0": output_string = "There was an error in uploading \"" + str( key2) + "\" to the victim machine." if value2 == "1": output_string = "\"" + str( key2 ) + "\" was uploaded to victim machine at " + str( value1) output = Result(key1, output_string, "") Results.append(output) #sort the results to be in chronological order sorted_list = sorted(Results, key=lambda result: result.epoch_id) for entry in sorted_list: entry.epoch_id = datetime.datetime.fromtimestamp( int(entry.epoch_id)) file.write("%s %s %s\n" % (entry.epoch_id, entry.command, entry.output)) #add sshspray output to the end of the file if sshspray_data: for entry in sshspray_data: output_string = "SSH Spraying was conducted and IP address " + entry + " can be accessed using this machine's SSH Key." file.write("%s\n" % (output_string)) file.close() # print(json.dumps(json_data, indent=2)) cleanup_query1 = """ UPDATE victim_machines SET command_output = 'e30=' WHERE victim_id =%s """ cleanup_query2 = """ UPDATE victim_machines SET sshspray = '' WHERE victim_id = %s """ cursor.execute(cleanup_query1, data) cursor.execute(cleanup_query2, data) else: print("id not found in the database, try again") connection.commit() except Error as error: print(error) finally: cursor.close() connection.close() return True
def uploadfile(id, file_path, file_name): #FILE_PATH IS WHERE THE FILE IS LOCALLY, FILE_NAME IS THE REMOTE FILE PATH TO STORE IT #base query to check whether or not the id requested appears in the database query = """ SELECT command, file_names, command_record FROM victim_machines WHERE victim_id =%s """ data = (id, ) #https://www.mysqltutorial.org/python-mysql-update/ try: #connect to the mysql database connection = MySQLConnection(host='localhost', database='JOB_C2', user='******', password='******', auth_plugin='mysql_native_password') if connection.is_connected(): print('Connected to JOB_C2 database') cursor = connection.cursor(buffered=True) cursor.execute(query, data) result = cursor.fetchall() # print(result[0][0]) if result: print("id found in the database, modifying entry...") #grab old command, decode it, add the new command in json, re-encode it decoded_command = decode(result[0][0]) json_data = json.loads(decoded_command) seconds_from_epoch = int(time.time()) new_command = {str(seconds_from_epoch): file_name} json_data["infiltrate"].update(new_command) # print(json.dumps(json_data)) json_string = str(json.dumps(json_data)) encoded_updated = encode(json_string) #update command_record SEPARATELY decoded_command_record = decode(result[0][2]) json_data = json.loads(decoded_command_record) json_data["infiltrate"].update(new_command) json_string_record = str(json.dumps(json_data)) encoded_record = encode(json_string_record) #add the epoch and custom file name to the table for future use decoded_filenames = decode(result[0][1]) filename_data = json.loads(decoded_filenames) new_filename = {str(seconds_from_epoch): {file_path: "0"}} filename_data.update(new_filename) # print(json.dumps(filename_data)) #encode the custom filename to be added to the database json_file = str(json.dumps(filename_data)) filename_updated = encode(json_file) #update the database if the row already exists update_query = """ UPDATE victim_machines SET command=%s WHERE victim_id=%s """ update_data = (encoded_updated, id) update_command_record = """ UPDATE victim_machines SET command_record=%s WHERE victim_id=%s """ update_command_data = (encoded_record, id) command_record_query = """ UPDATE victim_machines SET file_names=%s WHERE victim_id=%s """ update_file_data = (filename_updated, id) cursor.execute(update_query, update_data) cursor.execute(update_command_record, update_command_data) cursor.execute(command_record_query, update_file_data) #catch the case in which the victim_id does not exist in the table and create a new entry else: print("id not found in the database") print("creating new entry in database...") seconds_from_epoch = int(time.time()) empty_command_output = "e30=" default_group_id = 1 formatted_command = "{\"commands\": {}, \"exfiltrate\":{}, \"infiltrate\": {\"" + str( seconds_from_epoch ) + "\": \"" + file_name + "\"}, \"keylogger\": \"0\", \"shell\": {\"ip\": \"0.0.0.0\", \"port\": \"0\"}, \"sshspray\": \"0\"}" formatted_encoded = encode(formatted_command) custom_filename = "{\"" + str( seconds_from_epoch) + "\": {\"" + file_path + "\": \"0\"}}" encoded_custom_filename = encode(custom_filename) create_query = """ INSERT INTO victim_machines (victim_id, group_id, command, command_output, command_record, file_names) VALUES (%s, %s, %s, %s, %s, %s) """ create_data = (id, default_group_id, formatted_encoded, empty_command_output, formatted_encoded, encoded_custom_filename) cursor.execute(create_query, create_data) connection.commit() except Error as error: print(error) finally: cursor.close() connection.close() return True
def main(): connection = MySQLConnection(user='******', password='', database='names', collation='utf8mb4_unicode_ci') cursor = connection.cursor(buffered=True) add_name = u'INSERT INTO stored_names (label, url, class, type) VALUES (%s, %s, %s, %s)' check_for_name = u'SELECT * FROM stored_names WHERE url = %s AND label = %s AND class = %s COLLATE utf8mb4_unicode_ci' KEY_PREDICATES = [ 'http://schema.org/name', 'http://schema.org/alternateName' ] OBJECT_TYPES = [ 'http://schema.org/Person', 'http://schema.org/Organization', 'http://schema.org/Event', 'http://schema.org/Place' ] DATABASE_OBJECT_TYPES = ['personal', 'corporate', 'corporate', 'corporate'] AUTHORIZED_LABEL = 'skos:prefLabel' VARIANT_LABEL = 'skos:altLabel' starting_line = 0 durations = [] try: with open('viaf_index.txt', 'r') as infile: starting_line = int(infile.readline()) except: pass print(starting_line) with codecs.open('viaf-20180605-clusters-rdf.xml', 'r', encoding='utf-8') as readfile: line_counter = 0 start_time = datetime.datetime.now().time() try: for line in readfile: if line_counter >= starting_line: sections = line.split('\t') url = 'http://viaf.org/viaf/' + sections[0] authorized_labels = [] variant_labels = [] record_type = None root = etree.fromstring(sections[1]) discrete_descriptions = root.xpath( "/rdf:RDF/rdf:Description[rdf:type/@rdf:resource='http://www.w3.org/2004/02/skos/core#Concept']", namespaces={ "rdf": "http://www.w3.org/1999/02/22-rdf-syntax-ns#" }) for description in discrete_descriptions: authorized_labels.extend( description.xpath( "./" + AUTHORIZED_LABEL + "/text()", namespaces={ "skos": "http://www.w3.org/2004/02/skos/core#" })) variant_labels.extend( description.xpath( "./" + VARIANT_LABEL + "/text()", namespaces={ "skos": "http://www.w3.org/2004/02/skos/core#" })) authorized_labels = list( map(lambda x: unicode(x).strip(string.whitespace), authorized_labels)) authorized_labels = list( map(lambda x: x[:200] if len(x) > 200 else x, authorized_labels)) authorized_labels = list(set(authorized_labels)) variant_labels = list( map(lambda x: unicode(x).strip(string.whitespace), variant_labels)) variant_labels = list( map(lambda x: x[:200] if len(x) > 200 else x, variant_labels)) variant_labels = list(set(variant_labels)) index = 0 while record_type is None and index < len(OBJECT_TYPES): type_results = root.xpath( "/rdf:RDF/rdf:Description[rdf:type/@rdf:resource='" + OBJECT_TYPES[index] + "']", namespaces={ "rdf": "http://www.w3.org/1999/02/22-rdf-syntax-ns#" }) if len(type_results) > 0: record_type = DATABASE_OBJECT_TYPES[index] index = index + 1 for l in authorized_labels: cursor.execute(add_name, (l, url, 'authorized', record_type)) connection.commit() for m in variant_labels: cursor.execute(add_name, (m, url, 'variant', record_type)) connection.commit() # first_index = line.find('>') # first_component = line[:first_index+1] # # if first_component[:16] == '<http://viaf.org': # second_substring = line[first_index+2:] # second_index = second_substring.find('>') # second_component = second_substring[:second_index+1] # # if second_component in KEY_PREDICATES: # third_component = second_substring[second_index+2:-3] # if '@' in third_component and third_component.rfind('@') > third_component.rfind('"'): # third_component = third_component[1:third_component.rfind('@')-1] # elif second_component == KEY_PREDICATES[1] or (third_component[:1] == '"' and third_component[-1:] == '"'): # third_component = third_component[1:-1] # # if KEY_PREDICATES[0] == second_component: # name_class = 0 # else: # name_class = 1 # # first_component = first_component[1:-1] # second_component = second_component[1:-1] # third_component = third_component.decode('unicode_escape') # # if len(third_component) <= 200 and len(first_component) <= 200: # print(first_component) # print(second_component) # print(third_component) # # cursor.execute(check_for_name,(first_component,third_component,name_class)) # if not cursor.rowcount: # cursor.execute(add_name,(first_component,third_component,name_class)) # connection.commit() line_counter = line_counter + 1 if line_counter % 1000 == 0 and line_counter >= starting_line: print(line_counter) end_time = datetime.datetime.now().time() duration = datetime.datetime.combine( datetime.date.min, end_time) - datetime.datetime.combine( datetime.date.min, start_time) if duration >= timedelta(0): durations.append(duration) print("Run duration: " + str(duration)) average = reduce(lambda x, y: x + y, durations) / len(durations) print("Estimated average: " + str(average)) print("Estimated remining time: " + str(average * (211 - (line_counter / 1000)))) start_time = end_time except (KeyboardInterrupt, errors.DataError, errors.DatabaseError, UnicodeDecodeError) as e: with open('viaf_index.txt', 'w') as outfile: outfile.write(str(line_counter)) traceback.print_exc() # raise TypeError(e) cursor.close() connection.close()
# check the db connection before inserting if not conn_output.is_connected(): conn_output.reconnect() # insert the data try: cur_output.execute(command_insert, (vel, lat, lt, azm, dtm, season, rad)) except Exception, e: logging.error(e, exc_info=True) # check the db connection before committing if not conn_output.is_connected(): conn_output.reconnect() # commit the change try: conn_output.commit() except Exception, e: logging.error(e, exc_info=True) # close the connections conn_input.close() conn_output.close() return def worker(imf_table, output_table, stm, etm, ftype="fitacf",
def add_post(msg_received, header): dbconfig = read_db_config() conn = MySQLConnection(**dbconfig) cursor = conn.cursor() d = collections.OrderedDict() user_id = tokens.getID(header) post_details = msg_received["post_details"] timestamp = msg_received["timestamp"] post_images = msg_received["images"] audio = msg_received["audio"] video = msg_received["video"] post_id = runner() images = [] for i in post_images: images.append(i) if user_id == "Error expired token" or user_id == "Error invalid token": return json.dumps({'Error': 'login in again'}) else: cursor.execute("SELECT * FROM `user` WHERE user_id='" + str(user_id) + "';") name = cursor.fetchall() if len(name) == 1: for info in name: userName = info[2] fullName = info[1] user_post = { "post_id": post_id, "post_details": post_details, "posted_by": user_id, "userName": userName, "fullName": fullName, "timestamp": timestamp, "images": images, "post_likes": [], "audio": audio, "video": video } collection.insert_one(user_post) cursor.execute( "INSERT INTO `posts` (`id`, `user_id`, `post_id`, `date_created`,`uniqueID`) VALUES (NULL, '" + str(user_id) + "', '" + str(post_id) + "', CURRENT_TIMESTAMP,'null');") conn.commit() conn.close() cursor.close() result = collection.find({"post_id": post_id}) data = [] for i in result: d['post_id'] = i['post_id'] d['post_details'] = i['post_details'] d['userName'] = i['userName'] d['timestamp'] = i['timestamp'] d['images'] = i['images'] d['audio'] = i['audio'] d['video'] = i['video'] d['post_likes'] = i['post_likes'] data.append(d) return json.dumps(data)
class ANALYZER(object): # pylint: disable=too-many-instance-attributes """ Analyzer object """ def __init__(self): LOGGER.debug("Running Analyzer") self.previous_dal = 0 self.last_update = datetime.now() self.database = None self.cursor = None if ARGS.mac: self.macfilter = ARGS.mac else: self.macfilter = False self.pcap_time = ARGS.pcaptime self.min_update = ARGS.minupdate self.serial_num = ARGS.serialnum # Initialize filename, need to be after checking for captime as it will override it self.get_file() # Set up our database link self.config_database() def config_database(self): """ Configure our database connection using configuration file """ mysql_user = CONFIG.get('Database', 'username') mysql_pass = CONFIG.get('Database', 'password') mysql_host = CONFIG.get('Database', 'host') mysql_db = CONFIG.get('Database', 'db') self.database = MySQLConnection(host=mysql_host, user=mysql_user, passwd=mysql_pass, db=mysql_db) self.cursor = self.database.cursor() def get_file(self): """ returns and verifies paht for pcap file name (or stdin if one is not supplied) """ if ARGS.file: self.filename = ARGS.file if not os.path.isfile(self.filename): LOGGER.critical("Cannot find file %s", self.filename) sys.exit(1) else: LOGGER.debug("Using file %s", self.filename) # Use pcap capture time when replaying from file self.pcap_time = True else: self.filename = sys.stdin.buffer LOGGER.debug("Using stdin") def check_mac(self, packet): """ We should never have anything without our mac here, this is here just for safety as everything would break otherwise """ if self.macfilter: src_mac = packet.getlayer(Dot11).addr2 print(src_mac) return src_mac == self.macfilter else: return True def check_timediff(self, now): """ Returns true/false if self.min_update passed or not. Will set last_update if needed """ timediff = now - self.last_update if timediff.total_seconds() > self.min_update: self.last_update = now return True else: return False def update_database(self, clpm, dal, packet): """Update DB only if: 1. Current flow (cl/min) is > 0, or: 2. Previous dal measurement is smaller than current one, or: 3. Last report was made more than self.min_update seconds ago""" now = datetime.now() if clpm > 0 or self.previous_dal < dal or self.check_timediff(now): if self.pcap_time: pkt_time = "FROM_UNIXTIME({})".format(packet.time) else: pkt_time = "NULL" sql = "INSERT IGNORE INTO water_raw_data VALUES (NULL, {}, {}, {})".format( pkt_time, dal, clpm) LOGGER.debug(sql) self.cursor.execute(sql) self.previous_dal = dal self.last_update = now self.database.commit() return True else: return False def analyze_line(self, packet): """ Analyzes packets to extract water usage """ payload = get_payload(packet) msg = "" # SANITY if not self.check_mac(packet) or payload == "": LOGGER.debug("Mac address mismatch") return serial, dal, clpm = get_meter_values(payload) if serial != self.serial_num: LOGGER.debug("Found meter data but wrong Meter ID: %s", serial) return msg += "{}, {}".format(dal, clpm) if self.update_database(clpm, dal, packet): msg += ", Updated" else: msg += ", Skipping" LOGGER.info(msg)
def toggleshell( id, ip, port ): #base query to check whether or not the id requested appears in the database query = """ SELECT command, command_output, command_record FROM victim_machines WHERE victim_id =%s """ data = (id,) #https://www.mysqltutorial.org/python-mysql-update/ try: #connect to the mysql database connection = MySQLConnection(host='localhost',database='JOB_C2',user='******',password='******',auth_plugin='mysql_native_password') if connection.is_connected(): print('Connected to JOB_C2 database') cursor = connection.cursor(buffered=True) cursor.execute(query,data) result = cursor.fetchall() # print(result[0][0]) if result: print("id found in the database, modifying entry...") #grab old command, decode it, add the new command in json, re-encode it decoded_command = decode(result[0][0]) json_data = json.loads(decoded_command) json_data["shell"]["ip"] = ip json_data["shell"]["port"] = port # print(json.dumps(json_data)) json_string = str(json.dumps(json_data)) encoded_updated = encode(json_string) decoded_command_record = decode(result[0][2]) json_data = json.loads(decoded_command_record) json_data["shell"]["ip"] = ip json_data["shell"]["port"] = port json_string = str(json.dumps(json_data)) encoded_record = encode(json_string) #update the database if the row already exists update_query = """ UPDATE victim_machines SET command=%s WHERE victim_id=%s """ update_data = (encoded_updated, id) command_record_query = """ UPDATE victim_machines SET command_record=%s WHERE victim_id=%s """ command_record_data = (encoded_record, id) cursor.execute(update_query, update_data) cursor.execute(command_record_query, command_record_data) #catch the case in which the victim_id does not exist in the table and create a new entry else: print("id not found in the database") print("creating new entry in database...") seconds_from_epoch = int(time.time()) empty_json = "e30=" default_group_id = 1 formatted_command = "{\"commands\": {}, \"exfiltrate\": {}, \"infiltrate\": {}, \"keylogger\": \"0\", \"shell\": {\"ip\": \"" + ip + "\", \"port\": \"" + port + "\"}, \"sshspray\": \"0\"}" formatted_encoded = encode(formatted_command) create_query = """ INSERT INTO victim_machines (victim_id, group_id, command, command_output, command_record, file_names) VALUES (%s, %s, %s, %s, %s, %s) """ create_data = (id, default_group_id, formatted_encoded,empty_json, formatted_encoded, empty_json) cursor.execute(create_query, create_data) connection.commit() except Error as error: print(error) finally: cursor.close() connection.close() return True
def main(): # get configs for db db_config = read_db_config() url = 'http://muz-tv.ru/hit-online/' try: mydb = MySQLConnection(**db_config) cursor = mydb.cursor() print("Successfully connected to %s" % db_config['host']) check_db_table(cursor) p_number = 1 while not url.endswith("=31"): html = get_html(url) divs = html.find('div', class_='b-music-list').find_all( 'div', class_='b-block b-clearbox x-hit-item') for div in divs: h3 = div.find_all('h3') # link to detail page link = "muz-tv.ru" + (h3[0].find('a').get('href')) like = div.find( 'div', class_='b-but_text').find('span').text.strip('()') # get singer name and song info = {h3[0].text: h3[1].text.strip()} # insert into db for singer, song in info.items(): try: sql = "INSERT INTO top(singer, song, link, likes) VALUES(%s, %s, %s, %s);" val = singer, song, link, like cursor.execute(sql, val) mydb.commit() print(singer, song, link, like, "Successfully added to table") # exception for duplicates except: print(singer, song, "already exists in db. Moving to the next one") break # move to next page checking last div on page if div == divs[-1]: p_number += 1 url = f"http://muz-tv.ru/hit-online/?page={p_number}" print(f'Last item on a page. Going to the next one: {url}') print("\nFinished") except Error as e: print(e) finally: cursor.close() mydb.close()
def create_table(): """ create table in the MySQL database""" commands = (""" CREATE TABLE IF NOT EXISTS url ( url_id INT NOT NULL AUTO_INCREMENT, url VARCHAR(255) NOT NULL, title VARCHAR(255) NOT NULL, PRIMARY KEY (url_id) ); """, """ CREATE TABLE IF NOT EXISTS word ( wurl_id INT NOT NULL, word VARCHAR(255) NOT NULL, count INT NOT NULL, PRIMARY KEY (wurl_id, word), CONSTRAINT fk_word FOREIGN KEY (wurl_id) REFERENCES url (url_id) ON DELETE CASCADE ON UPDATE CASCADE ); """, """ CREATE TABLE IF NOT EXISTS refer_url ( rurl_id INT NOT NULL, ref_url VARCHAR(255) NOT NULL, PRIMARY KEY (rurl_id, ref_url), CONSTRAINT fk_refer_link FOREIGN KEY (rurl_id) REFERENCES url (url_id) ON DELETE CASCADE ON UPDATE CASCADE ); """, """ CREATE TABLE IF NOT EXISTS request ( id INT NOT NULL AUTO_INCREMENT, ip_addr VARCHAR(23) NOT NULL, word VARCHAR(255) NOT NULL, uri VARCHAR(255) NOT NULL, request_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id) ); """) 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() for command in commands: cursor.execute(command) cursor.close() # commit the changes conn.commit() print('Create table successfully.') except Error as e: print('Error:', e) finally: conn.close()
def count_class_word(start, end): try: dbconfig = read_db_config() conn = MySQLConnection(**dbconfig) cursor = conn.cursor(buffered=True) cursor.execute("TRUNCATE meta_class") # classes = ['joy', 'fear', 'anger', 'sadness', 'disgust', 'shame'] classes = [1, 2, 3, 4, 5, 6] i = 1 class_count = [0, 0, 0, 0, 0, 0, 0] spinner = PieSpinner('\nCounting Class Word :: ') for target in classes: text_list = [] # cursor.execute("SELECT class,SIT FROM data3 WHERE class=%(mytarget)s and id < 7001", {'mytarget': target}) if start == 1: cursor.execute( "SELECT class, sentence FROM data3 WHERE class=%(target)s and id > %(id_target)s", { 'target': target, 'id_target': end }) elif end == 7433: cursor.execute( "SELECT class, sentence FROM data3 WHERE class=%(target)s and id < %(id_target)s", { 'target': target, 'id_target': start }) else: cursor.execute("SELECT class, sentence FROM data3 WHERE class=%(target)s and (id < %(id_start)s or id > %(id_end)s)", {'target': target,\ 'id_start': start, 'id_end': end}) result = cursor.fetchall() for res in result: words = res[1].split(' ') for word in words: if word not in text_list and len(word) > 0: # print(word) text_list.append(word) spinner.next() class_count[i] = len(text_list) cursor.execute( "INSERT INTO meta_class values(%(id)s, %(class)s, %(word)s)", { 'id': i, 'class': classes[i - 1], 'word': len(text_list) }) i += 1 spinner.finish() # print(class_count) # break except Error as e: print(e) finally: conn.commit() cursor.close() conn.close() return 1 # if __name__ == '__main__': # process_words()
dbcursor.execute('SELECT count(*) FROM ' + TABLE + ' WHERE number_dub = 0 AND (mobile0 IS NOT NULL ' 'OR mobile1 IS NOT NULL OR mobile2 IS NOT NULL);') rows = dbcursor.fetchall() count_snils = rows[0][0] while count_snils > 0: start_snils -= 1 checksum_snils = checksum(start_snils) cached_snils = [] for i in range(0, 99): if i != checksum_snils: full_snils = start_snils * 100 + i dbcursor = dbconn.cursor() dbcursor.execute( 'SELECT `number` FROM clients WHERE `number` = %s', (full_snils, )) rows = dbcursor.fetchall() if len(rows) == 0: cached_snils.append((full_snils, )) count_snils -= 1 dbcursor = dbconn.cursor() dbcursor.executemany( 'UPDATE ' + TABLE + ' SET number_dub = %s WHERE number_dub = 0 AND (mobile0 IS NOT NULL ' 'OR mobile1 IS NOT NULL OR mobile2 IS NOT NULL) LIMIT 1;', cached_snils) dbconn.commit() print(datetime.datetime.now().strftime("%H:%M:%S"), start_snils) dbconn.close()
from mysql.connector import MySQLConnection, Error from python_mysql_dbconfig import read_db_config #from IDP_cheats import togglePulse import time #connection to the database db_config = read_db_config() cnnI = MySQLConnection(**db_config) crrI = cnnI.cursor() DN = "irut" #random database name query = "CREATE DATABASE " + DN crrI.execute(query) cnnI.commit() crrI.close() cnnI.close() #togglePulse() time.sleep(30) #connection to the database db_config = read_db_config(filename='config2.ini') cnnI = MySQLConnection(**db_config) crrI = cnnI.cursor() #main results table query = "CREATE TABLE `arun` (`idArun` int(11) NOT NULL AUTO_INCREMENT,`project` varchar(45) DEFAULT NULL,"\ "`Part` varchar(45) DEFAULT NULL,`Iteration_count` int(11) DEFAULT NULL,`CADfile` varchar(45) DEFAULT NULL,"\ "`BraidFile` varchar(45) DEFAULT NULL,`MeshFile` varchar(45) DEFAULT NULL,`FeFile` varchar(45) DEFAULT NULL,"\ "`span_ele_size` decimal(8,3) DEFAULT NULL,`xs_seed` int(11) DEFAULT NULL,`root_perimeter` decimal(8,3) DEFAULT NULL,"\ "`Pher` float DEFAULT NULL,`simulation_time` float DEFAULT NULL,`date` date DEFAULT NULL,PRIMARY KEY (`idArun`)"\
def main(listaNaloga): stringListeNaloga = "" for nalog in listaNaloga: stringListeNaloga += str(nalog) + "," stringListeNaloga = stringListeNaloga[0:len(stringListeNaloga)-1] print(stringListeNaloga) try: #Povezivanje sa bazom vezaSabazom = MySQLConnection(host='localhost', port="3333", database='emd', user='******', password='******') #Cursor objekti za sljanje upita bazi cursor = vezaSabazom.cursor(buffered=True) cursor2 = vezaSabazom.cursor(buffered=True) except Error as error: print(error) try: #While petlja prolazi kroz svaki nalog cursor.execute('SELECT brojNaloga, generiran FROM radniNalog WHERE brojNaloga IN(' + stringListeNaloga + ');') while True: #Fetchamo brojeve naloga sve dok ne prodemo sve naloge, dodemo do kraja #fetchone() vraca array [broj_naloga, generiran] nalog = cursor.fetchone() if nalog == None: break #Provjera ako je nalog vec generiran if nalog[1] == True: continue #Ucitavanje template objekta i ucitavanje sheet-a nalogTemplate = load_workbook('Primjeri naloga/radni nalog template.xlsx') #Work book sheet = nalogTemplate.get_sheet_by_name("List1") #Zapisivanje broja naloga #J1 predstavlja indexe čelije sheet['J1'] = nalog[0] #Dohvacanje svih nacrta(proizvoda) koji pripadaju trenutnom radnom nalogu cursor2.execute('SELECT nacrt FROM nalogPozicija WHERE nalog = "' + str(nalog[0])+'";') nacrti = cursor2.fetchall() #Za svaki prethodno dohvanceni nacrt, dohvati iz baze naziv proizvoda, materijal, cnc... for i in range(len(nacrti)): cursor2.execute('SELECT naziv FROM pozicija WHERE nacrt= "' + str(nacrti[i][0])+'";') naziv = cursor2.fetchall() #Zapisivanje u excel(template) nacrta i naziva sheet['C'+str(11+i)] = nacrti[i][0] sheet['B'+str(11+i)] = naziv[0][0] #Dohvacanje matetrijala iz baze i pisanje u nalog cursor2.execute('SELECT idMaterijal FROM pozicija WHERE nacrt= "' + str(nacrti[i][0])+'";') materijal = cursor2.fetchall() if materijal: sheet['D'+str(11+i)] = materijal[0][0] sheet['B5'] = materijal[0][0] #Pisanje navoja #vanjski cursor2.execute('SELECT alat FROM alatPozicija WHERE nacrt= "' + str(nacrti[i][0])+'" AND mjestoNavoja = "vanjski";') alati = cursor2.fetchall() #Ako postoji if len(alati) > 0: sheet['H' + str(11+i)] = alati[0][0] #unutranji cursor2.execute('SELECT alat FROM alatPozicija WHERE nacrt= "' + str(nacrti[i][0])+'" AND mjestoNavoja = "unutarnji";') alati = cursor2.fetchall() #Ako postoji if len(alati) > 0: sheet['I' + str(11+i)] = alati[0][0] #Pisanje dimenzije i duljine u nalog cursor2.execute('SELECT dimenzija, duljina FROM pozicija WHERE nacrt = "' + str(nacrti[i][0])+'";') dimenzijaDuljina = cursor2.fetchall() sheet['E' + str(i + 11)] = dimenzijaDuljina[0][0] sheet['F' + str(i + 11)] = dimenzijaDuljina[0][1] #Pisanje broja komada za odredeni nacrt u nalog #Trenutno dok je nacrt jedninstven u tablici pozicijaNarudba #Postoji samo jedan redak sa istim nacrtom, nepostoji npr. (nacrtA, narudbza1) # (nacrtA, narudzba2) cursor2.execute('SELECT komada FROM pozicijaNarudzba WHERE nacrt = "' + str(nacrti[i][0])+'";') komada = cursor2.fetchall() print(komada) if len(komada): sheet['G' + str(i + 11)] = komada[0][0] #Dohvacanje cnc tehnologija i pisanje isith u nalog cursor2.execute('SELECT cnc FROM tehnologijaPozicija WHERE nacrt= "' + str(nacrti[i][0])+'";') cnc = cursor2.fetchall() cnc_lista = [] if cnc: cnc_lista.append(cnc[0][0]) if cnc: if cnc[0][0] == 'nema': cnc_lista[0] = '' if len(cnc) > 1 and cnc[1][0] == 'nema': cnc_lista[1]= '' if len(cnc) > 1: cnc_lista.append(cnc[1][0]) sheet['J' + str(11+i)] = cnc_lista[0] +'+' + cnc_lista[1] sheet['B' + str(6+i)] = cnc_lista[0] +'+' + cnc_lista[1] elif cnc_lista: sheet['J' + str(11+i)] = cnc_lista[0] #Zapisivanje rednog broja cursor2.execute('SELECT redniBr FROM pozicija WHERE nacrt= "' + str(nacrti[i][0])+'";') redniBr = cursor2.fetchone() if redniBr: sheet['A' + str(11+i)] = redniBr[0] #Zapisivanje roka u nalog #Nalog br. 176, 166 imaju zapisan krivi datum cursor2.execute('SELECT rok FROM narudzba JOIN pozicijaNarudzba USING(narudzbenica) WHERE nacrt = "' + str(nacrti[i][0])+'";') rok = cursor2.fetchone() #Zbog nekog razloga jedan redak iz prethodnog upita je None #To je za randi nalog 180. Taj radni nalog nema nacrt if rok != None: sheet['K' + str(11+i)] = rok[0] #Spremi kao novi file nalogTemplate.save('Radni nalog ' + str(nalog[0]) + '.xlsx') #Označujemo trenutni nalog kao generiran cursor2.execute('UPDATE radniNalog SET generiran = "1" WHERE brojNaloga = "' + str(nalog[0]) + '"') vezaSabazom.commit() except (Error, IndexError) as error: print(error, nalog) return False cursor.close() vezaSabazom.close() return True
class Connector: # List all class variables here, so they are in one spot. conn = None cursor = None # The connection cursor. config_file = None # The file with MySQL credentials. section = None # The section to read from the config file. suffix = None def __init__(self, config='config.ini', section='mysql'): self.config_file = config self.section = section # Try connecting. self.__connect() def __del__(self): # Close the connection. if self.conn is not None and self.conn.is_connected(): print('Connection closed') self.conn.close() def __read_db_config(self): ''' Read the database config file and return a dictionary of the read values. ''' # Read the config file. parser = ConfigParser() parser.read(self.config_file) # Read the desired section. db = {} if parser.has_section(self.section): items = parser.items(self.section) for item in items: db[item[0]] = item[1] else: raise Exception( f'{self.section} not found in the file {self.config_file}') return db def __connect(self): ''' Try connecting to the MySQL database with credentials given in self.config_file. ''' db_config = self.__read_db_config() try: print('Connecting to database ...') self.conn = MySQLConnection(**db_config) if self.conn.is_connected(): print('Connection established') self.cursor = self.conn.cursor() else: print('Connection failed') except Error as error: print(error) def select_column(self, field, table): data = [] ''' Select data from a column in a table ''' try: cmd = f"SELECT {field} FROM {table} WHERE {field} <> '';" self.cursor.execute(cmd) for item in self.cursor: data.append(item[0]) return data except Error as error: print(error) return data def select_other_responses(self, table, fields): ''' Get a dictionary of fields (keys) and the responses typed in for an "Other" response (value) ''' d = {} data = None try: for field in fields: execute_cmd = f"SELECT {field} FROM {table} WHERE {field} like '%other%'" self.cursor.execute(execute_cmd) data = self.cursor.fetchall() d[field] = [] data_lst = [j[0].split(' ') for j in data] for item in data_lst: for i in range(1, len(item)): if item[i - 1] == 'other': d[field].append(' '.join(item[i:])) break except Error as e: print(e) return d def add_suffix(self, suffix): if suffix is None: return if self.suffix is None: self.suffix = suffix else: self.suffix += " " + suffix def set_query(self, query): # Add the suffix, if there is one. if self.suffix is not None: if 'WHERE' in query: query += ' AND ' + self.suffix else: query += ' WHERE ' + self.suffix self.cursor.execute(query) def select_count(self, table, field, field_val): ''' Get count of number of rows in specified MySQL table where field = field_val ''' data = None try: execute_cmd = f"SELECT COUNT(*) FROM {table} WHERE {field} REGEXP '(^|.* ){field_val}($| .*)'" self.set_query(execute_cmd) data = self.cursor.fetchall()[0][0] except Error as e: print(e) return data def select_num_rows(self, table): ''' Return the number of entries in the database. ''' data = None try: self.set_query(f"SELECT COUNT(*) FROM {table}") data = self.cursor.fetchall() except Error as e: print(e) return data def select_all_data(self, table): ''' Get all data from the specified MySQL table. table (string): the name of the table to read data from. ''' data = None try: self.set_query(f'SELECT * FROM {table}') data = self.cursor.fetchall() except Error as e: print(e) return data def insert_row(self, table, cols, args): ''' Insert a row into the MySQL specified table. table (string): the name of the table insert data into. cols (tuple): the names of the columns to insert data into. args (tuple): the data to insert; this must match the table's fields. ''' # Remove any quotation marks around the column names. strcols = str(cols).replace("'", '').replace('"', '') query = f'INSERT INTO {table} {strcols} VALUES {str(args)}' try: self.cursor.execute(query, ()) self.conn.commit() except Error as e: print(e)
class MainWindowSlots(Ui_Form): # Определяем функции, которые будем вызывать в слотах def setupUi(self, form): Ui_Form.setupUi(self,form) dbconfig = read_config(filename='realty.ini', section='mysql') self.dbconn = MySQLConnection(**dbconfig) # Открываем БД из конфиг-файла self.first_setup_tableWidget() return def click_cbHTML(self): self.setup_tableWidget() return def first_setup_tableWidget(self): self.tableWidget.setColumnCount(0) self.tableWidget.setRowCount(0) # Кол-во строк из таблицы sql = 'SELECT phone, tip, about, DATE_FORMAT(edit_date,"%d.%m %H:%i") FROM contacts' if lenl(self.leFilter.text()) > 0: sql += ' WHERE phone LIKE "%' + str(l(self.leFilter.text())) + '%"' self.dbconn.connect() read_cursor = self.dbconn.cursor() read_cursor.execute(sql) rows = read_cursor.fetchall() self.tableWidget.setColumnCount(4) # Устанавливаем кол-во колонок self.tableWidget.setRowCount(len(rows)) # Кол-во строк из таблицы for i, row in enumerate(rows): for j, cell in enumerate(row): label = QLabel() if j == 0: label.setText(fine_phone(str(cell))) else: label.setText(str(cell)) label.setAlignment(Qt.AlignCenter) self.tableWidget.setCellWidget(i, j, label) # self.tableWidget.setItem(i, j, QTableWidgetItem(str(cell))) # Устанавливаем заголовки таблицы self.tableWidget.setHorizontalHeaderLabels(["телефон", "тип", "описание","дата"]) # Устанавливаем выравнивание на заголовки self.tableWidget.horizontalHeaderItem(0).setTextAlignment(Qt.AlignCenter) self.tableWidget.horizontalHeaderItem(1).setTextAlignment(Qt.AlignCenter) self.tableWidget.horizontalHeaderItem(2).setTextAlignment(Qt.AlignCenter) # делаем ресайз колонок по содержимому self.tableWidget.resizeColumnsToContents() return def setup_tableWidget(self): self.first_setup_tableWidget() self.click_tableWidget() return def click_tableWidget(self, index=None): if index == None: index = self.tableWidget.model().index(0, 0) if index.row() < 0: return None self.leAbout.setText(self.tableWidget.cellWidget(index.row(),2).text()) return None def click_pbFilter(self): # Фильтровать a = self.leFilter.text().strip() # if lenl(a) > 0: # if str(l(a))[0] == '8': # a = '7' + str(lenl(a))[1:] # elif str(l(a))[0] != '7': # a = '7' + str(l(a)) # self.leFilter.setText(a) self.setup_tableWidget() def click_pbAccept(self): # Обновить/добавить a = self.leFilter.text().strip() if lenl(a) < 10 or lenl(a) > 11: return None elif lenl(a) == 10: a = l('7' + self.leFilter.text()) else: if str(l(a))[0] == '8': a = '7' + str(l(a))[1:] elif str(l(a))[0] == '7': q = 0 else: return None self.dbconn.connect() dbcursor = self.dbconn.cursor() dbcursor.execute('SELECT count(*) FROM contacts WHERE phone = %s', (a,)) rows = dbcursor.fetchall() if rows[0][0] == 0: dbcursor.execute('INSERT contacts (phone, about, edit_date) VALUES(%s,%s,%s)',(a, self.leAbout.text().strip(), datetime.now())) else: dbcursor.execute('UPDATE contacts SET about = %s, edit_date = %s WHERE phone = %s',(self.leAbout.text().strip(), datetime.now(), a)) self.dbconn.commit() self.leFilter.setText('') self.setup_tableWidget() return
from mysql.connector import MySQLConnection from binascii import hexlify import sys import os if sys.argv[1] == 'game' \ and sys.argv[2] == 'add': conn = MySQLConnection( user='******', password='******', host='localhost', database='gamehub', ) pubkey = hexlify(os.urandom(32)).decode('utf8') cursor = conn.cursor() cursor.execute(('insert into game (description, `publicKey`) ' 'values (%s, %s)'), ( sys.argv[3], pubkey, )) game_id = cursor.lastrowid conn.commit() print('inserted id: {}'.format(game_id))
def post_image(msg_received, header): user_id = tokens.getID(header) dbconfig = read_db_config() conn = MySQLConnection(**dbconfig) cursor = conn.cursor() if user_id == "Error expired token" or user_id == "Error invalid token": conn.close() cursor.close() return json.dumps({'Error': 'login in again'}) else: cursor.execute("SELECT * FROM `user` WHERE user_id='" + str(user_id) + "';") row = cursor.fetchall() if len(row) == 1: for record in row: uniqueID = str(generate_check.wall_post_gen()) S3_BUCKET = "firefansapp" file_type = msg_received["file_type"] fileName = "post_photos/" + uniqueID + "_" + str(user_id) my_config = Config(region_name='us-west-1', retries={ 'max_attempts': 10, 'mode': 'standard' }) s3 = boto3.client( 's3', aws_access_key_id=key_id['aws_access_key_id'], aws_secret_access_key=key_id['aws_secret_access_key'], config=my_config) presigned_post = s3.generate_presigned_post(Bucket=S3_BUCKET, Key=fileName, Fields={ "acl": "public-read", "Content-Type": file_type }, Conditions=[{ "acl": "public-read" }, { "Content-Type": file_type }], ExpiresIn=900) #cursor.execute("UPDATE `post_images` SET `image_url` = '" + str('https://%s.s3.amazonaws.com/%s' % (S3_BUCKET,fileName)) + "' AND SET `key_name` ='"+uniqueID+"' WHERE user_id=" + str(user_id) + ";") cursor.execute( "INSERT INTO `post_images` (`id`, `user_id`, `image_url`, `date_created`, `key_name`) VALUES (NULL, '" + str(user_id) + "', '" + str('https://%s.s3.amazonaws.com/%s' % (S3_BUCKET, fileName)) + "', CURRENT_TIMESTAMP, '" + str(uniqueID) + "_" + str(user_id) + "');") conn.commit() conn.close() cursor.close() return json.dumps({ 'data': presigned_post, 'post_image_url': 'https://%s.s3.amazonaws.com/%s' % (S3_BUCKET, fileName) })
def update_Permanent(Staff_StaffID = 0, Hiring_Date = '', Staff_StaffID_KEY = 0, condition = ''): coma_counter = [0]*2 attribute_list = ["Staff_StaffID = ", "Hiring_Date = '"] i = 0 if Staff_StaffID == '0': Staff_StaffID = 0 if Staff_StaffID_KEY == '0': Staff_StaffID_KEY = 0 if Staff_StaffID != 0: coma_counter[0] +=1 if Hiring_Date != '': coma_counter[1] +=1 j = sum(coma_counter) List = ["UPDATE Permanent SET "] if Staff_StaffID != 0 and j!=1: List += attribute_list[0] List += str(Staff_StaffID) List += "," j = j-1 elif Staff_StaffID != 0 and j==1: List += attribute_list[0] List += str(Staff_StaffID) List += "" if Hiring_Date != '' and j!=1: List += attribute_list[1] List += Hiring_Date List += "'," j = j-1 elif Hiring_Date != '' and j==1: List += attribute_list[1] List += Hiring_Date List += "'" if Staff_StaffID_KEY or condition!='': List += " Where " if Staff_StaffID_KEY != 0: List += "Staff_StaffID = " List += str(Staff_StaffID_KEY) List += "" else: List += condition List += ";" query = ''.join(List) try: db_config = read_db_config() conn = MySQLConnection(**db_config) cursor = conn.cursor() cursor.execute(query) if cursor.lastrowid: print('done') else: print('last update id not found') conn.commit() except Error as error: print(error) finally: cursor.close() conn.close()
def cover_photo(msg_received, header): user_id = tokens.getID(header) dbconfig = read_db_config() conn = MySQLConnection(**dbconfig) cursor = conn.cursor() if user_id == "Error expired token" or user_id == "Error invalid token": conn.close() cursor.close() return json.dumps({'Error': 'login in again'}) else: cursor.execute("SELECT * FROM `profile_photo` WHERE user_id='" + str(user_id) + "';") row = cursor.fetchall() if len(row) == 1: for record in row: uniqueID = record[3] S3_BUCKET = "firefansapp" file_type = msg_received["file_type"] fileName = "cover_photos/" + uniqueID my_config = Config(region_name='us-west-1', retries={ 'max_attempts': 10, 'mode': 'standard' }) s3 = boto3.client( 's3', aws_access_key_id=key_id['aws_access_key_id'], aws_secret_access_key=key_id['aws_secret_access_key'], config=my_config) presigned_post = s3.generate_presigned_post(Bucket=S3_BUCKET, Key=fileName, Fields={ "acl": "public-read", "Content-Type": file_type }, Conditions=[{ "acl": "public-read" }, { "Content-Type": file_type }], ExpiresIn=900) cursor.execute("UPDATE `cover_photo` SET `cover_pic` = '" + str('https://%s.s3.amazonaws.com/%s' % (S3_BUCKET, fileName)) + "' WHERE user_id=" + str(user_id) + ";") conn.commit() conn.close() cursor.close() return json.dumps({ 'data': presigned_post, 'image_url': 'https://%s.s3.amazonaws.com/%s' % (S3_BUCKET, fileName) })
def update_Belongs(Books_ISBN = '', Category_Name = '', Books_ISBN_KEY = '', Category_Name_KEY = '', condition = ''): coma_counter = [0]*2 attribute_list = ["Books_ISBN = '", "Category_Name = '"] i = 0 if Books_ISBN != '': coma_counter[0] +=1 if Category_Name != '': coma_counter[1] +=1 j = sum(coma_counter) List = ["UPDATE Belongs SET "] if Books_ISBN != '' and j!=1: List += attribute_list[0] List += Books_ISBN List += "'," j = j-1 elif Books_ISBN != '' and j==1: List += attribute_list[0] List += Books_ISBN List += "'" if Category_Name != '' and j!=1: List += attribute_list[1] List += Category_Name List += "'," j = j-1 elif Category_Name != '' and j==1: List += attribute_list[1] List += Category_Name List += "'" if (Books_ISBN_KEY != '' and Category_Name_KEY!='') or condition!='': List += " Where " if Books_ISBN_KEY != '' and Category_Name_KEY!='': List += "Books_ISBN = '" List += Books_ISBN_KEY List += "', Category_Name = '" List += Books_ISBN_KEY List += "'" else: List += condition List += ";" query = ''.join(List) try: db_config = read_db_config() conn = MySQLConnection(**db_config) cursor = conn.cursor() cursor.execute(query) if cursor.lastrowid: print('done') else: print('last update id not found') conn.commit() except Error as error: print(error) finally: cursor.close() conn.close()
class Database: """Database Related Operation Methods -------- 1.insert(data) 2.read_db_config(filename,section) Objcet Creation -------------- db=Database(table_name='crawler',fields=fields) Parameters: ---------- table_name : name of table fields : dictionary of field_name and their_type filename : configuration filename section : database section """ def __init__(self,table_name=None,fields=None,filename='config.ini',section='mysql'): """ Initialize the connection with the database and make the required table. :param table_name: name of the table to be inserted :param fields: a dictionary of field_name and their_type to be inserted in the table table_name :optional param filename: name of the configuration file :optional param section: section of database configuration """ self.table_name = table_name db_config = self.read_db_config(filename,section) try : self.conn = MySQLConnection(**db_config) except Error as error: print(error) return self.cursor = self.conn.cursor() if table_name is not None and fields is not None: self.cursor.execute(f"show tables like '{self.table_name}'") output = self.cursor.fetchone() #if no such table exits create one if output is None: fields_list = [f'{x} {fields[x]}' for x in fields.keys()] self.cursor.execute(f"CREATE TABLE {self.table_name}({','.join(fields_list)})") print('Table successfully created') self.conn.commit() def insert(self,data): """ This function is used to insert data into the table. :param data: dictonary contain the pair wise data {field_name:value} """ values = [data[x] for x in data.keys()] s = ('%s,'*len(data))[:-1] insert_statement = f"INSERT INTO {self.table_name} values({s})" self.cursor.execute(insert_statement,values) self.conn.commit() def read_db_config(self,filename,section): """ Read database configuration from the file and return dictionary object :param filename: name of the configuration file :param section: section of database configuration :return: a dictionary of database parameters """ parser = ConfigParser() parser.read(filename) db = {} if parser.has_section(section): items = parser.items(section) for item in items: db[item[0]] = item[1] else: raise Exception(f'{section} not found in the {filename} file') return db def __del__(self): self.cursor.close() self.conn.close()
def update_Copy(Number = 0, Books_ISBN = '', Position = '', Number_KEY = 0, Books_ISBN_KEY = '', condition = ''): coma_counter = [0]*3 attribute_list = ["Number = ", "Books_ISBN = '", "Position = '"] i = 0 if Number == '0': Number = 0 if Number_KEY == '0': Number_KEY = 0 if Number != 0: coma_counter[0] +=1 if Books_ISBN != '': coma_counter[1] +=1 if Position != '': coma_counter[2] +=1 j = sum(coma_counter) List = ["UPDATE Copy SET "] if Number != 0 and j!=1: List += attribute_list[0] List += str(Number) List += "," j = j-1 elif Number != 0 and j==1: List += attribute_list[0] List += str(Number) List += "" if Books_ISBN != '' and j!=1: List += attribute_list[1] List += Books_ISBN List += "'," j = j-1 elif Books_ISBN != '' and j==1: List += attribute_list[1] List += Books_ISBN List += "'" if Position != '' and j!=1: List += attribute_list[2] List += Position List += "'," j = j-1 elif Position != '' and j==1: List += attribute_list[2] List += Position List += "'" if (Number_KEY != 0 and Books_ISBN_KEY!='') or condition!='': List += " Where " if Number_KEY != 0 and Books_ISBN_KEY!='': List += "Number = " List += str(Number_KEY) List += " and Books_ISBN = '" List += Books_ISBN_KEY List += "'" else: List += condition List += ";" print(List) query = ''.join(List) try: db_config = read_db_config() conn = MySQLConnection(**db_config) cursor = conn.cursor() cursor.execute(query) if cursor.lastrowid: print('done') else: print('last update id not found') conn.commit() except Error as error: print(error) finally: cursor.close() conn.close()
def count_word_occurences(start, end): try: dbconfig = read_db_config() conn = MySQLConnection(**dbconfig) cursor = conn.cursor(buffered=True) cursor.execute("TRUNCATE dictionary") classes = [1, 2, 3, 4, 5, 6] spinner = PieSpinner("\nCounting Word Occurences ") for target in classes: if start == 1: cursor.execute( "SELECT class, sentence FROM data3 WHERE class=%(target)s and id > %(id_target)s", { 'target': target, 'id_target': end }) elif end == 7433: cursor.execute( "SELECT class, sentence FROM data3 WHERE class=%(target)s and id < %(id_target)s", { 'target': target, 'id_target': start }) else: cursor.execute("SELECT class, sentence FROM data3 WHERE class=%(target)s and (id < %(id_start)s or id > %(id_end)s)", {'target': target,\ 'id_start': start, 'id_end': end}) class_documents = cursor.fetchall() for row in class_documents: check = False sentence = row[1].split(' ') for word in sentence: spinner.next() if len(word) > 0: if row[0] == '1': cursor.execute( "SELECT word, joy_occurences from dictionary WHERE word=%(target)s", {'target': word}) elif row[0] == '2': cursor.execute( "SELECT word, fear_occurences from dictionary WHERE word=%(target)s", {'target': word}) elif row[0] == '3': cursor.execute( "SELECT word, anger_occurences from dictionary WHERE word=%(target)s", {'target': word}) elif row[0] == '4': cursor.execute( "SELECT word, sadness_occurences from dictionary WHERE word=%(target)s", {'target': word}) elif row[0] == '5': cursor.execute( "SELECT word, disgust_occurences from dictionary WHERE word=%(target)s", {'target': word}) elif row[0] == '6': cursor.execute( "SELECT word, shame_occurences from dictionary WHERE word=%(target)s", {'target': word}) check_word = cursor.fetchone() if check_word != None: result = check_word[1] + 1 if row[0] == '1': cursor.execute( "UPDATE dictionary SET joy_occurences=%(number)s WHERE word=%(target)s", { 'number': result, 'target': word }) # cursor.execute("SELECT word, joy_occurences from dictionary WHERE word=%(target)s", {'target':word}) elif row[0] == '2': cursor.execute( "UPDATE dictionary SET fear_occurences=%(number)s WHERE word=%(target)s", { 'number': result, 'target': word }) # cursor.execute("SELECT word, fear_occurences from dictionary WHERE word=%(target)s", {'target':word}) elif row[0] == '3': cursor.execute( "UPDATE dictionary SET anger_occurences=%(number)s WHERE word=%(target)s", { 'number': result, 'target': word }) # cursor.execute("SELECT word, anger_occurences from dictionary WHERE word=%(target)s", {'target':word}) elif row[0] == '4': cursor.execute( "UPDATE dictionary SET sadness_occurences=%(number)s WHERE word=%(target)s", { 'number': result, 'target': word }) # cursor.execute("SELECT word, sadness_occurences from dictionary WHERE word=%(target)s", {'target':word}) elif row[0] == '5': cursor.execute( "UPDATE dictionary SET disgust_occurences=%(number)s WHERE word=%(target)s", { 'number': result, 'target': word }) # cursor.execute("SELECT word, disgust_occurences from dictionary WHERE word=%(target)s", {'target':word}) elif row[0] == '6': cursor.execute( "UPDATE dictionary SET shame_occurences=%(number)s WHERE word=%(target)s", { 'number': result, 'target': word }) # cursor.execute("SELECT word, shame_occurences from dictionary WHERE word=%(target)s", {'target':word}) else: if row[0] == '1': cursor.execute( "INSERT INTO dictionary(word, joy_occurences) VALUES(%(target)s, 1)", {'target': word}) elif row[0] == '2': cursor.execute( "INSERT INTO dictionary(word, fear_occurences) VALUES(%(target)s, 1)", {'target': word}) elif row[0] == '3': cursor.execute( "INSERT INTO dictionary(word, anger_occurences) VALUES(%(target)s, 1)", {'target': word}) elif row[0] == '4': cursor.execute( "INSERT INTO dictionary(word, sadness_occurences) VALUES(%(target)s, 1)", {'target': word}) elif row[0] == '5': cursor.execute( "INSERT INTO dictionary(word, disgust_occurences) VALUES(%(target)s, 1)", {'target': word}) elif row[0] == '6': cursor.execute( "INSERT INTO dictionary(word, shame_occurences) VALUES(%(target)s, 1)", {'target': word}) except Error as e: print(e) finally: conn.commit() cursor.close() conn.close() spinner.finish() # if __name__ == '__main__': # count_word_occurences(5, 10)
def update_publishers(Name='', Date_of_Establishment='', Address='',Name_KEY='', condition=''): coma_counter = [0]*3 attribute_list = ["Name = '", "Date_of_Establishment = ", "Address = '"] i = 0 if Date_of_Establishment == '0': Date_of_Establishment = 0 if Name != '': coma_counter[0] +=1 if Date_of_Establishment != 0: coma_counter[1] +=1 if Address != '': coma_counter[2] +=1 j = sum(coma_counter) List = ["UPDATE Publishers SET "] if Name != '' and j!=1: List += attribute_list[0] List += Name List += "'," j = j-1 elif Name != '' and j==1: List += attribute_list[0] List += Name List += "'" if Date_of_Establishment != 0 and j!=1: List += attribute_list[1] List += Date_of_Establishment List += "," j = j-1 elif Date_of_Establishment != 0 and j==1: List += attribute_list[1] List += Date_of_Establishment List += "" if Address != '' and j!=1: List += attribute_list[2] List += Address List += "'," j = j-1 elif Address != '' and j==1: List += attribute_list[2] List += Address List += "'" if Name_KEY != '' or condition!='': List += " Where " if Name_KEY != '': List += "Name = '" List += Name_KEY List += "'" else: List += condition List += ";" query = ''.join(List) try: db_config = read_db_config() conn = MySQLConnection(**db_config) cursor = conn.cursor() cursor.execute(query) if cursor.lastrowid: print('done') else: print('last update id not found') conn.commit() except Error as error: print(error) finally: cursor.close() conn.close()
def sorgu_Calistir(self, sorgu): connection = MySQLConnection(**self.config) cursor = connection.cursor(dictionary=True) cursor.execute(sorgu) connection.commit() connection.close()
def create_all_top_100_table(conn: mysql.MySQLConnection): cursor = conn.cursor() sql = f'CREATE TABLE {TABLE_NAME} ({generate_sql_table_column_defines()});' cursor.execute(sql) conn.commit()
if len(tuples_ops) > 999: tuples_opses.append(tuples_ops) tuples_ops = [] tuples_fins.append(tuples_fin) tuples_opses.append(tuples_ops) print('\nОбработано: ', bad_zayavka + good_zayavka, ' загружено: ', good_zayavka, ' ошибки: ', bad_zayavka) if len(tuples_fin) > 0: for i, t_fin in enumerate(tuples_fins): cursor_fin = dbconn_fin.cursor() sql_fin = 'INSERT INTO saturn_fin.alfabank_products(remote_id, last_name, first_name, middle_name, ' \ 'e_mail, phone, birth_date, w_region, inserted_date, inserted_code, status_code, ' \ 'gender, birth_address, p_seria, p_number, p_date, p_police, p_police_code ' \ ') VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)' cursor_fin.executemany(sql_fin, t_fin) cursor_ops = dbconn_ops.cursor() # Статус "Загружено" (Бумага принята) sql_ops = 'UPDATE saturn_crm.contracts SET external_status_callcenter_code = 1 WHERE client_id = %s' cursor_ops.executemany(sql_ops, tuples_opses[i]) dbconn_fin.commit() dbconn_ops.commit() #if len(tuples_ops_err) > 0: # cursor_ops = dbconn_ops.cursor() # Статус "Ошибка" # sql_ops = 'UPDATE saturn_crm.contracts SET exchanged = 0 WHERE client_id = %s' # cursor_ops.executemany(sql_ops, tuples_ops_err) # dbconn_ops.commit() dbconn_fin.close() dbconn_ops.close()