Union statement statement in SQLAlchemy

I have multiple large csv files that I want to merge and store the final table in a database for using in Pandas in the future. I read them all using Pandas and store them as separate but similar tables into a Sqlite database.

I want to merge the rows (vertically) by a SQL string and then use them in pandas for more analyses. I was wondering if this is a good practice to go back and forth between pandas and SQL for when we deal with large files and have limited memory (16GB) ?

Also my code gives me an error and I was unsure if there is a syntax issue or something more significant that I missing here.

from sqlalchemy.sql import text
engine = create_engine('sqlite:///C:\master.db', echo=False)
string = text("""SELECT * INTO Flows FROM (select * from "f2007-08" UNION select * from "f2009-10")""")
engine.execute(string)

​x
 
from sqlalchemy.sql import textengine = create_engine('sqlite:///C:\master.db', echo=False)string = text("""SELECT * INTO Flows FROM (select * from "f2007-08" UNION select * from "f2009-10")""")engine.execute(string)​​

Answer

That’s not the correct syntax for inserting the results of a query into another table.

It’s INSERT INTO tablename SELECT ...

string = text("""
    INSERT INTO Flows 
    select * from "f2007-08" 
    UNION 
    select * from "f2009-10" 
""")

 
string = text("""    INSERT INTO Flows     select * from "f2007-08"     UNION     select * from "f2009-10" """)​

Advertisement

Answer