How to generate a file with DDL in the engine's SQL dialect? How to copy engine?

[Piotr Dobrogost]

At http://stackoverflow.com/q/870925/95735 there's a question titled "How to generate a file with DDL in the engine's SQL dialect in SQLAlchemy?" with the answer which gives the following code:

engine = create_engine( 'mssql+pyodbc://./MyDb', strategy='mock', executor= lambda sql, *multiparams, **params: print (sql)

In my case engine comes "from outside" so in order to make use of the above code I would have to clone engine changing only 'strategy' and 'executor'. I see  neither Engine.clone() method nor a parameter to create_engine() taking already existing engine as a template on which the new engine would be based upon. Is there any way to do such a copy and modification?

Is the answer above still the best way to get DDL resulting from calling create_all()?

Best regards,

Piotr Dobrogost

[Mike Bayer]

the only thing that is sigificant with "mock" is the first part of the
URL. You can just send the whole URL though, so just like this:


mock_engine = create_engine(real_engine.url, strategy="mock", ...)





>
>
> Best regards,
> Piotr Dobrogost
>
> --
> You received this message because you are subscribed to the Google
> Groups "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to sqlalchemy+...@googlegroups.com
> <mailto:sqlalchemy+...@googlegroups.com>.
> To post to this group, send email to sqlal...@googlegroups.com
> <mailto:sqlal...@googlegroups.com>.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

[Piotr Dobrogost]

On Monday, May 9, 2016 at 5:05:11 PM UTC+2, Mike Bayer wrote:

the only thing that is sigificant with "mock" is the first part of the
URL.   You can just send the whole URL though, so just like this:

mock_engine = create_engine(real_engine.url, strategy="mock", ...)

Thanks Mike.

In addition to DDL that comes from .create_all() there's additional DDL created by alembic. Is it possible to filter all log messages generated by SA by type of SQL (DDL in this case)?

Regards,

Piotr

[Simon King]

On Tue, May 10, 2016 at 12:37 PM, Piotr Dobrogost

Could you use Alembic's offline mode?

http://alembic.readthedocs.io/en/latest/offline.html

Simon

[Piotr Dobrogost]

On Tuesday, May 10, 2016 at 2:55:44 PM UTC+2, Simon King wrote:

Could you use Alembic's offline mode?

http://alembic.readthedocs.io/en/latest/offline.html

Thanks.

Will try.

Regards,

Piotr 

[Piotr Dobrogost]

On Monday, May 9, 2016 at 5:05:11 PM UTC+2, Mike Bayer wrote:

the only thing that is sigificant with "mock" is the first part of the
URL.   You can just send the whole URL though, so just like this:

mock_engine = create_engine(real_engine.url, strategy="mock", ...)

I see differences between SQL emitted by mock engine and the one emitted by real engine.

Some columns in my db use JsonType which is based on Text type:

class JsonType(TypeDecorator):

    impl = Text
    (...)

class Tab(Base):

    annotations = Column(NestedMutationDict.as_mutable(JsonType))

    (...)

For these columns the type used by mock engine is TEXT whereas the type used by real engine is CLOB.

From mock:

CREATE TABLE nodes (

id INTEGER NOT NULL, 

type VARCHAR(30) NOT NULL, 

parent_id INTEGER, 

position INTEGER, 

_acl TEXT, 

name VARCHAR(250), 

title VARCHAR(250), 

annotations TEXT, 

path VARCHAR(2000), 

PRIMARY KEY (id), 

UNIQUE (parent_id, name), 

FOREIGN KEY(parent_id) REFERENCES nodes (id)

)

From real engine:

CREATE TABLE nodes (

id INTEGER NOT NULL, 

type VARCHAR2(30 CHAR) NOT NULL, 

parent_id INTEGER, 

position INTEGER, 

"_acl" CLOB, 

name NVARCHAR2(250), 

title NVARCHAR2(250), 

annotations CLOB, 

path NVARCHAR2(2000), 

PRIMARY KEY (id), 

UNIQUE (parent_id, name), 

FOREIGN KEY(parent_id) REFERENCES nodes (id)

)

The second difference is that real engine quotes the name of column which begins with underscore (_acl) but the mock one does not. Oracle treats unquoted version as invalid giving the following error:

ORA-00911: invalid character

What's the reason for these differences?

Regards,

Piotr

[Simon King]

On Wed, May 11, 2016 at 9:39 AM, Piotr Dobrogost

At least for the quoting issue, it sounds like you aren't using the
Oracle dialect. What does "print mock_engine.dialect" report?

Simon

[Piotr Dobrogost]

> On Wednesday, May 11, 2016 at 11:03:57 AM UTC+2, Simon King wrote:
>> On Wed, May 11, 2016 at 9:39 AM, Piotr Dobrogost
>>

>> What's the reason for these differences?
>
> At least for the quoting issue, it sounds like you aren't using the
> Oracle dialect. What does "print mock_engine.dialect" report?

<sqlalchemy.dialects.oracle.cx_oracle.OracleDialect_cx_oracle object at 0x7f99d2b0db10> 

Regards,

Piotr

[Simon King]

On Wed, May 11, 2016 at 10:37 AM, Piotr Dobrogost

Ah, ok. The problem is that the "sql" parameter in your executor
function has not yet been compiled for the dialect in use. Try this
instead:

from __future__ import print_function
import sqlalchemy as sa

engine = sa.create_engine(
'oracle://',
strategy='mock',
executor=lambda sql, *multiparams, **params: print(sql.compile(bind=engine))
)

metadata = sa.MetaData()
t = sa.Table(
'_acl',
metadata,
sa.Column('_id', sa.Integer, primary_key=True),
)

metadata.create_all(engine)

engine.execute(t.select())

Simon

[Piotr Dobrogost]

On Wednesday, May 11, 2016 at 12:37:15 PM UTC+2, Simon King wrote:

Ah, ok. The problem is that the "sql" parameter in your executor
function has not yet been compiled for the dialect in use. Try this

Thanks Simon, this works (SA 1.0.12) although according to the answer in SA 0.9+ this shouldn't be needed.

Regards,

Piotr