def test_database(target_config):
db = Database(connection=DBConnection(target_config['connection']))
db.execute(
statement=
"create table if not exist {} (country text, year int, reports int)".
format(target_config['connection']['table']),
modify=True)
data = {
'county': ['Cochice', 'Pima', 'Santa Cruz', 'Maricopa', 'Yuma'],
'year': [2012, 2012, 2013, 2014, 2014],
'reports': [4, 24, 31, 2, 3]
}
df = DataFrame(data)
db.insert(data=df)
res = db.select(statement="select * from {}".format(
target_config['connection']['table']))
db.execute(statement="drop table if exist {}".format(
target_config['connection']['table']),
modify=True)
assert len(res) == 5
def create_table(collection: SourceDefinition) -> None:
"""
Auxiliary method to create database and table if you start
on a vanilla system
:param collection: target config from catalog
"""
def table_available(collection: SourceDefinition) -> bool:
db = Database(DBConnection(collection.target_config['connection']))
stmt = "select * from {} limit 1".format(collection.name)
return db.select(stmt) is not None
if not table_available(collection):
log.info("No database table available. Going to create "
"everything...")
db = Database(DBConnection(collection.target_config['connection']))
db.execute(
statement="drop table if exists {}".format(collection.name),
modify=True
)
col_types = []
for f in collection.fields.values():
col_types.append('"{}" {}'.format(
f.alias,
PostgresTranslator(
Datatype(f.data_type)
).dialect_datatype()
))
cols = ', '.join(col_types)
target_options = collection.target_config.get('options', {})
row_hash = target_options.get('row_hash', False)
if row_hash:
cols += ', row_hash text PRIMARY KEY'
stmt = "create table if not exists {} ({} )".format(
collection.target_config['connection'].get('table', 'default'),
cols
)
db.execute(statement=stmt, modify=True)
else:
log.info("Database table already exists. Nothing to do here...")
def create_table(collection) -> None:
db = Database(DBConnection(collection.target_config['connection']))
db.execute(
statement="drop table if exists {}".format(
collection.target_config['connection'].get('table', collection.name)
),
modify=True
)
col_types = ['"{}" {}'.format(
f.alias,
PostgresTranslator(Datatype(f.data_type)).dialect_datatype()
) for f in collection.fields.values()]
cols = ', '.join(col_types)
target_options = collection.target_config.get('options', {})
row_hash = target_options.get('row_hash', False)
if row_hash:
cols += ', row_hash text PRIMARY KEY'
stmt = "create table if not exists {} ({} )".format(
collection.target_config['connection'].get('table', collection.name),
cols
)
db.execute(statement=stmt, modify=True)
def __init__(self, config: Dict[str, Any], parser: Parser) -> None:
super().__init__(config, parser)
self.db = Database(connection=DBConnection(self.config['connection']))
def table_available(collection: SourceDefinition) -> bool:
db = Database(DBConnection(collection.target_config['connection']))
stmt = "select * from {} limit 1".format(collection.name)
return db.select(stmt) is not None
def test_connection(target_config):
connection = DBConnection(target_config['connection'])
conn = connection.connect()
assert connection.is_connected() == True
def test_invalid_connection(target_config):
config = target_config['connection']
config['urrri'] = config.pop('uri')
with pytest.raises(SchemaMissingKeyError):
DBConnection(config)
def select_table(collection) -> DataFrame:
db = Database(DBConnection(collection.target_config['connection']))
stmt = "select * from {}".format(
collection.target_config['connection'].get('table', collection.name)
)
return db.select(stmt)