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()
