def test_postgres(postgres):
#bonobo.settings.QUIET.set(True)
db_name = 'my_db'
port = postgres['NetworkSettings']['Ports']['5432/tcp'][0]['HostPort']
wait_for_postgres(port)
root_engine = create_root_engine(port)
_execute_sql(root_engine, "CREATE ROLE my_user WITH LOGIN PASSWORD '';")
_execute_sql(
root_engine,
'CREATE DATABASE {name} WITH OWNER=my_user TEMPLATE=template0 ENCODING="utf-8"'
.format(name=db_name))
engine = create_engine('my_user', db_name, port)
metadata.create_all(engine)
services = {'sqlalchemy.engine': engine}
graph = bonobo.Graph()
graph.add_chain(extract, bonobo_sqlalchemy.InsertOrUpdate(TABLE_1))
assert bonobo.run(graph, services=services)
buf = Bufferize()
graph = bonobo.Graph()
graph.add_chain(
bonobo_sqlalchemy.Select('SELECT * FROM ' + TABLE_1),
buf,
)
assert bonobo.run(graph, services=services)
assert buf.buffer == [((0, 'value for 0'), {}), ((1, 'value for 1'), {}),
((2, 'value for 2'), {}), ((3, 'value for 3'), {}),
((4, 'value for 4'), {}), ((5, 'value for 5'), {}),
((6, 'value for 6'), {}), ((7, 'value for 7'), {}),
((8, 'value for 8'), {}), ((9, 'value for 9'), {})]
graph = bonobo.Graph(
bonobo_sqlalchemy.Select('SELECT * FROM ' + TABLE_1),
bonobo_sqlalchemy.InsertOrUpdate(TABLE_2),
)
assert bonobo.run(graph, services=services)
buf = Bufferize()
graph = bonobo.Graph()
graph.add_chain(
bonobo_sqlalchemy.Select('SELECT * FROM ' + TABLE_2),
buf,
)
assert bonobo.run(graph, services=services)
assert buf.buffer == [((0, 'value for 0'), {}), ((1, 'value for 1'), {}),
((2, 'value for 2'), {}), ((3, 'value for 3'), {}),
((4, 'value for 4'), {}), ((5, 'value for 5'), {}),
((6, 'value for 6'), {}), ((7, 'value for 7'), {}),
((8, 'value for 8'), {}), ((9, 'value for 9'), {})]
def get_graph(**options):
return bonobo.Graph(
bonobo_sqlalchemy.Select('SELECT * FROM table',
engine='sqlalchemy.pgengine'),
bonobo_sqlalchemy.InsertOrUpdate(table_name='table_1',
engine='sqlalchemy.pgengine'),
)
def get_graph(**options):
graph = bonobo.Graph()
graph.add_chain(
bonobo_sqlalchemy.Select('SELECT * FROM example', limit=100),
bonobo.PrettyPrinter(),
)
return graph
def get_graph(**options):
"""This function builds the graph that needs to be executed. :return: bonobo.Graph """
return bonobo.Graph(
bonobo_sqlalchemy.Select('SELECT * FROM table',
engine='sqlalchemy.pgengine'),
bonobo_sqlalchemy.InsertOrUpdate(table_name='table_1',
engine='sqlalchemy.pgengine'),
)
def get_graph():
graph = bonobo.Graph()
graph.add_chain(
bonobo_sqlalchemy.Select(
'SELECT * FROM dbo.Location where PublicRelease=1',
engine='nm_aquifer'),
bonobo.PrettyPrinter(),
)
return graph
def get_graph(self):
graph = bonobo.Graph()
graph.add_chain(
# NOTE: The Select statement here assumes a default engine of sqlalchemy.engine
# defined in get_services()
# I'm fairly sure this can be overridden with paremeters
bonobo_sqlalchemy.Select(self.assemble_query_string(), limit=100),
self.create_xml,
self.write_to_string)
return graph
def get_graph(**options):
"""
This function builds the graph that needs to be executed.
:return: bonobo.Graph
"""
now = options['now']
# Null out time portion, go back 2 days in the past
now += relativedelta(days=-2, hour=0, minute=0, second=0, microsecond=0)
print("# Processing for %s" % now.date())
graph = bonobo.Graph()
STMT = """
select a.badgeid AS badgeid, b.user_id AS user_id, a.employee_id AS employee_id, a.email AS email,
b.item_description AS item_description, b.item_number AS item_number , b.transaction_date AS transaction_date,
b.transaction_id AS transaction_id, b.description AS description, '' AS drawer_id, b.quantity AS quantity
from ivm b , (select badgeid,email, employee_id from f_employee group by badgeid,email ,employee_id) a
where b.user_id = a.badgeid
and b.transaction_date = '{now}';
"""
graph.add_chain(
bonobo_sqlalchemy.Select(STMT.format(now=now),
engine=options['engine']),
trim_employee_id,
invalid_badge_id,
invalid_email,
format_payload,
create_ticket,
bonobo.UnpackItems(0),
)
return graph