Re: Rename an existing constraint

[jens . troeger]

Thank you, Mike!

I’ll take a closer look at your proposed code this week.

I am curious though: not even MySQL has a rename feature 
,
 
is that because of consistency? PostgreSQL adds ALTER TABLE … RENAME 
CONSTRAINT  
with 9.2 though. (Boy, I keep running into issues that keep pushing me 
towards Porstgres.)

Jens


On Wednesday, August 30, 2017 at 12:21:17 AM UTC+10, Mike Bayer wrote:
>
> Unfortunately there's no RENAME CONSTRAINT directive, so dropping and 
> recreating is how it has to go. 
>
> Alembic and SQLAlchemy together do have the ability to return lists of 
> constraints and produce CREATE instructions for them.   Recently I did 
> some of this related to foreign keys, and the same idea would apply to 
> indexes, unique constraints, CHECK constraints.   Here's two ways to 
> do that to give you some ideas: 
>
>
> from alembic.operations.ops import CreateForeignKeyOp 
> from alembic.migration import MigrationContext 
> from alembic.operations import Operations 
>
> with engine.connect() as conn: 
>
> ctx = MigrationContext.configure(conn) 
> op = Operations(ctx) 
> m = MetaData(naming_convention={...}) 
>
> t = Table(table_name, m, autoload_with=conn) 
> for fk in t.foreign_keys: 
> op.drop_constraint( 
> fk.name, table_name, type_="foreignkey") 
>
> # give fk the name we want 
> fk.name = "somename" 
>
> # or name it None to get the naming convention 
> # fk.name = None 
>
> create_fk = CreateForeignKeyOp.from_constraint(fk) 
>
> op.invoke(create_fk) 
>
>
> or using the inspector, more manual / explicit: 
>
> from sqlalchemy import inspect 
> with engine.connect() as conn: 
>
> ctx = MigrationContext.configure(conn) 
> op = Operations(ctx) 
> insp = inspect(conn) 
> for fk in insp.get_foreign_keys(table_name): 
>
> for fk in fks: 
> op.drop_constraint( 
> fk['name'], fk['source_table'], type_="foreignkey") 
>
> for fk in fks: 
> op.create_foreign_key( 
> fk['name'], fk['source_table'], 
> fk['referred_table'], 
> fk['constrained_columns'], 
> fk['referred_columns'], 
> onupdate=fk['options'].get('onupdate'), 
> ondelete=fk['options'].get('ondelete'), 
> deferrable=fk['options'].get('deferrable'), 
> initially=fk['options'].get('initially'), 
> ) 
>

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy-alembic" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy-alembic+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Is Textual SQL DB neutral

[Ken MacKenzie]

I have a query I have constructed and I had to deal with a composite 
primary key to select items;

q = s.query(cls)
or_cond = []
for x in id_batch:

pkf = [text(f + "=='" + v + "'") for f,v in zip(cls.SQL_PK, 
x)]
and_cond = (and_(*pkf))
or_cond.append(and_cond)


q = q.filter(or_(*or_cond)).delete(synchronize_session=False)

cls.SQL_PK is a tuple of the primary key fields for the model described by 
class.  This is a class method that is part of a inherited class to the 
model

The current target is SQL Server.  My concern is using text('field = 
'value'), is that going to work for other DB targets like say postgres?

The first round of doing this I tried using a 
tuple_(*cls.SQL_PK).in_(id_batch), but that did not work and the resulting 
SQL id not work in SSMS leading me to believe that SQL Server (or at least 
the version we are using) does not support tuples.

Ken

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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] Re: Pymssql and autocommit

[Mike Bayer]

good news, SQL Server now supports an easy to use linux container
which means I can SQL Server to my CI setup, which means I need it to
be able to CREATE DATABASE which means I need generalized
per-connection "AUTOCOMMIT" support for pyodbc and pymssql.I'll be
adding this soon.

On Sun, Aug 27, 2017 at 6:12 PM, Mike Bayer  wrote:
>
>
> On Aug 27, 2017 12:26 PM, "Thorsten Kampe" 
> wrote:
>
> * Mike Bayer (Sun, 27 Aug 2017 11:35:27 -0400)
>>
>> On Sun, Aug 27, 2017 at 9:52 AM, Thorsten Kampe
>>  wrote:
>> > Hi,
>> >
>> > is it possible to enable autocommit for a MSSQL
>> > connection with Pymssql? The feature is available in
>> > Pymssql since 2014. PyODBC - the default MSSQL driver -
>> > supports autoconnect in the connection string.
>> >
>> > I have been unsuccessful trying to enable autocommit in
>> > the connection string or as a keyword for
>> > `create_engine` (`isolation_level = 'AUTOCOMMIT'` or
>> > `autocommit = True`).
>>
>> the pyodbc connector does accept 'autocommit' as a query parameter
>> which will be coerced to boolean, so this should work:
>>
>> create_engine("mssql+pyodbc://scott:tiger@dsn?autocommit=true")
>
> Thanks for the quick answer. My question was
> specifically about the PyMSSQL driver (not the PyODBC
> driver).
>
> The reason why I'm asking is that I would like to
> connect from Linux.
>
> PyODBC works fine on Windows. On Linux I simply don't
> know what to use instead of...
>
> driver=SQL+Server+Native+Client+11.0
>
> ...in the connection string when using a "Hostname-
> based connection".
>
>
> You use the ODBC connector that comes with FreeTDS in conjunction with
> UnixODBC.   That said, pymssql probably works a little better on linux so
> you can set this autocommit flag using a connect hook
> (http://docs.sqlalchemy.org/en/latest/core/events.html?highlight=connect#sqlalchemy.events.PoolEvents.connect)
> or we can accept a PR for a real feature addition.
>
>
>
>
>
> Thorsten
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example.  See  http://stackoverflow.com/help/mcve for a full
> description.
> ---
> 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 - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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: Rename an existing constraint

[Mike Bayer]

Unfortunately there's no RENAME CONSTRAINT directive, so dropping and
recreating is how it has to go.

Alembic and SQLAlchemy together do have the ability to return lists of
constraints and produce CREATE instructions for them.   Recently I did
some of this related to foreign keys, and the same idea would apply to
indexes, unique constraints, CHECK constraints.   Here's two ways to
do that to give you some ideas:


from alembic.operations.ops import CreateForeignKeyOp
from alembic.migration import MigrationContext
from alembic.operations import Operations

with engine.connect() as conn:

ctx = MigrationContext.configure(conn)
op = Operations(ctx)
m = MetaData(naming_convention={...})

t = Table(table_name, m, autoload_with=conn)
for fk in t.foreign_keys:
op.drop_constraint(
fk.name, table_name, type_="foreignkey")

# give fk the name we want
fk.name = "somename"

# or name it None to get the naming convention
# fk.name = None

create_fk = CreateForeignKeyOp.from_constraint(fk)

op.invoke(create_fk)


or using the inspector, more manual / explicit:

from sqlalchemy import inspect
with engine.connect() as conn:

ctx = MigrationContext.configure(conn)
op = Operations(ctx)
insp = inspect(conn)
for fk in insp.get_foreign_keys(table_name):

for fk in fks:
op.drop_constraint(
fk['name'], fk['source_table'], type_="foreignkey")

for fk in fks:
op.create_foreign_key(
fk['name'], fk['source_table'],
fk['referred_table'],
fk['constrained_columns'],
fk['referred_columns'],
onupdate=fk['options'].get('onupdate'),
ondelete=fk['options'].get('ondelete'),
deferrable=fk['options'].get('deferrable'),
initially=fk['options'].get('initially'),
)







On Tue, Aug 29, 2017 at 7:01 AM,   wrote:
> Hello,
>
> I started out migrating my db schema forward using Alembic, and without any
> constraint naming convention. That caused constraints to be named using
> MySQL’s default naming. Alas, now I added a naming convention (see doc here)
> which is different than the db’s naming.
>
> So now I face a mix of current db-style constraint names, and new
> convention-stale constraint names. That’ll cause me a headache.
>
> What is the recommended way of renaming all existing constraint names? Is
> dropping/creating them the only way, or did I fail finding a
> “rename_constraint()” function in Alembic?
>
> Thanks!
> Jens
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy-alembic" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to sqlalchemy-alembic+unsubscr...@googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy-alembic" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy-alembic+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Rename an existing constraint

[jens . troeger]

Hello,

I started out migrating my db schema forward using Alembic, and without any 
constraint naming convention. That caused constraints to be named using 
MySQL’s default naming. Alas, now I added a naming convention (see doc here 
)
 
which is different than the db’s naming.

So now I face a mix of current db-style constraint names, and new 
convention-stale constraint names. That’ll cause me a headache.

What is the recommended way of renaming all existing constraint names? Is 
dropping/creating them the only way, or did I fail finding a 
“rename_constraint()” function in Alembic?

Thanks!
Jens

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy-alembic" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy-alembic+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.