def insert_into(connection: MySQLConnection, table, ip_ranges: Deque[CIDR], comment: str, delete_old: bool = False):
try:
count_where_list = f"SELECT COUNT(*) FROM {table} WHERE list = %s"
cursor = connection.cursor()
cursor.execute(count_where_list, (FIREWALL_LIST,))
old_count = cursor.fetchone()[0]
cursor.close()
if not delete_old:
print(f"Před vložením nových dat je v tabulce {table} {old_count} řádků.")
else:
print(f"Z tabulky {table} bude odstraněno {old_count} řádků.")
delete_where_list = f"DELETE FROM {table} WHERE list = %s"
cursor = connection.cursor()
cursor.execute(delete_where_list, (FIREWALL_LIST,))
cursor.close()
insert_query = (f"INSERT INTO {table} (address, mask, list, comment, disabled) "
"VALUES (%s, %s, %s, %s, %s)")
cursor = connection.cursor()
cursor.executemany(insert_query,
[(ip.ip, ip.suffix, FIREWALL_LIST, comment, 0) for ip in ip_ranges])
connection.commit()
cursor.close()
print(f"Bylo vloženo {len(ip_ranges)} řádků.")
except mysql.connector.Error:
connection.rollback()
raise
def query3(conn: MySQLConnection):
def preload_data():
return # no need to preload data, use the sample data from the database
preload_data()
cursor = conn.cursor()
sql = "SELECT (SELECT COUNT(DISTINCT cplate) FROM rent_records " \
"WHERE (DATE(date_from) BETWEEN DATE(%s) AND DATE(%s)) AND " \
"(TIME(date_from) BETWEEN TIME(%s) AND TIME(%s)))/(SELECT COUNT(*) FROM cars) AS Morning, " \
"(SELECT COUNT(DISTINCT cplate) FROM rent_records " \
"WHERE (DATE(date_from) BETWEEN DATE(%s) AND DATE(%s)) AND " \
"(TIME(date_from) BETWEEN TIME(%s) AND TIME(%s)))/(SELECT COUNT(*) FROM cars) AS Afternoon, " \
"(SELECT COUNT(DISTINCT cplate) FROM rent_records " \
"WHERE (DATE(date_from) BETWEEN DATE(%s) AND DATE(%s)) AND " \
"(TIME(date_from) BETWEEN TIME(%s) AND TIME(%s)))/(SELECT COUNT(*) FROM cars) AS Evening"
d1 = getstr(datetime(2018, 9, 1))
d2 = getstr(datetime(2018, 9, 7))
mor1 = time(7, 0)
mor2 = time(10, 0)
aft1 = time(12, 0)
aft2 = time(14, 0)
eve1 = time(17, 0)
eve2 = time(19, 0)
value = (d1, d2, mor1, mor2, d1, d2, aft1, aft2, d1, d2, eve1, eve2)
cursor.execute(sql, value)
return cursor.fetchall(), [i[0] for i in cursor.description]
def UpdateRecord(db_connection: MySQLConnection, record: Record):
db_cursor = db_connection.cursor()
sql = "UPDATE records SET title=%s, summary=%s WHERE full_serial_number = %s"
values = (record.title, record.summary, record.full_serial_number)
db_cursor.execute(sql, values)
db_connection.commit()
def query2(conn: MySQLConnection):
cursor = conn.cursor()
date = datetime(2018, 10, 5, 0, 0, 0)
def preload_data():
# get five charging stations to be used for statistics
sql = "SELECT * FROM charging_stations LIMIT 5"
cursor.execute(sql)
# get ids and # of sockets of five stations
data = [station for station in cursor.fetchall()]
rand_stations_id = [x[0] for x in data]
no_of_socket = [x[3] for x in data]
sql = "INSERT INTO charging_station_sockets (station_id, no_of_available_sockets, date_time) " \
"VALUES (%s, %s, %s)"
date_times = [date + timedelta(hours=x) for x in range(24)]
for i in range(len(rand_stations_id)):
for date_time in date_times:
value = (rand_stations_id[i], randint(0, no_of_socket[i]),
getstr(date_time))
cursor.execute(sql, value)
conn.commit()
preload_data()
query = "SELECT CONCAT(HOUR(css.date_time), 'h-', HOUR(css.date_time) + 1, 'h') AS Period, " \
"SUM(cs.total_no_of_sockets - css.no_of_available_sockets) AS OccupiedSockets " \
"FROM charging_station_sockets AS css " \
"INNER JOIN charging_stations AS cs ON css.station_id = cs.id " \
"WHERE DATE(css.date_time) = DATE(%s) GROUP BY TIME(css.date_time)"
val = (getstr(date), )
cursor.execute(query, val)
return cursor.fetchall(), [i[0] for i in cursor.description]
def UpdateBusinessUnit(db_connection: MySQLConnection, 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 CreateNewRecord(db_connection: MySQLConnection,
serial_number:int,
status:str,
title:str,
record_custodian_state:str,
record_revision_state:str,
record_link_state:str,
record_sow_state:str,
record_issue_date_state:str,
record_effective_date_state:str,
record_reaffirmation_date_state:str,
record_protection_lvl_state:str,
record_ec_state:bool,
record_permit_state:str,
record_ecl_state:str,
record_eccn_state:str,
record_usml_state:str,
record_cg_state:str,
record_us_exemption_state:str,
record_ca_exemption_state:str,
record_exp_date_state:str,
summary:str ="NA" ):
db_cursor = db_connection.cursor()
str_code = str(serial_number)
business_series = str_code[0]+"0"
business_code = str_code[0:2]
document_code = str_code[2:4]
sql = "INSERT INTO records (business_series_index, 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) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s) "
value = (business_series,
business_code,
document_code,
str_code,
status,
title,
record_custodian_state,
record_revision_state,
record_link_state,
record_sow_state,
record_issue_date_state,
record_effective_date_state,
record_reaffirmation_date_state,
record_protection_lvl_state,
record_ec_state,
record_permit_state,
record_ecl_state,
record_eccn_state,
record_usml_state,
record_cg_state,
record_us_exemption_state,
record_ca_exemption_state,
record_exp_date_state,
summary)
db_cursor.execute(sql, value)
db_connection.commit()
def UpdateDocumentType(db_connection: MySQLConnection,
document_type: DocumentType):
db_cursor = db_connection.cursor()
sql = "UPDATE document_types 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 prepare_business_series(db_connection: MySQLConnection):
db_cursor = db_connection.cursor()
sql = "INSERT INTO business_series (business_series_index, title) VALUES (%s, %s)"
val = [('10', 'Corporate'), ('20', 'Export Controls'),
('30', 'Project Controls'), ('40', 'Engineering'),
('50', 'Operations'), ('60', 'IT'), ('70', 'QMS'),
('80', 'unassigned'), ('90', 'Office General')]
db_cursor.executemany(sql, val)
db_connection.commit()
def UpdateRecord(db_connection: MySQLConnection, record: Record):
db_cursor = db_connection.cursor()
sql = "UPDATE records SET status=%s, title=%s, custodian=%s, revision=%s, link=%s, sow_no=%s, \
issue_date=%s, effective_date=%s, reaffirmation_date=%s, protection_lvl=%s, ec_technical_data=%s, permit=%s, ecl=%s, eccn=%s, usml=%s, cg=%s, \
us_exemption=%s, ca_exemption=%s, exp_date=%s, summary=%s WHERE full_serial_number = %s"
values = (record.status, record.title, record.custodian, record.revision, record.link, record.sow_no, record.issue_date, record.effective_date,\
record.reaffirmation_date, record.protection_lvl, record.ec_technical_data, record.permit, record.ecl, record.eccn, record.usml, record.cg, \
record.us_exemption, record.ca_exemption, record.exp_date, record.summary, record.full_serial_number)
db_cursor.execute(sql,values)
db_connection.commit()
def CreateNewDocumentType(db_connection: MySQLConnection,
document_code: int,
title: str,
summary: str = "NA"):
db_cursor = db_connection.cursor()
sql = "INSERT INTO document_types (document_code, title, summary) VALUES (%s, %s, %s) "
value = (str(document_code), title, summary)
db_cursor.execute(sql, value)
db_connection.commit()
class MySQL:
def __init__(self):
self.host = "localhost"
self.user = "******"
self.password = "******"
self.database = "RaspberryData"
def Conexion(self):
try
self.mydb = MySQLConnection(
host=self.host,
user=self.user,
password=self.password,
database=self.database
)
except Exception as e
self.mydb = MySQLConnection(
host="localhost",
user="******",
password="******",
database="RaspberryData"
)
return "Conexion a MySQL exitosa"
# DHT----------------------------------------------------------------
def guardarDatos(self, data):
self.sql = "insert into Sensors (IDName, Data, Type) values (%s, %s, %s)"
self.mycursor = self.mydb.cursor()
self.mycursor.execute(self.sql, (data['name'], str(data['data']), data['type']))
self.mydb.commit()
def getSensors(self):
self.sql = "select * from sensors_inst"
try:
self.mycursor = self.mydb.cursor()
self.mycursor.execute(self.sql)
data = self.mycursor.fetchall()
return data
except Exception as e:
raise e
class MysqlImport(SqlImport):
BATCH_SIZE = 1000
def __init__(self, host, port, user, password, db, log):
self.context = MySQLConnection(host=host,
port=port,
user=user,
password=password,
database=db)
self.cursor = self.context.cursor()
self.log = log
def run(self, parser, limit=0):
lines = []
keys = []
pbar = tqdm(total=parser.get_size())
count = 0
while limit == 0 or count <= limit:
line = parser.parse_line()
if line:
lines.append(tuple(line.values()))
keys = line.keys()
count += 1
if len(lines) >= self.BATCH_SIZE or not line:
try:
self.cursor.executemany(self.build_insert(parser, keys),
lines)
pbar.update(len(lines))
except IntegrityError as e:
self.log.error(str(e))
lines = []
if not line:
break
self.context.commit()
def run_script(self, filepath):
for line in open(filepath):
self.cursor.execute(line)
self.log.info('Ran script', filepath)
def truncate_table(self, table):
self.cursor.execute('TRUNCATE TABLE ' + table)
def close(self):
self.context.commit()
self.cursor.close()
self.context.close()
def CreateNewBusinessUnit(db_connection: MySQLConnection,
title: str,
code: int,
summary: str = "NA"):
db_cursor = db_connection.cursor()
str_code = str(code)
business_series = str_code[0] + "0"
sql = "INSERT INTO business_units (title, summary, business_code, business_series_index) VALUES (%s, %s, %s, %s) "
value = (title, summary, str_code, business_series)
db_cursor.execute(sql, value)
db_connection.commit()
def __init__(self, oriData: NewFileProcessor, session: MySQLConnection,
date: str) -> None:
"""
读入数据：从正式处理程序里读取DataFrame格式的数据
"""
assert isinstance(oriData, NewFileProcessor)
assert isinstance(oriData.insertDataFrame, DataFrame)
self.oriData = oriData.insertDataFrame.copy() # 要上传的数据（没有转化格式）
self.oriData_rows, self.oriData_cols = self.oriData.shape # 获取数据尺寸
self.path = oriData.fileFullPath
self.session = session # 数据库连接
self.cur = session.cursor() # 数据库游标
self.startDate = date # 第一周对应的日期
def query8(conn: MySQLConnection):
cursor = conn.cursor()
start_date = datetime(2018, 8, 1)
end_date = datetime(2018, 8, 31)
def preload_data():
# get one customer_id to demostrate how our query works
sql = "SELECT id FROM customers WHERE username = %s"
val = ("Liza", )
cursor.execute(sql, val)
customer_id = cursor.fetchone()[0]
# get 10 car plates to add them to renting and charge records
sql = "SELECT plate FROM cars ORDER BY RAND() LIMIT 10"
cursor.execute(sql)
cplates = [car[0] for car in cursor.fetchall()]
# get one random charging station id to add it to charge records
sql = "SELECT id FROM charging_stations ORDER BY RAND() LIMIT 1"
cursor.execute(sql)
station_id = cursor.fetchone()[0]
rent_sql = "INSERT INTO rent_records (date_from, date_to, cid, cplate, distance) " \
"VALUES (%s, %s, %s, %s, %s)"
charge_sql = "INSERT INTO charge_records (date_time, sid, cplate, price) " \
"VALUES (%s, %s, %s, %s)"
for cplate in cplates:
# insert rent record
date_from = get_fake_date_time(start=start_date,
end=end_date -
timedelta(days=1, hours=3))
date_to = get_fake_date_time(start=date_from,
end=date_from +
timedelta(hours=2, minutes=59))
val = (getstr(date_from), getstr(date_to), customer_id, cplate,
randint(10, 100))
cursor.execute(rent_sql, val)
# insert charge record the same day of rent record
date_time = get_fake_date_time(start=date_from.date(),
end=date_from.date() +
timedelta(hours=12))
val = (getstr(date_time), station_id, cplate, randint(10, 100))
cursor.execute(charge_sql, val)
conn.commit()
preload_data()
query = "SELECT rr.cid AS CustomerId, COUNT(cr.id) AS Amount FROM rent_records AS rr " \
"INNER JOIN charge_records AS cr ON rr.cplate = cr.cplate " \
"AND DATE(rr.date_from) = DATE(cr.date_time) " \
"WHERE DATE(cr.date_time) BETWEEN DATE(%s) AND DATE(%s) GROUP BY rr.cid"
value = (start_date, end_date)
cursor.execute(query, value)
return cursor.fetchall(), [i[0] for i in cursor.description]
def query9(conn: MySQLConnection):
def preload_data():
pass # no need to preload data, use the sample data from the database
preload_data()
cursor = conn.cursor()
sql = "SELECT wid AS WorkshopID, type AS CarPartType, MAX(amount) AS Amount " \
"FROM (SELECT D.wid, D.type, SUM(D.amount) AS amount " \
"FROM (SELECT d.amount, o.wid, p.type FROM order_details as d " \
"INNER JOIN orders AS o ON d.order_id = o.id " \
"INNER JOIN car_parts AS p ON p.trade_name = d.trade_name AND p.pid = d.pid) AS D " \
"GROUP BY D.wid, D.type ORDER BY Amount DESC) AS D2 GROUP BY WorkshopID"
cursor.execute(sql)
return cursor.fetchall(), [i[0] for i in cursor.description]
def query10(conn: MySQLConnection):
def preload_data():
pass # no need to preload data, use the sample data from the database
preload_data()
cursor = conn.cursor()
sql = "SELECT cplate AS CarPlate, AVG(day_price) AS AvgPrice " \
"FROM (SELECT cplate, SUM(price) AS day_price " \
"FROM ((SELECT date_time, price, cplate FROM repair_records) UNION " \
"(SELECT date_time, price, cplate FROM charge_records)) AS records " \
"GROUP BY DATE(date_time), cplate) AS prices " \
"GROUP BY cplate ORDER BY AvgPrice DESC LIMIT 1"
cursor.execute(sql)
return cursor.fetchall(), [i[0] for i in cursor.description]
class MySQL:
def __init__(self):
self.host="localhost"
self.user="******"
self.password="******"
self.database="examen"
def Conexion(self):
self.mydb = MySQLConnection(
host=self.host,
user=self.user,
password=self.password,
database=self.database
)
return "Conexion a MySQL exitosa"
#DHT----------------------------------------------------------------
def guardarDatos(self,datos):
self.sql = "insert into SensorDHT (temperatura, humedad, fechaLectura) values (%s, %s, %s)"
self.mycursor = self.mydb.cursor()
self.mycursor.execute(self.sql,datos)
self.mydb.commit()
#PIR----------------------------------------------------------------
def guardarDatosPIR(self,estado):
self.sql = "insert into SensorPIR (estado,fechaLectura) values (%s,%s)"
self.mycursor = self.mydb.cursor()
self.mycursor.execute(self.sql,estado)
self.mydb.commit()
#HCR----------------------------------------------------------------
def guardarDatosHCR(self,distancia):
self.sql = "insert into SensorHCR (distancia,fechaLectura) values (%s,%s)"
self.mycursor = self.mydb.cursor()
self.mycursor.execute(self.sql,distancia)
self.mydb.commit()
def prepare_document_types(db_connection: MySQLConnection):
db_cursor = db_connection.cursor()
sql = "INSERT INTO document_types (document_code, title, summary) VALUES (%s, %s, %s)"
val = [('0', 'Template', 'NA'), ('01', 'Manuals/Policies', 'NA'),
('02', 'Workflow', 'NA'), ('03', 'Process/Procedure', 'NA'),
('04', 'Work Instruction', 'NA'), ('05', 'Form/Checklist', 'NA'),
('06', 'Presentation', 'NA'), ('07', 'Specification', 'NA'),
('08', 'Report', 'NA'), ('09', 'Drawing', 'NA'),
('10', 'Cover Sheet', 'NA'), ('97', 'Logs', 'NA'),
('98', 'Emails and Communication Records', 'NA'),
('99', 'Non-Communication Records', 'NA')]
db_cursor.executemany(sql, val)
db_connection.commit()
def CreateNewRecord(db_connection: MySQLConnection,
serial_number: int,
title: str,
summary: str = "NA"):
db_cursor = db_connection.cursor()
str_code = str(serial_number)
business_series = str_code[0] + "0"
business_code = str_code[0:2]
document_code = str_code[2:4]
sql = "INSERT INTO records (business_series_index, business_code, document_code, full_serial_number, title, summary) VALUES (%s, %s, %s, %s, %s, %s) "
value = (business_series, business_code, document_code, str_code, title,
summary)
db_cursor.execute(sql, value)
db_connection.commit()
def query7(conn: MySQLConnection):
def preload_data():
pass # no need to preload data, use the sample data from the database
start_first_month = datetime(2018, 8, 1)
end_third_month = datetime(2018, 10, 31)
preload_data()
cursor = conn.cursor()
# create variable in database - 10% of amount of all cars
sql = "SELECT 0.1 * COUNT(*) FROM cars"
cursor.execute(sql)
limit = int(cursor.fetchone()[0])
sql = "SELECT cplate AS CarPlate, COUNT(rr.id) AS RentAmount FROM rent_records AS rr " \
"WHERE DATE(rr.date_from) BETWEEN DATE(%s) AND DATE(%s) " \
"GROUP BY cplate ORDER BY RentAmount LIMIT %s"
val = (start_first_month, end_third_month, limit)
cursor.execute(sql, val)
return cursor.fetchall(), [i[0] for i in cursor.description]
class MySQLConnector:
def __init__(self):
try:
self.connection = MySQLConnection(**config)
print("Connection created")
self.cursor = self.connection.cursor(dictionary=True)
except mysql.connector.Error as err:
if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
print("Something is wrong with your user name or password")
elif err.errno == errorcode.ER_BAD_DB_ERROR:
print("Database does not exist")
else:
print(err)
def query(self, query, args=None):
if args is None:
self.cursor.execute(query)
else:
self.cursor.execute(query, args)
def fetchone(self):
return self.cursor.fetchone()
def fetchall(self):
return self.cursor.fetchall()
def insert(self, query):
self.query(query)
self.connection.commit()
def update(self, query, args):
self.query(query, args)
self.connection.commit()
def delete(self, query):
self.query(query)
self.connection.commit()
def close(self):
self.cursor.close()
self.connection.close()
print("Connection closed")
def query6(conn: MySQLConnection):
def preload_data():
pass # no need to preload data, use the sample data from the database
preload_data()
mor1 = time(7, 0)
mor2 = time(10, 0)
aft1 = time(12, 0)
aft2 = time(14, 0)
eve1 = time(17, 0)
eve2 = time(19, 0)
cursor = conn.cursor()
sql = "SELECT COUNT(IF(TIME(date_from) BETWEEN TIME(%s) AND TIME(%s), 1, NULL)) AS MorningTravels," \
"COUNT(IF(TIME(date_from) BETWEEN TIME(%s) AND TIME(%s), 1, NULL)) AS AfternoonTravels," \
"COUNT(IF(TIME(date_from) BETWEEN TIME(%s) AND TIME(%s), 1, NULL)) AS EveningTravels " \
"FROM rent_records"
value = (mor1, mor2, aft1, aft2, eve1, eve2)
cursor.execute(sql, value)
return cursor.fetchall(), [i[0] for i in cursor.description]
def insert_block(conn: MySQLConnection, block: Block, coin_type: str):
c = conn.cursor()
try:
b = block
with profiler.profile("sql: insert block"):
c.execute(
INSERT_BLOCK,
(b.height, b.block_size, b.block_weight, b.difficulty,
b.cumulative_difficulty, b.hash, b.long_term_weight,
b.major_version, b.minor_version, b.nonce, b.reward,
b.timestamp, b.wide_cumulative_difficulty, b.wide_difficulty))
bt = b.miner_tx
with profiler.profile("insert cb transaction"):
insert_transaction(c, bt, coin_type)
# c.execute(INSERT_TRANSACTION, (bt.version, bt.hash_hex, bt.fee, bt.block_height, bt.unlock_time))
bt.tx_id = c.lastrowid
for t in b.txs:
# hash_value = int(t.hash_hex, 16)
with profiler.profile("insert transaction"):
insert_transaction(c, t, coin_type)
# c.execute(INSERT_TRANSACTION, (t.version, t.hash_hex, t.fee, t.block_height, t.unlock_time))
# t.tx_id = c.lastrowid
# print(f"inserted transaction, rows updated{c.rowcount}")
# for tx_in in t.tx_ins:
# c.execute(INSERT_TXIN, (t.tx_id, tx_in.amount, tx_in.key_image, tx_in.coinbase))
# tx_in.txin_id = c.lastrowid
# for keyoffset in tx_in.key_offsets:
# c.execute(INSERT_KEY_OFFSET, (tx_in.txin_id, keyoffset))
# for od in tx_in.out_details:
# od.tx_id = t.tx_id
# c.execute(INSERT_OUTPUT_DETAILS, (od.tx_id, od.height, od.key_hex, od.mask_hex, od.unlocked))
# for tx_out in t.tx_outs:
# c.execute(INSERT_TXOUT, (t.tx_id, tx_out.amount, tx_out.target_key))
conn.commit()
c.close()
except Exception as e:
logger.exception(f"ERROR AT HEIGHT:{block.height}"
) #should automatically dump the stack
conn.rollback()
c.close()
class SqlConnector:
def __init__(self, schema_name):
self.schema_name = schema_name
self.cnx = None
self.error_log = []
def __enter__(self, schema_name):
try:
self.cnx = MySQLConnection(**config_local)
except:
self.cnx = MySQLConnection(**config_remote)
self.cnx.database = schema_name
print("Successfully opened MySQLConnection")
def __exit__(self):
if self.cnx is not None:
self.cnx.close()
print("Successfully closed MySQLConnection")
def execute_in_cursor(self, command):
new_cursor = self.cnx.cursor()
try:
new_cursor.execute(command)
result = list(new_cursor)
new_cursor.close()
print(f"Query results for ( {command} ): {result}")
return result
except Exception as e:
print(f"Query error occured: {e}")
self.error_log += [(command, e)]
finally:
new_cursor.close()
def print_error_log(self):
if not self.error_log: return print("No errors logged.\nDatabase update successful!")
print("Errors logged:")
for error in self.error_log:
print(error)
print("Database update unsuccessful!")
def query5(conn: MySQLConnection):
cursor = conn.cursor()
date = datetime(2018, 11, 27, 0, 0, 0)
def preload_data():
sql = "SELECT * FROM customers LIMIT 100"
cursor.execute(sql)
ids = [x[0] for x in cursor.fetchall()]
sql = "SELECT * FROM cars LIMIT 50"
cursor.execute(sql)
plates = [x[0] for x in cursor.fetchall()]
# remove all rent records on the date loaded by sample data
sql = "DELETE FROM rent_records WHERE DATE(date_from) = DATE(%s)"
cursor.execute(sql, (date, ))
conn.commit()
# insert random rent records on the date with random pairs of customers and cars
for i in range(100):
sql = "INSERT INTO rent_records (date_from, date_to, cid, cplate, distance) " \
"VALUES (%s, %s, %s, %s, %s)"
date_from = get_fake_date_time(start=date,
end=date + timedelta(days=1))
date_to = get_fake_date_time(start=date_from,
end=date + timedelta(days=1))
val = (date_from, date_to, choice(ids), choice(plates),
randint(1, 100))
cursor.execute(sql, val)
conn.commit()
preload_data()
query = "SELECT AVG(MINUTE(TIMEDIFF(date_from, date_to))) AS AvgTripDurationInMinutes " \
"FROM rent_records WHERE DATE(date_from) = %s"
value = (date, )
cursor.execute(query, value)
return cursor.fetchall(), [i[0] for i in cursor.description]
def abre_cursor(con: MySQLConnection,
log: bool = False) -> Union[CursorBase, int]:
"""Cria o cursor para executar queries sql.
Args:
con (MySQLConnection): Uma conexão com um banco de previamente aberta.
log (bool, optional): Ativa e desativa o logging. Default é False.
Returns:
Union[CursorBase, int]: O cursor aberto com buffer ativado caso tenha dado certo ou
-1 caso alguma coisa tenha dado errado ao abrir o cursor.
"""
try:
# if con is None or not con.is_connected():
# con = conecta_servidor()
cursor = con.cursor(buffered=True)
if log:
print("Cursor aberto.")
return cursor
except Exception as e:
if log:
print("Cursor não foi aberto", e)
return 0
class TestsCursor(TestsDataTypes):
def setUp(self):
config = self.getMySQLConfig()
self.db = MySQLConnection(**config)
c = self.db.cursor()
tblNames = self.tables.values()
c.execute("DROP TABLE IF EXISTS %s" % (",".join(tblNames)))
c.close()
def tearDown(self):
c = self.db.cursor()
tblNames = self.tables.values()
c.execute("DROP TABLE IF EXISTS %s" % (",".join(tblNames)))
c.close()
self.db.close()
def test_numeric_int(self):
"""MySQL numeric integer data types"""
c = self.db.cursor()
columns = [
"tinyint_signed",
"tinyint_unsigned",
"bool_signed",
"smallint_signed",
"smallint_unsigned",
"mediumint_signed",
"mediumint_unsigned",
"int_signed",
"int_unsigned",
"bigint_signed",
"bigint_unsigned",
]
c.execute(
"""CREATE TABLE %s (
`id` TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
`tinyint_signed` TINYINT SIGNED,
`tinyint_unsigned` TINYINT UNSIGNED,
`bool_signed` BOOL,
`smallint_signed` SMALLINT SIGNED,
`smallint_unsigned` SMALLINT UNSIGNED,
`mediumint_signed` MEDIUMINT SIGNED,
`mediumint_unsigned` MEDIUMINT UNSIGNED,
`int_signed` INT SIGNED,
`int_unsigned` INT UNSIGNED,
`bigint_signed` BIGINT SIGNED,
`bigint_unsigned` BIGINT UNSIGNED,
PRIMARY KEY (id)
)
"""
% (self.tables["int"])
)
data = [
(
-128, # tinyint signed
0, # tinyint unsigned
0, # boolean
-32768, # smallint signed
0, # smallint unsigned
-8388608, # mediumint signed
0, # mediumint unsigned
-2147483648, # int signed
0, # int unsigned
-9223372036854775808, # big signed
0, # big unsigned
),
(
127, # tinyint signed
255, # tinyint unsigned
127, # boolean
32767, # smallint signed
65535, # smallint unsigned
8388607, # mediumint signed
16777215, # mediumint unsigned
2147483647, # int signed
4294967295, # int unsigned
9223372036854775807, # big signed
18446744073709551615, # big unsigned
),
]
insert = self._get_insert_stmt(self.tables["int"], columns)
select = self._get_select_stmt(self.tables["int"], columns)
c.executemany(insert, data)
c.execute(select)
rows = c.fetchall()
def compare(name, d, r):
self.assertEqual(d, r, "%s %s != %s" % (name, d, r))
for i, col in enumerate(columns):
compare(col, data[0][i], rows[0][i])
compare(col, data[1][i], rows[1][i])
c.close()
def test_numeric_bit(self):
"""MySQL numeric bit data type"""
c = self.db.cursor()
columns = ["c8", "c16", "c24", "c32", "c40", "c48", "c56", "c63", "c64"]
c.execute(
"""CREATE TABLE %s (
`id` int NOT NULL AUTO_INCREMENT,
`c8` bit(8) DEFAULT NULL,
`c16` bit(16) DEFAULT NULL,
`c24` bit(24) DEFAULT NULL,
`c32` bit(32) DEFAULT NULL,
`c40` bit(40) DEFAULT NULL,
`c48` bit(48) DEFAULT NULL,
`c56` bit(56) DEFAULT NULL,
`c63` bit(63) DEFAULT NULL,
`c64` bit(64) DEFAULT NULL,
PRIMARY KEY (id)
)
"""
% self.tables["bit"]
)
insert = self._get_insert_stmt(self.tables["bit"], columns)
select = self._get_select_stmt(self.tables["bit"], columns)
data = list()
data.append(tuple([0] * len(columns)))
values = list()
for col in columns:
values.append(1 << int(col.replace("c", "")) - 1)
data.append(tuple(values))
values = list()
for col in columns:
values.append((1 << int(col.replace("c", ""))) - 1)
data.append(tuple(values))
c.executemany(insert, data)
c.execute(select)
rows = c.fetchall()
self.assertEqual(rows, data)
c.close()
def test_numeric_float(self):
"""MySQL numeric float data type"""
c = self.db.cursor()
columns = ["float_signed", "float_unsigned", "double_signed", "double_unsigned"]
c.execute(
"""CREATE TABLE %s (
`id` int NOT NULL AUTO_INCREMENT,
`float_signed` FLOAT(6,5) SIGNED,
`float_unsigned` FLOAT(6,5) UNSIGNED,
`double_signed` DOUBLE(15,10) SIGNED,
`double_unsigned` DOUBLE(15,10) UNSIGNED,
PRIMARY KEY (id)
)"""
% (self.tables["float"])
)
insert = self._get_insert_stmt(self.tables["float"], columns)
select = self._get_select_stmt(self.tables["float"], columns)
data = [
(-3.402823466, 0, -1.7976931348623157, 0),
(-1.175494351, 3.402823466, 1.7976931348623157, 2.2250738585072014),
(-1.23455678, 2.999999, -1.3999999999999999, 1.9999999999999999),
]
c.executemany(insert, data)
c.execute(select)
rows = c.fetchall()
def compare(name, d, r):
self.assertEqual(d, r, "%s %s != %s" % (name, d, r))
for j in range(0, len(data)):
for i, col in enumerate(columns[0:2]):
compare(col, round(data[j][i], 5), rows[j][i])
for i, col in enumerate(columns[2:2]):
compare(col, round(data[j][i], 10), rows[j][i])
c.close()
def test_numeric_decimal(self):
"""MySQL numeric decimal data type"""
c = self.db.cursor()
columns = ["decimal_signed", "decimal_unsigned"]
c.execute(
"""CREATE TABLE %s (
`id` int NOT NULL AUTO_INCREMENT,
`decimal_signed` DECIMAL(65,30) SIGNED,
`decimal_unsigned` DECIMAL(65,30) UNSIGNED,
PRIMARY KEY (id)
)"""
% (self.tables["decimal"])
)
insert = self._get_insert_stmt(self.tables["decimal"], columns)
select = self._get_select_stmt(self.tables["decimal"], columns)
data = [
(
Decimal("-9999999999999999999999999.999999999999999999999999999999"),
Decimal("+9999999999999999999999999.999999999999999999999999999999"),
),
(Decimal("-1234567.1234"), Decimal("+123456789012345.123456789012345678901")),
(
Decimal("-1234567890123456789012345.123456789012345678901234567890"),
Decimal("+1234567890123456789012345.123456789012345678901234567890"),
),
]
c.executemany(insert, data)
c.execute(select)
rows = c.fetchall()
self.assertEqual(data, rows)
c.close()
def test_temporal_datetime(self):
"""MySQL temporal date/time data types"""
c = self.db.cursor()
c.execute("SET SESSION time_zone = '+00:00'")
columns = ["t_date", "t_datetime", "t_time", "t_timestamp", "t_year_2", "t_year_4"]
c.execute(
"""CREATE TABLE %s (
`id` int NOT NULL AUTO_INCREMENT,
`t_date` DATE,
`t_datetime` DATETIME,
`t_time` TIME,
`t_timestamp` TIMESTAMP DEFAULT 0,
`t_year_2` YEAR(2),
`t_year_4` YEAR(4),
PRIMARY KEY (id)
)"""
% (self.tables["temporal"])
)
insert = self._get_insert_stmt(self.tables["temporal"], columns)
select = self._get_select_stmt(self.tables["temporal"], columns)
data = [
(
datetime.date(2010, 1, 17),
datetime.datetime(2010, 1, 17, 19, 31, 12),
datetime.timedelta(hours=43, minutes=32, seconds=21),
datetime.datetime(2010, 1, 17, 19, 31, 12),
10,
0,
),
(
datetime.date(1000, 1, 1),
datetime.datetime(1000, 1, 1, 0, 0, 0),
datetime.timedelta(hours=-838, minutes=59, seconds=59),
datetime.datetime(*time.gmtime(1)[:6]),
70,
1901,
),
(
datetime.date(9999, 12, 31),
datetime.datetime(9999, 12, 31, 23, 59, 59),
datetime.timedelta(hours=838, minutes=59, seconds=59),
datetime.datetime(2038, 1, 19, 3, 14, 7),
69,
2155,
),
]
c.executemany(insert, data)
c.execute(select)
rows = c.fetchall()
from pprint import pprint
def compare(name, d, r):
self.assertEqual(d, r, "%s %s != %s" % (name, d, r))
for j in range(0, len(data)):
for i, col in enumerate(columns):
compare("%s (data[%d])" % (col, j), data[j][i], rows[j][i])
c.close()
def build_cursor(self, connection: MySQLConnection) -> MySQLCursor:
return connection.cursor()
class MySqlDB:
def __init__(self, settings):
self.settings = dict(**settings)
self.connection = None
def init_connection(self):
if self.connection is None:
self.connection = MySQLConnection(
host = self.settings.get('DB_HOST', '127.0.0.1'),
port = self.settings.get('DB_PORT', 3306),
user = self.settings.get('DB_USER', 'root'),
password = self.settings.get('DB_PASSWORD', ''),
database = self.settings.get('DB_NAME', 'mysql'),
)
def is_connected(self):
if self.connection is None:
self.init_connection()
return self.connection.is_connected()
def reconnect(self, attempts, delay):
if self.connection is None:
self.init_connection()
return self.connection.reconnect()
def rollback(self):
if self.connection is None:
self.init_connection()
self.connection.rollback()
def commit(self):
if self.connection is None:
self.init_connection()
self.connection.commit()
@contextlib.contextmanager
def cursor(self, buffered=True, rollback_on_error=True, **kwargs):
if self.connection is None:
self.init_connection()
cursor = None
try:
kwargs = kwargs or {}
kwargs.setdefault("dictionary", True)
kwargs.setdefault("buffered", buffered)
cursor = self.connection.cursor(**kwargs)
yield cursor
except MySqlError as error:
if rollback_on_error:
self.connection.rollback()
raise error from None
finally:
if cursor:
cursor.close()
def fetchone(self, query, params=None, **kwargs):
if self.connection is None:
self.init_connection()
cursor = None
try:
kwargs = kwargs or {"dictionary": True}
kwargs.setdefault("buffered", True)
cursor = self.connection.cursor(**kwargs)
cursor.execute(query, params)
result = cursor.fetchone()
return result
finally:
if cursor:
cursor.close()
def fetchall(self, query, params=None, **kwargs):
if self.connection is None:
self.init_connection()
cursor = None
try:
kwargs = kwargs or {"dictionary": True}
kwargs.setdefault("buffered", True)
cursor = self.connection.cursor(**kwargs)
cursor.execute(query, params)
result = cursor.fetchall()
return result
finally:
if cursor:
cursor.close()
def close(self):
if self.connection is not None:
self.connection.close()
class TestsCursor(TestsDataTypes):
def setUp(self):
config = self.getMySQLConfig()
self.db = MySQLConnection(**config)
c = self.db.cursor()
tblNames = self.tables.values()
c.execute("DROP TABLE IF EXISTS %s" % (','.join(tblNames)))
c.close()
def tearDown(self):
c = self.db.cursor()
tblNames = self.tables.values()
c.execute("DROP TABLE IF EXISTS %s" % (','.join(tblNames)))
c.close()
self.db.close()
def test_numeric_int(self):
"""MySQL numeric integer data types"""
c = self.db.cursor()
columns = [
'tinyint_signed',
'tinyint_unsigned',
'bool_signed',
'smallint_signed',
'smallint_unsigned',
'mediumint_signed',
'mediumint_unsigned',
'int_signed',
'int_unsigned',
'bigint_signed',
'bigint_unsigned',
]
c.execute("""CREATE TABLE %s (
`id` TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
`tinyint_signed` TINYINT SIGNED,
`tinyint_unsigned` TINYINT UNSIGNED,
`bool_signed` BOOL,
`smallint_signed` SMALLINT SIGNED,
`smallint_unsigned` SMALLINT UNSIGNED,
`mediumint_signed` MEDIUMINT SIGNED,
`mediumint_unsigned` MEDIUMINT UNSIGNED,
`int_signed` INT SIGNED,
`int_unsigned` INT UNSIGNED,
`bigint_signed` BIGINT SIGNED,
`bigint_unsigned` BIGINT UNSIGNED,
PRIMARY KEY (id)
)
""" % (self.tables['int']))
data = [
(
-128, # tinyint signed
0, # tinyint unsigned
0, # boolean
-32768, # smallint signed
0, # smallint unsigned
-8388608, # mediumint signed
0, # mediumint unsigned
-2147483648, # int signed
0, # int unsigned
-9223372036854775808, # big signed
0, # big unsigned
),
(
127, # tinyint signed
255, # tinyint unsigned
127, # boolean
32767, # smallint signed
65535, # smallint unsigned
8388607, # mediumint signed
16777215, # mediumint unsigned
2147483647, # int signed
4294967295, # int unsigned
9223372036854775807, # big signed
18446744073709551615, # big unsigned
)
]
insert = self._get_insert_stmt(self.tables['int'], columns)
select = self._get_select_stmt(self.tables['int'], columns)
c.executemany(insert, data)
c.execute(select)
rows = c.fetchall()
def compare(name, d, r):
self.assertEqual(d, r, "%s %s != %s" % (name, d, r))
for i, col in enumerate(columns):
compare(col, data[0][i], rows[0][i])
compare(col, data[1][i], rows[1][i])
c.close()
def test_numeric_bit(self):
"""MySQL numeric bit data type"""
c = self.db.cursor()
columns = [
'c8', 'c16', 'c24', 'c32', 'c40', 'c48', 'c56', 'c63', 'c64'
]
c.execute("""CREATE TABLE %s (
`id` int NOT NULL AUTO_INCREMENT,
`c8` bit(8) DEFAULT NULL,
`c16` bit(16) DEFAULT NULL,
`c24` bit(24) DEFAULT NULL,
`c32` bit(32) DEFAULT NULL,
`c40` bit(40) DEFAULT NULL,
`c48` bit(48) DEFAULT NULL,
`c56` bit(56) DEFAULT NULL,
`c63` bit(63) DEFAULT NULL,
`c64` bit(64) DEFAULT NULL,
PRIMARY KEY (id)
)
""" % self.tables['bit'])
insert = self._get_insert_stmt(self.tables['bit'], columns)
select = self._get_select_stmt(self.tables['bit'], columns)
data = list()
data.append(tuple([0] * len(columns)))
values = list()
for col in columns:
values.append(1 << int(col.replace('c', '')) - 1)
data.append(tuple(values))
values = list()
for col in columns:
values.append((1 << int(col.replace('c', ''))) - 1)
data.append(tuple(values))
c.executemany(insert, data)
c.execute(select)
rows = c.fetchall()
self.assertEqual(rows, data)
c.close()
def test_numeric_float(self):
"""MySQL numeric float data type"""
c = self.db.cursor()
columns = [
'float_signed',
'float_unsigned',
'double_signed',
'double_unsigned',
]
c.execute("""CREATE TABLE %s (
`id` int NOT NULL AUTO_INCREMENT,
`float_signed` FLOAT(6,5) SIGNED,
`float_unsigned` FLOAT(6,5) UNSIGNED,
`double_signed` DOUBLE(15,10) SIGNED,
`double_unsigned` DOUBLE(15,10) UNSIGNED,
PRIMARY KEY (id)
)""" % (self.tables['float']))
insert = self._get_insert_stmt(self.tables['float'], columns)
select = self._get_select_stmt(self.tables['float'], columns)
data = [
(
-3.402823466,
0,
-1.7976931348623157,
0,
),
(-1.175494351, 3.402823466, 1.7976931348623157,
2.2250738585072014),
(-1.23455678, 2.999999, -1.3999999999999999, 1.9999999999999999),
]
c.executemany(insert, data)
c.execute(select)
rows = c.fetchall()
def compare(name, d, r):
self.assertEqual(d, r, "%s %s != %s" % (name, d, r))
for j in (range(0, len(data))):
for i, col in enumerate(columns[0:2]):
compare(col, round(data[j][i], 5), rows[j][i])
for i, col in enumerate(columns[2:2]):
compare(col, round(data[j][i], 10), rows[j][i])
c.close()
def test_numeric_decimal(self):
"""MySQL numeric decimal data type"""
c = self.db.cursor()
columns = [
'decimal_signed',
'decimal_unsigned',
]
c.execute("""CREATE TABLE %s (
`id` int NOT NULL AUTO_INCREMENT,
`decimal_signed` DECIMAL(65,30) SIGNED,
`decimal_unsigned` DECIMAL(65,30) UNSIGNED,
PRIMARY KEY (id)
)""" % (self.tables['decimal']))
insert = self._get_insert_stmt(self.tables['decimal'], columns)
select = self._get_select_stmt(self.tables['decimal'], columns)
data = [
(Decimal(
'-9999999999999999999999999.999999999999999999999999999999'),
Decimal(
'+9999999999999999999999999.999999999999999999999999999999')),
(Decimal('-1234567.1234'),
Decimal('+123456789012345.123456789012345678901')),
(Decimal(
'-1234567890123456789012345.123456789012345678901234567890'),
Decimal(
'+1234567890123456789012345.123456789012345678901234567890')),
]
c.executemany(insert, data)
c.execute(select)
rows = c.fetchall()
self.assertEqual(data, rows)
c.close()
def test_temporal_datetime(self):
"""MySQL temporal date/time data types"""
c = self.db.cursor()
c.execute("SET SESSION time_zone = '+00:00'")
columns = [
't_date',
't_datetime',
't_time',
't_timestamp',
't_year_4',
]
c.execute("""CREATE TABLE %s (
`id` int NOT NULL AUTO_INCREMENT,
`t_date` DATE,
`t_datetime` DATETIME,
`t_time` TIME,
`t_timestamp` TIMESTAMP DEFAULT 0,
`t_year_4` YEAR(4),
PRIMARY KEY (id)
)""" % (self.tables['temporal']))
insert = self._get_insert_stmt(self.tables['temporal'], columns)
select = self._get_select_stmt(self.tables['temporal'], columns)
data = [
(datetime.date(2010, 1,
17), datetime.datetime(2010, 1, 17, 19, 31, 12),
datetime.timedelta(hours=43, minutes=32, seconds=21),
datetime.datetime(2010, 1, 17, 19, 31, 12), 0),
(datetime.date(1000, 1, 1), datetime.datetime(1000, 1, 1, 0, 0, 0),
datetime.timedelta(hours=-838, minutes=59, seconds=59),
datetime.datetime(*time.gmtime(1)[:6]), 1901),
(datetime.date(9999, 12,
31), datetime.datetime(9999, 12, 31, 23, 59, 59),
datetime.timedelta(hours=838, minutes=59, seconds=59),
datetime.datetime(2038, 1, 19, 3, 14, 7), 2155),
]
c.executemany(insert, data)
c.execute(select)
rows = c.fetchall()
def compare(name, d, r):
self.assertEqual(d, r, "%s %s != %s" % (name, d, r))
for j in (range(0, len(data))):
for i, col in enumerate(columns):
compare("%s (data[%d])" % (col, j), data[j][i], rows[j][i])
# Testing YEAR(2), which is now obsolete since MySQL 5.6.6
tblname = self.tables['temporal_year']
c.execute("CREATE TABLE %s ("
"`id` int NOT NULL AUTO_INCREMENT KEY, "
"`t_year_2` YEAR(2))" % tblname)
c.execute(self._get_insert_stmt(tblname, ['t_year_2']), (10, ))
c.execute(self._get_select_stmt(tblname, ['t_year_2']))
row = c.fetchone()
if tests.MYSQL_VERSION >= (5, 6, 6):
self.assertEqual(2010, row[0])
else:
self.assertEqual(10, row[0])
c.close()
class TestsCursor(TestsDataTypes):
def setUp(self):
config = self.getMySQLConfig()
self.db = MySQLConnection(**config)
c = self.db.cursor()
tblNames = self.tables.values()
c.execute("DROP TABLE IF EXISTS %s" % (','.join(tblNames)))
c.close()
def tearDown(self):
c = self.db.cursor()
tblNames = self.tables.values()
c.execute("DROP TABLE IF EXISTS %s" % (','.join(tblNames)))
c.close()
self.db.close()
def test_numeric_int(self):
"""MySQL numeric integer data types"""
c = self.db.cursor()
columns = [
'tinyint_signed',
'tinyint_unsigned',
'bool_signed',
'smallint_signed',
'smallint_unsigned',
'mediumint_signed',
'mediumint_unsigned',
'int_signed',
'int_unsigned',
'bigint_signed',
'bigint_unsigned',
]
c.execute("""CREATE TABLE %s (
`id` TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
`tinyint_signed` TINYINT SIGNED,
`tinyint_unsigned` TINYINT UNSIGNED,
`bool_signed` BOOL,
`smallint_signed` SMALLINT SIGNED,
`smallint_unsigned` SMALLINT UNSIGNED,
`mediumint_signed` MEDIUMINT SIGNED,
`mediumint_unsigned` MEDIUMINT UNSIGNED,
`int_signed` INT SIGNED,
`int_unsigned` INT UNSIGNED,
`bigint_signed` BIGINT SIGNED,
`bigint_unsigned` BIGINT UNSIGNED,
PRIMARY KEY (id)
)
""" % (self.tables['int']))
data = [
(
-128, # tinyint signed
0, # tinyint unsigned
0, # boolean
-32768, # smallint signed
0, # smallint unsigned
-8388608, # mediumint signed
0, # mediumint unsigned
-2147483648, # int signed
0, # int unsigned
-9223372036854775808, # big signed
0, # big unsigned
),
(
127, # tinyint signed
255, # tinyint unsigned
127, # boolean
32767, # smallint signed
65535, # smallint unsigned
8388607, # mediumint signed
16777215, # mediumint unsigned
2147483647, # int signed
4294967295, # int unsigned
9223372036854775807, # big signed
18446744073709551615, # big unsigned
)
]
insert = self._get_insert_stmt(self.tables['int'],columns)
select = self._get_select_stmt(self.tables['int'],columns)
c.executemany(insert, data)
c.execute(select)
rows = c.fetchall()
def compare(name, d, r):
self.assertEqual(d,r,"%s %s != %s" % (name,d,r))
for i,col in enumerate(columns):
compare(col,data[0][i],rows[0][i])
compare(col,data[1][i],rows[1][i])
c.close()
def test_numeric_bit(self):
"""MySQL numeric bit data type"""
c = self.db.cursor()
columns = [
'c8','c16','c24','c32',
'c40','c48','c56','c63',
'c64']
c.execute("""CREATE TABLE %s (
`id` int NOT NULL AUTO_INCREMENT,
`c8` bit(8) DEFAULT NULL,
`c16` bit(16) DEFAULT NULL,
`c24` bit(24) DEFAULT NULL,
`c32` bit(32) DEFAULT NULL,
`c40` bit(40) DEFAULT NULL,
`c48` bit(48) DEFAULT NULL,
`c56` bit(56) DEFAULT NULL,
`c63` bit(63) DEFAULT NULL,
`c64` bit(64) DEFAULT NULL,
PRIMARY KEY (id)
)
""" % self.tables['bit'])
insert = self._get_insert_stmt(self.tables['bit'],columns)
select = self._get_select_stmt(self.tables['bit'],columns)
data = list()
data.append(tuple([0]*len(columns)))
values = list()
for col in columns:
values.append( 1 << int(col.replace('c',''))-1)
data.append(tuple(values))
values = list()
for col in columns:
values.append( (1 << int(col.replace('c',''))) -1)
data.append(tuple(values))
c.executemany(insert, data)
c.execute(select)
rows = c.fetchall()
self.assertEqual(rows, data)
c.close()
def test_numeric_float(self):
"""MySQL numeric float data type"""
c = self.db.cursor()
columns = [
'float_signed',
'float_unsigned',
'double_signed',
'double_unsigned',
]
c.execute("""CREATE TABLE %s (
`id` int NOT NULL AUTO_INCREMENT,
`float_signed` FLOAT(6,5) SIGNED,
`float_unsigned` FLOAT(6,5) UNSIGNED,
`double_signed` DOUBLE(15,10) SIGNED,
`double_unsigned` DOUBLE(15,10) UNSIGNED,
PRIMARY KEY (id)
)""" % (self.tables['float']))
insert = self._get_insert_stmt(self.tables['float'],columns)
select = self._get_select_stmt(self.tables['float'],columns)
data = [
(-3.402823466,0,-1.7976931348623157,0,),
(-1.175494351,3.402823466,1.7976931348623157,2.2250738585072014),
(-1.23455678,2.999999,-1.3999999999999999,1.9999999999999999),
]
c.executemany(insert, data)
c.execute(select)
rows = c.fetchall()
def compare(name, d, r):
self.assertEqual(d,r,"%s %s != %s" % (name,d,r))
for j in (range(0,len(data))):
for i,col in enumerate(columns[0:2]):
compare(col,round(data[j][i],5),rows[j][i])
for i,col in enumerate(columns[2:2]):
compare(col,round(data[j][i],10),rows[j][i])
c.close()
def test_numeric_decimal(self):
"""MySQL numeric decimal data type"""
c = self.db.cursor()
columns = [
'decimal_signed',
'decimal_unsigned',
]
c.execute("""CREATE TABLE %s (
`id` int NOT NULL AUTO_INCREMENT,
`decimal_signed` DECIMAL(65,30) SIGNED,
`decimal_unsigned` DECIMAL(65,30) UNSIGNED,
PRIMARY KEY (id)
)""" % (self.tables['decimal']))
insert = self._get_insert_stmt(self.tables['decimal'],columns)
select = self._get_select_stmt(self.tables['decimal'],columns)
data = [
(Decimal('-9999999999999999999999999.999999999999999999999999999999'),
Decimal('+9999999999999999999999999.999999999999999999999999999999')),
(Decimal('-1234567.1234'),
Decimal('+123456789012345.123456789012345678901')),
(Decimal('-1234567890123456789012345.123456789012345678901234567890'),
Decimal('+1234567890123456789012345.123456789012345678901234567890')),
]
c.executemany(insert, data)
c.execute(select)
rows = c.fetchall()
self.assertEqual(data,rows)
c.close()
def test_temporal_datetime(self):
"""MySQL temporal date/time data types"""
c = self.db.cursor()
c.execute("SET SESSION time_zone = '+00:00'")
columns = [
't_date',
't_datetime',
't_time',
't_timestamp',
't_year_4',
]
c.execute("""CREATE TABLE %s (
`id` int NOT NULL AUTO_INCREMENT,
`t_date` DATE,
`t_datetime` DATETIME,
`t_time` TIME,
`t_timestamp` TIMESTAMP DEFAULT 0,
`t_year_4` YEAR(4),
PRIMARY KEY (id)
)""" % (self.tables['temporal']))
insert = self._get_insert_stmt(self.tables['temporal'],columns)
select = self._get_select_stmt(self.tables['temporal'],columns)
data = [
(datetime.date(2010,1,17),
datetime.datetime(2010,1,17,19,31,12),
datetime.timedelta(hours=43,minutes=32,seconds=21),
datetime.datetime(2010,1,17,19,31,12),
0),
(datetime.date(1000,1,1),
datetime.datetime(1000,1,1,0,0,0),
datetime.timedelta(hours=-838,minutes=59,seconds=59),
datetime.datetime(*time.gmtime(1)[:6]),
1901),
(datetime.date(9999,12,31),
datetime.datetime(9999,12,31,23,59,59),
datetime.timedelta(hours=838,minutes=59,seconds=59),
datetime.datetime(2038,1,19,3,14,7),
2155),
]
c.executemany(insert, data)
c.execute(select)
rows = c.fetchall()
def compare(name, d, r):
self.assertEqual(d,r,"%s %s != %s" % (name,d,r))
for j in (range(0,len(data))):
for i,col in enumerate(columns):
compare("%s (data[%d])" % (col,j),data[j][i],rows[j][i])
# Testing YEAR(2), which is now obsolete since MySQL 5.6.6
tblname = self.tables['temporal_year']
c.execute("CREATE TABLE %s ("
"`id` int NOT NULL AUTO_INCREMENT KEY, "
"`t_year_2` YEAR(2))" % tblname)
c.execute(self._get_insert_stmt(tblname, ['t_year_2']), (10,))
c.execute(self._get_select_stmt(tblname, ['t_year_2']))
row = c.fetchone()
if tests.MYSQL_VERSION >= (5, 6, 6):
self.assertEqual(2010, row[0])
else:
self.assertEqual(10, row[0])
c.close()
class ParseXML(object):
def __init__(self, file):
self.cnx = MySQLConnection()
self.local_cursor = MySQLCursor()
self.external_db_cnx = MySQLConnection()
self.file = file
self.xml = etree.parse(file).getroot()
self.dict = self.xml[1]
#self.truncate()
#self.generate_model()
def connect_to_db(self):
self.cnx = connect(user='******', password ='******', host='127.0.0.1', database='combinatoria') #database='test')
self.local_cursor = self.cnx.cursor()
def generate_model(self):
project = models.Project()
project.save()
script = models.Script()
info_node = self.xml[0]
script.author = info_node[0].text
script.title = info_node[1].text
script.description = info_node[2].text
script.date = date(year=2007, month=7, day=1)
script.save()
project.script_set.add(script)
act = models.Act()
act.title = 'conversations'
act.script = script
act.save()
scenes = self.xml.findall('conversation')
for scene in scenes:
scene_model = models.Scene()
scene_model.title = self.dict_value(scene.attrib['title'])
scene_model.description
scene_model.act = act
scene_model.save()
parts = scene.findall('section')
for part in parts:
part_model = models.Part()
part_model.scene = scene_model
part_model.name = self.dict_value(part.attrib['keyword'])
part_model.description = part.attrib['summary']
part_model.save()
contents = part.findall('cut')
for cut in contents:
content_model = models.Content()
content_model.part = part_model
content_model.save()
part_model.content_set.add(content_model)
self.content_factory(content_model, cut)
pass
def content_factory(self, content, cut):
type = cut.attrib['type']
if(type == 'pause'):
self.process_pause(content, cut)
elif(type == 'default'):
self.process_default(content, cut)
elif(type == 'alternative'):
self.process_alternative(content, cut)
def process_pause(self, content, cut):
scene_name = self.get_scene_name(content.part_id)
scene_code = self.dict_key(scene_name)
part_name = models.Part.objects.get(pk=content.part_id).name
part_code = self.dict_key(part_name)
pause = models.Group()
pause.content_id = content.id
pause.name = scene_code + '_' + part_code + '_' + 'pause'
pause.save()
type = models.Type()
type.name = 'SEQUENCE_SET'
params = [{'sets':['short', 'medium', 'long']}]
type.arguments = json.dumps(params)
type.group_id = pause.id
type.content_id = content.id
pause.type_set.add(type)
pause_txt = models.Line()
pause_txt.line = 'pause'
pause_txt.save()
content.line_set.add(pause_txt)
pause.line.add(pause_txt)
library = models.Group()
library.name = 'library'
library.content_id = content.id
library.save()
pause_library = models.GroupContainer(container=pause, group=library)
pause_library.save()
angles = cut.findall("./shots/angle")
for angle in angles:
source = models.Source()
source.file = scene_code + '_' + part_code + '_' + angle.attrib['type'] + '.mp4'
source.mime = 'video/mp4'
source.duration = float(angle.attrib['length']) * 1000
source.size = int(angle.attrib['bytes'])
source.save()
library_source = models.GroupSource(group=library, source=source, order=0)
library_source.save()
sets = models.Group()
sets.name = 'sets'
sets.content_id = content.id
sets.save()
short = models.Group()
short.name = 'short'
short.content_id = content.id
short.save()
set_short = models.GroupContainer(container=sets, group=short, has_group=True)
set_short.save()
short_seq_str = cut.find('./sequence[@type="short"]').text
short_seq = short_seq_str.split(',')
sequences = [re.split('[+]', items) for items in short_seq]
i = 0
for seq in sequences:
for items in seq:
items = items.split(',')
group = models.Group()
i += 1
group.name = 'short_' + str(i)
group.content_id = content.id
group.save()
counter = 0
for item in items:
counter += 1
source = models.Source.objects.get(file=scene_code + '_' + part_code + '_' + item + '.mp4')
group_source = models.GroupSource(group=group, source=source, order=counter)
group_source.save()
short_group = models.GroupContainer(container=short, group=group)
short_group.save()
medium = models.Group()
medium.name = 'medium'
medium.content_id = content.id
medium.save()
sets_medium = models.GroupContainer(container=sets, group=medium, has_group=True)
sets_medium.save()
med_seq_str = cut.find('./sequence[@type="medium"]').text
med_seq = med_seq_str.split(',')
sequences = [re.split('[+]', items) for items in med_seq]
i = 0
for seq in sequences:
group = models.Group()
i += 1
group.name = 'medium_' + str(i)
group.content_id = content.id
group.save()
counter = 0
for item in seq:
counter += 1
source = models.Source.objects.get(file=scene_code + '_' + part_code + '_' + item + '.mp4')
group_source = models.GroupSource(group=group, source=source, order=counter)
group_source.save()
#source.group_set.add(group)
medium_group = models.GroupContainer(container=medium, group=group)
medium_group.save()
#medium.group_set.add(group)
#long = models.Item()
long = models.Group()
long.name = 'long'
long.content_id = content.id
long.save()
#sets.item.add(long)
sets_long = models.GroupContainer(container=sets, group=long, has_group=True)
sets_long.save()
long_seq_str = cut.find('./sequence[@type="long"]').text
long_seq = long_seq_str.split(',')
sequences = [re.split('[+]', items) for items in long_seq]
i = 0
for seq in sequences:
group = models.Group()
i += 1
group.name = 'long_' + str(i)
group.content_id = content.id
group._deferred = False
group.save()
long_group = models.GroupContainer(container=long, group=group)
long_group.save()
#long.group_set.add(group)
counter = 0
for item in seq:
counter += 1
source = models.Source.objects.get(file=scene_code + '_' + part_code + '_' + item + '.mp4')
group_source = models.GroupSource(group=group, source=source, order=counter)
group_source.save()
#source.group_set.add(group)
group.save()
pause.save()
content.group_set.add(pause)
return 0
def process_default(self, content, cut):
default = models.Line()
default.line = cut.find('line').text
default.speaker = cut.attrib['speaker']
default.save()
content.line_set.add(default)
default_type = models.Type()
default_type.name = 'DEFAULT'
default_type.content_id = content.id
default_type.save()
default.type_set.add(default_type)
shots = cut.findall('./shots/angle')
for shot in shots:
source = models.Source()
source.file = cut.find('name').text + '_' + shot.attrib['type'] + '.mp4'
source.duration = float(shot.attrib['length']) * 1000
source.mime = 'video/mp4'
source.size = shot.attrib['bytes']
source.save()
line_source = models.LineSource(source=source, line=default)
line_source.save()
#default.source.add(source)
def process_alternative(self, content, cut):
alternative = self.init_alternative(content, cut)
alt_type = alternative.type_set.get().name
print(alt_type)
if alt_type == 'ALTERNATIVE_FREE':
self.process_options(content, alternative, cut)
if alt_type == 'ALTERNATIVE_PAIRED':
self.process_options(content, alternative, cut)
if alt_type == 'ALTERNATIVE_PARENT':
alts = cut.findall('./alternative')
parent = models.Group.objects.last()#(content_id=content.id)
#parent.name = 'alternative_parent'
#parent.save()
counter = 0
for alt in alts:
counter += 1
alt.attrib['speaker'] = cut.attrib['speaker']
self.process_alternative(content, alt)
child = models.Group.objects.last()
parent_child = models.GroupContainer(container=parent, group=child, order=counter)
parent_child.save()
if alt_type == 'ALTERNATIVE_PAIRED_PARENT':
alts = cut.findall('./alternative')
parent = models.Group.objects.last() #(content_id=content.id)
#parent.name = 'alternative_paired_parent'
#parent.save()
counter = 0
for alt in alts:
counter += 1
alt.attrib['speaker'] = cut.attrib['speaker']
self.process_alternative(content, alt)
child = models.Group.objects.last()
parent_child = models.GroupContainer(container=parent, group=child, order=counter)
parent_child.save()
if alt_type == 'ALTERNATIVE_COMPOUND':
default = self.write_line(content.id, cut.attrib['speaker'], cut.find('./default/line').text)
content.line_set.add(default)
# default_type = models.Type()
# default_type.name = 'DEFAULT'
# default_type.content_id = content.id
# default_type.save()
# default.type_set.add(default_type)
shots = cut.findall('./default/shots/angle')
for shot in shots:
source = models.Source()
source.file = cut.find('./default/name').text + '_' + shot.attrib['type'] + '.mp4'
source.duration = float(shot.attrib['length']) * 1000
source.mime = 'video/mp4'
source.size = shot.attrib['bytes']
source.save()
line_source = models.LineSource(source=source, line=default)
line_source.save()
alternative.line.add(default)
self.process_options(content, alternative, cut)
if alt_type == 'ALTERNATIVE_PAIRED_MIXED':
nested = cut.findall('./nested')
nested[0].attrib['speaker'] = cut.attrib['speaker']
self.init_alternative(content, nested[0])
return
def init_alternative(self, content, cut):
alternative = models.Group()
alternative.save()
type = self.write_alt_type(cut)
content.type_set.add(type)
alternative.type_set.add(type)
alternative.name = type.name.lower()
alternative.content_id = content.id
alternative.save()
return alternative
def process_options(self, content, alternative, cut):
options = cut.findall('./option')
order_pos = 0
for option in options:
order_pos += 1
dialogue = self.write_line(content.id, cut.attrib['speaker'], option.find('./line').text)
alt_line = models.GroupLine(group=alternative, line=dialogue, order=order_pos)
alt_line.save()
name = option.find('./name').text
shots = option.findall('./shots/angle')
for angle in shots:
source = models.Source()
source.file = name + '_' + angle.attrib['type'] + '.mp4'
source.duration = float(angle.attrib['length']) * 1000
source.mime = 'video/mp4'
source.size = angle.attrib['bytes']
source.save()
line_source = models.LineSource(source=source, line=dialogue)
line_source.save()
def write_alt_type(self, cut):
type = models.Type()
if cut.attrib['alt']:
subtype = cut.attrib['alt']
type.name = 'ALTERNATIVE' + '_' + subtype.upper()
else:
type.name = 'ALTERNATIVE'
if 'paired' in subtype:
position = int(cut.attrib['position'][0:1])
total = int(cut.attrib['position'][2:])
next = cut.attrib['next'] if 'next' in cut.attrib else None
previous = cut.attrib['previous'] if 'previous' in cut.attrib else None
data = {'pos': position, 'total': total, 'next':next, 'prev': previous}
data_string = json.dumps(data)
if 'mixed' in subtype:
nested_positions = []
nested = cut.findall('./nested')
nested_positions.append(int(nested[0].attrib['id']))
data['nested'] = nested_positions
data_string = json.dumps(data)
type.arguments = data_string
if 'compound' in subtype:
data = [{'default':1}]
data_string = json.dumps(data)
type.arguments = data_string
type.save()
return type
def write_line(self, content_id, speaker, text):
dialogue = models.Line()
dialogue.line = text
dialogue.speaker = speaker
dialogue.content_id = content_id
dialogue.save()
return dialogue
def dict_value(self, code):
return self.dict.find('item/.[@code="{0}"]'.format(code)).attrib['content']
def dict_key(self, code):
return self.dict.find('item/.[@content="{0}"]'.format(code)).attrib['code']
def get_scene_name(self, part_id):
return models.Part.objects.get(pk=part_id).scene.title
def truncate(self):
models.Project.objects.all().delete()
models.Script.objects.all().delete()
models.Act.objects.all().delete()
models.Scene.objects.all().delete()
models.Part.objects.all().delete()
models.Content.objects.all().delete()
models.Item.objects.all().delete()
models.Line.objects.all().delete()
models.Group.objects.all().delete()
models.Source.objects.all().delete()
models.Type.objects.all().delete()
models.ItemSource.objects.all().delete()
models.LineSource.objects.all().delete()
models.GroupSource.objects.all().delete()
models.GroupItem.objects.all().delete()
models.GroupLine.objects.all().delete()
