[sqlalchemy] DropTable if exists
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.
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.
