sqlalchemy 0.6 and mock strategy produces different SQL for postgres

[Diez B. Roggisch]

Hi,

we have a utility-script "schemautil" that allows us to re-create our DB based
on the metadata of SA, or simply dump the generated SQL for manual
inspection - the latter is used when one writes migration scripts.

Now we noticed a strange behavior: the generated SQL isn't working for
postgres anymore. This is how we create the engines:

if strategy is None:
self.engine = create_engine(dburi)
else:
self.buf = StringIO()
def executor(s):
self.buf.write(str(s))
self.buf.write(";\n")

self.engine = create_engine(
dburi,
strategy=strategy, # this is "mock"
executor=executor
)
self.engine.echo = echo

The output is e.g.

CREATE TABLE user_db.addresses (
id INTEGER NOT NULL,
address_line_1 VARCHAR(100) DEFAULT '' NOT NULL,
address_line_2 VARCHAR(100) DEFAULT '' NOT NULL,
city VARCHAR(100) DEFAULT '' NOT NULL,
zip_postal_code VARCHAR(100) DEFAULT '' NOT NULL,
state_region VARCHAR(100) DEFAULT '' NOT NULL,
company VARCHAR(100) DEFAULT '' NOT NULL,
country_code VARCHAR(2) DEFAULT '' NOT NULL,
created DATETIME DEFAULT now() NOT NULL,
PRIMARY KEY (id)
);

Please note the created-column. It's a DATETIME - something PG doesn't
understand.

We use

SQLAlchemy-0.6beta1dev_r6615

with psycopg2.

Any suggestions on how to fix this?

Diez

[Michael Bayer]

On Jan 5, 2010, at 11:35 AM, Diez B. Roggisch wrote:

> Hi,
>
> we have a utility-script "schemautil" that allows us to re-create our DB based
> on the metadata of SA, or simply dump the generated SQL for manual
> inspection - the latter is used when one writes migration scripts.
>
> Now we noticed a strange behavior: the generated SQL isn't working for
> postgres anymore. This is how we create the engines:
>
> if strategy is None:
> self.engine = create_engine(dburi)
> else:
> self.buf = StringIO()
> def executor(s):
> self.buf.write(str(s))
> self.buf.write(";\n")
>
> self.engine = create_engine(
> dburi,
> strategy=strategy, # this is "mock"
> executor=executor
> )
> self.engine.echo = echo
>
> The output is e.g.

dburi needs to have "postgres://" in it in order for the PG dialect to be invoked. theres a slim chance something doesnt work here in 0.6 that does in 0.5. Does your system work when using 0.5 ?

>
>
> CREATE TABLE user_db.addresses (
> id INTEGER NOT NULL,
> address_line_1 VARCHAR(100) DEFAULT '' NOT NULL,
> address_line_2 VARCHAR(100) DEFAULT '' NOT NULL,
> city VARCHAR(100) DEFAULT '' NOT NULL,
> zip_postal_code VARCHAR(100) DEFAULT '' NOT NULL,
> state_region VARCHAR(100) DEFAULT '' NOT NULL,
> company VARCHAR(100) DEFAULT '' NOT NULL,
> country_code VARCHAR(2) DEFAULT '' NOT NULL,
> created DATETIME DEFAULT now() NOT NULL,
> PRIMARY KEY (id)
> );
>
> Please note the created-column. It's a DATETIME - something PG doesn't
> understand.
>
> We use
>
> SQLAlchemy-0.6beta1dev_r6615
>
> with psycopg2.
>
> Any suggestions on how to fix this?
>
> Diez

> --
> 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.
>
>

[Diez B. Roggisch]

Yes. And we debugged into it with pdb. This is what the engine looks right
after the above code is run, for dumping:

-> self.engine.echo = echo
(Pdb) pp self.engine.__dict__
{'Connection': <class 'sqlalchemy.engine.base.Connection'>,
'_should_log_debug': False,
'_should_log_info': False,
'dialect': <sqlalchemy.dialects.postgresql.psycopg2.PostgreSQL_psycopg2
object at 0xa36b62c>,
'engine': Engine(postgres://),
'logger': <logging.Logger object at 0xa36bb0c>,
'pool': <sqlalchemy.pool.QueuePool object at 0xa36b96c>,
'url': <sqlalchemy.engine.url.URL object at 0xa36b4ec>}
(Pdb)

HTH. Looks like the URI is ok though.

Diez

[Michael Bayer]

then its a bug. if you can file a ticket that would be helpful otherwise
I'll forget to get around to this.

[Michael Bayer]

Diez B. Roggisch wrote:
> On Tuesday 05 January 2010 19:26:56 Michael Bayer wrote:
>> On Jan 5, 2010, at 11:35 AM, Diez B. Roggisch wrote:
>> > Hi,
>> >
>> > we have a utility-script "schemautil" that allows us to re-create our
>> DB
>> > based on the metadata of SA, or simply dump the generated SQL for
>> manual
>> > inspection - the latter is used when one writes migration scripts.
>> >
>> > Now we noticed a strange behavior: the generated SQL isn't working for
>> > postgres anymore. This is how we create the engines:
>> >
>> > if strategy is None:
>> > self.engine = create_engine(dburi)
>> > else:
>> > self.buf = StringIO()
>> > def executor(s):
>> > self.buf.write(str(s))
>> > self.buf.write(";\n")
>> >
>> > self.engine = create_engine(
>> > dburi,
>> > strategy=strategy, # this is "mock"
>> > executor=executor
>> > )
>> > self.engine.echo = echo

oh, sorry, here's your issue:

def executor(s):
self.buf.write(str(s.compile(dialect=self.engine.dialect))
self.buf.write(";\n")