Sounds good. I will try concating strings for Postgres - see if that works. Seems like it should maybe work from a few posts I read.
I tried out the insert on SQLite by zipping the dict keys to values - works great! I tried out the triple-quoting of the strings through SQLAlchemy but that did not either. It seems that that may have a chance with the psycopg2 driver. Thanks much. Mono On Monday, September 23, 2013 6:39:33 AM UTC-4, Simon King wrote: > > 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...@gmail.com <javascript:>> > 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+...@googlegroups.com <javascript:>. > > To post to this group, send email to > > sqlal...@googlegroups.com<javascript:>. > > > 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.
