def insert_list_to_db(videos): try: connection = storage.connect() cursor = connection.cursor() count_row = 0 for mydict in videos: columns = ', '.join("`" + str(x).replace('/', '_') + "`" for x in mydict.keys()) values = ', '.join("'" + str(x).replace('/', '_') + "'" for x in mydict.values()) sql = "INSERT IGNORE INTO %s ( %s ) VALUES ( %s );" % ( 'videos_details', columns, values) cursor.execute(sql) connection.commit() if cursor.rowcount == 1: count_row = count_row + 1 print(count_row) #update is_done filed after insert to table. it's a flag to show it's taken. sql = "UPDATE youtube_list SET is_done= 1 WHERE video_id='%s' LIMIT 1" % ( row_id) cursor.execute(sql) connection.commit() cursor.close() except mysql.connector.Error as error: print("Failed to insert record into videos_details table {}".format( error)) finally: if (connection.is_connected()): connection.close() print("MySQL connection is closed")
def main(): config.parse_options() global_vars.storage_handle = storage.connect(global_vars.db_filename) model_list = generate_model_list() clear_tables(model_list) create_new_tables(model_list) storage.close(global_vars.storage_handle)
def insert_list_to_db(videos): try: connection = storage.connect() cursor = connection.cursor() count_row = 0 for mydict in videos: columns = ', '.join("`" + str(x).replace('/', '_') + "`" for x in mydict.keys()) values = ', '.join("'" + str(x).replace('/', '_') + "'" for x in mydict.values()) sql = "INSERT IGNORE INTO %s ( %s ) VALUES ( %s );" % ( 'youtube_list', columns, values) cursor.execute(sql) connection.commit() if cursor.rowcount == 1: count_row = count_row + 1 print(count_row) cursor.close() except mysql.connector.Error as error: print( "Failed to insert record into Youtube_list table {}".format(error)) finally: if (connection.is_connected()): connection.close() print("MySQL connection is closed")
def update_db(txx, rid, timestr, datestr): timebty = bytearray(timestr, 'utf-8') datebty = bytearray(datestr, 'utf-8') txx = bytearray(txx, 'utf-8') rid = bytearray(rid, 'utf-8') conn = storage.connect() c = conn.cursor(buffered=True) c.execute("SELECT * FROM veh_all_bl where veh_no=%s", (txx, )) row = c.fetchone() if not row: #print("Not exist") c.execute("SELECT MAX(veh_id) FROM veh_all_bl") row1 = c.fetchone() new_id = row1[0] + 1 #print(row1[0]) c.execute("INSERT INTO veh_all_bl(veh_id,veh_no) VALUES(%s,%s)", (new_id, txx)) time.sleep(0.1) c.execute( "INSERT INTO veh_lpinfotbl(lp_noid,ckpnt_id,lp_time,lp_date) VALUES(%s,%s,%s,%s)", (new_id, rid, timebty, datebty)) else: #print ("exist") c.execute("SELECT veh_id FROM veh_all_bl WHERE veh_no =%s", (txx, )) row2 = c.fetchone() c.execute( "INSERT INTO veh_lpinfotbl(lp_noid,ckpnt_id,lp_time,lp_date) VALUES(%s,%s,%s,%s)", (row2[0], rid, timebty, datebty)) time.sleep(0.1) conn.commit() c.close()
def delete_data(self): con = storage.connect() cur = con.cursor() cur.execute("delete from destinations where priority=%s", self.priority.get()) rows = cur.fetchall() con.commit() con.close() self.fetch_all() self.clear_it()
def fetch_all(self): con = storage.connect() cur = con.cursor() cur.execute("select * from destinations") rows = cur.fetchall() if len(rows) != 0: self.travel_table.delete(*self.travel_table.get_children()) for x in rows: self.travel_table.insert('', END, values=x) con.commit() con.close()
def search_all(self): con = storage.connect() cur = con.cursor() cur.execute("select * from destinations where " + str(self.search_drop.get()) + " LIKE '%" + str( self.search_text.get()) + "%'") rows = cur.fetchall() if len(rows) != 0: self.travel_table.delete(*self.travel_table.get_children()) for x in rows: self.travel_table.insert('', END, values=x) con.commit() con.close()
def connectandload(load_sql): try: conn = storage.connect() cursor = conn.cursor() cursor.execute(load_sql) print("Successfully loaded the table from csv.") except mysql.connector.Error as e: if e.errno == errorcode.ER_ACCESS_DENIED_ERROR: print("Something is wrong with your user name or password") elif e.eno == errorcode.ER_BAD_DB_ERROR: print("Database does not exist") else: print("Error: {}").format(str(e)) sys.exit(1) else: print("Successful execution!") conn.close()
def execute_geocoding(club_cluster): cnx = connect() cursor = cnx.cursor() cursor.execute('SELECT club_name from cluster_fount') clubs = cursor.fetchall() cnx.commit() cursor.close() for club in clubs: if club[0] not in str(club_cluster): validLetters = "abcdefghijklmnopqrstuvwxyz/:-|&.#0123456789 " club = ''.join([char for char in str(club).lower()[2:] if char in validLetters]) response = requests.get('https://maps.googleapis.com/maps/api/geocode/json?address=' + str(club) + ',Berlin,Germany&key=AIzaSyBnxwGFcisM77sVnpepLzoKXkEtt9Bnwlo') results = response.json() if len(results['results']) == 0: output = { "formatted_address": None, "latitude": None, "longitude": None, } else: answer = results['results'][0] output = { "formatted_address": answer.get('formatted_address'), "latitude": answer.get('geometry').get('location').get('lat'), "longitude": answer.get('geometry').get('location').get('lng'), } cursor = cnx.cursor() cursor.execute('UPDATE cluster_fount SET latitude="%s", longitude="%s", event_address="%s" WHERE club_name="%s"' % (output['latitude'], output['longitude'], output['formatted_address'], club)) cnx.commit() cursor.close() print("Uploaded Coordinates and Address Database")
def update_data(self): con = storage.connect() cur = con.cursor() cur.execute( "update destinations set name=%s, country=%s, climate=%s, activities=%s, travel_mode=%s, stay=%s where " "priority=%s", ( self.name.get(), self.country.get(), self.climate.get(), self.txt_activity.get('1.0', END), self.travel_mode.get(), self.stay.get(), self.priority.get() )) con.commit() self.fetch_all() self.clear_it() con.close()
def add_travels(self): if self.priority.get() == "" or self.name.get() == "": messagebox.showerror("Error", "Priority and Name cannot be blank") else: con = storage.connect() cur = con.cursor() cur.execute("insert into destinations values(%s, %s, %s, %s, %s, %s, %s)", ( self.priority.get(), self.name.get(), self.country.get(), self.climate.get(), self.txt_activity.get('1.0', END), self.travel_mode.get(), self.stay.get() )) con.commit() self.fetch_all() self.clear_it() con.close() messagebox.showinfo("Congrats", "New destination entered. You better start saving $$$!")
def chkdbtime(): try: conn = storage.connect() c = conn.cursor(buffered=True) c.execute( "SELECT UPDATE_TIME FROM information_schema.tables WHERE TABLE_SCHEMA = 'db_lp_ckpnt20193' AND TABLE_NAME = 'veh_all_bl'" ) c.execute( "SELECT UPDATE_TIME FROM information_schema.tables WHERE TABLE_SCHEMA = 'db_lp_ckpnt20193' AND TABLE_NAME = 'veh_lpinfotbl'" ) y = c.fetchone() tdb = y[0] #the data from db is tuple, convert it to date timetuple tdb = datetime.strptime(str(tdb), "%Y-%m-%d %H:%M:%S") tdb = str(tdb) conn.commit() c.close() return (tdb) except Exception as e: print(e) pass
def get_viol(): global told try: conn = storage.connect() c = conn.cursor(buffered=True) tnew = chkdbtime() if tnew > told: print("Updating viol list") #c.execute("SELECT veh_no FROM veh_all_bl WHERE bl_flg=%s",(bl,)) f = open('viol.txt', 'w') c.execute("SELECT veh_no FROM veh_all_bl WHERE bl_flg='1'") rows = c.fetchall() for row in rows: #print(row[0]) f.write(row[0] + '\n') f.close conn.commit() c.close() told = tnew else: print("no viol updates") except Exception as e: print(e) pass
from oauth2client.client import OAuth2WebServerFlow from werkzeug.exceptions import Unauthorized CLIENT_ID = os.environ['CLIENT_ID'] CLIENT_SECRET = os.environ['CLIENT_SECRET'] SCOPE = ['https://mail.google.com/', 'https://www.googleapis.com/auth/userinfo.email', 'https://www.googleapis.com/auth/gmail.readonly', 'https://www.googleapis.com/auth/gmail.modify', 'https://www.googleapis.com/auth/gmail.labels', 'https://www.googleapis.com/auth/gmail.settings.basic'] APP_SECRET_KEY = 'VfedCzx,eT88kj7A33^K' logger = log.build_logger(__name__) app = flask.Flask(__name__) conn = storage.connect(os.environ['DATABASE_URL']) storage.migrate(conn) app.config['DEBUG'] = True app.config['SECRET_KEY'] = APP_SECRET_KEY app.config['CONNECTION'] = conn app.config['TIMER'] = services.start_timer(conn) @app.route('/timer/start', methods=['POST']) def start(): conn = app.config['CONNECTION'] user_id = flask.session['user_id'] logger.info('starting timer for user: {}') storage.save_timer(conn, user_id, True) return flask.redirect(flask.url_for('index'))
def db_connect(self) -> ModLinkBotConnection: """Connect to the database.""" return connect(getattr(self.config, "database_path", "modlinkbot.db"))
import matplotlib.pyplot as plt import numpy as np import pandas as pd from pandas.plotting import register_matplotlib_converters register_matplotlib_converters() from matplotlib import dates as mpl_dates from matplotlib.ticker import (MultipleLocator, FormatStrFormatter, AutoMinorLocator) import datetime import storage conn = storage.connect() today = (datetime.datetime.now().strftime("%Y-%m-%d")) print(today) todayweek = (datetime.datetime.now().strftime("%W")) # Make the figure wider to see things better plt.figure(figsize=(12,6)) ### 2017 my_query2017 = ''' SELECT `day_of_year`, YEAR(d_utc) AS yeary, `temp_f_davg` , `temp_f_dmin` , `temp_f_dmax`, recs FROM `weather`.`v_E1248_daily` WHERE YEAR(d_utc)=2018 AND recs >200 ORDER BY day_of_year ASC;''' df1 = pd.read_sql_query(my_query2017,conn) tempf_vals2017 = df1['temp_f_davg'] dater2017 = df1['day_of_year'] lower_y_error2017 = df1['temp_f_dmin'] upper_y_error2017 = df1['temp_f_dmax'] y_error = [lower_y_error2017, upper_y_error2017] # plt.errorbar(x, y, yerr = y_error plt.errorbar(dater2017, tempf_vals2017, yerr = y_error, fmt='-o') plt.plot(dater2017, tempf_vals2017, linestyle="", color="red", linewidth=1, marker='o', label="2017", markersize=2) ### Max
from datetime import datetime import mysql.connector from mysql.connector import Error, errorcode import time import configparser import storage #connection to db and get token config = configparser.ConfigParser() config.read('config.ini') key = config['token']['key'] q = config['keyword']['q'] #Get start and end date try: connection = storage.connect() cursor = connection.cursor() #select a row of video's ID table, to get detail of the video. get_row = "SELECT * FROM youtube_list WHERE is_going=0 and is_done=0 LIMIT 1" cursor.execute(get_row) records = cursor.fetchall() #Check if there is a row to select or not if not records: print("there is no video ID left in youtube_list to select") sys.exit() #if there is a row, select it's video_id for record in records: row_id = record[0] connection.commit()
import storage as st collectors_db = st.connect() class Collector: def __init__(self, _id=None, id=None, fullName=None, cellphone=None, password=None): self._id = _id self.id = id self.fullName = fullName self.cellphone = cellphone self.password = password def get(self, _id): collector = collectors_db.collectors.find_one({'id': _id}, {'_id': False}) if collector: return collector else: return None
+ "£" + str(int(avprice))) except: print("Cannot calculate average") save_prompt = input("\nSave results as spreadsheet? y/n: ") if "y" in save_prompt: filename = f"{search_time}_{city}" df.to_csv(f"{filename}.csv") print(f"Saved data in file: '{filename}.csv'") else: print("No spreadsheet saved") print(f"Saving {len(proplist)} properties to {city.upper()} database...") storage.connect(city) properties_saved = 0 properties_existing = 0 for p in proplist: # consider adding tqdm - and removing print statements in storage if storage.insert(city, 'N/A', p['Price'], p['Address'], p['Beds'], 'N/A', 'N/A', p['Agent_Name'], p['Agent_tel'], p['Website'], p['Acquire_time']) == 'new': properties_saved += 1 else: properties_existing += 1 print( f"Saved {properties_saved} to {city} - {properties_existing} already in database" )
from flask_restful import Resource, reqparse, request from flask_jwt_extended import (create_access_token, create_refresh_token, jwt_required, jwt_refresh_token_required, get_jwt_identity) import Controllers.CollectorController as CollectorController import storage as st db = st.connect() # collection donde estan toda la info de los analistas CollectorCollection = db.collectors CollectorCodesCollection = db.collector_codes # para validar que el JSON del body tenga los campos parser = reqparse.RequestParser() parser.add_argument('id', help='This field cannot be blank', required=True) parser.add_argument('password', help='This field cannot be blank', required=True) # validar todo los campos al momento de registrar un collector parserCollector = reqparse.RequestParser() parserCollector.add_argument('id', help='This field cannot be blank', required=True) parserCollector.add_argument('fullName', help='This field cannot be blank', required=True) parserCollector.add_argument('password', help='This field cannot be blank', required=True) parserCollector.add_argument('cellphone',
import storage as st analist_db = st.connect() from bson.json_util import dumps class Analist: def __init__(self, _id=None, id=None, fullName=None, password=None, state=None): self._id = _id self.id = id self.fullName = fullName self.password = password self.state = state def get(self, _id): analist = analist_db.analists.find_one({'id': _id}, {'_id': False}) if analist: return analist else: return None def insert(self, id, fullName, password, state): analist = { "id": id, "fullName": fullName, "password": password,
def localstorage(): return json.dumps(connect())