A quick google for "psycopg multiple statements" doesn't turn up anything useful, so I suspect you are going to be out of luck. And unless there are more database drivers than sqlite that support an "executescript" method, it doesn't seem likely that it'll get added to SQLAlchemy either...
For batch loading of data, you could look at the examples at http://docs.sqlalchemy.org/en/rel_0_7/core/tutorial.html#executing-multiple-statements Simon On Mon, Sep 23, 2013 at 6:54 AM, <monosij.for...@gmail.com> wrote: > Hi Simon - > > Great! The executescript from sqlite worked great. I had not seen that. I > was able to execute indices and fks as well. > > Is the same not possible from SQLAlchemy then? Would version 0.9 have it? I > don't know if Michael is planning on having this feature. > > Meaning even for PostgreSQL or MySQL? Would I then have to find another > driver (such as for SQLite) that would do this? > > For bulk loading of data then would SQLAlchemy's ORM capabilities be the > best than executing SQL in bulk load operations. 'Bulk' is relative term, > but 10K - 100K records and trying not to resort to ETL tools. > > Look forward to your reply and thanks for your help. > > Mono > > On Sunday, September 22, 2013 5:30:37 PM UTC-4, Simon King wrote: >> >> The documentation for the Python sqlite driver specifically says: >> >> """ >> execute() will only execute a single SQL statement. If you try to execute >> more than one statement with it, it will raise a Warning. Use >> executescript() if you want to execute multiple SQL statements with one >> call. >> """ >> >> (http://docs.python.org/2/library/sqlite3.html#sqlite3.Cursor.execute) >> >> ("raise a Warning" is a slightly vague statement, but it appears that it >> is actually an exception) >> >> SQLAlchemy's conn.execute eventually calls sqlite's Cursor.execute, so has >> the same limitations. I don't know, but I suspect that postgres will be the >> same. >> >> I think you need to find a way to split your scripts up into individual >> statements that you can run one at a time. >> >> Hope that helps, >> >> Simon >> >> On 21 Sep 2013, at 16:14, monosij...@gmail.com wrote: >> >> > Hi Michael and Simon - Thank you for your responses and help. Sorry I >> > should have provided more details. >> > >> > Putting in the raise gave this error trace blow. >> > >> > It says it cannot execute more than one line at a time. It says the same >> > even if I do not have it in a transaction block, but in this case I do. >> > >> > Meaning if just do a: conn.execute(scriptFile) - it says cannot execute >> > more than 1 statement at a time, which is the same error I am getting with >> > the transaction. >> > >> > ... >> > In this case I do a try catch as in: >> > conn = engine.connect() transact = conn.begin() try: >> > conn.execute(scriptFile) transact.commit() except: raise >> > transact.rollback() >> > >> > With scriptFile being just a string with 4 CREATE TABLE statements with >> > 3 - 10 fields each. >> > The scriptFile does have '/n' and '/t' (newlines/tabs) in each line of >> > fields in the CREATE TABLE statements. >> > >> > And as I said before I can execute the same statements (with the /n and >> > /t) 1 at a time with either the sqlalchemy or sqlite3 drivers, no problem. >> > Eventually I do want to run these in PostgresSQL so I assume it is not a >> > SQLite3 issue. I do need to run on SQLite3 as well. >> > >> > Btw, in my use case, while I can run the CREATE TABLEs 1 at a time >> > eventually I do have to run CREATE INDEXES / FKs / INITIAL DATA LOAD >> > multiple at a time, so I would like to get the multiple statements working >> > right. >> > ... >> > This is the error raise generates - at the end it says it can only >> > execute 1 statement at a time but it s warning. >> > >> > 2013-09-21 10:47:35,543 INFO sqlalchemy.engine.base.Engine BEGIN >> > (implicit) >> > 2013-09-21 10:47:35,543 INFO sqlalchemy.engine.base.Engine >> > >> > ... then shows the CREATE TABLE statements ... >> > >> > 2013-09-21 10:47:35,543 INFO sqlalchemy.engine.base.Engine () >> > Traceback (most recent call last): >> > File "dbms.model/python3/Controller.py", line 54, in <module> >> > if __name__ == "__main__": main() >> > File "dbms.model/python3/Controller.py", line 50, in main >> > controller.initiateEnvironment() >> > >> > ... lines from traceback in my code >> > ... end of traceback from SQLAlchemy driver below ... >> > >> > File >> > "/space/development/python/dbms.model/python3/DBMS/ExecutorSQL.py", line >> > 47, >> > in executeCreateOnSQLite2 >> > conn.execute(scriptFile) >> > File >> > "/usr/local/lib/python3.3/dist-packages/sqlalchemy/engine/base.py", line >> > 662, in execute >> > params) >> > File >> > "/usr/local/lib/python3.3/dist-packages/sqlalchemy/engine/base.py", line >> > 805, in _execute_text >> > statement, parameters >> > File >> > "/usr/local/lib/python3.3/dist-packages/sqlalchemy/engine/base.py", line >> > 874, in _execute_context >> > context) >> > File >> > "/usr/local/lib/python3.3/dist-packages/sqlalchemy/engine/base.py", line >> > 1027, in _handle_dbapi_exception >> > util.reraise(*exc_info) >> > File >> > "/usr/local/lib/python3.3/dist-packages/sqlalchemy/util/compat.py", line >> > 183, in reraise >> > raise value >> > File >> > "/usr/local/lib/python3.3/dist-packages/sqlalchemy/engine/base.py", line >> > 867, in _execute_context >> > context) >> > File >> > "/usr/local/lib/python3.3/dist-packages/sqlalchemy/engine/default.py", line >> > 326, in do_execute >> > cursor.execute(statement, parameters) >> > sqlite3.Warning: You can only execute one statement at a time. >> > ... >> > >> > Hope this helps. >> > Please let me know if you need anything else. >> > Thank you again for all your help. >> > >> > Mono >> > >> > >> > On Friday, September 20, 2013 7:07:57 PM UTC-4, Simon King wrote: >> > >> > On 20 Sep 2013, at 08:15, monosij...@gmail.com wrote: >> > >> > > >> > > I am trying to create multiple tables from a set of 'create table' >> > > scripts.The set of scripts are in a list and I am executing as below. >> > > >> > > The scripts create tables, all scripts tested and work through the >> > > sqlite3 driver fine - one at a time. >> > > >> > > However, through the sqlarchemy driver when I try as below - only a >> > > few of the tables get created, ie the script gets executed. >> > > >> > > The scripts (for the tables) always come in the same order but >> > > randomly a few gets executed, somes 1st,2nd or 1st,3rd; etc. >> > > >> > > The same scripts iterated from the same list get created fine using >> > > sqlite3 driver as mentioned, with no issues. >> > > >> > > However only a few of the tables get when I try the following, and >> > > then there is an error which causes the rollback from the script that >> > > failed >> > > and it exits. >> > > >> > > Any suggestions will be very appreciated as I actually plan to use the >> > > scripts for Postgres eventually. >> > > >> > > Btw I also try executing them all together by trying to execute the >> > > list - that also failed. Eventually i would like to try and bulk execute >> > > if >> > > possible, but not urgent for 'create table' >> > > >> > > Thank you for your help. >> > > >> > > Mono >> > > >> > > ... >> > > dbms = 'sqlite:////dataSpace/sqlite3/test.sqlite3' >> > > engine = create_engine(dbms, echo = True) >> > > t = conn.begin() >> > > try: >> > > for script in scripts: >> > > print (script) >> > > conn.execute(script) # I tried executing scripts all >> > > together as well but failed. >> > > t.commit() # I tried commit outside the for as well >> > > executed = True >> > > except: >> > > t.rollback() >> > > … >> > > >> > >> > If your script really looks like this, then you are suppressing any >> > error messages that might be happening during the import with your "except" >> > clause. Trying putting a bare "raise" after the t.rollback() so that your >> > script aborts after an exception. >> > >> > Simon >> > >> > >> > -- >> > You received this message because you are subscribed to the Google >> > Groups "sqlalchemy" group. >> > To unsubscribe from this group and stop receiving emails from it, send >> > an email to sqlalchemy+...@googlegroups.com. >> > To post to this group, send email to sqlal...@googlegroups.com. >> > Visit this group at http://groups.google.com/group/sqlalchemy. >> > For more options, visit https://groups.google.com/groups/opt_out. >> > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to sqlalchemy+unsubscr...@googlegroups.com. > To post to this group, send email to sqlalchemy@googlegroups.com. > Visit this group at http://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/groups/opt_out. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
