can I use SA to dump a table's DDL to a file?

[Randy Syring]

I'd like to be able to dump an MS SQL server's objects to text on the
local file system. I have a working solution for views, stored
procedures, and functions, but tables are a different story. Can i
use SA's reflection and table creation abilities to write create table
DDL to a text file?

[Randy Syring]

In the FAQ...sorry:

http://www.sqlalchemy.org/trac/wiki/FAQ#HowcanIgettheCREATETABLEDROPTABLEoutputasastring

[Michael Bayer]

sure, you'd use the "mock" executor as in the second example here: http://www.sqlalchemy.org/trac/wiki/FAQ#HowcanIgettheCREATETABLEDROPTABLEoutputasastring

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

[Randy Syring]

Looks like I beat you to the answer by about 3 mins, thanks for
answering though. :)

I have a follow-up though. The foreign key constraints are not
getting created with "ON DELETE CASCADE" as expected. I tried this in
both MSSQL and Postgres. Is this expected behavior?

If so, is it possible to modify the FKs after they are reflected to
set ondelete and onupdate as appropriate? My test code:

http://paste.pocoo.org/show/399307/

And my output:

CREATE TABLE blog_comments (
id INTEGER NOT NULL,
blog_id INTEGER,
CONSTRAINT blog_comments_pkey PRIMARY KEY (id),
CONSTRAINT blog_comments_blog_id_fkey FOREIGN KEY(blog_id) REFERENCES
blog (id)
)

It would seem to be a reflection issue though, b/c if I create a table
manually, the "ON DELETE CASCADE" is added correctly.

On Jun 1, 8:36 pm, Michael Bayer <mike...@zzzcomputing.com> wrote:
> sure, you'd use the "mock" executor as in the second example here:http://www.sqlalchemy.org/trac/wiki/FAQ#HowcanIgettheCREATETABLEDROPT...

[Michael Bayer]

On Jun 2, 2011, at 3:14 AM, Randy Syring wrote:

> Looks like I beat you to the answer by about 3 mins, thanks for
> answering though. :)
>
> I have a follow-up though. The foreign key constraints are not
> getting created with "ON DELETE CASCADE" as expected. I tried this in
> both MSSQL and Postgres. Is this expected behavior?

yeah I don't think this aspect of the FK is reflected right now. Plenty of room for that as a new feature of course....

>
> If so, is it possible to modify the FKs after they are reflected to
> set ondelete and onupdate as appropriate? My test code:
>
> http://paste.pocoo.org/show/399307/
>
> And my output:
>
> CREATE TABLE blog_comments (
> id INTEGER NOT NULL,
> blog_id INTEGER,
> CONSTRAINT blog_comments_pkey PRIMARY KEY (id),
> CONSTRAINT blog_comments_blog_id_fkey FOREIGN KEY(blog_id) REFERENCES
> blog (id)
> )
>
> It would seem to be a reflection issue though, b/c if I create a table
> manually, the "ON DELETE CASCADE" is added correctly.

I think if you went through the Table object's .constraints collection and looked for ForeignKeyConstraint objects you could set up the "onupdate" and "ondelete" attributes on them, sure.

[Randy Syring]

That was it, thanks. I was trying to go through the column and looking
at it's foreign_keys collection. When I set those values, it didn't
affect the output. Reflects my ignorance of SA, obviously.

Thanks again.

--------------------------------------
Randy Syring
Intelicom
Direct: 502-276-0459
Office: 502-212-9913

For the wages of sin is death, but the
free gift of God is eternal life in
Christ Jesus our Lord (Rom 6:23)

[Randy Syring]

If anyone is interested, I have some code that writes basic details for
tables, constraints, indexes, and triggers out to files. Code is here:

https://bitbucket.org/rsyring/mssqlddlwriter/

--------------------------------------
Randy Syring
Intelicom
Direct: 502-276-0459
Office: 502-212-9913

For the wages of sin is death, but the
free gift of God is eternal life in
Christ Jesus our Lord (Rom 6:23)

On 06/02/2011 11:03 AM, Michael Bayer wrote: