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