def list_active_legislators_first(): query = 'select bioguide_id from bioguide_legislatorrole group by bioguide_id order by max(end_date) desc, bioguide_id' from MySQLdb import Connection cursor = Connection('localhost', 'capwords', 'capwords', 'capwords').cursor() cursor.execute(query) results = [x[0] for x in cursor.fetchall()] return results
def login_valid(username: str, password: str, connection: Connection) -> bool: """ Does this username and password combination exist? """ cursor = connection.cursor() cursor.execute(f""" SELECT incorrect_logins FROM {UsersDatabase.table_name} WHERE username LIKE "{username}"; """) cnt = int(cursor.fetchone()[0]) cursor.close() cursor1 = connection.cursor() if cnt < 3: cursor1.execute(f""" SELECT COUNT(*) FROM {UsersDatabase.table_name} WHERE username LIKE "{username}" AND password LIKE "{password}";""") x = cursor1.fetchone() cursor1.close() return x[0] > 0 else: freeze_window(username, password, connection)
def freeze_window(username: str, connection: Connection): cursor3 = connection.cursor() cursor3.execute(f""" SELECT freezestarttime FROM {UsersDatabase.table_name} WHERE username LIKE "{username}"; """) starttime = cursor3.fetchone()[0] flash(starttime) cursor3.close() print("starttime ", starttime) endtime = starttime + datetime.timedelta(seconds=45) print("endtime ", endtime) cursor2 = connection.cursor() if endtime < datetime.datetime.now(): cursor2.execute(f""" UPDATE {UsersDatabase.table_name} SET incorrect_logins = 0 WHERE username LIKE '{username}'; """) cursor2.close() else: flash("Your account is locked for 45 seconds, due to multiple failure attemps")
class MySQLAPI(object): """ MySQL API Usage: client = MySQLAPI( host='127.0.0.1', port=3306, user='******', password='******', db='your-database-name') client.connect() result = client.query('select * from table_a') """ __connected = False def __init__(self, host, port, user, password, db, timeout=3): self.__host = host self.__user = user self.__password = password self.__db = db self.__port = port self.__timeout = timeout def connect(self): try: self.__connect = Connection(host=self.__host, user=self.__user, passwd=self.__password, db=self.__db, port=self.__port, connect_timeout=self.__timeout) self.__cursor = self.__connect.cursor(DictCursor) self.__connected = True except Exception as e: raise MySQLAPIException(e) # connection status for etcd def is_connect(self): return self.__connected # check connection status for etcd def __checkStatus__(self): if not self.is_connect(): raise MySQLAPIException('Database NOT Connected') def __del__(self): try: if self.is_connect(): self.__cursor = None self.__connect.close() self.__connected = False except Exception as e: raise MySQLAPIException(e) @db_check_connect def query(self, sql): try: self.__cursor.execute(sql) return self.__cursor.fetchall() except Exception as e: raise MySQLAPIException(e)
def update_user(user: User, conn: MySQLdb.Connection, commit=True): cursor = conn.cursor() cursor.execute( "UPDATE users SET name=%s,email=%s,notification_via=%s,lang=%s WHERE chat_id=%s", (user.name, user.email, user.notification_via, user.lang), ) if commit: conn.commit() cursor.close()
def get_or_save_user(chat_id: str, username: str, email: str, notification_via: str, conn: MySQLdb.Connection): cursor = conn.cursor() cursor.execute( "IF NOT EXISTS (SELECT id FROM users WHERE chat_id=%s) THEN " "INSERT INTO users(chat_id, name, email, notification_via) VALUES(%s, %s, %s, %s);" "END IF;", (chat_id, chat_id, username, email, notification_via)) conn.commit() return get_user(conn, chat_id=chat_id)
def __init__(self, config): ''' Constructor ''' self.config=config conn=Connection(**config) if not conn: #print("connection failed") pass self.cursor=conn.cursor() self.conn=conn
def connect(self): try: self.__connect = Connection(host=self.__host, user=self.__user, passwd=self.__password, db=self.__db, port=self.__port, connect_timeout=self.__timeout) self.__cursor = self.__connect.cursor(DictCursor) self.__connected = True except Exception as e: raise MySQLAPIException(e)
def insert_train_in_db(train: Train, conn: MySQLdb.Connection, commit=True): if train.depart_date >= datetime.now() or train.check_daily: cursor = conn.cursor() cursor.execute( "INSERT INTO monitoring (train, station, departDate, user, check_daily, check_interval, coach, seat) " "VALUES(%s, %s, %s, %s, %s, %s, %s, %s)", (train.code, train.depart_stat, train.depart_date, train.user, train.check_daily, train.check_interval, train.coach, train.seat)) if commit: conn.commit() cursor.close() else: raise TrainInPastError( f"Train depart date: {train.depart_date}; now: {datetime.now()}")
def update_user_lang(conn: MySQLdb.Connection, user_id="", chat_id="", lang="it", commit=True): cursor = conn.cursor() if user_id: cursor.execute("UPDATE users SET lang=%s WHERE id=%s", (lang, user_id)) else: cursor.execute("UPDATE users SET lang=%s WHERE chat_id=%s", (lang, chat_id)) if commit: conn.commit() cursor.close()
def dump_table_to_dict(table_name: str, connection: Connection): """Given a table name, return a dictionary that contains all its data. Example: foo('users_table', <CONNECTION_OBJECT>) -> { 'data': ( ('henry','iliketofarm', ...), ('reshma','coolestmanager', ...), ), 'header': ('username', 'password', ...), } """ data = { table_name: { 'data': (), 'header': (), } } cursor = connection.cursor() cursor.execute(f"SELECT * FROM {table_name};") data[table_name]['data'] = cursor.fetchall() cursor.execute(f"SHOW columns FROM {table_name};") results = cursor.fetchall() # List of column header infos. results = (result[0] for result in results) # Get first field, the name. data[table_name]['header'] = results cursor.close() return data
def get_train(pk: int, conn: MySQLdb.Connection) -> Train: cursor = conn.cursor() cursor.execute( "SELECT id, train, station, departDate, user, checked, check_daily, check_interval, coach, seat " "FROM monitoring " "WHERE id=%s", (pk, )) row = cursor.fetchone() train = Train(*row) cursor.close() return train
def get_all_trains(chat_id: str, conn: MySQLdb.Connection) -> [Train]: trains = list() cursor = conn.cursor() cursor.execute( "SELECT id, train, station, departDate, user, checked, check_daily, check_interval, coach, seat " "FROM monitoring WHERE user=%s", (chat_id, )) for row in cursor: trains.append(Train(*row)) cursor.close() return trains
def get_station_from_code(station_code: str, conn: MySQLdb.Connection) -> Station: cursor = conn.cursor() cursor.execute("SELECT `name`, `code` FROM stations WHERE code=%s", (station_code, )) row = cursor.fetchone() if not row: return None station = Station(name=row[0], code=row[1]) cursor.close() return station
def login_invalid(username: str, password: str, connection: Connection) -> None: """ A bunch of code that should disable the login attemps for 300 seconds """ cursor = connection.cursor() cursor.execute(f""" SELECT incorrect_logins FROM {UsersDatabase.table_name} WHERE username LIKE "{username}"; """) cnt = int(cursor.fetchone()[0]) #flash(cnt) cursor.close() cursor1 = connection.cursor() if cnt < 2: cursor1.execute(f""" UPDATE {UsersDatabase.table_name} SET incorrect_logins = incorrect_logins + 1 WHERE username LIKE '{username}'; """) cursor1.close() elif cnt == 2: cursor1.execute(f""" UPDATE {UsersDatabase.table_name} SET incorrect_logins = incorrect_logins + 1 WHERE username LIKE '{username}'; """) cursor1.close() update_freezestart_time_to_now(username, connection) else: freeze_window(username, password, connection)
def get_user(conn: MySQLdb.Connection, user_id="", chat_id="") -> User: cursor = conn.cursor() if user_id: cursor.execute("SELECT * FROM users WHERE id=%s", (user_id, )) else: cursor.execute("SELECT * FROM users WHERE chat_id=%s", (chat_id, )) row = cursor.fetchone() if not row: return None user = create_user_from_row(row) cursor.close() return user
def get_trains_to_monitor(conn: MySQLdb.Connection) -> [Train]: train_lists = list() cursor = conn.cursor() max_date = datetime.now() + timedelta(hours=4) cursor.execute( "SELECT id, train, station, departDate, user, checked, check_daily, check_interval, coach, seat " "FROM monitoring WHERE (departDate BETWEEN %s AND %s" "OR check_daily=TRUE)", (datetime.now(), max_date)) for elems in cursor: train_lists.append(Train(*elems)) return train_lists
def get_subset_of_trains(chat_id: str, start: int, conn: MySQLdb.Connection) -> [Train]: trains = list() cursor = conn.cursor() cursor.execute( "SELECT id, train, station, departDate, user, checked, check_daily, check_interval, coach, seat " "FROM monitoring " "WHERE id >= %s AND user = %s LIMIT 7", (start, chat_id)) for row in cursor: trains.append(Train(*row)) cursor.close() return trains
def update_freezestart_time(username: str, time: str, connection: Connection) -> None: cursor = connection.cursor() cursor.execute(f""" UPDATE {UsersDatabase.table_name} SET freezestarttime = TIME('{time}') WHERE username LIKE '{username}'; """) cursor.close()
def db_connect(): while True: try: mysqldb = Connection(host=database['host'], user=database['user'], passwd=database['password'], db=database['db'], charset=database['charset']) except Exception: print('error') continue else: return mysqldb
def GenerateConn(dbname, host="localhost", port=3306, user="******", passwd="123456", charset='utf8'): conn = Connection(host=host, port=port, user=user, passwd=passwd, db=dbname, charset=charset) return conn
def update_login_time(username: str, time: str, connection: Connection) -> None: cursor = connection.cursor() cursor.execute(f""" UPDATE {UsersDatabase.table_name} SET last_logged_in = TIME('{time}') WHERE username LIKE '{username}'; """) cursor.close()
def get_user_last_logged_in(username: str, connection: Connection): cursor = connection.cursor() cursor.execute(f""" SELECT last_logged_in FROM {UsersDatabase.table_name} WHERE username LIKE "{username}"; """) return cursor.fetchone()[0]
def create_migrations_table_if_not_exists( mysql_connection: MySQLdb.Connection): cursor = mysql_connection.cursor() cursor.execute("SHOW TABLES LIKE 'migrations_applied'") if cursor.fetchone() is None: logger.info( 'Migrations table: "migrations_applied" does not exists. Creating.' ) cursor.execute("""CREATE TABLE `migrations_applied` ( `id` INT NOT NULL AUTO_INCREMENT, `name` VARCHAR(256) NOT NULL, `date` DATETIME NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=`utf8`;""")
def get_incorrect_logins(username: str, connection: Connection) -> int: cursor = connection.cursor() cursor.execute(f""" SELECT incorrect_logins FROM {UsersDatabase.table_name} WHERE username LIKE "{username}"; """) cnt = int(cursor.fetchone()[0]) cursor.close() return cnt
def user_exists(username: str, connection: Connection) -> bool: cursor = connection.cursor() cursor.execute(f""" SELECT COUNT(*) FROM {UsersDatabase.table_name} WHERE username LIKE "{username}" """) x = cursor.fetchone() cursor.close() return x[0] > 0
def create_connection(*args, **kwargs): print(kwargs) if not kwargs: raise ValueError("Must send connection details") if not kwargs.get("host", None) or not kwargs.get( "user", None) or not kwargs.get("passwd", None): raise ValueError("Arguments 'host', 'user' and 'passwd' are required") connection = None try: connection = Connection(host=kwargs.get("host", None), user=kwargs.get("user", None), passwd=kwargs.get("passwd", None), database=kwargs.get("database", "mysql")) print("Connection to MySQL DB successful") except Error as e: traceback.print_exc() raise return connection
def store_feedback(feedback: str, chat_id: str, conn: MySQLdb.Connection): cursor = conn.cursor() cursor.execute("INSERT INTO feedbacks(user, feedback) VALUES(%s, %s)", (chat_id, feedback)) conn.commit() cursor.close()
d2 = name[2:4] fullpath = os.path.join(PATH, d1, d2, name) print '-' * 80 print os.path.exists(fullpath), fullpath # if 0: #FIXME copy to tmp, backup. os.system('cp ' + fullpath + ' /tmp/ck/'+ name) if os.path.exists(fullpath): return edit_config(fullpath, name) else: return False, False if __name__ == '__main__': connection = Connection(host='127.0.0.1', port=3307, user='******', passwd='senhaderoot', db=DB) #connection = MySQLdb.connect('127.0.0.1:3307', 'root', 'senhaderoot', db = DB) c = connection.cursor() c2 = connection.cursor() c.execute('select contenthash, id from mdl_files where filename=%s', ['jsConfig.js']) count = 0 for i in c: print count size, hashname = move_file(i[0], count) if size: cmd = 'update mdl_files set filesize = %s where id = %s'%(size, i[1]) c2.execute(cmd) c2.connection.commit() count += 1
# -*- coding:utf-8 -*- __author__ = 'wanggen' import os from MySQLdb import Connection conn = Connection(host='wg-linux', user='******', passwd='wanggen', db='groupon', charset='utf8') cursor = conn.cursor() cursor.execute('select * from ecp_orders limit 10') cursor.close() a = """ ls -l ../ """ files = os.popen(a).read() with open('test', mode='wr') as f: f.write(files)
def has_mail_been_parsed(mail_id: str, conn: MySQLdb.Connection) -> bool: cursor = conn.cursor() cursor.execute( "SELECT EXISTS(SELECT from_mail_id FROM monitoring WHERE from_mail_id=%s)", (mail_id, )) return bool(cursor.fetchone())