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 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 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 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 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 main():
"""Entry point."""
db = mysql.connector.connect(option_files="config.conf")
print(f"connection number: {db.connection_id}"
f"\ncharacter settings: {db.charset}"
f"\ncollation: {db.collation}")
# Creating cursor using object method.
# The advantage of using cursor() method is that, it can provide arguments
# for the cursor and the method will return a cursor object using the
# appropriate cursor class.
# ARGUMENTS:
# buffered:, raw:, prepared:, cursor_class:, dictionary:, named_tuple:,
cursor1 = db.cursor()
cursor1.close()
# Creating cursor using the constructor.
# MySQLCursorBuffered - buffered result sets converts to PYthon types
# MySQLcursorRaw - returns raw results as byte array.
# MySQLCursorBufferedRaw - raw result set enables buffering.
# MySQLCursorDict - same as MySQLCursor, rows are returned as dictionaries.
# MySQLCursorBufferedDict - same as MySQLCursorBufferd, raws returns as dictionaries.
# MySQLCursorNamedTuple - same as MySQLCursor, rows returns as tuples.
# ...
cursor2 = MySQLCursor(db) # Unbuffered output converted to Python types
cursor2.close()
# Closing the cursor when done with it.
# It's ensures that the reference back to the connection
# object is deleted, thus avoiding memory leaks.
db.close()
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 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 UpdateBusinessUnit(db_connection: MySQLCursor, business_unit: int):
db_cursor = db_connection.cursor()
sql = "UPDATE business_units SET title=%s,summary=%s WHERE business_code = %s",
values = (business_unit.title, business_unit.summary,
business_unit.specific_code)
db_cursor.execute(sql, values)
db_connection.commit()
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 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 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 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 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 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 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 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 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 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 close_instance(self, connection: PooledMySQLConnection,
cursor: MySQLCursor) -> None:
if connection is not None and connection.is_connected():
if cursor is not None:
cursor.close()
connection.close()
self._logger.info(
f"MySQL connection is closed. - PID: {os.getpid()}")
else:
self._logger.info(
"Connection has been disconnect or be killed before.")
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 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 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 _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 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 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 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 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 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_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
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)
