How do i execute whole sql file into database using SQLAlchemy? There might be a variety of sql queries within the file including begin and commit/rollback.
Regrettably I am unaware of a great general answer with this. Some dbapi's (psycopg2 for example) support performing many claims at any given time. When the files aren't huge you can easily load them right into a string and execute them on the connection. For other people, I'd use an order-line client for your db and pipe the information into that while using subprocess module.
If individuals approaches aren't acceptable, then you will need to proceed and implement a little SQL parser that may split the file apart into separate claims. This is actually tricky to obtain 100% correct, as you will need to element in database dialect specific literal getting away rules, the charset used, any database configuration options affecting literal parsing (e.g. PostgreSQL standard_conforming_strings).
Should you only have to understand this 99.9% correct, then some regexp miracle should enable you to get the majority of the way there.
If you work with sqlite3 it features a helpful extension to dbapi known as conn.executescript(str), I have hooked this up via something similar to this also it appeared to operate: (Not every context is proven but it ought to be enough to obtain the drift)
def init_from_script(script): Base.metadata.drop_all(db_engine) Base.metadata.create_all(db_engine) # HACK ALERT: we can do this using sqlite3 low level api, then reopen session. f = open(script) script_str = f.read().strip() global db_session db_session.close() import sqlite3 conn = sqlite3.connect(db_file_name) conn.executescript(script_str) conn.commit() db_session = Session()
Is pure evil I question? I looked in vain for any 'pure' sqlalchemy equivalent, possibly that may be put into the library, something similar to db_session.execute_script(file_title) ? I am wishing that db_session works all right in the end that (ie you don't need to restart engine) although not sure yet... further research needed (ie do we have to customize the engine or simply a session after going behind sqlalchemy's back?)
FYI sqlite3 features a related routine: sqlite3.complete_statement(sql) should you roll your personal parser...