def _find_attrs(
self, cursor: MySQLCursor, corpus_id: str, search: StructAttr, values:
List[str], fill: List[StructAttr]):
if len(values) == 0:
cursor.execute('SELECT 1 FROM dual WHERE false')
else:
cursor.execute(
f'''
SELECT
t.id,
GROUP_CONCAT(CONCAT(t_value.structure_name, '.', t_value.structattr_name, '=', t_value.value)
SEPARATOR '\n') as data
FROM (
SELECT DISTINCT value_tuple_id as id
FROM corpus_structattr_value AS t_value
JOIN corpus_structattr_value_mapping AS t_value_mapping ON t_value.id = t_value_mapping.value_id
WHERE corpus_name = %s AND structure_name = %s AND structattr_name = %s
AND value IN ({', '.join(['%s'] * len(values))})
) AS t
JOIN corpus_structattr_value_mapping AS t_value_mapping ON t_value_mapping.value_tuple_id = t.id
JOIN corpus_structattr_value AS t_value ON t_value_mapping.value_id = t_value.id
WHERE {' OR '.join(['(t_value.structure_name = %s AND t_value.structattr_name = %s)'] * len(fill))}
GROUP BY t.id
''',
(corpus_id, search.struct, search.attr, *values, *list(chain(*[[f.struct, f.attr] for f in fill]))))
def connectDatabase(username, password):
sshtunnel.SSH_TIMEOUT = 5.0
sshtunnel.TUNNEL_TIMEOUT = 5.0
with sshtunnel.SSHTunnelForwarder(
('ssh.pythonanywhere.com'),
ssh_username='******',
ssh_password='******',
remote_bind_address=(
'Gengruijie.mysql.pythonanywhere-services.com',
3306)) as tunnel:
connection = mysql.connector.connect(
user='******',
password='******',
host='127.0.0.1',
port=tunnel.local_bind_port,
database='Gengruijie$AutoGrading',
)
# Do stuff
query = "SELECT password, level, score from main where name = \"" + username + "\""
# print(query)
# cur = connection.cursor(buffered=True)
cursor = MySQLCursor(connection)
cursor.execute(query)
data = cursor.fetchall()
# print(data, password)
if data[0][0] != password:
return (False, 1, 1)
return (True, data)
def set_prefix(guild_id: int, prefix: Optional[str], cur: MySQLCursor = None):
cur.execute(
'''
UPDATE config SET prefix = %s
WHERE guild_id = %s
''', (prefix, guild_id))
cnx.commit()
def add_guild(guild_id: int, cur: MySQLCursor = None):
cur.execute(
'''
INSERT INTO config (guild_id) VALUES (%s)
ON DUPLICATE KEY UPDATE guild_id=guild_id
''', (guild_id, ))
cnx.commit()
def add_user(user: User, cur: MySQLCursor = None):
cur.execute('''
INSERT INTO users (id, name, discriminator)
VALUES (%s,%s,%s)
ON DUPLICATE KEY UPDATE id=id
''', (user.id, user.name, user.discriminator))
cnx.commit()
def init_shared(cur: MySQLCursor = None):
cur.execute('''
CREATE TABLE IF NOT EXISTS users (
key_id SMALLINT UNSIGNED AUTO_INCREMENT,
id BIGINT UNSIGNED NOT NULL UNIQUE,
name VARCHAR(32) NOT NULL,
discriminator SMALLINT UNSIGNED NOT NULL,
PRIMARY KEY (key_id)
);
CREATE TABLE IF NOT EXISTS channels (
key_id SMALLINT UNSIGNED AUTO_INCREMENT,
id BIGINT UNSIGNED NOT NULL UNIQUE,
name VARCHAR(100) NOT NULL,
PRIMARY KEY (key_id)
);
CREATE TABLE IF NOT EXISTS config (
guild_id BIGINT UNSIGNED NOT NULL,
prefix VARCHAR(255),
pins_channel BIGINT UNSIGNED,
channel_download_blacklist TEXT,
PRIMARY KEY (guild_id)
);
''', multi=True)
def show_all_entries(db_cursor: MySQLCursor, table_name: str):
print(table_name)
db_cursor.execute("SELECT * FROM " + table_name)
myresult = db_cursor.fetchall()
for x in myresult:
print(x)
def create_document_type_table(db_cursor: MySQLCursor):
title = "title VARCHAR(255) NOT NULL UNIQUE"
summary = "summary VARCHAR(255)"
document_code = "document_code INT NOT NULL PRIMARY KEY"
create_documenttype_table_cmd = "CREATE TABLE document_types (" + title + ", " + summary + ", " + document_code + ")"
db_cursor.execute(create_documenttype_table_cmd)
def get_pins_channel(guild_id: int, cur: MySQLCursor = None):
cur.execute(
'''
SELECT pins_channel FROM config
WHERE guild_id = %s
''', (guild_id, ))
result = cur.fetchone()
return result[0] if result else None
def create_business_unit_table(db_cursor: MySQLCursor):
title = "title VARCHAR(255) NOT NULL"
summary = "summary VARCHAR(255)"
business_code = "business_code INT NOT NULL PRIMARY KEY"
business_series_index = "business_series_index INT NOT NULL"
foreign_key_link = "FOREIGN KEY (business_series_index) REFERENCES business_series(business_series_index)"
create_bu_table_cmd = "CREATE TABLE business_units (" + title + ", " + summary + ", " + business_code + ", " + business_series_index + ", " + foreign_key_link + ")"
db_cursor.execute(create_bu_table_cmd)
def UpdateDocumentType(db_connection: MySQLConnection, db_cursor: MySQLCursor,
document_type: DocumentType):
sql = "UPDATE business_units SET title=%s, summary=%s WHERE document_code = %s"
values = (document_type.title, document_type.summary,
document_type.document_code)
db_cursor.execute(sql, values)
db_connection.commit()
def ReadBusinessUnit(db_cursor: MySQLCursor, business_code: int):
db_cursor.execute(
"SELECT title, business_code, summary FROM business_units WHERE business_code="
+ str(business_code))
myresult = db_cursor.fetchone()
my_bu = BusinessUnit(title=myresult[0],
specific_code=myresult[1],
summary=myresult[2])
return my_bu
def set_pins_channel(guild_id: int,
channel_id: Optional[int],
cur: MySQLCursor = None):
cur.execute(
'''
UPDATE config SET pins_channel = %s
WHERE guild_id = %s
''', (channel_id, guild_id))
cnx.commit()
def set_channel_download_blacklist(guild_id: int,
blacklist: str,
cur: MySQLCursor = None):
cur.execute(
'''
UPDATE config SET channel_download_blacklist = %s
WHERE guild_id = %s
''', (blacklist, guild_id))
cnx.commit()
def show_release_status_keys(cursor: MySQLCursor):
"""
Displays the release_status table
:param cursor: mySQL
"""
cursor.execute('SELECT * FROM release_status_key')
print('\nRelease status table')
print('release_statusID, abbreviation, description')
for row in cursor.fetchall():
print('{}, {}, {}'.format(*row))
def display():
conn = mysql.connector.connect(user='******', password='******', host='127.0.0.1' ,database='stocklaundry')
mycursor = MySQLCursor(conn)
mycursor.execute('SELECT `Item_name`, `Quantity`FROM ` balance_stock` WHERE Date= "2019-06-19"')
sbothval = mycursor.fetchall()
sbothvall = dict(sbothval)
print(sbothvall)
def ReadDocumentType(db_cursor: MySQLCursor, document_code: int):
db_cursor.execute(
"SELECT document_code, title, summary FROM document_types WHERE document_code="
+ str(document_code))
myresult = db_cursor.fetchone()
my_document = DocumentType(document_code=myresult[0],
title=myresult[1],
summary=myresult[2])
return my_document
def create_account(cursor: MySQLCursor, account: Account):
account_table_name = AccountTable.ACCOUNT_LIST.value
try:
create_account_query = ('INSERT INTO ' + account_table_name +
'(name, salt, verifier) VALUES (%s, %s, %s)')
create_account_data = (account.name.upper(), account.salt, account.verifier)
cursor.execute(create_account_query, create_account_data)
except (DataError, ProgrammingError, DatabaseError) as e:
Logger.error('[Account Manager]: (create_account) error {}'.format(e))
def show_products(cursor: MySQLCursor):
"""
Displays a list of all the products included in the database with their productID
:param cursor: mySQL
"""
cursor.execute(r'SELECT productID, product_name FROM product_list;')
results = cursor.fetchall()
print('\nCurrent product list:')
print('productID: product_name')
for productID, product_name in results:
print(f'{productID}: {product_name}')
def ReadRecord(db_cursor: MySQLCursor, serial_number:int):
db_cursor.execute("SELECT business_code, document_code, full_serial_number, status, title, custodian, revision, link, sow_no, issue_date, effective_date, \
reaffirmation_date, protection_lvl, ec_technical_data, permit, ecl, eccn, usml, cg, us_exemption, ca_exemption, exp_date, summary FROM \
records WHERE full_serial_number=" + str(serial_number))
myresult = db_cursor.fetchone()
my_record = Record(business_code = myresult[0], document_code = myresult[1], full_serial_number = myresult[2], status = myresult[3], title = myresult[4], custodian = myresult[5], \
revision = myresult[6], link = myresult[7], sow_no = myresult[8], issue_date = myresult[9], effective_date = myresult[10], reaffirmation_date = myresult[11], \
protection_lvl = myresult[12], ec_technical_data = myresult[13], permit = myresult[14], ecl = myresult[15], eccn = myresult[16], usml = myresult[17], \
cg = myresult[18], us_exemption = myresult[19], ca_exemption = myresult[20], exp_date = myresult[21], summary = myresult[22])
return my_record
def get_channel_download_blacklist(guild_id: int,
cur: MySQLCursor = None
) -> Optional[List[int]]:
cur.execute(
'''
SELECT channel_download_blacklist FROM config
WHERE guild_id = %s
''', (guild_id, ))
result = cur.fetchone()
if result:
return _split_channel_download_blacklist(result[0])
return []
def execute_none(sql: str, *params):
"""
Executes a query that does not select any rows.
:param str sql: The SQL statement.
:param params: The values for the statement.
:return: int The number of affected rows.
"""
cursor = MySQLCursor(StaticDataLayer.connection)
cursor.execute(sql, params)
cursor.close()
return cursor.rowcount
def ReadRecord(db_cursor: MySQLCursor, serial_number: int):
db_cursor.execute(
"SELECT full_serial_number, title, business_code, document_code, summary FROM records WHERE full_serial_number="
+ str(serial_number))
myresult = db_cursor.fetchone()
my_record = Record(full_serial_number=myresult[0],
title=myresult[1],
business_code=myresult[2],
document_code=myresult[3],
summary=myresult[4])
return my_record
def _get_most_recent_update(guild_id: int, channel_id: int,
cur: MySQLCursor) -> Optional[dt.datetime]:
typecheck(guild_id, int, 'guild_id')
cur.execute(f'''
SELECT MAX(timestamp) FROM g{guild_id}_messages as msgs
INNER JOIN channels ON (channels.key_id = msgs.channel)
WHERE channels.id = %s
GROUP BY channel;
''', (channel_id,))
timestamp = cur.fetchone()
return timestamp[0] if timestamp else None
def generate_tags(guild_id: int, cur: MySQLCursor = None):
typecheck(guild_id, int, 'guild_id')
logger.info(f'Generating part of speech tags for server {guild_id}')
cur.execute(f'''
SELECT users.id, channels.id, content FROM g{guild_id}_messages AS msgs
INNER JOIN users ON (msgs.user = users.key_id)
INNER JOIN channels ON (msgs.channel = channels.key_id)
WHERE content <> ''
''')
for user_id, channel_id, content in cur:
for tag, word in _get_tags(content):
_insert_word(guild_id, user_id, channel_id, tag, word)
def ReadAllBusinessSeries(db_cursor: MySQLCursor):
bs_list = []
db_cursor.execute(
"SELECT business_series_index, title FROM business_series")
bs_tuple = db_cursor.fetchall()
sorted_bs_tuple = sorted(bs_tuple, key=lambda tup: tup[0])
for business_series in sorted_bs_tuple:
business_series_index = business_series[0]
title = business_series[1]
bs = BusinessSeries(specific_code=business_series_index, title=title)
bs_list.append(bs)
return bs_list
def ReadAllBusinessUnits(db_cursor: MySQLCursor):
bu_list = []
db_cursor.execute(
"SELECT title, business_code, summary FROM business_units")
bu_tuple = db_cursor.fetchall()
for business_unit in bu_tuple:
title = business_unit[0]
code = business_unit[1]
summary = business_unit[2]
bu = BusinessUnit(specific_code=code, title=title, summary=summary)
bu_list.append(bu)
return bu_list
def init_guild(guild: Guild, cur: MySQLCursor = None):
guild_id = guild.id
typecheck(guild_id, int, 'guild_id')
cur.execute(f'''
CREATE TABLE IF NOT EXISTS g{guild_id}_messages (
id BIGINT UNSIGNED NOT NULL,
user SMALLINT UNSIGNED NOT NULL,
channel SMALLINT UNSIGNED NOT NULL,
timestamp DATETIME NOT NULL,
content TEXT NOT NULL,
images TEXT,
PRIMARY KEY (id),
INDEX (user),
INDEX (channel)
);
CREATE TABLE IF NOT EXISTS g{guild_id}_markov (
user SMALLINT UNSIGNED NOT NULL,
channel SMALLINT UNSIGNED NOT NULL,
base TEXT,
potentials MEDIUMTEXT,
INDEX (user),
INDEX (channel),
INDEX (base(32))
);
CREATE TABLE IF NOT EXISTS g{guild_id}_pos_tags (
user SMALLINT UNSIGNED NOT NULL,
channel SMALLINT UNSIGNED NOT NULL,
tag VARCHAR(8) NOT NULL,
word TEXT NOT NULL,
use_count MEDIUMINT UNSIGNED DEFAULT 0,
INDEX (user),
INDEX (channel),
INDEX (tag(3)),
UNIQUE KEY unique_entry (user, channel, tag, word(32))
);
CREATE TABLE IF NOT EXISTS g{guild_id}_pins (
original BIGINT UNSIGNED NOT NULL,
pin BIGINT UNSIGNED NOT NULL
);
''', multi=True)
db_config.add_guild(guild_id)
for channel in guild.text_channels:
add_channel(channel)
def create_documents_table(db_cursor: MySQLCursor):
title = "title VARCHAR(255) NOT NULL PRIMARY KEY"
document_path = "document_path VARCHAR(255) NOT NULL UNIQUE"
full_serial_number = "full_serial_number INT NOT NULL"
business_code = "business_code INT NOT NULL"
document_code = "document_code INT NOT NULL"
foreign_key_business = "FOREIGN KEY (business_code) REFERENCES business_units(business_code)"
foreign_key_document = "FOREIGN KEY (document_code) REFERENCES document_types(document_code)"
foreign_key_serial = "FOREIGN KEY (full_serial_number) REFERENCES records(full_serial_number)"
create_document_table_cmd = "CREATE TABLE document_files (" + title + ", " + document_path + ", " + full_serial_number
create_document_table_cmd = create_document_table_cmd + ", " + business_code + ", " + document_code
create_document_table_cmd = create_document_table_cmd + ", " + foreign_key_business + ", " + foreign_key_document + ", " + foreign_key_serial + ")"
db_cursor.execute(create_document_table_cmd)
def update_account(cursor: MySQLCursor, account: Account):
account_table_name = AccountTable.ACCOUNT_LIST.value
try:
update_account_query = ('UPDATE ' + account_table_name +
' SET ip = %s, timezone = %s, os = %s, platform = %s, locale = %s' +
' WHERE name = %s'
)
update_account_data = (
account.ip_addr, account.timezone, account.os, account.platform, account.locale, account.name.upper()
)
cursor.execute(update_account_query, update_account_data)
except (DataError, ProgrammingError, DatabaseError) as e:
Logger.error('[Account Manager]: (update_account) error {}'.format(e))
def execute_none(sql, *params):
"""
Executes a query that does not select any rows. Returns the number of affected rows.
:param str sql: The SQL statement.
:param iterable params: The values for the statement.
:rtype: int
"""
cursor = MySQLCursor(StaticDataLayer.connection)
StaticDataLayer.last_sql = sql
cursor.execute(sql, params)
rowcount = cursor.rowcount
cursor.close()
return rowcount
def execute_multi(sql):
"""
Executes a multi query that does not select any rows.
:param str sql: The SQL statements.
"""
cursor = MySQLCursor(StaticDataLayer.connection)
StaticDataLayer.last_sql = sql
for _ in cursor.execute(sql, multi=True):
pass
cursor.close()
def execute_sp_none(sql: str, *params) -> int:
"""
Executes a stored procedure that does not select any rows.
:param str sql: The SQL call the the stored procedure.
:param params: The arguments for the stored procedure.
:return: The number of affected rows.
"""
cursor = MySQLCursor(StaticDataLayer.connection)
itr = cursor.execute(sql, params, multi=True)
result = itr.__next__()
n = result.rowcount
cursor.close()
return n
def execute(self, query, params):
"""Execute an SQL query.
This method prepares the query for use with MySQL and then
calls the (superclass) MySQLCursor.execute method.
This is for compatability with SQL statements which were
written for SQLite."""
# Replace placeholders.
query = re.sub('\?', '%s', query)
# Remove column type instructions.
query = re.sub('AS "([a-z]+) \[timestamp\]"', '', query)
return MySQLCursor.execute(self, query, params)
def execute_sp_none(sql, *params):
"""
Executes a stored routine that does not select any rows. Returns the number of affected rows.
:param str sql: The SQL calling the stored procedure.
:param iterable params: The arguments for the stored procedure.
:rtype: int
"""
cursor = MySQLCursor(StaticDataLayer.connection)
StaticDataLayer.last_sql = sql
itr = cursor.execute(sql, params, multi=True)
result = itr.__next__()
rowcount = result.rowcount
cursor.close()
return rowcount