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