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 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 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 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 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 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 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 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 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 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 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 Connect():
kwargs = ReadingMySQLConfig()
MyConnection = MySQLConnection(**kwargs)
try:
if MyConnection.is_connected():
print("Connected")
except Error as e:
print(e)
finally:
MyConnection.close()
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 read_db():
try:
dbconfig = read_db_config()
conn = MySQLConnection(**dbconfig)
cursor = conn.cursor()
cursor.execute("SELECT * FROM temperature;")
row = cursor.fetchone()
while row is not None:
print(row)
row = cursor.fetchone()
finally:
cursor.close()
conn.close()
def getAllUsers(self):
try:
conn = MySQLConnection(host='localhost', database='mydb', user='******', password='******')
cursor = conn.cursor()
cursor.execute("SELECT * FROM users")
rows = cursor.fetchall()
return rows
except Error as error:
print error
finally:
cursor.close()
conn.close()
def post(self):
try:
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
cursor.callproc('sp_insertmember')
except Error as e :
return {'error': str(e)}
finally:
cursor.close()
conn.close()
def create_database(cursor):
try:
conn = MySQLConnection(**db_data)
cursor = conn.cursor()
cursor.execute(
"CREATE DATABASE {} DEFAULT CHARACTER SET 'utf8'".format(DB_NAME))
except Error as e:
print(e)
finally:
cursor.close()
conn.close()
def ReadImage(author_id, filename):
kwargs = ReadingMySQLConfig()
query = 'SELECT photo FROM authors WHERE id = %s'
try:
MyConnection = MySQLConnection(**kwargs)
cursor = MyConnection.cursor()
cursor.execute(query, (author_id,))
photo = cursor.fetchone()[0]
WriteFile(photo, filename)
except Error as e:
print(e)
finally:
MyConnection.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_table(tables, db_data):
try:
dbconfig = db_data
conn = MySQLConnection(**dbconfig)
cursor = conn.cursor()
for name, ddl in tables.iteritems():
cursor.execute(ddl)
except Error as e:
print(e)
finally:
cursor.close()
conn.close()
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 connect():
""" Connect to MySQL database """
dbConfig = readDbConfig()
try:
print('Connecting to MySQL database...')
conn = MySQLConnection(**dbConfig)
if(conn.is_connected()):
print('Connected to database')
return conn
else:
print('Failed to connect to database')
except Error as error:
print(error)
def __init__(self):
global cursor
global conn
try:
print('Connecting to MySQL database...')
conn = MySQLConnection(user='******', password='******', database='geom') # Connect to MySQL database
cursor = conn.cursor()
if conn.is_connected():
print('connection established.')
else:
print('connection failed.')
except Error as error:
print(error)
def query_with_fetchmany():
print "FetchMany...........\n"
try:
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()
cursor.execute('select * from test')
for row in iter_rows(cursor, 10):
print row
except Error as e:
print "[SOMETHING BAD HAPPEND ]", e
finally:
conn.close()
cursor.close()
def get_users_one_month():
count = 0
try:
dbconfig = read_db_config()
conn = MySQLConnection(**dbconfig)
cursor = conn.cursor()
cursor.execute("SELECT COUNT(*) FROM users")
count = cursor.fetchone()
except Error as e:
print(e)
finally:
cursor.close()
conn.close()
count = count[0]
return jsonify({'count': count})
def query(q):
try:
dbconfig = read_db_config()
conn = MySQLConnection(**dbconfig)
cursor = conn.cursor()
cursor.execute(q)
rows = cursor.fetchall()
return rows
except Error as e:
print(e)
finally:
cursor.close()
conn.close()
def connect():
db_config = read_db_config()
try:
print 'Connecting to database: {0}...'.format(db_config['database'])
conn = MySQLConnection(**db_config)
if conn.is_connected():
print 'Connection established.'
return conn
else:
print 'Connection failed.'
except Error as e:
print e.message
def query_fetchall():
try:
dbconfig = read_db_config()
conn = MySQLConnection(**dbconfig)
cursor = conn.cursor(dictionary=True)
cursor.execute("SELECT * FROM vouchedfor.answer LIMIT 100")
rows = cursor.fetchall()
except Error as e:
print e
finally:
cursor.close()
conn.close()
return rows
def get_main_page(self, request):
db = MySQLConnection(
host="localhost",
user="******",
password="******",
database="test",
autocommit=True
)
cursor = db.cursor()
cursor.execute("SELECT * FROM posts ORDER BY id LIMIT 9")
posts = cursor.fetchall()
db.close()
return HtmlRenderHelper.render_main_page(self.jinja_env, posts=posts)
class MySQLVariables(Resource):
def __init__(self, host=None, port=None):
try:
self.cnx = MySQLConnection(host=host, port=port)
self.cursor = self.cnx.get_cursor()
except:
raise
def get(self, host=None, port=None, var=None):
mysql_variables = { }
if var is None:
sql = "show global variables;"
else:
sql = "show global variables like '%s';" % var
self.cursor.execute(sql)
for row in self.cursor:
mysql_variables[row['Variable_name']] = row['Value']
self.cursor.close()
return mysql_variables
def put(self, host=None, port=None, var=None):
if var:
sql = "set global %s = '%s';" % (var, request.form['value'])
self.cursor.execute(sql)
self.cursor.close()
return {}
def connect_to_database():
try:
dbconfig = read_db_config()
return MySQLConnection(**dbconfig)
except Error as e:
print(e)
def update_Borrows(Members_MemberID = 0, Copy_Number = 0, Copy_Books_ISBN = '', Start_Date = '', Return_Date = '', Due_Date = '', Members_MemberID_KEY = 0, Copy_Number_KEY = 0, Copy_Books_ISBN_KEY = '', condition = ''):
coma_counter = [0]*6
attribute_list = ["Members_MemberID = ", "Copy_Number = ", "Copy_Books_ISBN = '", "Start_Date = '", "Return_Date = '", "Due_Date = '"]
i = 0
if Members_MemberID == '0':
Members_MemberID = 0
if Copy_Number == '0':
Copy_Number = 0
if Members_MemberID != 0:
coma_counter[0] +=1
if Copy_Number != 0:
coma_counter[1] +=1
if Copy_Books_ISBN != '':
coma_counter[2] +=1
if Start_Date != '':
coma_counter[3] +=1
if Return_Date != '':
coma_counter[4] +=1
if Due_Date != '':
coma_counter[5] +=1
j = sum(coma_counter)
List = ["UPDATE Borrows SET "]
if Members_MemberID != 0 and j!=1:
List += attribute_list[0]
List += str(Members_MemberID)
List += ","
j = j-1
elif Members_MemberID != 0 and j==1:
List += attribute_list[0]
List += str(Members_MemberID)
List += ""
if Copy_Number != 0 and j!=1:
List += attribute_list[1]
List += str(Copy_Number)
List += ","
j = j-1
elif Copy_Number != 0 and j==1:
List += attribute_list[1]
List += str(Copy_Number)
List += ""
if Copy_Books_ISBN != '' and j!=1:
List += attribute_list[2]
List += Copy_Books_ISBN
List += "',"
j = j-1
elif Copy_Books_ISBN != '' and j==1:
List += attribute_list[2]
List += Copy_Books_ISBN
List += "'"
if Start_Date != '' and j!=1:
List += attribute_list[3]
List += Start_Date
List += "',"
j = j-1
elif Start_Date != '' and j==1:
List += attribute_list[3]
List += Start_Date
List += "'"
if Return_Date != '' and j!=1:
List += attribute_list[4]
List += Return_Date
List += "',"
j = j-1
elif Return_Date != '' and j==1:
List += attribute_list[4]
List += Return_Date
List += "'"
if Due_Date != '' and j!=1:
List += attribute_list[5]
List += Due_Date
List += "',"
j = j-1
elif Due_Date != '' and j==1:
List += attribute_list[5]
List += Due_Date
List += "'"
if (Members_MemberID_KEY != 0 and Copy_Number_KEY!=0 and Copy_Books_ISBN_KEY!='') or condition!='':
List += " Where "
if Members_MemberID_KEY != 0 and Copy_Number_KEY!=0 and Copy_Books_ISBN_KEY!='':
List += "Members_MemberID = "
List += str(Members_MemberID_KEY)
List += " and Copy_Number = "
List += str(Copy_Number_KEY)
List += " and Copy_Books_ISBN = '"
List += Copy_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 update_Reminds(Staff_StaffID = 0, Members_MemberID = 0, Date_of_Reminder = '', Staff_StaffID_KEY = 0, Members_MemberID_KEY = 0, condition = ''):
coma_counter = [0]*3
attribute_list = ["Staff_StaffID = ", "Members_MemberID = ", "Date_of_Reminder = '"]
i = 0
if Staff_StaffID == '0':
Staff_StaffID = 0
if Members_MemberID == '0':
Members_MemberID = 0
if Staff_StaffID != 0:
coma_counter[0] +=1
if Members_MemberID != 0:
coma_counter[1] +=1
if Date_of_Reminder != '':
coma_counter[2] +=1
j = sum(coma_counter)
List = ["UPDATE Reminds 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 Members_MemberID != 0 and j!=1:
List += attribute_list[1]
List += str(Members_MemberID)
List += ","
j = j-1
elif Members_MemberID != 0 and j==1:
List += attribute_list[1]
List += str(Members_MemberID)
List += ""
if Date_of_Reminder != '' and j!=1:
List += attribute_list[2]
List += Date_of_Reminder
List += "',"
j = j-1
elif Date_of_Reminder != '' and j==1:
List += attribute_list[2]
List += Date_of_Reminder
List += "'"
if (Staff_StaffID_KEY != 0 and Members_MemberID_KEY!=0) or condition!='':
List += " Where "
if (Staff_StaffID_KEY != 0 and Members_MemberID_KEY!=0):
List += "Staff_StaffID = "
List += str(Staff_StaffID_KEY)
List += " and Members_MemberID = "
List += str(Members_MemberID_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 update_members(MemberID=0,Name='',Surname='',Address='',num_books_borrowed=-1,Birthdate='',Can_Borrow=-1, LibraryName='',MemberID_KEY=0, condition=''):
coma_counter = [0]*8
attribute_list = ["MemberID = ", "Name = '", "Surname = '", "Address = '", "num_books_borrowed = ", "Birthdate = '", "Can_Borrow = ", "LibraryName = '"]
i = 0
if Can_Borrow == '0':
Can_Borrow = 0
if Can_Borrow == '1':
Can_Borrow = 1
if int(num_books_borrowed) <0 or int(num_books_borrowed)>5:
num_books_borrowed == '-1'
if int(num_books_borrowed) == 5:
Can_Borrow = 0
if MemberID != 0:
coma_counter[0] +=1
if Name != '':
coma_counter[1] +=1
if Surname != '':
coma_counter[2] +=1
if Address != '':
coma_counter[3] +=1
if num_books_borrowed != '-1' or num_books_borrowed != -1:
coma_counter[4] +=1
if Birthdate != '':
coma_counter[5] +=1
if Can_Borrow == 0 or Can_Borrow == 1:
coma_counter[6] +=1
if LibraryName != '':
coma_counter[7] +=1
j = sum(coma_counter)
List = ["UPDATE Members SET "]
if MemberID != 0 and j!=1:
List += attribute_list[0]
List += MemberID
List += ","
j = j-1
elif MemberID != 0 and j==1:
List += attribute_list[0]
List += MemberID
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 Surname != 0 and j!=1:
List += attribute_list[2]
List += Surname
List += "',"
j = j-1
elif Surname != 0 and j==1:
List += attribute_list[2]
List += Surname
List += "'"
if Address != '' and j!=1:
List += attribute_list[3]
List += Address
List += "',"
j = j-1
elif Address != '' and j==1:
List += attribute_list[3]
List += Address
List += "'"
if num_books_borrowed != -1 and j!=1:
List += attribute_list[4]
List += num_books_borrowed
List += ","
j = j-1
elif num_books_borrowed != -1 and j==1:
List += attribute_list[4]
List += num_books_borrowed
List += ""
if Birthdate != '' and j!=1:
List += attribute_list[5]
List += Birthdate
List += "',"
j = j-1
elif Birthdate != '' and j==1:
List += attribute_list[5]
List += Birthdate
List += "'"
if (Can_Borrow == 0 or Can_Borrow == 1) and j!=1:
List += attribute_list[6]
List += str(Can_Borrow)
List += ","
j = j-1
elif (Can_Borrow == 0 or Can_Borrow == 1) and j==1:
List += attribute_list[6]
List += str(Can_Borrow)
List += ","
if LibraryName != '' and j!=1:
List += attribute_list[7]
List += LibraryName
List += "'"
j = j-1
elif LibraryName != '' and j==1:
List += attribute_list[7]
List += LibraryName
List += "'"
if MemberID!= 0 or condition!='':
List += " Where "
if MemberID != '':
List += "MemberID = '"
List += MemberID
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 connect():
#"""Gets Springserve data and writes to MySQL table"""
db = "mysql_sl"
api = "springs"
# Connect to DB:
db_config = read_db_config(db)
try:
print('Connecting to database...')
conn = MySQLConnection(**db_config)
if conn.is_connected():
print('Connection established')
cursor = conn.cursor()
# Create Table:
sql = "DROP TABLE IF EXISTS springserve_market_today"
cursor.execute(sql)
sql = "CREATE TABLE springserve_market_today (date varchar(25), hour varchar(2), demand_partner_name varchar(255), source_id varchar(10), \
supply_source varchar(255), total_requests bigint, ad_opportunities bigint, ad_impressions bigint, \
clicks bigint, revenue decimal(15, 5))"
cursor.execute(sql)
# call to get logintoken
logintoken = springs_api.get_logintoken(api)
print(logintoken)
jsoninfo = {
"date_range": "Today",
"interval": "hour",
"timezone": "America/Los_Angeles",
"dimensions": ["supply_tag_id", "demand_partner_id"],
#"start_date": Today,
#"endDate": Today,
#"sort": [{ "field": "ad_revenue", "order": "desc"}]
}
result = springs_api.get_data(logintoken, jsoninfo)
#print(result.text)
info = json.loads(result.text)
#print(info)
# use default to populate null data
default = '0'
for x in info:
date1 = x['date']
date = date1[:10]
time1 = x['date']
time2 = time1[11:-8].replace("00", "*0").lstrip("0")
hour = time2.replace("*0", "0")
demand_partner1 = x['demand_partner_name']
demand_partner_name = demand_partner1[:-4]
source_id = x['supply_tag_id']
supply_source = x['supply_tag_name']
total_requests = x['demand_requests']
ad_opportunities = x['demand_requests']
ad_impressions = x['impressions']
clicks = x['clicks']
revenue = x['revenue']
list = (date, hour, demand_partner_name, source_id,
supply_source, total_requests, ad_opportunities,
ad_impressions, clicks, revenue)
#print(list)
sql = """INSERT INTO springserve_market_today VALUES ("%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s")""" % \
(date, hour, demand_partner_name, source_id, supply_source, total_requests, ad_opportunities, ad_impressions, clicks, revenue)
cursor.execute(sql)
cursor.execute('commit')
else:
print('Connection failed.')
except Error as error:
print(error)
finally:
conn.close()
print('Connection closed.')
def connect():
# """Gets AOL Data and writes them to a MySQL table"""
db = "mysql_eom"
api = "aol"
# Connect To DB:
db_config = read_db_config(db)
try:
print('Connecting to database...')
conn = MySQLConnection(**db_config)
if conn.is_connected():
print('Connection established.')
cursor = conn.cursor()
sql = "DROP TABLE IF EXISTS dna_market_public_EOM"
cursor.execute(sql)
sql = "CREATE TABLE dna_market_public_EOM (date varchar(25), inventory_source varchar(255), geo_country varchar(50), \
ad_opportunities varchar(2), ad_attempts bigint, ad_impressions bigint, \
ad_revenue decimal(15, 5), ecpm decimal(6, 4), media_spend decimal(15, 5), completed_views int, clicks int, \
platform int)"
cursor.execute(sql)
# calls get_access_token function and starts script
logintoken = aol_api.get_access_token(api)
print(logintoken)
result = aol_api.run_existing_report(logintoken, "193890")
#print(result)
info = json.loads(result)
#print(info)
for x in json.loads(result)['data']:
date = x['row'][0]
inventory_source = x['row'][1].replace("'", " -")
geo_country = x['row'][2].replace(",", " -")
ad_opportunities = x['row'][3]
ad_attempts = x['row'][4]
ad_impressions = x['row'][5]
ad_revenue = x['row'][6]
ecpm = x['row'][7]
media_spend = x['row'][8]
completed_views = x['row'][9]
clicks = x['row'][10].replace(" ", "0")
platform = '1'
list = (date, inventory_source, geo_country, ad_opportunities, ad_attempts, ad_impressions, \
ad_revenue, ecpm, media_spend, completed_views, clicks, platform)
#print(list)
sql = """INSERT INTO dna_market_public_EOM VALUES ("%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", \
"%s", "%s")""" % (date, inventory_source, geo_country, ad_opportunities, \
ad_attempts, ad_impressions, ad_revenue, ecpm, media_spend, completed_views, clicks, platform)
cursor.execute(sql)
cursor.execute('commit')
else:
print('Connection failed.')
except Error as error:
print(error)
finally:
conn.close()
print('Connection closed.')
elif not sys.argv[1].endswith('.xlsx'):
print(datetime.now().strftime("%H:%M:%S"),
'В командной строке не указан файл Excel')
sys.exit()
wb = openpyxl.load_workbook(filename=sys.argv[1], read_only=True)
sheet = wb[wb.sheetnames[0]]
if not sheet.max_row:
print(
datetime.now().strftime("%H:%M:%S"),
'Файл Excel некорректно сохранен OpenPyxl. Откройте и пересохраните его'
)
sys.exit()
keys = {}
last_cell = 0
dbconfig = read_config(filename='move.ini', section='mysql')
dbconn = MySQLConnection(**dbconfig)
cursor = dbconn.cursor()
#big_string = ''
tuples_contracts = []
for j, row in enumerate(sheet.rows):
if j == 0:
for k, cell in enumerate(row): # Проверяем, чтобы был client_id
if str(cell.value).upper() in IN_IDS:
keys[IN_IDS[0]] = k
if len(keys) > 0:
for k, cell in enumerate(row):
for n, name in enumerate(IN_NAMES):
if n == 0:
continue
if cell.value != None:
if str(cell.value).strip() != '':
def __init__(self):
self.configPath = os.path.join(os.path.dirname(__file__)+"/config/db_config_inner.ini")
self.dbconfig = cu.read_db_config(self.configPath)
self.connector = MySQLConnection(charset='utf8', **self.dbconfig)
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 update_password(msg_received, header):
password = msg_received["password"]
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:
cursor.execute("UPDATE `user` SET `password` = '" + str(password) +
"' WHERE user_id=" + str(user_id) + ";")
conn.commit()
conn.close()
cursor.close()
return json.dumps({'notification': 'password updated'})
else:
conn.close()
cursor.close()
return json.dumps({'Error': 'password not updated'})
def cos_fit(input_table,
output_table,
db_name=None,
config_filename="../mysql_dbconfig_files/config.ini",
section="midlat",
ftype="fitacf",
coords="mlt",
azbin_nvel_min=10,
naz_min=3,
az_span_min=30,
sqrt_weighting=True):
""" Does cosine fitting to the LOS data in each MLAT/MLT grid,
and stores the results in a different table named "master_cosfit_xxx".
This table has only the qualified latc-lonc grid points.
Parameters
----------
input_table : str
A table name in db_name db
output_table : str
A table name in db_name db
config_filename: str
name and path of the configuration file
section: str, default to "midlat"
section of database configuration
db_name : str, default to None
Name of the master db
ftype : str
SuperDARN file type
coords : str
Coordinates in which the binning process took place.
Default to "mlt, can be "geo" as well.
azbin_nvel_min : int
The minimum number of measurements an azimuthal bin should
have to be qualified for cosfitting.
naz_min : int
The minimum number of azimuthal bins within a grid cell.
cosine fitting is done if a grid cell has at least
naz_min number of qualified azimuthal bins
az_span_min : int
The minimum azimuhtal span a grid cell should have to
be qualified for cosfitting.
sqrt_weighting : bool
if set to True, the fitting is weighted by the number of points within
each azimuthal bin. if set to False, all azimuthal bins are
considered equal regardless of the nubmer of points
each of them contains.
Returns
-------
Nothing
"""
import numpy as np
import datetime as dt
from mysql.connector import MySQLConnection
import sys
sys.path.append("../")
from mysql_dbutils.db_config import db_config
import logging
# construct a db name
if db_name is None:
db_name = "master_" + coords + "_" + ftype
# read db config info
config = db_config(config_filename=config_filename, section=section)
config_info = config.read_db_config()
# make a connection to master db
try:
conn = MySQLConnection(database=db_name, **config_info)
cur = conn.cursor(buffered=True)
except Exception, e:
logging.error(e, exc_info=True)
''' Created on Jul 16, 2016 @author: weizhenyuan ''' from mysql.connector import MySQLConnection MySQLConnection.connect() print
from mysql.connector import MySQLConnection, Error
import home
cnx = MySQLConnection(user='******', password='******', database='project')
cursor = cnx.cursor()
# Startup login or create account
def start():
print("--------------Student Organization Database----------------")
while True:
option = raw_input("Please login with 'login' or create an account with 'create'.\n")
if option == "login":
return login()
elif option == "create":
return new_user()
else:
print("Command not recognized.")
# Login Function
def login():
print("----------------Login---------------\n Go back with back")
while True:
name = raw_input("Enter your username:\n")
if name == 'back':
return start()
password = raw_input("Enter your password:\n")
if password == 'back':
return start()
result_args = cursor.callproc('login', [name, password, 0])
def connect():
# """Gets AOL Data and writes them to a MySQL table"""
db = "mysql_dp"
api = "tm"
# Connect To DB:
db_config = read_db_config(db)
try:
print('Connecting to database...')
conn = MySQLConnection(**db_config)
if conn.is_connected():
print('Connection established.')
cursor = conn.cursor()
sql = "DROP TABLE IF EXISTS tm_inventoryreport"
cursor.execute(sql)
sql = "CREATE TABLE tm_inventoryreport (date varchar(50), inventory_source varchar(255), geo_country varchar(50), \
ad_opportunities bigint, ad_attempts bigint, ad_impressions bigint, \
ad_revenue decimal(15, 5), ecpm decimal(15, 5), platform int)"
cursor.execute(sql)
# calls get_access_token function and starts script
logintoken = aol_api.get_access_token(api)
print(logintoken)
result = aol_api.run_existing_report(logintoken, "169370")
#print(result)
info = json.loads(result)
#print(info)
for x in json.loads(result)['data']:
date = x['row'][0]
inventory_source = x['row'][1]
geo_country = x['row'][2]
ad_opportunities = x['row'][3]
ad_attempts = x['row'][4]
ad_impressions = x['row'][5]
ad_revenue = x['row'][6]
ecpm = x['row'][7]
platform = '5'
list = (date, inventory_source, geo_country, ad_opportunities, ad_attempts, ad_impressions, ad_revenue, ecpm, \
platform)
# print(list)
sql = """INSERT INTO tm_inventoryreport VALUES ("%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s")""" % \
(date, inventory_source, geo_country, ad_opportunities, ad_attempts, ad_impressions, ad_revenue, ecpm, \
platform)
cursor.execute(sql)
cursor.execute('commit')
else:
print('Connection failed.')
except Error as error:
print(error)
finally:
conn.close()
print('Connection closed.')
def create(self):
return (MySQLConnection(user=self.user,
password=self.password,
host=self.host,
database=self.db))
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 connect():
# """Gets AOL Data and writes them to a MySQL table"""
db = "mysql_sl"
api = "dc"
# Connect To DB:
db_config = read_db_config(db)
try:
print('Connecting to database...')
conn = MySQLConnection(**db_config)
if conn.is_connected():
print('Connection established.')
cursor = conn.cursor()
sql = "DROP TABLE IF EXISTS dc_core_yesterday_media"
cursor.execute(sql)
sql = "CREATE TABLE dc_core_yesterday_media (date varchar(25), inventory_source varchar(255), media varchar(255), \
ad_opportunities bigint, market_opportunities bigint, ad_attempts bigint, ad_impressions bigint, ad_errors bigint, \
ad_revenue decimal(15, 5), aol_cost decimal(15, 5), epiphany_gross_revenue decimal(15, 5), dc_revenue decimal(15, 5), \
clicks bigint, iab_viewability_measurable_ad_impressions bigint, iab_viewable_ad_impressions bigint, platform int)"
cursor.execute(sql)
# calls get_access_token function and starts script
logintoken = aol_api.get_access_token(api)
print(logintoken)
result = aol_api.run_existing_report(logintoken, "143983")
#print(result)
info = json.loads(result)
#print(info)
for x in json.loads(result)['data']:
date = x['row'][0]
inventory_source = x['row'][1]
media = x['row'][2]
ad_opportunities = x['row'][3]
market_opportunities = x['row'][4]
ad_attempts = x['row'][5]
ad_impressions = x['row'][6]
ad_errors = x['row'][7]
ad_revenue = x['row'][8]
aol_cost = x['row'][8]
epiphany_gross_revenue = x['row'][8]
dc_revenue = x['row'][8]
clicks = x['row'][9]
iab_viewability_measurable_ad_impressions = x['row'][10]
iab_viewable_ad_impressions = x['row'][11]
platform = '2'
list = (date, inventory_source, media, ad_opportunities, market_opportunities, ad_attempts, ad_impressions, \
ad_errors, ad_revenue, aol_cost, epiphany_gross_revenue, dc_revenue, clicks, \
iab_viewability_measurable_ad_impressions, iab_viewable_ad_impressions, platform)
#print(list)
sql = """INSERT INTO dc_core_yesterday_media VALUES ("%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", \
"%s"*.20, "%s"*.56, "%s"*.24, "%s", "%s", "%s", "%s")""" % (date, inventory_source, media,
ad_opportunities, market_opportunities, ad_attempts, ad_impressions, ad_errors, ad_revenue, aol_cost, \
epiphany_gross_revenue, dc_revenue, clicks, iab_viewability_measurable_ad_impressions, \
iab_viewable_ad_impressions, platform)
cursor.execute(sql)
cursor.execute('commit')
else:
print('Connection failed')
except Error as error:
print(error)
finally:
conn.close()
print('Connection closed.')
def 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()
from urlparse import urljoin, urlparse, urlunparse
from bs4 import BeautifulSoup
import hashlib
import sys
import re
import datetime
from mysql.connector import MySQLConnection
from python_mysql_dbconfig import read_db_config
run_id = -1
source_id = -1
g_URLsDict = dict()
g_link_limit = 200
db_config = read_db_config()
cnx = MySQLConnection(**db_config)
cursor = cnx.cursor()
class Crawler(threading.Thread):
global g_URLsDict
varLock = threading.Lock()
count = 0
def __init__(self, queue):
threading.Thread.__init__(self)
self.queue = queue
# self.url = self.queue.get()
# self.links = []
def getLinks(self, url):
def update_authors(AuthorID = 0, Name = '', Surname = '', Birthdate = '', AuthorID_KEY=0, condition=''):
coma_counter = [0]*4
attribute_list = ["AuthorID = ", "Name = '", "Surname = '", "Birthdate = '"]
i = 0
if AuthorID != 0:
coma_counter[0] +=1
if Name != '':
coma_counter[1] +=1
if Surname != 0:
coma_counter[2] +=1
if Birthdate != '':
coma_counter[3] +=1
j = sum(coma_counter)
List = ["UPDATE Authors SET "]
if AuthorID != 0 and j!=1:
List += attribute_list[0]
List += AuthorID
List += ","
j = j-1
elif AuthorID != 0 and j==1:
List += attribute_list[0]
List += AuthorID
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 Surname != '' and j!=1:
List += attribute_list[2]
List += Surname
List += "',"
j = j-1
elif Surname != '' and j==1:
List += attribute_list[2]
List += Surname
List += "'"
if Birthdate != '' and j!=1:
List += attribute_list[3]
List += Birthdate
List += "',"
j = j-1
elif Birthdate != '' and j==1:
List += attribute_list[3]
List += Birthdate
List += "'"
if AuthorID_KEY!= 0 or condition!='':
List += " Where "
if AuthorID_KEY != 0:
List += "AuthorID = '"
List += AuthorID_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_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 update_Category(Name ='', Parent_category = '', Name_KEY = '', condition = ''):
coma_counter = [0]*2
attribute_list = ["Name = '", "Parent_category = '"]
i = 0
if Name != '':
coma_counter[0] +=1
if Parent_category != '':
coma_counter[1] +=1
j = sum(coma_counter)
List = ["UPDATE Category 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 Parent_category != '' and j!=1:
List += attribute_list[1]
List += Parent_category
List += "',"
j = j-1
elif Parent_category != '' and j==1:
List += attribute_list[1]
List += Parent_category
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 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()
def connect():
# """Gets AOL Data and writes them to a MySQL table"""
db = "mysql_dl"
api = "tm"
# Connect To DB:
db_config = read_db_config(db)
try:
print('Connecting to database...')
conn = MySQLConnection(**db_config)
if conn.is_connected():
print('Connection established.')
cursor = conn.cursor()
# calls get_access_token function and starts script
logintoken = aol_api.get_access_token(api)
print(logintoken)
result = aol_api.run_existing_report(logintoken, "190599")
#print(result)
info = json.loads(result)
#print(info)
for x in json.loads(result)['data']:
rownum = ''
date = x['row'][0]
inventory_source = x['row'][1].replace("'", " -").replace('"', "")
geo_country = x['row'][2].replace("'", "")
media = x['row'][3].replace('"', "").replace("'", "")
ad_opportunities = x['row'][4]
ad_attempts = x['row'][5]
ad_impressions = x['row'][6]
ad_revenue = x['row'][7]
ecpm = x['row'][8]
ad_errors = x['row'][9]
iab_viewability_measurable_ad_impressions = x['row'][10]
iab_viewable_ad_impressions = x['row'][11]
market_ops = x['row'][12]
clicks = x['row'][13].replace(" ", "0")
list = (rownum, date, inventory_source, geo_country, media, ad_opportunities, ad_attempts, ad_impressions, \
ad_revenue, ecpm, ad_errors, iab_viewability_measurable_ad_impressions, iab_viewable_ad_impressions, market_ops, clicks)
#print(list)
sql = """INSERT INTO tm_InventorySources_v2 VALUES ("%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", \
"%s", "%s", "%s", "%s")""" % (rownum, date, inventory_source, geo_country, media, ad_opportunities, ad_attempts, ad_impressions, \
ad_revenue, ecpm, ad_errors, iab_viewability_measurable_ad_impressions, iab_viewable_ad_impressions, market_ops, clicks)
cursor.execute(sql)
cursor.execute('commit')
else:
print('Connection failed.')
except Error as error:
print(error)
finally:
conn.close()
print('Connection closed.')
def 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 _create_connection(self):
try:
cnx = MySQLConnection(**self._dbconfig)
return cnx
except Error as err:
raise err
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()
from mysql.connector import MySQLConnection con = MySQLConnection(host='localhost',database='mysql_study',user='******',password='******') print(con)
def connect():
# """Gets AOL Data and writes them to a MySQL table"""
db = "mysql_dl"
api = "adtrans"
report_book = [190605, 190606, 190607, 190608, 190609, 190610]
# Connect To DB:
db_config = read_db_config(db)
try:
print('Connecting to databse...')
conn = MySQLConnection(**db_config)
if conn.is_connected():
print('Connection established')
cursor = conn.cursor()
sql = "DROP TABLE IF EXISTS adtrans_InventorySources_v2"
cursor.execute(sql)
sql = "CREATE TABLE adtrans_InventorySources_v2 (rownum INT NOT NULL AUTO_INCREMENT PRIMARY KEY, date varchar(25), inventory_source varchar(255), geo_country varchar(50), \
media varchar(255), ad_opportunities bigint, ad_attempts bigint, ad_impressions bigint, ad_revenue decimal(15,5), \
ecpm decimal(6,4), ad_errors int, iab_viewability_measurable_ad_impressions bigint, \
iab_viewable_ad_impressions bigint, market_ops varchar(255), clicks int)"
cursor.execute(sql)
# calls get_access_token function and starts script
logintoken = aol_api.get_access_token(api)
print(logintoken)
for report in report_book:
result = aol_api.run_existing_report(logintoken, str(report))
#print(result)
print(str(report))
info = json.loads(result)
#print(info)
for x in json.loads(result)['data']:
rownum = ''
date = x['row'][0]
inventory_source = x['row'][1].replace("'", " -").replace(
'"', "")
geo_country = x['row'][2].replace("'", "")
media = x['row'][3].replace('"', "").replace("'", "")
ad_opportunities = x['row'][4]
ad_attempts = x['row'][5]
ad_impressions = x['row'][6]
ad_revenue = x['row'][7]
ecpm = x['row'][8]
ad_errors = x['row'][9]
iab_viewability_measurable_ad_impressions = x['row'][10]
iab_viewable_ad_impressions = x['row'][11]
market_ops = x['row'][12]
clicks = x['row'][13].replace(" ", "0")
list = (rownum, date, inventory_source, geo_country, media, ad_opportunities, ad_attempts, ad_impressions, \
ad_revenue, ecpm, ad_errors, iab_viewability_measurable_ad_impressions, iab_viewable_ad_impressions, market_ops, clicks)
#print(list)
sql = """INSERT INTO adtrans_InventorySources_v2 VALUES ("%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", "%s", \
"%s", "%s", "%s", "%s")""" % (rownum, date, inventory_source, geo_country, media, ad_opportunities, ad_attempts, ad_impressions, \
ad_revenue, ecpm, ad_errors, iab_viewability_measurable_ad_impressions, iab_viewable_ad_impressions, market_ops, clicks)
cursor.execute(sql)
cursor.execute('commit')
else:
print('Connection failed.')
except Error as error:
print(error)
finally:
conn.close()
print('Connection closed.')
