Re: [sqlalchemy] Declaring column NOT NULL only for specific dialect (Oracle)?
On 04/26/2016 11:19 AM, Piotr Dobrogost wrote:
Questions I asked in my last post are concerned more with implementing
IDENTITY for Oracle. Here the situation is not so simple and simply
replacing text is not possible as "GENERATE AS IDENTITY" phrase must
be placed in right order with regard to other keywords. That's what
made me looking at DDLCompiler.get_column_specification() and what
lead me to asking the questions I asked. I assure you I've read the
link you sent before asking my questions. This example just doesn't
seem to answer those questions.
I'd like you to know that I'm treating our conversation as occasion to
learn more about SA and to know how things should be done. Replacing
NOT NULL with NULL or vice versa is just very specific way of solving
particular problem and does not bring me closer to knowing how to
manipulate DDL for column creation in general.
OK so for the "GENERATE AS IDENTITY", first off we'd like to support
that for Oracle, and that would involve lots of version detection /
switches / new logic added to oracle/base.py, as it changes not only the
DDL but a lot of the specific workings of the dialect in how it renders
an INSERT statement.
As far as getting just the DDL right now, the "text.replace()" approach
is still close to the most expedient approach, which is to get at that
text, then use a regular expression replace in order to add "GENERATE AS
IDENTITY". I'd do it like this:
from sqlalchemy.schema import CreateColumn
@compiles(CreateColumn, "oracle")
def _do_thing(element, compiler, **kw):
text = compiler.visit_create_column(element, **kw)
if element.element.info.get('use_identity', False):
text = re.sub(r'(NUMBER|INTEGER) ', r'\1 GENERATED ALWAYS AS
IDENTITY', text)
return text
then in Column I'd set up a flag as follows:
Column('mycol', Numeric, info={"use_identity": True})
For your approach with NULL, I'd still advise using @compiles for that
as well. Looking at the engine.name and changing the column.nullable is
fine but it means you need to coordinate the engine with the table
metadata itself which is awkward and wouldn't work if your application
had multiple engines in one process.
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.
Re: [sqlalchemy] Declaring column NOT NULL only for specific dialect (Oracle)?
On Tue, Apr 26, 2016 at 4:42 PM, Mike Bayer wrote:
>
> So first we'll take a breathbreathe...and we're back. The best thing
> about air is that it's in most places we live and it's free.
Breathing is very important indeed :) I highly recommend taking
freediving course to anyone – 5 minutes without breathing is really
unique experience.
> So going back
> to the link I sent,
> http://docs.sqlalchemy.org/en/rel_1_0/core/ddl.html?highlight=createtable#sqlalchemy.schema.CreateColumn
> , scroll down and the second example illustrates how to get the default DDL
> for the column, as rendered by the current compiler, here is a 5 second
> adaptation of that:
>
> from sqlalchemy.schema import CreateColumn
>
> @compiles(CreateColumn, "oracle")
> def _do_thing(element, compiler, **kw):
> text = compiler.visit_create_column(element, **kw)
> text = text.replace("NOT NULL", "NULL")
> return text
>
> I hope this helps.
I own you explanation which I should have written in my last post. In
the end the need to change nullability concerned only one table so I
went with
elif engine.dialect.name == 'oracle': # pragma: no cover
Node.name.prop.columns[0].nullable = True
in Kotti framework which works.
Questions I asked in my last post are concerned more with implementing
IDENTITY for Oracle. Here the situation is not so simple and simply
replacing text is not possible as "GENERATE AS IDENTITY" phrase must
be placed in right order with regard to other keywords. That's what
made me looking at DDLCompiler.get_column_specification() and what
lead me to asking the questions I asked. I assure you I've read the
link you sent before asking my questions. This example just doesn't
seem to answer those questions.
I'd like you to know that I'm treating our conversation as occasion to
learn more about SA and to know how things should be done. Replacing
NOT NULL with NULL or vice versa is just very specific way of solving
particular problem and does not bring me closer to knowing how to
manipulate DDL for column creation in general.
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.
Re: [sqlalchemy] Declaring column NOT NULL only for specific dialect (Oracle)?
On 04/26/2016 06:27 AM, Piotr Dobrogost wrote:
Do I see right, that using @compiles(schema.CreateColumn, 'oracle') is
not good as it's being invoked too late to have access to colspec?
It seems I have to override DDLCompiler.get_column_specification() and
then I have to copy & paste code from this method because there's no
way to reuse code which generates DEFAULT and NULL statements, yes?
Overriding DDLCompiler.get_column_specification() means I subsequently
have to "register" my DDLCompiler's subclass somehow. How do I do it?
I did not find any information on this in docs. Looking at PGDialect
in source, there's ddl_compiler attribute but I don't see any API to
change it. You don't want me to monkey patch OracleDialect, do you?.
So first we'll take a breathbreathe...and we're back. The best
thing about air is that it's in most places we live and it's free. So
going back to the link I sent,
http://docs.sqlalchemy.org/en/rel_1_0/core/ddl.html?highlight=createtable#sqlalchemy.schema.CreateColumn
, scroll down and the second example illustrates how to get the default
DDL for the column, as rendered by the current compiler, here is a 5
second adaptation of that:
from sqlalchemy.schema import CreateColumn
@compiles(CreateColumn, "oracle")
def _do_thing(element, compiler, **kw):
text = compiler.visit_create_column(element, **kw)
text = text.replace("NOT NULL", "NULL")
return text
I hope this helps.
Regards,
Piotr Dobrogost
ps.
I have an impression there's hardly any traffic on #sqlalchemy during
Europe's working hours :(
--
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] Declaring column NOT NULL only for specific dialect (Oracle)?
On Fri, Apr 22, 2016 at 5:57 PM, Mike Bayer wrote:
>
> On 04/22/2016 11:51 AM, Piotr Dobrogost wrote:
>>
>> >Column('some_col', nullable=False, info={"oracle_not_null": False})
>> >
>> >then in your @compiles recipe look for column.info
>>
Re: [sqlalchemy] Declaring column NOT NULL only for specific dialect (Oracle)?
On 04/22/2016 11:51 AM, Piotr Dobrogost wrote:
>On Friday, April 22, 2016 at 5:02:17 PM UTC+2, Mike Bayer wrote:
>
>For this one, assuming you're using metadata.create_all() and not an
>alembic migration, the CreateColumn DDL construct gives you a hook for
>this kind of thing:
Thanks for info. Yes, I'm using create_all() but eventually I'll have to
start using alembic. Are there any problems with using alembic in the
future while creating initial db schema with create_all()?
In Alembic I don't think directives like "add_column()" are currently
taking advantage of the CreateColumn construct. create_table() should
be however.
>Column('some_col', nullable=False, info={"oracle_not_null": False})
>
>then in your @compiles recipe look for column.info
.
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.
Re: [sqlalchemy] Declaring column NOT NULL only for specific dialect (Oracle)?
>On Friday, April 22, 2016 at 5:02:17 PM UTC+2, Mike Bayer wrote:
>
>For this one, assuming you're using metadata.create_all() and not an
>alembic migration, the CreateColumn DDL construct gives you a hook for
>this kind of thing:
Thanks for info. Yes, I'm using create_all() but eventually I'll have to
start using alembic. Are there any problems with using alembic in the
future while creating initial db schema with create_all()?
>Column('some_col', nullable=False, info={"oracle_not_null": False})
>
>then in your @compiles recipe look for column.info['oracle_not_null']
>while at the same time looking at compiler.dialect.name == 'oracle'.
I get it. However as we want this for every autoincrement column I guess we
can check for already provided "autoincrement" flag and avoid passing
column.info altogether. Nice.
It seems like this could be used to render IDENTITY keyword for
autoincrement columns for Oracle 12c which I asked about recently in my
post titled "Support for Oracle 12c auto increment (IDENTITY) columns?"
(https://groups.google.com/forum/#!topic/sqlalchemy/Jg_kV6VF0_E).
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.
Re: [sqlalchemy] Declaring column NOT NULL only for specific dialect (Oracle)?
For this one, assuming you're using metadata.create_all() and not an
alembic migration, the CreateColumn DDL construct gives you a hook for
this kind of thing:
http://docs.sqlalchemy.org/en/rel_1_0/core/ddl.html?highlight=createtable#sqlalchemy.schema.CreateColumn
in the example there you can see it's looking for a flag in column.info.
So your column would be like this:
Column('some_col', nullable=False, info={"oracle_not_null": False})
then in your @compiles recipe look for column.info['oracle_not_null']
while at the same time looking at compiler.dialect.name == 'oracle'.
On 04/22/2016 07:29 AM, Piotr Dobrogost wrote:
As Oracle does not support inserting empty string into NOT NULL column I
would like to declare specific column as nullable only for Oracle.
How can I do this?
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.
[sqlalchemy] Declaring column NOT NULL only for specific dialect (Oracle)?
As Oracle does not support inserting empty string into NOT NULL column I would like to declare specific column as nullable only for Oracle. How can I do this? 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.
