def __init__(self, obj_cfg = dict(), dbo = None, *args, **kwargs):
if not obj_cfg:
self.log(ERROR, "Failed loading dba configuration. Database access initialization aborted.")
return (None)
try:
self.config = {
'host': obj_cfg['dbs_addr'],
'port': obj_cfg['dbs_port'],
'user': obj_cfg['dbs_login'],
'password': obj_cfg['dbs_pass'],
'use_unicode': obj_cfg['dbs_unicode'],
'charset': obj_cfg['dbs_charset'],
'time_zone': '%+03d:00' % ((time.timezone if (time.localtime().tm_isdst == 0) else time.altzone) / -3600),
'max_recursive_level': obj_cfg['dbs_max_recursive_level']
}
except KeyError as kye:
self.log(ERROR, "Invalid database configuration.")
return None
except ValueError as vle:
self.log(ERROR, "Invalid database configuration.")
return (None)
else:
self.dbo = dbo
try:
self.config['database'] = obj_cfg['dbs_default_db']
except KeyError as kye:
self.log(WARNING, "No database specified, connecting to the server only.")
pass
try:
MySQLConnection.__init__(**self.config)
#return super(dba, self).__init__(config)
except mysqlc.Error as dce:
self.log(ERROR, "Connot connect to the database server specified.")
return (None)
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 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 add_connection(self, cnx=None):
"""Add a connection to the pool
This method instantiates a MySQLConnection using the configuration
passed when initializing the MySQLConnectionPool instance or using
the set_config() method.
If cnx is a MySQLConnection instance, it will be added to the
queue.
Raises PoolError when no configuration is set, when no more
connection can be added (maximum reached) or when the connection
can not be instantiated.
"""
with CONNECTION_POOL_LOCK:
if not self._cnx_config:
raise errors.PoolError(
"Connection configuration not available")
if self._cnx_queue.full():
raise errors.PoolError(
"Failed adding connection; queue is full")
if not cnx:
cnx = MySQLConnection(**self._cnx_config)
# pylint: disable=W0212
cnx._pool_config_version = self._config_version
# pylint: enable=W0212
else:
if not isinstance(cnx, MySQLConnection):
raise errors.PoolError(
"Connection instance not subclass of MySQLConnection.")
self._queue_connection(cnx)
def add_connection(self, cnx=None):
"""Add a connection to the pool
This method instantiates a MySQLConnection using the configuration
passed when initializing the MySQLConnectionPool instance or using
the set_config() method.
If cnx is a MySQLConnection instance, it will be added to the
queue.
Raises PoolError when no configuration is set, when no more
connection can be added (maximum reached) or when the connection
can not be instantiated.
"""
with CONNECTION_POOL_LOCK:
if not self._cnx_config:
raise errors.PoolError(
"Connection configuration not available")
if self._cnx_queue.full():
raise errors.PoolError(
"Failed adding connection; queue is full")
if not cnx:
cnx = MySQLConnection(**self._cnx_config)
# pylint: disable=W0212
cnx._pool_config_version = self._config_version
# pylint: enable=W0212
else:
if not isinstance(cnx, MySQLConnection):
raise errors.PoolError(
"Connection instance not subclass of MySQLConnection.")
self._queue_connection(cnx)
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 __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 Conexion(self):
self.mydb = MySQLConnection(
host=self.host,
user=self.user,
password=self.password,
database=self.database
)
return "Conexion a MySQL exitosa"
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 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 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 connect(self):
"""
Connects to the db using read_db_info()
:return: a connection to the db. Remember to close it
"""
connection_info = self.read_config()
self.__connection = MySQLConnection(user=connection_info["db_user"],
password=connection_info["db_pass"],
host=connection_info["db_host"],
database=connection_info["db_name"])
def __init__(self, **kwargs):
"""Init a new connection instance """
MySQLConnection.__init__(self)
self.connect(
host=MYSQL_HOST,
user=MYSQL_USER,
password=MYSQL_PWD,
database=MYSQL_DATABASE,
**kwargs
)
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 close_instance(self, connection: MySQLConnection,
cursor: MySQLConnection) -> 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 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()
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):
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 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 connect(*args, **kwargs):
"""Create or get a MySQL connection object
In its simpliest form, Connect() will open a connection to a
MySQL server and return a MySQLConnection object.
When any connection pooling arguments are given, for example pool_name
or pool_size, a pool is created or a previously one is used to return
a PooledMySQLConnection.
Returns MySQLConnection or PooledMySQLConnection.
"""
if all(['fabric' in kwargs, 'failover' in kwargs]):
raise InterfaceError("fabric and failover arguments can not be used")
if 'fabric' in kwargs:
return mysql.connector.fabric.connect(*args, **kwargs)
# Failover
if 'failover' in kwargs:
return _get_failover_connection(**kwargs)
# Pooled connections
if any([key in kwargs for key in CNX_POOL_ARGS]):
return _get_pooled_connection(**kwargs)
# Regular connection
return MySQLConnection(*args, **kwargs)
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 cursor(cls,
buffered=None,
raw=None,
prepared=None,
cursor_class=None,
dictionary=None,
named_tuple=None,
pool=True) -> CursorBase:
conn = cls._connection_pool.get_connection(
) if pool else MySQLConnection(host=cls.mysql_host,
user=cls.mysql_user,
password=cls.mysql_password,
db=cls.mysql_db,
charset='utf8',
buffered=True)
cursor = conn.cursor(buffered=buffered,
raw=raw,
prepared=prepared,
cursor_class=cursor_class,
dictionary=dictionary,
named_tuple=named_tuple)
try:
yield cursor
except Exception as e:
conn.rollback()
if cursor.statement:
logger.error(sqlparse.format(cursor.statement, reindent=True))
logger.error(e)
raise e
finally:
conn.commit()
cursor.close()
conn.close()
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"
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]
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 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 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 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 set_config(self, **kwargs):
"""Set the connection configuration for MySQLConnection instances
This method sets the configuration used for creating MySQLConnection
instances. See MySQLConnection for valid connection arguments.
Raises PoolError when a connection argument is not valid, missing
or not supported by MySQLConnection.
"""
if not kwargs:
return
with CONNECTION_POOL_LOCK:
try:
test_cnx = MySQLConnection()
test_cnx.config(**kwargs)
self._cnx_config = kwargs
self._config_version = uuid4()
except AttributeError as err:
raise errors.PoolError(
"Connection configuration not valid: {0}".format(err))
def set_config(self, **kwargs):
"""Set the connection configuration for MySQLConnection instances
This method sets the configuration used for creating MySQLConnection
instances. See MySQLConnection for valid connection arguments.
Raises PoolError when a connection argument is not valid, missing
or not supported by MySQLConnection.
"""
if not kwargs:
return
with CONNECTION_POOL_LOCK:
try:
test_cnx = MySQLConnection()
test_cnx.config(**kwargs)
self._cnx_config = kwargs
self._config_version = uuid4()
except AttributeError as err:
raise errors.PoolError(
"Connection configuration not valid: {0}".format(err))
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
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()
def test___init__(self):
dbconfig = tests.get_mysql_config()
cnxpool = pooling.MySQLConnectionPool(pool_size=1, **dbconfig)
self.assertRaises(TypeError, pooling.PooledMySQLConnection)
cnx = MySQLConnection(**dbconfig)
pcnx = pooling.PooledMySQLConnection(cnxpool, cnx)
self.assertEqual(cnxpool, pcnx._cnx_pool)
self.assertEqual(cnx, pcnx._cnx)
self.assertRaises(AttributeError, pooling.PooledMySQLConnection,
None, None)
self.assertRaises(AttributeError, pooling.PooledMySQLConnection,
cnxpool, None)
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")
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()
def createConn(self):
con=MySQLConnection()
con.connect(Base.MYSQL_DATABASE_NAME, Base.MYSQL_USER_DB, Base.MYSQL_USER_PASSWORD, Base.MYSQL_HOST_NAME, Base.MYSQL_HOST_PORT)
return con
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()
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()
