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, [email protected] 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, [email protected] 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 [email protected]. > To post to this group, send email to [email protected]. > 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 [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
