def Add_Trans(self, particulars: str, _type: str, amount: float, date: str):
cursor = conn.cursor()
try:
cursor.execute(f"""
INSERT INTO transactions (
user_id,
username,
particulars,
exp_type,
amount,
exp_date
)
VALUES (
{self.user.user_id},
'{self.user.uname}',
'{particulars}',
'{_type}',
{amount},
"{date}"
);
""")
conn.commit()
Popup("Transaction successfully added.")
self.win.Refresh()
except SQLErrors.ProgrammingError:
PopupError("ERROR: Invalid details.\nRectify and try again.")
cursor.close()
def create_trip(keyword, location_name, user, lpnum):
'''
Create trip from location's go next
'''
go_nexts = get_location_go_nexts(location_name)
if not go_nexts:
raise ValueError('Location {} does not exist'.format(location_name))
create_trip_location(keyword, location_name, lpnum)
create_location_image(location_name)
# Get info and create location for all go nexts
num_go_nexts = min(len(go_nexts), 5)
for location in go_nexts[:num_go_nexts]:
name = location[0]
coords = location[1]
if name:
create_trip_location(keyword, name, lpnum)
create_location_image(name)
# Create trip
cur.execute('''
INSERT INTO Trip
VALUES ('{}', '{}', {})
'''.format(keyword, location_name, lpnum))
conn.commit()
def create_account(u: Union[str, int], p: Union[str, int], e: Union[str, int], f: str, l: str) -> None:
cursor = conn.cursor()
e = 'NULL' if e in (None, '') else e
try:
'''
Creates a new entry in the db.
Required two cases as the email field is optional
'''
if e != 'NULL':
cursor.execute(f"""
INSERT INTO users (username,passwd,email_id,first_name,last_name)
VALUES ('{u}','{p}','{e}','{f}','{l}');
""")
else:
cursor.execute(f"""
INSERT INTO users (username,passwd,email_id,first_name,last_name)
VALUES ('{u}','{p}',{e},'{f}','{l}');
""")
conn.commit()
except ConnectionError:
Popup("Error Connecting to Database.",
"Please try again or restart the program")
cursor.close()
def create_location_image(location_name):
cur.execute(
"SELECT * FROM Photo WHERE LocationName='{}'".format(location_name))
photo = cur.fetchone()
if photo:
return
key = 'AIzaSyCoIJcakxVen5qGdu_PsV_ajdl33qwGskI'
rv = requests.get(
'https://maps.googleapis.com/maps/api/place/autocomplete/json?key={}&input={}&type=geocode&'
.format(key, location_name))
data = rv.json()
if data['status'] != 'OK':
return
location = data['predictions'][0]
place_id = location['place_id']
rv = requests.get(
'https://maps.googleapis.com/maps/api/place/details/json?key={}&placeid={}'
.format(key, place_id))
data = rv.json()['result']
if 'photos' not in data:
url = None
else:
photo_ref = data['photos'][0]['photo_reference']
url = 'https://maps.googleapis.com/maps/api/place/photo?maxwidth=400&photoreference={}&key={}'.format(
photo_ref, key)
cur.execute("INSERT INTO Photo VALUES ('{}', '{}')".format(
url, location_name))
conn.commit()
def delete_past_trip(username, keyword, lpnum):
cur.execute('''
DELETE FROM TripUser
WHERE Username='******' AND TripKeyword='{}' AND LPNum={}
'''.format(username, keyword, lpnum))
conn.commit()
profile_url = "{}/{}".format(url, username)
return redirect(profile_url)
def _add_user(email, location):
try:
cursor.execute('''INSERT INTO subscribers(email, city)
VALUES(?,?)''', (email, location))
logger.debug('User %s added', email)
conn.commit()
except sqlite3.IntegrityError as er:
return "User already exists"
return "User Added"
def create_user(username, password, name):
user = get_user_by_username(username)
if user:
return False
else:
cur.execute("INSERT INTO User VALUES ('{}', '{}', '{}', {})".format(
username, password, name, True))
conn.commit()
return True
def like_trip(username, keyword, lpnum, like):
like = like == 'True'
cur.execute('''
UPDATE TripUser
SET Assessment={}
WHERE TripKeyword='{}' AND Username='******' AND LPNum={}
'''.format(like, keyword, username, lpnum))
conn.commit()
return redirect("{}/{}/search/{}/{}".format(url, username, keyword, lpnum))
def insert_time_sql(id, time, date, content):
dt = datetime.datetime.now().strftime("%Y-%m-%d")
dt = '2012-12-12'
sql = "INSERT INTO time (id,time_s,date_s,content) VALUES(%s,%s,'%s','%s')" % (
id, time, date, content)
try:
# 执行SQL语句
cursor.execute(sql)
conn.commit()
return 1
except:
print("Error: unable to fetch data")
return 0
def importExcelToMysql(path):
workbook=xlrd.open_workbook(path)
sheets=workbook.sheet_names()
worksheet=workbook.sheet_by_name(sheets[0])
#把以前的删了
sql = "delete from user"
cur.execute(sql)
##遍历行
for i in range(1,worksheet.nrows):
row=worksheet.row(i)
##初始化数组
sqlstr=[]
##遍历列
for j in range(0,worksheet.ncols):
##构造数组
sqlstr.append(worksheet.cell_value(i,j))
##tb_play_type表
valuestr=[int(sqlstr[0]),str(int(sqlstr[1])),str(sqlstr[2])]
print(valuestr)
try:
# 执行SQL语句
#sql = "insert into user(id,password,name) vlaues(5,'5','好人')" # (%s,'%s','%s')" % (int(sqlstr[0]),str(int(sqlstr[1])),str(sqlstr[2]))
sql = "INSERT INTO user(id,password,name) VALUES(%s,'%s','%s')" % (int(sqlstr[0]),str(int(sqlstr[1])),str(sqlstr[2]))
cur.execute(sql)
except:
print("Error: unable to fetch data")
##tb_play_type表
cur.close()
conn.commit()
conn.close()
#打印信息
print("恭喜，数据导入成功！")
if correct_info == True:
for (key, value) in sign_details.items():
if key not in ('pass_conf', 'name_check', 'pass_check'):
sign_dets.append(value)
if key != 'u_name':
dets = dets + ',' + repr(value)
else:
dets += repr(value)
print(dets)
query = f'insert into users {user_fields} values ({dets});'
print(query)
signup_cursor = conn.cursor()
signup_cursor.execute(query)
conn.commit()
if check_of_existence_of(sign_details['u_name']):
lt = [[sg.Text('Signup Success!')],
[sg.Button('Return to Login')]]
success = sg.Window('Success', lt)
while True:
s_eve, s_val = success.Read()
if s_eve == 'Return to Login' or s_eve is None:
success.Close()
signup.Hide()
login_window.UnHide()
signup_active = False
break
elif correct_info == False and error == 'incorrect_vals':
def change_user_logged_in(username, logged_in):
cur.execute("UPDATE User SET LoggedIn={} WHERE Username='******'".format(
logged_in, username))
conn.commit()
def create_trip_user(keyword, username, lpnum):
trip_user = get_trip_user(keyword, username, lpnum)
if not trip_user:
cur.execute("INSERT INTO TripUser VALUES ('{}','{}', {}, 0)".format(
keyword, username, lpnum))
conn.commit()
def create_trip_location(keyword, location_name, lpnum):
cur.execute("INSERT INTO TripLocation VALUES ('{}','{}', {})".format(
keyword, location_name, lpnum))
conn.commit()
