DropTable if exists
Chris Withers
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
Michael Bayer
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.
> --
> You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
> To post to this group, send email to sqlal...@googlegroups.com.
> To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
>
Chris Withers
> 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,
Michael Bayer
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.
Chris Withers
>> 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...
Michael Bayer
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
>
Mike Conley
oh probably it doesn't like table name as a bound parameter.
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...