def main():
print('Put txt file in the same folder of exe!')
table_name = 'lst_' + (input('Please name your list (lst_ will be add as prefix automatically):').lower())
if table_name == 'lst_':
sys.exit(0)
exist_control = exist_list(table_name)
if exist_control is None:
create_table(table_name)
elif exist_control == 0:
drop_table(table_name)
create_table(table_name)
else:
print('The named table is exist and contain ' + str(exist_control) + ' Records')
ask = input('Do you want to drop ' + str(exist_control) + ' table? (yes/no)')
if ask.lower() == 'yes':
drop_table(table_name)
create_table(table_name)
else:
print('Exit without any change!')
sys.exit(0)
extension = str(input('Please Enter Your Extension(ir,co.ir ,...):'))
list_method=input('Choice your Method( 1:Auto Letter Generator 2:Import Text File ) :')
if list_method == '1':
letter_number = int(input('Number of Letters:'))
keywords = [''.join(i) for i in product(ascii_lowercase, repeat=letter_number)]
db_config = read_db_config()
conn = MySQLConnection(**db_config)
for line in range(0, len(keywords)):
cursor = conn.cursor()
Query = "insert into %s (site) values (concat('%s','.','%s'));" \
% (table_name, '{0}'.format(str(keywords[line])),extension)
cursor.execute(Query)
print(str(line+1),end='\r')
print(str(line+1),'Records Imported!')
elif list_method == '2':
dic_filename_mask = str(input('Whats the Text Dictionary Filename {without extension}:'))
filename = dic_filename_mask + '.txt'
dic_list = open(filename)
print('Total Count of Records: ' + str(sum(1 for _ in dic_list)))
dic_list.close()
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
load_text_file_query = "LOAD DATA LOCAL INFILE '%s' INTO TABLE %s LINES TERMINATED BY '\\r\\n' (SITE);" \
% (filename, table_name)
print('Transferring List...')
cursor.execute(load_text_file_query)
print('Add Extension to list...')
update_extension_query = "update %s set site=concat(site,'.','%s')" % (table_name, extension)
cursor.execute(update_extension_query)
conn.commit()
cursor.close()
conn.close()
else:
print('Wrong Choice,Bye!')
print("Finish")
def data_laundry(user,saldo_user):
kodeLau = randomTiket()
total = 0
status = ""
print '1. Cuci laundry'
print '2. Cuci laundry + setrika'
print '3. Cuci laundry + setrika + delivery'
pilLaundry = input('Masukan pilihan : ')
berat = input('Masukan berat pakaian : ')
if pilLaundry == 1 :
total = 5000 * berat
elif pilLaundry == 2:
total = 7000 * berat
elif pilLaundry == 3:
total = 7000 * berat + 3000
fix_update = saldo_user - total
if fix_update < 0:
status = "Lunas"
print "Saldo anda tidak mencukupi"
tambah_saldo = raw_input("Ingin menambah saldo anda ? ")
if tambah_saldo == 'y' or 'Y' or 'Ya' or 'YA' or 'ya':
saldoTambah(saldo_user,fix_update,user)
else:
status = "Lunas"
query1 = "INSERT INTO data_user (kodeLau,user,status,total)"\
"VALUES(%s,%s,%s,%s)"
args1 = (kodeLau,user,status,total)
try:
dbConfig = bacaConfig()
connection = MySQLConnection(**dbConfig)
query = connection.cursor()
query.execute(query1,args1)
if query.lastrowid:
print "Data laundry berhasil dimasukan"
query2 = "UPDATE user SET saldo = %s WHERE user = %s"
data2 = (fix_update,user)
try:
dbConfig = bacaConfig()
conn2 = MySQLConnection(**dbConfig)
cursor2 = conn2.cursor()
cursor2.execute(query2,data2)
conn2.commit()
except Error as e:
print (e)
finally:
cursor2.close()
conn2.close()
else:
print "Gagal menginput"
connection.commit()
except Error as e:
print (e)
finally:
query.close()
connection.close()
def query_with_fetchone():
try:
dbconfig = {'password': '******', 'host': 'localhost', 'user': '******', 'database': 'stats'}
conn = MySQLConnection(**dbconfig)
cursor = conn.cursor()
cursor.execute("select COUNT(*) from clientBookings where MONTH(CheckInDate) = '2' AND YEAR(CheckInDate) = '2005' AND status = 'A'")
accept = cursor.fetchone()
x = accept[0]
print(accept)
print x
cursor1 = conn.cursor()
cursor1.execute("select COUNT(*) from clientBookings where MONTH(CheckInDate) = '2' AND YEAR(CheckInDate) = '2005' AND status = 'X'")
cancel = cursor1.fetchone()
print(cancel)
cursor2 = conn.cursor()
cursor2.execute("select COUNT(*) from clientBookings where MONTH(CheckInDate) = '2' AND YEAR(CheckInDate) = '2005' AND status = 'p'")
wait = cursor2.fetchone()
print(wait)
cursor3 = conn.cursor()
cursor3.execute("select COUNT(*) from clientBookings where MONTH(CheckInDate) = '2' AND YEAR(CheckInDate) = '2005' AND status = 'R'")
request = cursor3.fetchone()
print(request)
except Error as e:
print(e)
finally:
cursor.close()
conn.close()
def search():
try:
dbconfig = read_db_config()
conn = MySQLConnection(**dbconfig)
cursor = conn.cursor()
cursor.execute("SELECT * FROM data_train")
#row_train = cursor.fetchone()
data_train = list(cursor)
cursor = conn.cursor()
cursor.execute("SELECT * FROM data_test")
data_test = list(cursor)
index1 = 0
for x in range(len(data_train)) :
canbera = 0
index2 = 2
for y in range(82):
temp1 = (data_test[0][index2] - data_train[index1][index2])
temp2 = (data_test[0][index2] + data_train[index1][index2])
#print "temp1 "+str(temp1)
#print "temp2 "+str(temp2)
if temp2 == 0 :
hasil_temp = abs(temp1)
else :
hasil_temp = float(abs(temp1)) / float(temp2)
#print "hasil temp = " + str(hasil_temp)
canbera = float(canbera) + float(hasil_temp)
index2 += 1
index1+=1
print canbera
#for x in range(len(gabungan)):
#row_train[x]
except Error as e:
print(e)
finally:
cursor.close()
conn.close()
def create_user():
print ('create')
data = request.json
browser = data['browser']
ip = data['ip']
query = "SELECT COUNT(*) FROM users WHERE browser = %s AND ip = %s"
try:
dbconfig = read_db_config()
conn = MySQLConnection(**dbconfig)
cursor = conn.cursor()
cursor.execute(query, (browser, ip),)
count = cursor.fetchone()
count = count[0]
except Error as e:
print(e)
finally:
cursor.close()
conn.close()
if count != 0:
query = "SELECT id FROM users WHERE browser = %s AND ip = %s"
try:
dbconfig = read_db_config()
conn = MySQLConnection(**dbconfig)
cursor = conn.cursor()
cursor.execute(query, (browser, ip),)
id = cursor.fetchone()
except Error as e:
print(e)
finally:
cursor.close()
conn.close()
#if user already exist return his id
return jsonify({'state': 'fail', 'index' : id})
#if user is not exist add him to db
query = "INSERT INTO users (date, browser, ip) VALUES (%s, %s, %s)"
date = datetime.now()
try:
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
cursor.execute(query, (date, browser, ip, ),)
conn.commit()
except Error as error:
print(error)
finally:
cursor.close()
conn.close()
return jsonify({'state': 'OK'})
def insert_user(name, password, email):
query = "INSERT INTO users(username, password, email) " \
"VALUES(%s,%s,%s)"
args = (name, password, email)
try:
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
cursor.execute(query, args)
if cursor.lastrowid:
print('last insert id', cursor.lastrowid)
else:
print('last insert id not found')
conn.commit()
except Error as error:
print(error)
finally:
cursor.close()
conn.close()
def dataDiri(user):
pswd = getpass.getpass('Masukan password : '******'Sukses mendaftar')
else:
print("Last insert id not found")
conn.commit()
except Error as e:
print(e)
finally:
cursor.close()
conn.close
def query_with_fetchone(Lista1,v2):
try:
conn = MySQLConnection(host=DB_HOST,user=DB_USER,password=DB_PASS,database=DB_NAME)
cursor = conn.cursor()
cursor.execute("SELECT nombre FROM clientes")
row = cursor.fetchone()
Lista1.delete(0,END)
while row is not None:
d=""
for i in row:
if i== "("or i==")"or i=="'"or i ==",":
pass
else:
d = d+i
Lista1.insert(END,d)
row = cursor.fetchone()
except Error as e:
print(e)
finally:
Lista1.grid(column=1,row=2)
update(v2)
cursor.close()
conn.close()
def get_comments(self, article_id):
try:
conn = MySQLConnection(**self.db)
query = "SELECT * FROM comment WHERE article_id=%s"
cursor = conn.cursor()
cursor.execute(query, (article_id,))
list_of_comments = list()
row_comment = cursor.fetchone()
if row_comment is None:
return None
while row_comment is not None:
list_of_comments.append(row_comment)
row_comment = cursor.fetchone()
return list_of_comments
except Error as e:
print(e)
finally:
cursor.close()
conn.close()
def update_book(book_id, title):
# read database configuration
db_config = read_db_config()
# prepare query and data
query = """ UPDATE books
SET title = %s
WHERE id = %s """
data = (title, book_id)
try:
conn = MySQLConnection(**db_config)
# update book title
cursor = conn.cursor()
cursor.execute(query, data)
# accept the changes
conn.commit()
except Error as error:
print(error)
finally:
cursor.close()
conn.close()
def login_data(request):
try:
username = request.POST.get('username','')
phone = request.POST.get('phone', '')
Error_dict = {}
dbconfig = {'password': '******', 'host': 'localhost', 'user': '******', 'database': 'login_database'}
conn = MySQLConnection(**dbconfig)
cursor = conn.cursor()
username = "******" + username + "'"
cursor.execute("select COUNT(*) from client_info where Name = " + username + " AND phone_no = " + str(phone) + " ")
count = cursor.fetchone()
if(count[0] <= 0) :
Error_dict['Wrong_values'] = "Wrong Username or Phone no"
else :
context_dict = {}
context_dict['name'] = username
return render_to_response('logged_in_user.html', context_dict)
except Error as e:
print(e)
finally:
cursor.close()
conn.close()
return render_to_response('login_form.html', Error_dict)
def input_data():
for x in range(len(gabungan)):
gabungan[x]
query = "INSERT INTO data_test "
nilai = "VALUES (\"\"," + "\"" + file + "\"" + ","
for x in range(len(gabungan)):
nilai = nilai + str(gabungan[x])
if x < len(gabungan)-1 :
nilai = nilai + ", "
query = query + nilai + ")"
# print query
try:
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
cursor.execute(query)
conn.commit()
except Error as e:
print('Error:', e)
finally:
cursor.close()
conn.close()
def insert_one(star_name, star_location):
'''Insert one row into a MySQL database'''
query = ("INSERT INTO space(star_name, star_location)"
"VALUES(%s, %s)")
args = (star_name, star_location)
try:
db_config = read_db_config()
#Creating a new MySQLConnection object
conn = MySQLConnection(**db_config)
#Creating a new MySQLCursor object from the MySQLConnection object
cursor = conn.cursor()
#Inserts one row into the database
cursor.execute(query, args)
if cursor.lastrowid:
print('Insert ID: ', cursor.lastrowid)
else:
print('Insert Failure')
conn.commit()
except Error as error:
print(error)
finally:
cursor.close()
conn.close()
print('Connection closed.')
def insert_book(title, isbn):
query = "INSERT INTO books(title, isbn) " "VALUES(%s, %s)"
args = (title, isbn)
try:
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
cursor.execute(query, args)
if cursor.lastrowid:
print ("last insert id", cursor.lastrowid)
else:
print ("last insert id not found")
conn.commit()
except Error as error:
print error
finally:
cursor.close()
conn.close()
def query_row(table):
"""Returns the next row of a query result set or None
str -> tuple"""
try:
dbconfig = read_db_config() # returns a dict of connection parameters
print("Connecting " + dbconfig["user"] + " to " + dbconfig["database"] + "...")
conn = MySQLConnection(**dbconfig)
if conn.is_connected():
print("Connection established.")
else:
print("Connection failed")
cursor = conn.cursor(buffered=True)
sql_command = "SELECT * FROM " + table
print("Executed command: " + sql_command + ".")
cursor.execute(sql_command)
row = cursor.fetchone()
return row
# The fetchall method is similar but memory-consuming
# rows = cursor.fetchall()
# print('Total rows:', cursor.rowcount)
# return rows
except Error as e:
print(e)
finally:
cursor.close()
conn.close()
def insert_character(char_id, name, realname, gender, origin, image, siteurl, deck):
query = "INSERT INTO characters(id, name, realname, gender, origin, image, siteurl, deck) " \
"VALUES(%s, %s, %s, %s, %s, %s, %s, %s)"
args = (char_id, name, realname, gender, origin, image, siteurl, deck)
try:
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
cursor.execute(query, args)
if cursor.lastrowid:
print('last insert id', cursor.lastrowid)
else:
print('last insert id not found')
conn.commit()
except Error as error:
print error
finally:
cursor.close()
conn.close()
def update_one(star_id, star_location):
'''Update a field in a MySQL database'''
query = ("UPDATE space"
"SET star_name = %s"
"WHERE id = %s")
args = (star_name, star_id)
try:
db_config = read_db_config()
#Creating a new MySQLConnection object
conn = MySQLConnection(**db_config)
#Creating a new MySQLCursor object from the MySQLConnection object
cursor = conn.cursor()
#Updates a field in the database
cursor.execute(query, args)
conn.commit()
except Error as error:
print(error)
finally:
cursor.close()
conn.close()
print('Connection closed.')
def insert_imei(query, args):
try:
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
cursor.execute(query, args)
if cursor.lastrowid:
mensaje = ('Last insert id: %s' % (cursor.lastrowid,) )
print(mensaje)
else:
mensaje = 'Last insert id not found'
conn.commit()
except Error as error:
print(error)
finally:
cursor.close()
conn.close()
return mensaje
def register_data(request):
try:
username = request.POST.get('username','')
email = request.POST.get('email', '')
phone = request.POST.get('phone', '')
Error_dict = {}
if(username == '' or email == "" or phone == "" ) :
Error_dict['Invalid'] = "Enter Correct Values"
else:
dbconfig = {'password': '******', 'host': 'localhost', 'user': '******', 'database': 'login_database'}
conn = MySQLConnection(**dbconfig)
cursor = conn.cursor()
cursor.execute("select COUNT(*) from client_info where phone_no = " + str(phone) + " ")
count = cursor.fetchone()
count = count[0]
if(count > 0):
Error_dict['user_exist'] = "User Already Exist"
else:
cursor.execute("insert into client_info (Name , email, phone_no) values ('%s', '%s', '%s')" % (username, email, phone))
conn.commit()
return HttpResponseRedirect('register_success')
except Error as e:
print(e)
return render_to_response('register_form.html', Error_dict)
def mysql_fetchone():
'''Query a MySQL database using fetchone()'''
try:
db_config = read_db_config()
#Creating a new MySQLConnection object
conn = MySQLConnection(**db_config)
#Creating a new MySQLCursor object from the MySQLConnection object
cursor = conn.cursor()
#Selects all rows from the space table
cursor.execute("SELECT * from space")
#selects the next row in the cursor result set
row = cursor.fetchone()
#prints the row out and gets the next row
while row is not None:
print(row)
row = cursor.fetchone()
except Error as error:
print(error)
finally:
cursor.close()
conn.close()
print('Connection closed.')
def ads():
website = request.args.get('website', 'earthtravelers.com')
user = app.config['DB_LOGIN']
password = app.config['DB_PW']
host = app.config['DB_HOST']
database = app.config['DB_NAME']
conn = MySQLConnection(user=user, password=password, host=host, database=database)
conn.autocommit = True
cursor = conn.cursor()
args = (website,)
try:
cursor.callproc('AdMania.prc_GetAds', args)
except Error as e:
print e
# In order to handle multiple result sets being returned from a database call,
# mysql returns results as a list of lists.
# Therefore, even if there is only one result set, you still have to get it from the list of lists.
for result in cursor.stored_results():
row_set = result.fetchall()
result_set = []
for row in row_set:
result_set.append(row[0].decode().replace('##DOMAIN_ID##', '7782886'))
cursor.close()
conn.close()
return render_template('T1.html',resultsSET=result_set)
def delete_one(star_id):
'''Delete one row of a MySQL database'''
query = "DELETE FROM space WHERE id = %s"
try:
db_config = read_db_config()
#Creating a new MySQLConnection object
conn = MySQLConnection(**db_config)
#Creating a new MySQLCursor object from the MySQLConnection object
cursor = conn.cursor()
#Deletes one row in the database
cursor.execute(query, (star_id,))
conn.commit()
except Error as error:
print(error)
finally:
cursor.close()
conn.close()
print('Connection closed.')
def insert_name(name, gender):
query = "INSERT INTO babynames(babyName, gender) " \
"VALUES(%s,%s)"
args = (name, gender)
try:
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
cursor.execute(query, args)
if cursor.lastrowid:
print('last insert id', cursor.lastrowid)
else:
print('last insert id not found')
conn.commit()
except Error as error:
print(error)
finally:
cursor.close()
conn.close()
def insert_Face_Details(face_id, gender,age,emotion,emotion_percentage):
query = "INSERT INTO FaceData(face_id, gender,age,emotion,emotion_percentage) " \
"VALUES(%s,%s,%s,%s,%s)"
args = (face_id, gender,age,emotion,emotion_percentage)
try:
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
cursor.execute(query, args)
if cursor.lastrowid:
print('last insert id', cursor.lastrowid)
else:
print('last insert id not found')
conn.commit()
except Error as error:
print(error)
finally:
cursor.close()
conn.close()
print('Face Data transfered into Database')
def create_table(tbl_name_val):
query = "CREATE TABLE IF NOT EXISTS %s ( " \
"No int NOT NULL auto_increment," \
"Site varchar(100)," \
"Status varchar(30)," \
"Email varchar(100)," \
"Person varchar(100)," \
"Phone varchar(25)," \
"lockedby bigint, " \
"lockedat datetime," \
"Trycount int default 0 , " \
"Hostname varchar(50)," \
"HFlag char(1) DEFAULT NULL," \
"IP varchar(15) DEFAULT NULL," \
"PRIMARY KEY (No)" \
") ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;" % tbl_name_val
args = tbl_name_val
try:
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
cursor.execute(query)
print('New Table Created:' + str(tbl_name_val))
except Error as error:
print(error)
finally:
cursor.close()
conn.close()
def login_page():
error = ''
gc.collect()
try:
dbconfig = read_db_config()
conn = MySQLConnection(**dbconfig)
cur = conn.cursor()
if request.method == "POST":
query = ("""SELECT * FROM users WHERE username = %s""")
cur.execute(query, (request.form['username'],))
userpw = cur.fetchone()[2]
if sha256_crypt.verify(request.form['password'], userpw):
session['logged_in'] = True
session['username'] = request.form['username']
session['user-ip'] = request.remote_addr
if session['username'] == 'admin':
flash(Markup('The Dark Knight <span class="glyphicon glyphicon-knight"></span>'))
else:
flash('Logged In')
return redirect(url_for('blog'))
else:
error = "Invalid Credentials. Please try again."
gc.collect()
return render_template('login.html', error = error)
except Exception as e:
error = 'Invalid Credentials. Please try again.'
return render_template('login.html', error = error)
def mysql_fetchall():
'''Query a MySQL database using fetchall()'''
try:
db_config = read_db_config()
#Creating a new MySQLConnection object
conn = MySQLConnection(**db_config)
#Creating a new MySQLCursor object from the MySQLConnection object
cursor = conn.cursor()
#Selects all rows from the space table
cursor.execute("SELECT * from space")
#selects all the rows in the cursor result set
rows = cursor.fetchall()
print('Number of rows: %d' % cursor.rowcount)
#prints the rows out
for row in rows:
print(row)
except Error as error:
print(error)
finally:
cursor.close()
conn.close()
print('Connection closed.')
def insert_many(stars):
'''Insert multiple rows into a MySQL database'''
query = ("INSERT INTO space(star_name, star_location)"
"VALUES(%s, %s)")
try:
db_config = read_db_config()
#Creating a new MySQLConnection object
conn = MySQLConnection(**db_config)
#Creating a new MySQLCursor object from the MySQLConnection object
cursor = conn.cursor()
#Inserts many rows into the database
cursor.executemany(query, stars)
conn.commit()
except Error as error:
print(error)
finally:
cursor.close()
conn.close()
print('Connection closed.')
def query_with_fetchone(request):
try:
month_var = request.GET['month']
year_var = request.GET['year']
context_dict = {}
context_dict['month'] = month_var
context_dict['year'] = year_var
dbconfig = {'password': '******', 'host': 'localhost', 'user': '******', 'database': 'stats'}
conn = MySQLConnection(**dbconfig)
# print month_var, year_var
cursor = conn.cursor()
cursor.execute("select COUNT(*) from clientBookings where MONTH(CheckInDate) = " + str(month_var) + " AND YEAR(CheckInDate) = " + str(year_var) + " AND status = 'A'")
accept = cursor.fetchone()
# print(accept)
context_dict['accept'] = accept[0]
cursor1 = conn.cursor()
cursor1.execute("select COUNT(*) from clientBookings where MONTH(CheckInDate) = " + str(month_var) + " AND YEAR(CheckInDate) = " + str(year_var) + " AND status = 'X'")
cancel = cursor1.fetchone()
# print(cancel)
context_dict['cancel'] = cancel[0]
cursor2 = conn.cursor()
cursor2.execute("select COUNT(*) from clientBookings where MONTH(CheckInDate) = " + str(month_var) + " AND YEAR(CheckInDate) = " + str(year_var) + " AND status = 'p'")
wait = cursor2.fetchone()
# print(wait)
context_dict['wait'] = wait[0]
cursor3 = conn.cursor()
cursor3.execute("select COUNT(*) from clientBookings where MONTH(CheckInDate) = " + str(month_var) + " AND YEAR(CheckInDate) = " + str(year_var) + " AND status = 'R'")
req = cursor3.fetchone()
# print(req)
context_dict['req'] = req[0]
except Error as e:
print(e)
finally:
cursor.close()
conn.close()
return render_to_response('gchart.html'
, context_dict)
def database_update():
config=Config().config
user = config['DB_LOGIN']
password = config['DB_PW']
host = config['DB_HOST']
database = config['DB_NAME']
cjauth = config['CJ_AUTH']
cjurl = config['CJ_URL']
conn = MySQLConnection(user=user, password=password, host=host, database=database)
conn.autocommit = True
cursor = conn.cursor()
page_number = 0
records_per_page = 100 # this is the max number allowed by the affiliate api per call.
records_returned = records_per_page
headers = {'authorization': cjauth}
while records_returned == records_per_page:
page_number += 1
params = {'website-id': '7782886', 'link-type': 'banner', 'advertiser-ids': 'joined', 'page-number': page_number, 'records-per-page': records_per_page}
result = requests.get(cjurl, headers=headers, params=params)
result_xml = result.text
root = ET.fromstring(result_xml.encode('utf8'))
records_returned = int(root.find('links').get('records-returned'))
for link in root.iter('link'):
link_code_html = html.fromstring(link.find('link-code-html').text)
height = int(link_code_html.xpath('//img/@height')[0])
width = int(link_code_html.xpath('//img/@height')[0])
mysql_args = (
link.find('link-id').text,
link.find('advertiser-id').text,
link.find('advertiser-name').text,
link.find('category').text,
'None' if link.find('promotion-start-date').text == None else link.find('promotion-start-date').text,
'None' if link.find('promotion-end-date').text == None else link.find('promotion-end-date').text,
height,
width,
link.find('link-code-html').text)
try:
cursor.callproc('AdMania.prc_UpdateAd',mysql_args)
except Error as e:
print e
cursor.close()
conn.close()
def getData():
try:
dbconfig = read_db_config()
conn = MySQLConnection(**dbconfig)
cursor = conn.cursor()
cursor.execute(("""
select
table.row
from
table
"""))
rows = cursor.fetchall()
return rows
except Error as e:
print(e)
finally:
cursor.close()
conn.close()
def dumpToDataBase(csv_name, selection, neighbors, results):
query = "INSERT INTO data(csv_name,selection,neighbors,results)"\
"VALUES(%s,%s,%s,%s)"
try:
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
cursor.execute(query, args)
if cursor.lastrowid:
print('last insert id', cursor.lastrowid)
else:
print('last insert id not found')
conn.commit()
except Error as error:
print(error)
finally:
cursor.close()
conn.close()
def sql_insert(sql_query, database):
try:
db_config = read_db_config(filename='mysql_client/config.ini',
section=database)
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
cursor.execute(sql_query)
if cursor.lastrowid:
print('last insert id', cursor.lastrowid)
else:
print('last insert id not found')
conn.commit()
except Error as error:
print(error)
finally:
cursor.close()
conn.close()
def findWordByAbbreviation(word): try: db_config = read_db_config() # db_config['database'] = "travel" conn = MySQLConnection(**db_config) cursor = conn.cursor() command = "SELECT word FROM abbreviation_dictionary WHERE abbreviation = '" + word + "'" cursor.execute(command) rows = cursor.fetchall() if rows: row = rows[0] return row[0] return '' except Error as error: print error finally: cursor.close() conn.close()
def getCuisineFromRestaurantId(db_name, rest_id, restaurant_cuisine):
try:
db_config = read_db_config()
db_config['database'] = db_name
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
cursor.execute(
"SELECT cuisine_name FROM cuisine_restaurant WHERE restaurant_id = '"
+ str(rest_id) + "'")
rows = cursor.fetchall()
for row in rows:
restaurant_cuisine.append(row[0])
except Error as error:
print "Error: getCuisineFromRestaurantId"
print error
finally:
cursor.close()
conn.close()
def query_items_for_build(god_id, category):
try:
dbconfig = read_db_config()
conn = MySQLConnection(**dbconfig)
cursor = conn.cursor()
cursor.execute(f"""
SELECT god_name, item_name FROM {CATEGORIES[category]}
JOIN god ON {CATEGORIES[category]}.god_id=god.god_id
JOIN item ON {CATEGORIES[category]}.item_id=item.item_id
WHERE god.god_id={god_id}
""")
rows = cursor.fetchall()
items = [row[1] for row in rows]
# print('Total Row(s):', cursor.rowcount)
except Error as e:
print(e)
finally:
cursor.close()
conn.close()
return items
def update_table_with_csv_data(conn: mysql.MySQLConnection):
current_file = read_csv()
date_of_dataset = format_date_to_sql(current_file.get(0)[TOP100.TIME])
if dataset_already_in_database(conn, date_of_dataset):
print('Dataset already in database.')
return
cursor = conn.cursor()
for row in current_file:
if "Downloaded from" in current_file.get(row)[TOP100.SYMBOL]:
break
column_names, values = get_column_names_and_values(current_file[row])
sql = f'INSERT INTO {TABLE_NAME} {column_names} VALUES {values}'
cursor.execute(sql)
conn.commit()
print(
f'Dataset from "{date_of_dataset} was sucessfully added to database.')
class SaveToDBPipeline(object):
def __init__(self):
self.configPath = "./util/config/db_config_inner.ini"
self.dbconfig = cu.read_db_config(self.configPath)
self.connector = MySQLConnection(charset='utf8', **self.dbconfig)
def process_item(self, item, spider):
fun_name = 'process_' + item['pipeline_type']
method = getattr(self, fun_name)
if not method:
raise NotImplementedError("Method %s not implemented" % fun_name)
method(item, spider)
def process_IPItem(self, item, spider):
if item:
cursor = self.connector.cursor()
sql_query = 'insert into ip_address (ip,port,address,ip_type) values (%s,%s,%s,%s)'
cursor.execute(
sql_query,
(item['ip'], item['port'], item['address'], item['ip_type']))
cursor.close()
self.connector.commit()
def modulo_rastreador(modulo, fabricante, gateway):
import ipdb
ipdb.set_trace()
sql_modulo_rastreador = """
SELECT MODULO_RASTREADOR.ID_MODULO_RASTREADOR,
MODULO_RASTREADOR.ID_MODELO_RASTREADOR,
GATEWAY.ID_GATEWAY
FROM MODULO_RASTREADOR,
GATEWAY
WHERE MODULO_RASTREADOR.ID_ORIGINAL = {MODULO}
AND MODULO_RASTREADOR.ID_FABRICANTE = {FABRICANTE}
AND GATEWAY.ID_MODELO_MODULO_RASTREAMENTO =
MODULO_RASTREADOR.ID_MODELO_RASTREADOR
AND GATEWAY.CODIGO_GATEWAY_FISICO = {GATEWAY}""".format(
MODULO=modulo, FABRICANTE=fabricante, GATEWAY=gateway)
try:
dbconfig = read_db_config()
conn = MySQLConnection(**dbconfig)
cursor = conn.cursor()
cursor.execute(sql_modulo_rastreador)
rows = cursor.fetchall()
if not rows:
e = "Sem registros para este item no módulo rastreador"
#log_erros(item, e, 'MODULO_RASTREADOR')
print(e)
for row in rows:
id_modulo_rastreador = row[0]
return id_modulo_rastreador
except Error as e:
#log_erros(item, e, 'MODULO_RASTREADOR')
print(e)
finally:
cursor.close()
conn.close()
def query_title_matching_records():
try:
dbconfig = read_db_config()
conn = MySQLConnection(**dbconfig)
cursor = conn.cursor()
cursor.execute(
"select sourceid,sourcetitlename,sourcereleasedate,year(sourcereleasedate) as releaseyear from "
+ TABLE + " where SourceLookup like '%magic_id%'")
magic_titles = list()
for b in cursor:
c = dict()
c['movie_id'] = b[0]
c['movie_name'] = b[1]
c['release_date'] = b[2]
c['release_year'] = b[3]
magic_titles.append(c)
except Error as e:
print(e)
finally:
cursor.close()
conn.close()
return magic_titles
def delete_president(president_id):
db_config = read_db_config()
query = "DELETE FROM presidents WHERE id = %s"
try:
# connect to the database server
conn = MySQLConnection(**db_config)
# execute the query
cursor = conn.cursor()
cursor.execute(query, (president_id, ))
# accept the change
conn.commit()
except Error as error:
print(error)
finally:
cursor.close()
conn.close()
def create_index():
""" create index in the table"""
commands = ("ALTER TABLE word ADD INDEX idx_word (word, count);",
"ALTER TABLE url ADD INDEX idx_url (url);")
conn = None
try:
# read the connection parameters
params = config(section='mysql')
# connect to the MySQL server
conn = MySQLConnection(**params)
# create table one by one
cursor = conn.cursor()
# create tables index
for command in commands:
cursor.execute(command)
cursor.close()
conn.commit()
print('Create index successfully.')
except Error as e:
print('Error:', e)
finally:
conn.close()
def get_word(word, req_ip, request):
""" query matched words of specified word from word table """
sql = "SELECT DISTINCT word FROM word WHERE word LIKE {0} ORDER BY word DESC LIMIT 10;".format(
"'{0}%'".format(word))
conn = None
try:
# read the connection parameters
params = config(section='mysql')
# connect to the MySQL server
conn = MySQLConnection(**params)
# create table one by one
cursor = conn.cursor()
cursor.execute(sql)
rows = cursor.fetchall()
cursor.close()
insert_request(req_ip, word, request)
# If no row return []
return rows
except Error as e:
print('Error:', e)
finally:
conn.close()
def insertOneCuisine(cuisine_name):
query = "INSERT INTO cuisine(cuisine_name) " \
"VALUES(%s)"
args = (cuisine_name)
try:
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
cursor.execute(query, args)
conn.commit()
except Error as error:
print error
finally:
cursor.close()
conn.close()
print "CUISINE_RESTAURANT DATA INSERTED!!!"
def get_user_enrollments(user_id):
try:
dbconfig = read_db_config()
conn = MySQLConnection(**dbconfig)
cursor = conn.cursor()
query = "SELECT * FROM enrollments WHERE tg_id =" + str(user_id)
cursor.execute(query)
row = cursor.fetchone()
events = []
while row is not None:
events.append(row)
print(row)
row = cursor.fetchone()
except Error as e:
print(e)
finally:
cursor.close()
conn.close()
return(events)
def get_user_id(name, phone_number):
try:
connector = MySQLConnection(user='******',
password='******',
host='127.0.0.1',
database="project")
cursor = connector.cursor()
except Error as e:
print("connection error {}".format(e))
sys.exit("unable to connect to db")
try:
# check the values in the database
insert_values = (str(name.split()[0]), str(name.split()[1]),
phone_number)
insert_command = "SELECT user_id FROM user WHERE (user_first_name=%s and user_last_name=%s and user_phone_number=%s)"
cursor.execute(insert_command, insert_values)
connector.commit()
user_id = cursor.fetchone()[0]
return user_id
except:
print("cannot find user ID sorry cannot enter the Comments")
return None
def fetch_all(TestType):
try:
dbconfig = read_db_config()
conn = MySQLConnection(**dbconfig)
cursor = conn.cursor()
sql0 = "select COLUMN_NAME from information_schema.COLUMNS where table_name = '" + TestType + "';"
cursor.execute(sql0)
columns_temp = cursor.fetchall()
columns = ''
for c in columns_temp:
c = str(c).strip('()')
c = c.strip("',")
columns = columns + ',' + c
columns = columns.strip(',')
sql = "select * from " + TestType
cursor.execute(sql)
results = cursor.fetchall()
#print(columns)
#print(columns,results)
return columns, results
except Error as e:
print(e)
def get_data_from(table):
'''conecting to db and get average value from table_avg '''
try:
conn = MySQLConnection(host='localhost',
database='pars_db',
user='******',
password='******')
if conn.is_connected():
print('Connected to MySQL database')
cursor = conn.cursor()
sql_select_query = "SELECT * FROM {} ".format(table)
cursor.execute(sql_select_query)
records = cursor.fetchall()
except Error as e:
print('Error:', e)
finally:
cursor.close()
conn.close()
return records
def partStatus(partID, parameter):
dbconfig = read_db_config()
try:
conn = MySQLConnection(**dbconfig)
cursor = conn.cursor()
sql = "SELECT R.stringValue FROM PartParameter R WHERE (R.name = '{}') AND (R.part_id = {})".format(
parameter, partID)
cursor.execute(sql)
partStatus = cursor.fetchall()
if partStatus == []:
part = "Unknown"
else:
part = str(partStatus[0])[2:-3]
return (part)
except UnicodeEncodeError as err:
print(err)
finally:
cursor.close()
conn.close()
def query_with_fetchone():
try:
dbconfig = read_db_config()
conn = MySQLConnection(**dbconfig)
cursor = conn.cursor()
cursor.execute("SELECT * FROM books")
row = cursor.fetchone()
while row is not None:
print(row)
row = cursor.fetchone()
except Error as e:
print(e)
finally:
cursor.close()
conn.close()
def insertOneLocalityRestaurant(locality_id, restaurant_id):
query = "INSERT INTO locality_restaurant(locality_id, restaurant_id) " \
"VALUES(%s, %s)"
args = (locality_id, restaurant_id)
try:
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
cursor.execute(query, args)
conn.commit()
except Error as error:
print error
finally:
cursor.close()
conn.close()
print "LOCALITY_RESTAURANT DATA INSERTED!!!"
def insert_profile(sy, gr, fa, du, ha, na):
query = 'update plant_usda set _group = %s, family=%s, duration=%s, growth_habit=%s, native_status=%s WHERE symbol = %s'
args = (gr, fa, du, ha, na, sy)
try:
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
cursor.execute(query, args)
if cursor.lastrowid:
print('last insert id', cursor.lastrowid)
else:
print('last insert id not found')
conn.commit()
except Error as error:
print(error)
finally:
cursor.close()
conn.close()
def delete2(self, x):
dbconfig = read_db_config()
conn = MySQLConnection(**dbconfig)
cursor = conn.cursor()
cursor.execute("SELECT * FROM product")
a = []
b = []
c = []
d = []
for row in cursor:
a.append(row[0])
b.append(row[1])
c.append(row[2])
d.append(row[3])
if len(a) == 0:
return "no"
for i in range(len(a)):
cursor.execute("DELETE FROM product WHERE p_id LIKE '%s'" % (x))
conn.commit()
print "deleted"
cursor.close()
conn.close()
def insert_pdf_data(field, value, sy):
query = 'update plant_usda set %s = \'%s\' WHERE symbol = \'%s\'' % (
field, value, sy)
try:
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
cursor.execute(query)
if cursor.lastrowid:
print('last insert id', cursor.lastrowid)
else:
print('last insert id not found')
conn.commit()
except Error as error:
print(error)
finally:
cursor.close()
conn.close()
def sendup():
stime = time.time()
query = "INSERT INTO uptime(datetime, also) " \
"VALUES(now(), 20)"
args = (stime)
try:
dbconfig = read_db_config()
conn = MySQLConnection(**dbconfig)
cursor = conn.cursor()
cursor.execute(query)
row = cursor.fetchone()
return (row)
print(row)
except Error as e:
print(e)
finally:
cursor.close()
conn.close()
def insert_symbol(sy, _id):
query = " insert into plant_usda(symbol,id) values(%s,%s)"
args = (sy, _id)
try:
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
cursor.execute(query, args)
if cursor.lastrowid:
print('last insert id', cursor.lastrowid)
else:
print('last insert id not found')
conn.commit()
except Error as error:
print(error)
finally:
cursor.close()
conn.close()
def insert_factsheet(fa, _id):
query = 'update plant_usda set fact_sheet = %s WHERE id = %s'
args = (fa, _id)
try:
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
cursor.execute(query, args)
if cursor.lastrowid:
print('last insert id', cursor.lastrowid)
else:
print('last insert id not found')
conn.commit()
except Error as error:
print(error)
finally:
cursor.close()
conn.close()
def insertOneCity(city_id, name, data_resource):
query = "INSERT INTO city(city_id, name, data_resource) " \
"VALUES(%s, %s, %s)"
args = (city_id, name, data_resource)
try:
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
cursor.execute(query, args)
conn.commit()
except Error as error:
print error
finally:
cursor.close()
conn.close()
print "CITY DATA INSERTED!!!"
def get_urls_from_word(word, req_ip, request):
""" get multiple urls, titles and counts of the specified word from the joined table """
sql = "SELECT url, title, CAST(SUM(count) AS UNSIGNED) AS count FROM url INNER JOIN word ON url.url_id = word.wurl_id WHERE word LIKE {0} GROUP BY wurl_id ORDER BY count DESC;".format(
"'%{0}%'".format(word))
conn = None
try:
# read the connection parameters
params = config(section='mysql')
# connect to the MySQL server
conn = MySQLConnection(**params)
# create table one by one
cursor = conn.cursor()
cursor.execute(sql)
rows = cursor.fetchall()
cursor.close()
insert_request(req_ip, word, request)
# If no row return []
return rows
except Error as e:
print('Error:', e)
finally:
conn.close()
def getCuisineIdFromName(cuisine_name):
try:
db_config = read_db_config()
db_config['database'] = "warehouse"
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
query = "SELECT cuisine_id from cuisine WHERE cuisine_name = '" + cuisine_name + "'"
cursor.execute(query)
row = cursor.fetchone()
if row == None:
return -1
return str(row[0])
except Error as error:
print "Error: getCuisineIdFromName"
print error
finally:
cursor.close()
conn.close()
