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