Re: [sqlalchemy] DropTable if exists
well the easiest is mytable.drop(engine, checkfirst=True). The check is not within the DropTable construct, which represents just the actual DROP TABLE statement.If you were using DropTable directly you'd call engine.has_table(tablename) first to check for it. On Sep 28, 2011, at 8:07 AM, Chris Withers wrote: Hi, Much less controversial question this time, I hope ;-) I have: class MyModel(Base) ... I want to do: engine = create_engine(...) engine.execute(DropTable(MyModel.__table__)) engine.execute(CreateTable(MyModel.__table__)) ...of course, this barfs the first time I run it as the table doesn't exist. I was looking for something like: engine.execute(DropTable(MyModel.__table__, if_exist=True)) what's the right way to do this? Chris -- Simplistix - Content Management, Batch Processing Python Consulting - http://www.simplistix.co.uk -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] DropTable if exists
On 28/09/2011 13:19, Michael Bayer wrote: well the easiest is mytable.drop(engine, checkfirst=True). The check is not within the DropTable construct, which represents just the actual DROP TABLE statement.If you were using DropTable directly you'd call engine.has_table(tablename) first to check for it. Hmm, but both mysql and postgres (I suspect others do too, but I haven't checked) have DROP TABLE IF EXISTS statements so you don't need to do any checking. That feels like it should be supported by the DropTable construct, what am I missing? Anyway, in an effort to get this, I tried: File ...model.py, line 46, in module engine.execute('drop table if exists %s', table.name) File sqlalchemy/engine/base.py, line 2285, in execute return connection.execute(statement, *multiparams, **params) File sqlalchemy/engine/base.py, line 1399, in execute params) File sqlalchemy/engine/base.py, line 1576, in _execute_text statement, parameters File sqlalchemy/engine/base.py, line 1640, in _execute_context context) File sqlalchemy/engine/base.py, line 1633, in _execute_context context) File sqlalchemy/engine/default.py, line 325, in do_execute cursor.execute(statement, parameters) sqlalchemy.exc.ProgrammingError: (ProgrammingError) syntax error at or near E'table_name' LINE 1: drop table if exists E'table_name' Where's that E coming from? cheers, Chris -- Simplistix - Content Management, Batch Processing Python Consulting - http://www.simplistix.co.uk -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] DropTable if exists
On Sep 28, 2011, at 8:32 AM, Chris Withers wrote: On 28/09/2011 13:19, Michael Bayer wrote: well the easiest is mytable.drop(engine, checkfirst=True). The check is not within the DropTable construct, which represents just the actual DROP TABLE statement.If you were using DropTable directly you'd call engine.has_table(tablename) first to check for it. Hmm, but both mysql and postgres (I suspect others do too, but I haven't checked) have DROP TABLE IF EXISTS statements so you don't need to do any checking. That feels like it should be supported by the DropTable construct, what am I missing? oh, that. Right you'd need to use @compiles to enhance a new subclass of DropTable to do that, as SQLA's compiler doesn't have the IF EXISTS feature present at the moment (it could be added). The E is how psycopg2 formats the %s - table.name parameter in your statement for certain versions of Postgresql. I don't know what it actually means but if you watch your PG logs you'll see it's used for all bound parameters. Anyway, in an effort to get this, I tried: File ...model.py, line 46, in module engine.execute('drop table if exists %s', table.name) File sqlalchemy/engine/base.py, line 2285, in execute return connection.execute(statement, *multiparams, **params) File sqlalchemy/engine/base.py, line 1399, in execute params) File sqlalchemy/engine/base.py, line 1576, in _execute_text statement, parameters File sqlalchemy/engine/base.py, line 1640, in _execute_context context) File sqlalchemy/engine/base.py, line 1633, in _execute_context context) File sqlalchemy/engine/default.py, line 325, in do_execute cursor.execute(statement, parameters) sqlalchemy.exc.ProgrammingError: (ProgrammingError) syntax error at or near E'table_name' LINE 1: drop table if exists E'table_name' Where's that E coming from? cheers, Chris -- Simplistix - Content Management, Batch Processing Python Consulting - http://www.simplistix.co.uk -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] DropTable if exists
On 28/09/2011 14:09, Michael Bayer wrote: Hmm, but both mysql and postgres (I suspect others do too, but I haven't checked) have DROP TABLE IF EXISTS statements so you don't need to do any checking. That feels like it should be supported by the DropTable construct, what am I missing? oh, that. Right you'd need to use @compiles to enhance a new subclass of DropTable to do that, as SQLA's compiler doesn't have the IF EXISTS feature present at the moment (it could be added). I guess it probably should, I think that one could legitimately be in sqlalchemy itself ;-) The E is how psycopg2 formats the %s - table.name parameter in your statement for certain versions of Postgresql. I don't know what it actually means but if you watch your PG logs you'll see it's used for all bound parameters. Hmm, any ideas why it'd cause a syntax error here? I'm doing engine.execute('drop table if exists %s' + table.name) in the meantime, which just feels icky... Chris -- Simplistix - Content Management, Batch Processing Python Consulting - http://www.simplistix.co.uk -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] DropTable if exists
On Sep 28, 2011, at 9:47 AM, Chris Withers wrote: On 28/09/2011 14:09, Michael Bayer wrote: Hmm, but both mysql and postgres (I suspect others do too, but I haven't checked) have DROP TABLE IF EXISTS statements so you don't need to do any checking. That feels like it should be supported by the DropTable construct, what am I missing? oh, that. Right you'd need to use @compiles to enhance a new subclass of DropTable to do that, as SQLA's compiler doesn't have the IF EXISTS feature present at the moment (it could be added). I guess it probably should, I think that one could legitimately be in sqlalchemy itself ;-) The E is how psycopg2 formats the %s - table.name parameter in your statement for certain versions of Postgresql. I don't know what it actually means but if you watch your PG logs you'll see it's used for all bound parameters. Hmm, any ideas why it'd cause a syntax error here? I'm doing engine.execute('drop table if exists %s' + table.name) in the meantime, which just feels icky... oh probably it doesn't like table name as a bound parameter. Chris -- Simplistix - Content Management, Batch Processing Python Consulting - http://www.simplistix.co.uk -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] DropTable if exists
On Wed, Sep 28, 2011 at 8:56 AM, Michael Bayer mike...@zzzcomputing.comwrote: On Sep 28, 2011, at 9:47 AM, Chris Withers wrote: On 28/09/2011 14:09, Michael Bayer wrote: I'm doing engine.execute('drop table if exists %s' + table.name) in the meantime, which just feels icky... oh probably it doesn't like table name as a bound parameter. Don't you mean 'drop table if exists %s' % table.name not + table.name if table.name is mytable wouldn't using + generate drop table if exists %smytable? -- Mike -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.