Closed pawl closed 10 years ago
Here's my example which shows joining two tables, running a select query on the joined tables, then inserting the query results into the database:
from sqlalchemy import create_engine, Table, Column, Integer, Unicode, MetaData, String, Text, update, and_, select, func, types
from sqlaload import connect, get_table, distinct, update, query, add_row, upsert
srcEngine = create_engine('mysql+mysqldb://user:pass@111.111.111.111/database')
metadata = MetaData(bind=srcEngine) # needed for creating tables
metadata.reflect(srcEngine) # won't find existing columns without this
# create table objects for joining
tableA = Table("tableA", metadata)
tableB = Table("tableB", metadata)
# change column_to_join to join into whatever column you're using for join criteria, and change 9499 to your criteria
# apply labels will apply unique names to each column for you
q = tableA.join(tableB, tableB.c.column_to_join=="9499").select().apply_labels()
# get types for add_row
types = [col.type for col in q.columns]
result = srcEngine.execute(q) # get query result
# create engine object for the database you're going to put the data into
destDb = connect('mysql+mysqldb://user:pass@localhost/database')
table = get_table(destDb, 'newtable')
for row in result:
row = dict(zip(row.keys(), row))
add_row(destDb, table, row, ensure=True, types=types)
sqlaload has been renamed to pudo/dataset, see its documentation.
Would you be able to add documentation for how to build a new table from a select query? I think that would make this library even more attractive.