Re: [sqlalchemy] How to generate a file with DDL in the engine's SQL dialect? How to copy engine?

[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

-- 
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+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] How to generate a file with DDL in the engine's SQL dialect? How to copy engine?

[Simon King]

On Wed, May 11, 2016 at 10:37 AM, Piotr Dobrogost
 wrote:
>> 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?
>
>  0x7f99d2b0db10>
>

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

-- 
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+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] How to generate a file with DDL in the engine's SQL dialect? How to copy engine?

[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? 

 


Regards,
Piotr

-- 
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+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] How to generate a file with DDL in the engine's SQL dialect? How to copy engine?

[Simon King]

On Wed, May 11, 2016 at 9:39 AM, Piotr Dobrogost
 wrote:
> 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?
>

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

Simon

-- 
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+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] How to generate a file with DDL in the engine's SQL dialect? How to copy engine?

[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

-- 
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+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] How to generate a file with DDL in the engine's SQL dialect? How to copy engine?

[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 

-- 
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+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] How to generate a file with DDL in the engine's SQL dialect? How to copy engine?

[Simon King]

On Tue, May 10, 2016 at 12:37 PM, Piotr Dobrogost
 wrote:
> 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)?
>

Could you use Alembic's offline mode?

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

Simon

-- 
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+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] How to generate a file with DDL in the engine's SQL dialect? How to copy engine?

[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

-- 
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+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] How to generate a file with DDL in the engine's SQL dialect? How to copy engine?

[Mike Bayer]

On 05/09/2016 10:51 AM, Piotr Dobrogost wrote:

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()?


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+unsubscr...@googlegroups.com
.
To post to this group, send email to sqlalchemy@googlegroups.com
.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.


--
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+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.