SQLAlchemy 0.9.6 Released

[Mike Bayer]

Hey folks -

Building on the great success of 0.9.5 just a few hours ago today, 0.9.6
is out!   One of the changes in 0.9.5 had more of an impact than
expected and I determined it was better just reverting this particular
change, as there's a much better solution to it on deck for version 1.0
- this change involved whether or not the None value implicitly
created when you do an attribute get should impact the flush process,
and it was driven by the edge case of the so-called row switch
scenario when an INSERT/DELETE pair is turned into an UPDATE.  In 1.0,
at the moment it looks like the approach will be to do away with the
implicit set of None when you fetch a missing attribute; you'll still
get the None but the underlying state won't be changed.

For details see issue #3060 and #3061.

Download SQLAlchemy 0.9.6 at:

http://www.sqlalchemy.org/download.html


-- 
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 1.0.1 Released

[Mike Bayer]

SQLAlchemy release 1.0.1 is now available.

This is a quick bug-fix release that repairs several new regressions 
identified in the 1.0.0 release, not found during the beta period. All 
users of 1.0.0 are encouraged to upgrade to 1.0.1.


Key elements of this release include fixes regarding the NEVER_SET 
symbol leaking into queries in some cases, fixes to SQLite when using 
DDL in conjunction with referential integrity enabled, a fix to the 
EXISTS construct which primarily impacts queries that use special 
datatypes, and repairs to the Firebird dialect regarding the new 
LIMIT/OFFSET features.


In order to accommodate some of these fixes, there are three additional 
behavioral changes in 1.0.1; a new warning is emitted when using DDL 
with SQLite in conjunction with mutually-dependent foreign keys (e.g. a 
reference cycle), a new warning is emitted when running ORM relationship 
comparisons when the target object contains the value None for any of 
the Python-side column values, and a change is made regarding which data 
values are used within a relationship comparison that uses the != 
operator, in order to make the behavior consistent with that of the == 
operator as used in the same context. The migration notes contains 
updates for all three of these changes, and they are each linked 
directly from the changelog which should be carefully reviewed.


Changelog for 1.0.1 is at: http://www.sqlalchemy.org/changelog/CHANGES_1_0_1

SQLAlchemy 1.0.1 is available on the download page at:

http://www.sqlalchemy.org/download.html

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

Re: Branching

[Mike Bayer]

On 5/3/15 3:44 PM, Walter King wrote:
We've been using branching for a bit and it mostly works the way we 
want.  Twice now we've run into the error when upgrading heads:


AssertionError: Can't do an UPDATE because downrevision is ambiguous

The policy we've had is when you create a new revision, if theres a 
branch then you must first make a merge on your branch.  So in this 
case, two people branch off of three heads, they make an identical 
merge revision, and have a child each off the merge.  When you look at 
the history it seems like it does what youd expect, makes two heads, 
but it wont apply.


33e21c000cfe - 178d4e761bbd (head),
2bef33cb3a58, 3904558db1c6, 968330f320d - 33e21c000cfe (mergepoint), 
Merging

46c99f866004 - 18f46b42410d (head),
2bef33cb3a58, 3904558db1c6, 968330f320d - 46c99f866004 (mergepoint), 
Merging

f0fa4315825 - 3904558db1c6 (branchpoint),


let's start with what you have there:

33e21c000cfe - 178d4e761bbd (head),
2bef33cb3a58, 3904558db1c6, 968330f320d - 33e21c000cfe 
(mergepoint)

46c99f866004 - 18f46b42410d (head),
2bef33cb3a58, 3904558db1c6, 968330f320d - 46c99f866004 
(mergepoint)

f0fa4315825 - 3904558db1c6 (branchpoint),


let me substitute names that are more human friendly:

A - B2

B1, B2, B3 - C1
B1, B2, B3 - C2

C1 - D1 (head),

C2 - D2 (head),

So I think, that's not all the history?  Doesn't matter for the test 
case I'm making so I assume it's something more like this:


A - B1,
A - B2,
A - B3,

B1, B2, B3 - C1
B1, B2, B3 - C2

C1 - D1 (head),

C2 - D2 (head),

that is, you started with a single root, I'm assuming.

The steps to upgrade from A to D1/D2 come out normally:

upgrade a - b3, b3
upgrade a - b1, b1
upgrade a - b2, b2
upgrade b1, b2, b3 - c2, c2
upgrade c2 - d2, d2
upgrade b1, b2, b3 - c1, c1
upgrade c1 - d1, d1

so those states have to be each represented in alembic_version 
individually.  Logging those transitions they come out as:


DEBUG:alembic.migration:update a to b3
DEBUG:alembic.migration:new branch insert b1
DEBUG:alembic.migration:new branch insert b2
DEBUG:alembic.migration:merge, delete ['b1', 'b2'], update b3 to c2
DEBUG:alembic.migration:update c2 to d2

where update, insert, and delete mean that's the SQL we're going 
to run on alembic_version.


It then crashes on b1, b2, b3 - c1, because it thinks it's supposed to 
to an UPDATE, but there is no row to UPDATE because all three of b1, b2, 
b3 are gone.  What it really should do here is an INSERT of c1. The 
logic that asks, should we insert a row? looks to see if the revision 
(in this case c1) has multiple entries as descendants, in which case 
it's a MERGE point, as c1 is.   the MERGE operation is currently coded 
in all cases to expect that when we reach a MERGE point, all of its 
ancestors are definitely there, else how else did we arrive at a MERGE 
point?  But in this case you've taken the same three revs and merged 
them twice, in effect merged and branched at the same time.  This was 
never expected.


The internal mechanics ask the question, if we have more than one 
anscestor, we're a MERGE point, therefore we definitely aren't INSERTing 
an identifier.  They also assert that, if we have only one anscestor, 
we're not a MERGE point, so if our ansestor *is* in the current heads, 
we do an UPDATE and if it isn't, we do an INSERT. The logic here 
can be simplified, such that, if none of our ancestors are in the 
current heads, we do an INSERT.


I've captured this all in 
https://bitbucket.org/zzzeek/alembic/issue/297/transition-from-multiple-revs-to-one-as-a 
where the logic has been opened up for mergepoints such that an INSERT 
is considered to be OK under these conditions, that is in 
c75be37640a19990d385a805 and will be out in 0.7.6.
















Any pointers on how to avoid this?

Thanks!
--
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 
mailto: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.

SQLAlchemy 1.0.4 Released

[Mike Bayer]

SQLAlchemy release 1.0.4 is now available.

With release 1.0.4 we are pretty much through all the bumps we had in 
the 0.9 to 1.0 movement. Four additional very minor regressions are 
solved in this release. Most prominently, another issue regarding recent 
enhancements to the Query.column_descriptions collection, also slated 
for release in 0.9.10, was fixed.


Changelog for 1.0.4 is at: http://www.sqlalchemy.org/changelog/CHANGES_1_0_4

SQLAlchemy 1.0.4 is available on the download page at:

http://www.sqlalchemy.org/download.html

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

Alembic 0.7.6 Released

[Mike Bayer]

Alembic release 0.7.6 is now available.

This release has a few bug fixes in the new batch mode as well as one 
in the new branching model, which otherwise has gone amazingly well 
considering how complicated it was to implement.


Also a couple of new features to help with custom types and with better 
generation of literals in --sql mode.


Changelog is up at: 
http://alembic.readthedocs.org/en/latest/changelog.html#change-0.7.6


Download Alembic at: https://pypi.python.org/pypi/alembic/0.7.6


--
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 1.0.0 Released

[Mike Bayer]

Hello list !

It is my great pleasure to announce that SQLAlchemy release 1.0.0 is now 
available.


Release 1.0.0 marks the tenth major SQLAlchemy series and occurs within 
the tenth year of the SQLAlchemy project overall; development started in 
2005 and the release of 0.1.0 was in February of 2006.


Calls for SQLAlchemy to go 1.0 started early on, as early as version 
0.3 (!).   However, the magnitude of the task taken on by SQLAlchemy was 
much broader than that; the development philosophy taken by the project 
is one of slowly building out a wide-reaching base of functionality, 
integrating many rounds of refactoring and rethinks over a long period 
of time and building new paradigms and features on top only as the 
foundation matures enough to support them.


Users of 1.0 have the benefit of ten years of production deployments, 
total rethinks of core APIs in early versions, a vast number of API 
additions and refinements over later versions, at least a dozen major 
internal rearchitectures, and as always a relentless focus on improving 
performance.


The SQLAlchemy project could not be what it is today without the 
unbelievable support, input, and sheer love of the user community - from 
the vast amounts of knowledge and improvements gained from tens of 
thousands of mailing list messages, to the improvements hammered out 
through over three thousand bug reports, to the amazing developers all 
around the world who have presented talks and tutorials on SQLAlchemy 
and of course the audiences who continue to attend them, to the bloggers 
and book authors supporting our community, to the tweeters sending 
gratitude our way, to our many hundreds of contributors of patches and 
pull requests, as well as financial contributors who have consistently 
supported SQLAlchemy's hosting costs, as well as more than a few 
burritos ;).


In particular, SQLAlchemy's success was made possible by its original 
developer team, and I would like to express to all of them my very deep 
gratitude for their tremendous efforts towards contributing code and 
wisdom to the project, as well as support of my work from very early on:


* Jason Kirtland
* Gaëtan de Menten
* Diana Clarke
* Michael Trier
* Philip Jenvey
* Ants Aasma
* Paul Johnston
* Jonathan Ellis

I'd also like to thank Simon King and Jonathan Vanasco for their ongoing 
contributions towards the mailing list, Alex Grönholm, creator of the 
excellent sqlacodegen [1] project, for his energetic and ubiquitous 
support of thousands of IRC users, and Sanjiv Singh, early developer of 
GeoAlchemy [2] for the awesome set of drink coasters I use every day :).


Release 1.0.0 features an array of usability enhancements, new features, 
bug fixes, and considerable performance enhancements. After five short 
beta releases, it is anticipated that the impact of upgrading from 0.9 
or even 0.8 to 1.0.0 should be minimal; however in all cases, users are 
highly encouraged to carefully read through the behavioral enhancements 
and changes documented in the 1.0 migration notes, at What's new in 
1.0? at http://www.sqlalchemy.org/docs/10/changelog/migration_10.html.


Changelog for 1.0.0 is at: http://www.sqlalchemy.org/changelog/CHANGES_1_0_0

SQLAlchemy 1.0.0 is available on the download page at 
http://www.sqlalchemy.org/download.html



[1] https://pypi.python.org/pypi/sqlacodegen
[2] http://geoalchemy.org/


--
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 1.0.6 Released

[Mike Bayer]

SQLAlchemy release 1.0.6 is now available.

This release includes a handful of new bugfixes and some small feature 
adds, pushed along by a high level of community involvement.


The most critical issue fixed involves the versioning feature of the 
ORM, where the version counter of a row could be incremented when there 
was otherwise no net change on the row; this was a regression from 0.9. 
A few other minor issues were also regressions or semi-regressions 
(e.g. a bug that existed in 0.9, but is more likely to be encountered in 
1.0 due to greater use of a feature). Issues involving the SQL Server 
VARBINARY type, ORM queries for single inheritance mappings, and 
support for the very latest (2.7.1) release of psycopg2cffi are resolved.


The new features are all Postgresql centric, and include support for 
setting the storage parameters of a Postgresql index, as well as 
reflection of those parameters and the using algorithm of an index; 
fixes to the ExcludeConstraint construct to allow more open-ended SQL 
expressions, and support for controlling the size of the row buffer used 
when server side cursor mode is used.


Changelog for 1.0.6 is at:

http://www.sqlalchemy.org/changelog/CHANGES_1_0_6

SQLAlchemy 1.0.6 is available on the download page: 
http://www.sqlalchemy.org/download.html



http://www.sqlalchemy.org/download.html

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

Re: percentile symbol quoting in op

[Mike Bayer]

On 6/26/15 12:23 PM, Jonathon Nelson wrote:

Greetings!

I am trying to use alembic to drop and re-create a PL/pgSQL function, 
and the function has embedded percentile (for use in RAISE but also 
present in comments, etc...).


When rendering the upgrade as an 'offline' sql upgrade script, the 
percentiles end up escaped (with percentile):


Thus:

RAISE 'Param foo (%) has some badness to it.', foo;

turns into:

RAISE 'Param foo (%%) has some badness to it.', foo;

I'm creating the function like this:

op.execute( sa.text( sql_function_body ) )

What am I doing wrong?


the compiler for the Psycopg2 backend doubles up percent signs because 
they aren't otherwise accepted by the DBAPI.


you can have your offline SQL use the generic Postgresql backend instead 
which won't do this.  But it requires a monkeypatch:


Where your env.py says something like:

context.configure(
url=url, target_metadata=target_metadata, literal_binds=True)


for the url, do this:

from sqlalchemy.engine import url
from sqlalchemy.dialects.postgresql import PGDialect
u = url.make_url(postgresql://)
u.get_dialect = PGDialect

context.configure(
url=u, target_metadata=target_metadata, literal_binds=True)


Alternatively you can make a compile rule on text() that reverses the 
escaping:


from sqlalchemy.ext.compiler import compiles
from sqlalchemy.sql.expression import TextClause

@compiles(TextClause, postgresql)
def _reverse_escaping(element, compiler, **kw):
text = compiler.process_text(element, **kw)
text = text.replace(%%, %)
return text











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

Re: percentile symbol quoting in op

[Mike Bayer]

On 6/26/15 12:49 PM, Mike Bayer wrote:



On 6/26/15 12:23 PM, Jonathon Nelson wrote:

Greetings!

I am trying to use alembic to drop and re-create a PL/pgSQL function, 
and the function has embedded percentile (for use in RAISE but also 
present in comments, etc...).


When rendering the upgrade as an 'offline' sql upgrade script, the 
percentiles end up escaped (with percentile):


Thus:

RAISE 'Param foo (%) has some badness to it.', foo;

turns into:

RAISE 'Param foo (%%) has some badness to it.', foo;

I'm creating the function like this:

op.execute( sa.text( sql_function_body ) )

What am I doing wrong?


the compiler for the Psycopg2 backend doubles up percent signs because 
they aren't otherwise accepted by the DBAPI.


you can have your offline SQL use the generic Postgresql backend 
instead which won't do this.  But it requires a monkeypatch:


Where your env.py says something like:

context.configure(
url=url, target_metadata=target_metadata, literal_binds=True)


for the url, do this:

from sqlalchemy.engine import url
from sqlalchemy.dialects.postgresql import PGDialect
u = url.make_url(postgresql://)
u.get_dialect = PGDialect

context.configure(
url=u, target_metadata=target_metadata, literal_binds=True)


Alternatively you can make a compile rule on text() that reverses the 
escaping:


from sqlalchemy.ext.compiler import compiles
from sqlalchemy.sql.expression import TextClause

@compiles(TextClause, postgresql)
def _reverse_escaping(element, compiler, **kw):
text = compiler.process_text(element, **kw)
text = text.replace(%%, %)
return text
I have some thoughts on working out this case eventually at 
https://bitbucket.org/zzzeek/alembic/issue/304/dbapi-agnostic-dialect-use-for-offline
















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

Re: Autogenerate - unknown table popping up in sqlalchemy.exc.NoSuchTableError

[Mike Bayer]

On 5/31/15 7:00 PM, Wavemaker wrote:
Well, upon digging further myself, I noticed that was an old foreign 
key in my Pictures table referring to the non-existant auth_user table:
 sql: last_changed_user_id integer NOT NULL REFERENCES 
auth_user (id)
This was not referenced anymore in the model. So, I guess that in 
order to use alembic properly I should not have manually deleted that 
table (in fact I did that before starting to use alembic), but to 
remove both the table and the foreign key in the model and then let 
alembic apply both changes to the database in one go. Correct?


that doesn't make too much sense because a relational database can't 
have a FOREIGN KEY to a table that doesn't exist. *unless* this is 
SQLite, which is just an odd case.   feel free to manually delete 
whatever tables you want, you just need to make sure you keep track of 
what's going on when you point a schema reflection tool at that database 
at some later point.



For reference purposes still find here the complete stack trace 
without having the solution applied yet. Note again that I inserted 
some debug statements here and there in the sqlalchemy/alembic code, 
so line numbers may deviate.


Traceback (most recent call last):
  File virtualenv\Scripts\alembic-script.py, line 11, in module
load_entry_point('alembic==0.7.6', 'console_scripts', 'alembic')()
  File F:\Workspace\MyProject\src\virtualenv\Lib\site-packages\alembi
c\config.py, line 439, in main
CommandLine(prog=prog).main(argv=argv)
  File F:\Workspace\MyProject\src\virtualenv\Lib\site-packages\alembi
c\config.py, line 433, in main
self.run_cmd(cfg, options)
  File F:\Workspace\MyProject\src\virtualenv\Lib\site-packages\alembi
c\config.py, line 416, in run_cmd
**dict((k, getattr(options, k)) for k in kwarg)
  File F:\Workspace\MyProject\src\virtualenv\Lib\site-packages\alembi
c\command.py, line 113, in revision
script.run_env()
  File F:\Workspace\MyProject\src\virtualenv\Lib\site-packages\alembi
c\script.py, line 390, in run_env
util.load_python_file(self.dir, 'env.py')
  File F:\Workspace\MyProject\src\virtualenv\Lib\site-packages\alembi
c\util.py, line 243, in load_python_file
module = load_module_py(module_id, path)
  File F:\Workspace\MyProject\src\virtualenv\Lib\site-packages\alembi
c\compat.py, line 79, in load_module_py
mod = imp.load_source(module_id, path, fp)
  File alembic\env.py, line 99, in module
run_migrations_online()
  File alembic\env.py, line 92, in run_migrations_online
context.run_migrations()
  File string, line 7, in run_migrations
  File F:\Workspace\MyProject\src\virtualenv\Lib\site-packages\alembi
c\environment.py, line 738, in run_migrations
self.get_context().run_migrations(**kw)
  File F:\Workspace\MyProject\src\virtualenv\Lib\site-packages\alembi
c\migration.py, line 300, in run_migrations
for step in self._migrations_fn(heads, self):
  File F:\Workspace\MyProject\src\virtualenv\Lib\site-packages\alembi
c\command.py, line 95, in retrieve_migrations
autogen._produce_migration_diffs(context, template_args, imports)
  File F:\Workspace\MyProject\src\virtualenv\Lib\site-packages\alembi
c\autogenerate\api.py, line 154, in _produce_migration_diffs
autogen_context, object_filters, include_schemas)
  File F:\Workspace\MyProject\src\virtualenv\Lib\site-packages\alembi
c\autogenerate\api.py, line 256, in _produce_net_changes
inspector, metadata, diffs, autogen_context)
  File F:\Workspace\MyProject\src\virtualenv\Lib\site-packages\alembi
c\autogenerate\compare.py, line 84, in _compare_tables
inspector.reflecttable(t, None)
  File F:\Workspace\MyProject\src\virtualenv\Lib\site-packages\sqlalc
hemy\engine\reflection.py, line 590, in reflecttable
exclude_columns, reflection_options)
  File F:\Workspace\MyProject\src\virtualenv\Lib\site-packages\sqlalc
hemy\engine\reflection.py, line 720, in _reflect_fk
**reflection_options
  File F:\Workspace\MyProject\src\virtualenv\Lib\site-packages\sqlalc
hemy\sql\schema.py, line 441, in __new__
metadata._remove_table(name, schema)
  File F:\Workspace\MyProject\src\virtualenv\Lib\site-packages\sqlalc
hemy\util\langhelpers.py, line 60, in __exit__
compat.reraise(exc_type, exc_value, exc_tb)
  File F:\Workspace\MyProject\src\virtualenv\Lib\site-packages\sqlalc
hemy\sql\schema.py, line 432, in __new__
table._init(name, metadata, *args, **kw)
  File F:\Workspace\MyProject\src\virtualenv\Lib\site-packages\sqlalc
hemy\sql\schema.py, line 513, in _init
self._autoload(metadata, autoload_with, include_columns)
  File F:\Workspace\MyProject\src\virtualenv\Lib\site-packages\sqlalc
hemy\sql\schema.py, line 528, in _autoload
self, include_columns, exclude_columns
  File F:\Workspace\MyProject\src\virtualenv\Lib\site-packages\sqlalc
hemy\engine\base.py, line 1481, in run_callable
return callable_(self, *args, **kwargs)
  File F:\Workspace\MyProject\src\virtualenv\Lib\site-packages\sqlalc
hemy\engine\default.py, 

Re: Autogenerate - unknown table popping up in sqlalchemy.exc.NoSuchTableError

[Mike Bayer]

On 5/29/15 6:36 PM, Wavemaker wrote:

Hello All,

I'm trying to use Alembic for versioning my Camelot 
(http://www.python-camelot.com) schema, which is essentially 
sqlalchemy on the database side. When using autogenerate I get a 
NoSuchTableError. The odd thing is that I get this error for a table 
auth_user that cannot find in my schema:


[snip]
  File F:\Workspace\MyProject\src\virtualenv\Lib\site-packages\sqlalc
hemy\util\langhelpers.py, line 60, in __exit__
compat.reraise(exc_type, exc_value, exc_tb)
  File F:\Workspace\MyProject\src\virtualenv\Lib\site-packages\sqlalc
hemy\sql\schema.py, line 432, in __new__
table._init(name, metadata, *args, **kw)
  File F:\Workspace\MyProject\src\virtualenv\Lib\site-packages\sqlalc
hemy\sql\schema.py, line 513, in _init
self._autoload(metadata, autoload_with, include_columns)
  File F:\Workspace\MyProject\src\virtualenv\Lib\site-packages\sqlalc
hemy\sql\schema.py, line 528, in _autoload
self, include_columns, exclude_columns
  File F:\Workspace\MyProject\src\virtualenv\Lib\site-packages\sqlalc
hemy\engine\base.py, line 1481, in run_callable
return callable_(self, *args, **kwargs)
  File F:\Workspace\MyProject\src\virtualenv\Lib\site-packages\sqlalc
hemy\engine\default.py, line 364, in reflecttable
return insp.reflecttable(table, include_columns, exclude_columns)
  File F:\Workspace\MyProject\src\virtualenv\Lib\site-packages\sqlalc
hemy\engine\reflection.py, line 578, in reflecttable
raise exc.NoSuchTableError(table.name)
sqlalchemy.exc.NoSuchTableError: auth_user

Note: line numbers may be slightly different from the official 
alembic/sqlalchemy releases as I inserted some debug print/log 
statements. If helpful I can of course resort to the stock release.
not really sure, that trace is just within SQLAlchemy and I can't see 
where Alembic is attempting to locate a table of that name. Would need 
to at least see the stack trace going up into alembic's autogenerate logic.


Alembic shouldn't be trying to reflect a table of a certain name unless 
it detected that name when it asked the database for all table names.


Things to suspect are, casing problems, e.g. if your database has 
something like Auth_User on it, I see this is windows, if this is MySQL 
(is it?) the casing behavior changes based on operating system.




It doesn't matter whether I use an existing or a freshly initialized 
database. I was trying to follow the code to see where Alembic comes 
up with this additional table, but I get lost. Can somebody tell me 
some hints on how Alembic can come up with tables not directly defined 
in my schema?

it really can't.   Share your env.py as well.


I then hope I can figure whether I need that table at all. Another 
question however is why would alembic not handle the situation and 
simple create the new table, even if I am unaware that I need it?


Thanks!
--
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 
mailto: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.

Re: Is Firebird supported?

[Mike Bayer]

On 5/22/15 2:18 PM, Sylvain Martel wrote:
Hi, I tested this and while the firebird error message is now gone, 
I am getting this error:


Error while preparing SQL statement:)
sqlalchemy.exc.DatabaseError: (fdb.fbcore.DatabaseError) ('Error while 
preparing SQL statement:\n- SQLCODE: -204\n- Dynamic S
QL Error\n- SQL error code = -204\n- Table unknown\n- 
ALEMBIC_VERSION\n- At line 1, column 13', -204, 335544569) [SQL: INSER

T INTO alembic_version (version_num) VALUES ('38c4e85512a9')]
Exception ignored in: bound method PreparedStatement.__del__ of 
fdb.fbcore.PreparedStatement object at 0x044AC690

Traceback (most recent call last):
  File G:\Usr\py\Z1Venv\lib\site-packages\fdb\fbcore.py, line 3166, 
in __del__
  File G:\Usr\py\Z1Venv\lib\site-packages\fdb\fbcore.py, line 3042, 
in _close

ReferenceError: weakly-referenced object no longer exists

looks like an fdb bug to me







On Tuesday, 17 March 2015 18:53:17 UTC-4, Michael Bayer wrote:

on Alembic, there’s no dialect as of yet.Though you can make a
“fake” one like this:

from alembic.ddl import impl

class FirebirdDialect(impl.DefaultImpl):
__dialect__ = ‘firebird'
transactional_ddl = True


just making that class will add it to the _impls dict and will get
you past the KeyError you have there.

though as far as support for firebird-specific ALTER directives,
we haven’t looked into that.




Steve st...@canary.md javascript: wrote:

 Hi,

 I am testing out Firebird b/c I want to use Alembic to set up my
unit tests. Is Firebird supported by Alembic? I am getting the
error message below. I am running Alembic 0.6.0 and SqlAlchemy 0.8.2

 File
.../.virtualenv/local/lib/python2.7/site-packages/alembic/ddl/impl.py,
line 50, in get_by_dialect
 return _impls[dialect.name http://dialect.name]
 KeyError: 'firebird'

 Thanks,
 Steve


 --
 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 javascript:.
 For more options, visit https://groups.google.com/d/optout
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 
mailto: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.

Re: SQlite migration with batch mode - help needed

[Mike Bayer]

On 5/26/15 9:47 AM, Sylvain Martel wrote:

Hi,

   Not knowing much about SQL stuff, I used Flask-Migrate, which use 
alembic, to deal with migrations.  But now I have to deal with a 
migration where I can't simply destroy the SQLite database and remake 
it.(which is how I dealt so far when an ALTER constraint message 
popped up)


So I read the doc on batch mode for SQLite, but I admit it makes no 
sense to me.  How can I take this line in the migration script
op.create_foreign_key(None, 'incomes', 'classtype', 
['income_classtype'], ['id'])


and transform it to use batch mode so it works with SQLite?


Well one thing to note is that SQLite doesn't enforce foreign key 
constraints anyway unless you are enabling constraints on each 
connection.  So typical real-world use case, adding an FK constraint is 
not very useful unless you want to reflect it later.



Batch mode just means, take any op.XYZ() you want, and throw it 
underneath op.batch_alter_table(), and remove the tablename arg, that's it:



def upgrade():
with op.batch_alter_table('incomes') as batch_op:
batch_op.create_foreign_key(None, 'classtype', 
['income_classtype'], ['id'])



http://alembic.readthedocs.org/en/latest/ops.html#alembic.operations.BatchOperations.create_foreign_key



Basically, I'm adding a column to 2 tables with a one-to-one 
relationship.  Here are the models in case it helps.  The new columns 
are the last line in both models.


class Income(db.Model):
 __tablename__='incomes' id = db.Column(db.Integer,primary_key=True)
 date = db.Column(db.DateTime)
 amount = db.Column(Numeric)
 user_ = db.Column(db.Integer, db.ForeignKey('users.id'))
 income_classtype = db.Column(db.Integer,db.ForeignKey('classtype.id'))

class ClassType(db.Model):
 __tablename__ ='classtype' id = db.Column(db.Integer,primary_key=True)
 name = db.Column(db.String(64),unique=True)
 default = db.Column(db.Boolean,default=False,index=True)
 class_ = db.relationship('Classes',backref='classtype',lazy='dynamic')
 punchcard_type = 
db.relationship('Punchcard',backref='classtype',lazy='dynamic')
 seasonpass_type = 
db.relationship('SeasonPass',backref='classtype',lazy='dynamic')
 income_type = db.relationship('Income',backref='classtype',lazy ='dynamic')



Thanks
--
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 
mailto: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.

Re: Sharing metadata between alembic and SQLAlchemy

[Mike Bayer]

On 8/1/15 6:59 PM, Ian McCullough wrote:
I've been getting up to speed with SQLAlchemy and alembic and having a 
great time of it. This is some great stuff!


One thing that's been confounding me is this: My Alembic schema 
revisions are 'authoritative' for my metadata (i.e. I've started from 
scratch using alembic to build the schema from nothing), yet it 
doesn't appear that the metadata that exists in my alembic scripts can 
be leveraged by my models in my main app. So far, I've been 
maintaining effectively two versions of the metadata, one in the form 
of the flattened projection of my alembic schema rev scripts, and 
another in my application models scripts. I understand that there are 
some facilities to auto-re-generate the metadata from the RDBMS on the 
application side, but that seems potentially lossy, or at least 
subject to the whims of whatever DBAPI provider I'm using.


Is there a way to pull this flattened projection of metadata out of 
alembic and into my app's models at runtime? (i.e. import alembic, 
read the version from the live DB, then build the metadata by playing 
the upgrade scripts forward, not against the database, but against a 
metadata instance?) It seems like a fool's errand to try to keep my 
app models in sync with the flattened projection of the schema 
revisions by hand. My assumption is that I'm missing something 
super-obvious here.


There's a lot to say on this issue.The idea of the migrations 
themselves driving the metadata would be nice, and I think that the 
recent rewrite of django south does something somewhat analogous to this.


Also, the reorganization of Alembic operations into objects that you can 
hang any number of operations upon, this is due for Alembic 0.8, is also 
something that we'd leverage to make this kind of thing happen.


However, where it gets thorny is that neither Alembic migrations nor 
SQLAlchemy metadata are supersets of each other. That is, there's 
many things in SQLAlchemy metadata that currently has no formal 
representation in Alembic operations, the primary example is that of 
Python-side default operations on columns, which have no relevance to 
emitting ALTER statements.On the Alembic side, a set of migrations 
that takes care to only use the official Alembic op.* operations, and 
also does not use execute() for any of them, is the only way to 
guarantee that each change is potentially representable in SQLAlchemy 
metadata.A migration that emits op.execute(ALTER TABLE foo ADD 
COLUMN xyz) wouldn't work here, and a migration that has lots of 
conditionals and runtime logic might also not be useful in this way.


SQLAlchemy Table and Column objects also do not support removal from 
their parents.   This would be necessary in order to represent drop 
mutations as targeted at a SQLAlchemy metadata structure. This is 
something that could be implemented but SQLA has always made a point to 
not get into this because it's very complicated to handle cascades of 
dependent objects, whether that means raising an error or mimicking 
other functionality of a real drop operation.


Finally, the whole workflow of Alembic up til now has been organized for 
the opposite workflow; the MetaData is the authoritative model, and 
migrations are generated using tools like autogenerate to minimize how 
much they need to be coded by hand (and there is of course no issue of 
maintaining the same code in two places because migration scripts are a 
fixed point in time once created).This model is practical for many 
reasons; all of the above reasons, plus that it is compatible with 
applications that weren't using migrations up to point or were using 
some other system, plus that it allows easy pruning of old migrations.








Thanks,
Ian


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

Alembic 0.8.0 Released

[Mike Bayer]

Hi list -

Alembic 0.8.0 is now available.

This release includes a large series of reorganizations in order to 
provide several new extension APIs which dramatically increase the 
degree to which Alembic can be extended.


The average Alembic environment should hopefully not notice anything 
different as we've strived to maintain full backwards compatibility.   
However, both the operations and the autogenerate features have been 
entirely reorganized in order to produce a much more open-ended and 
integrated flow between these two systems.


At the base of the new system, everything in Alembic that is an 
operation is now boiled down into an object, known as a 
MigrateOperation.   Subclasses of MigrateOperation include the familiar 
things like AddColumnOp, CreateForeignKeyOP, and DropTableOp.These 
objects do *not* represent the SQL that we emit for these operations; 
the implementation of that remains within the alembic.ddl.* packages as 
it did before.Instead, they represent the operation as defined in 
Alembic as part of the interface.From these MigrateOperation 
objects, we now hang a variety of functions onto each object, including 
its representation in the alembic.op.* space we're all familiar with 
as well as in the batch_op.* namespace recently introduced; we hang an 
implementation onto it which includes how it should interact with the 
backend ddl system; we hang an Autogenerate render hook onto it, 
which represents how we'll write out its Python code inside of an 
autogenerated revision script, we hang an Autogenerate determine diffs 
hook onto it, which represents how we should compare a database to a 
MetaData structure in order to potentially produce this object during 
autogenerate, and we even hang things like a tuple diff as well as a 
reverse operation, to convert upgrades into downgrades and vice 
versa.   Within autogeneration, we now build up a structure of these 
MigrateOperation objects fully before rendering them, rather than going 
straight from raw diff information into a Python render.


With this new system, we haven't necessarily added any new functionality 
that wasn't there before, but we've now organized all the things we know 
about an Alembic operation centered around this object using 
now-public APIs to associate operations with each one.


 What this means is that you can now:

* create your own Alembic operations freely, which become available as 
first-class functions in alembic.op.* like any other


* create your own autogenerate schemes for these operations, including 
defining how to do the database compare as well as render the Python


* Modify completely what autogenerate does when it produces a new 
migration script; the entire contents of a generated script are 
delivered in object form to user-available hooks which can then rewrite 
the structure completely.  The autogenerate operation can be made to 
generate multiple files to different directories and/or branches, or no 
files at all.   A helper is also provided to make it easy to write short 
replacements of specific elements.Common user requests such as 
having autogenerate not produce a file if there were no net changes, 
converting an add column operation into an add column, nullable=True 
+ alter column set not null, or supporting alternative migration flows 
such as splitting migrations into expand and contract branches are 
now easily scriptable with no need to add new flags and switches to 
Alembic itself.


* Create applications that can receive an autogenerate stream and push 
it out directly as Alembic operations to a live database or SQL script, 
without even generating any revision scripts.


* Create alternative series of implementations for Alembic operations, 
such as writing them out to JSON or XML formats or perhaps building up a 
live MetaData structure based on them.



The release also includes a few other fixes and features.   In 
particular, in conjunction with the reorganization of operations, there 
is also a renaming of positional arguments in the alembic.op.* 
namespace, however a backwards-compatibility translation layer is 
included.   I came across some environments where positional arguments 
were passed with names, such as op.create_foreign_key(name='foo', 
source='source', referent='othertable', ...).   Those names are now 
changed, however if the operation is called from within the 
alembic.op.* namespace, the old names when passed as keywords will 
still be honored, though a warning will be emitted.   The one thing that 
does *not* work is if an application is programmatically constructing an 
alembic.operations.Operations object directly, then calling methods upon 
it and passing positional arguments using the old names.The 
overwhelming vast majority of applications should not be constructing 
Operations objects as this is a little-needed workflow, but those 
applications will need to check their use of old names passed explicitly 
for 

Re: Alembic 0.7.6 slow with a lot of merges.

[Mike Bayer]

Hi Paweł -

this is fixed in master and the rel_0_7 branch.   I should be releasing 
today.


- mike



On 7/22/15 6:10 AM, Paweł Piotr Przeradowski wrote:

Hi,

Thanks for reply. It completes with correct results. I am gonna send 
you a stripped version of scrips in a private message.


Pawel.

On Tuesday, 21 July 2015 18:06:59 UTC+2, Michael Bayer wrote:



On 7/21/15 7:32 AM, Paweł Piotr Przeradowski wrote:

Hi,

First of all thanks for alembic which is great asset to have in
your development toolbelt.

I am experiencing large slowdowns when generating offline or
doing online migrations or even generating history. One of the
CPU cores stays at 100% for a large number of seconds.

|
I have 141migrations
32branchpoints and40merges
|



The hardware is:
|
Intel®Core™i7-4510UCPU @2.00GHz×4
with8GB of RAM
|



Each time I do a merge the time seems to grows. It looks to me
that alembic spends a lot of time calculating some dependency
tree. Some timings on commands *minutes:seconds*:

|

alembic history 1:31.57total
|
|

|
|
alembic downgrade head:325b273d61bd--sql 1:24.10total // |This
just targets one migration|
|


Is this a known problem? Any way I can speed it up?


not known at all, and it is likely a bug in the algorithm, but
it's very weird that it comes up with an answer, rather than just
going into a recursion overflow.   it comes up with the right
answer?  can you supply your full migration script structure in a
.tar.gz archive ?  Just the scripts with the headers in them, I
don't need the upgrade() or downgrade() methods to be filled
in.  thanks.


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

Alembic 0.7.7 Released

[Mike Bayer]

Alembic 0.7.7 is now available.

This is a bugfix release sent out ahead of the very soon to release 0.8 
version, which features major new public APIs involving extensibility of 
operations and autogeneration.


Most critical in this fix is a recently discovered CPU-intensive 
situation which occurs when the new revisioning system encounters a 
complex series of branches and merges in a series of files; the 
operation would take too long and could even cause the machine to freeze 
for many minutes.Therefore this update is recommended for all users 
of the new branching and merging system.


Changelog for 0.7.7 is at:

http://alembic.readthedocs.org/en/rel_0_7/changelog.html#change-0.7.7

Download Alembic at:

https://pypi.python.org/pypi/alembic/


--
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 0.9.10 Released

[Mike Bayer]

SQLAlchemy release 0.9.10 is now available.

This is a maintenance release of the 0.9 series, delivering an array of 
bug fixes and small feature enhancements which typically have been 
backported from the 1.0 series. As the 1.0 series has been in production 
use for some months now, the 0.9 series is expected to receive only 
critical bugfixes subsequent to this version.


Changelog for 0.9.10 is at:

http://www.sqlalchemy.org/changelog/CHANGES_0_9_10

SQLAlchemy 0.9.10 is available on the download page at: 
http://www.sqlalchemy.org/download.html



--
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 1.0.8 Released

[Mike Bayer]

SQLAlchemy release 1.0.8 is now available.

Release 1.0.8 comes almost immediately after 1.0.7, as a new issue 
involving the connection pool has been identified and fixed which 
impacts any application that relies upon consistent behavior of the 
.info dictionary on a connection that is undergoing reconnect attempts. 
Applications and libraries which make use of connection pool event 
handlers may benefit from this release, as it repairs the behavior of 
the .info dictionary and reduces the likelihood of stale connections 
being passed to the checkout handler.


Changelog for 1.0.8 is at:

http://www.sqlalchemy.org/changelog/CHANGES_1_0_8

SQLAlchemy 1.0.8 is available on the download page: 
http://www.sqlalchemy.org/download.html



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

Re: Per package migration history and upgrades

[Mike Bayer]

On 7/13/15 12:46 AM, Mikko Ohtamaa wrote:

Hi,

I am trying to use Alembic in a way that the codebase is split to 
several Python packages and each package tracks their own tables and 
migration history independently. Effective each package has its own 
versions and env.py. Is this possible?  I assume it is possible as 
Alembic is pretty powerful. I think the major issue here is that each 
package should have their own migration history table, so they can be 
tracked and upgraded independently.


Any pointers how I should approach this issue?


yes.   use the approach documented at 
http://alembic.readthedocs.org/en/rel_0_7/branches.html#working-with-multiple-bases.




Other issues I become aware is that autogenerate consider tables 
outside the package itself alien and tries to drop them. Please see 
this SO question: http://stackoverflow.com/q/31196631/315168
full control over what objects autogenerate considers is present at 
http://alembic.readthedocs.org/en/rel_0_7/api.html?highlight=include_object#alembic.environment.EnvironmentContext.configure.params.include_object. 
if you are trying to run autogenerate in such a way that it considers 
only individual MetaData objects at a time you probably want to add 
customization in your env.py file that takes advantage of the X 
argument: 
http://alembic.readthedocs.org/en/rel_0_7/api.html?highlight=include_object#alembic.environment.EnvironmentContext.get_x_argument. 
use this argument to receive which sub-component you want to work on, 
and consult that within your include_object function to look at just the 
objects that are relevant to that sub-component.






Just for your information Django migrations handle this kind of 
situations.


by all means, use Django if it meets your needs.



Cheers,
Mikko
https://opensourcehacker.com
--
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 
mailto: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.

SQLAlchemy 1.0.7 Released

[Mike Bayer]

SQLAlchemy release 1.0.7 is now available.

This release contains a series of regression fixes, some additional 
bugfixes, and a few new Core level features, including a new .cast() 
method available on column constructs as well as support for many new 
keywords used in creating sequences.


Next on the roadmap for SQLAlchemy are the beginnings of the 1.1 series; 
the 1.1 milestone has already accumulated several dozen proposed 
bugfixes and features. Look for development and documentation of the 1.1 
series to begin within the coming weeks.


Changelog for 1.0.7 is at:

http://www.sqlalchemy.org/changelog/CHANGES_1_0_7

SQLAlchemy 1.0.7 is available on the download page at 
http://www.sqlalchemy.org/download.html



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

Re: Multi-column foreign key constrait using postgresql, autogenerated migration fails

[Mike Bayer]

On 10/29/15 4:26 PM, Gastón Avila wrote:
> Hi all,
> 
> I used alembic to generate (auto) a migration which added a table to my
> postgresql DB which had a foreign key constraint matching two columns in
> the new table to two columns in an existing one. The two columns in the
> existing table where a joint primary key (hence unique). The generated
> migration had TWO lines for the foreign key constraint and that caused
> it to fail with this error
>  
> 
> there is no unique constraint matching given keys for referenced
> table "agency_version"
> 
>  [SQL: '\nCREATE TABLE agency_tag (\n\tagency_id INTEGER NOT
> NULL, \n\ttransaction_id INTEGER NOT NULL, \n\tname VARCHAR(12),
> \n\tPRIMARY KEY (agency_id, transaction_id), \n\tFOREIGN
> KEY(agency_id) REFERENCES agency_version (agency_id),
> \n\tFOREIGN KEY(transaction_id) REFERENCES agency_version
> (transaction_id)\n)\n\n']
> 
> The migration had these lines
> 
> sa.ForeignKeyConstraint(['agency_id', 'transaction_id'],
> ['agency_version.agency_id'], ),
> sa.ForeignKeyConstraint(['transaction_id'],
> ['agency_version.transaction_id'], ),
> 
> 
> which when changed to 
> 
> sa.ForeignKeyConstraint(['agency_id', 'transaction_id'],
> ['agency_version.agency_id', 'agency_version.transaction_id'], ),


those mean two totally different things, and you likely want the latter.
 You should make sure your original model / table metadata uses the
composite ForeignKeyConstraint as well, and not ForeignKey() which isn't
typically compatible with a composite primary key as a target.



> 
> 
> worked allright.
> 
> Is postgresql the only DBMS which will complain about this? It took me a
> while to figure this out.
> 
> Thanks
>  
> 
> -- 
> 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.

Re: alter VARCHAR column to TEXT under Oracle

[Mike Bayer]

On 11/08/2015 05:42 AM, Ofir Herzas wrote:
> Changing a VARCHAR column to CLOB (TEXT) raises the following exception
> under Oracle:
> 
> |
> sqlalchemy.exc.DatabaseError:(cx_Oracle.DatabaseError)ORA-22858:invalid
> alteration of datatype
>  [SQL:'ALTER TABLE tab MODIFY col CLOB']
> 
> |
> 
> While this is an Oracle fault, it would be nice if the abstraction layer
> took care of it.
> 
> The proposed solution that I found was to add a new clob column, copy
> the data, remove the old column and rename

Alembic offers a rough version of this feature in the form of "batch
migrations", but that involves a whole table copy.

Recipes like adding new columns and copying data can be achieved using
custom directives, see
http://alembic.readthedocs.org/en/latest/cookbook.html#replaceable-objects
for an example of how to make new directives.  I will gladly accept
documentation illustrating a recipe for this behavior.



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

Re: Extending OpContainer?

[Mike Bayer]

I'd take a look at

http://alembic.readthedocs.org/en/latest/api/operations.html#operation-plugins
and for a longer example
http://alembic.readthedocs.org/en/latest/cookbook.html#replaceable-objects.





On 11/11/2015 04:26 PM, Alyssa Kwan wrote:
> Hi all,
> 
> I have a single logical Alembic operation that is best implemented as a
> series of lower level operations (like create_sequence, create_table(s),
> add_constraint(s), etc.). What's the best way to implement this? I
> assume it would be to somehow extend OpContainer? I don't see any
> examples, nor do I see how OpContainer is used.
> 
> Thanks!
> Alyssa
> 
> -- 
> 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.

Re: How to delete with order_by and limit in core?

[Mike Bayer]

we just had nearly the identical problem here where we hit the bizarre
fact that LIMIT won't work in the correlated subquery, and we went with
a temp table.


On 11/07/2015 02:27 PM, vitaly numenta wrote:
> Thank you Michael. I was hoping to do it the native sqlalchemy way,
> because my function takes an sqlalchemy-based predicate that needs to be
> used in this and another query, so I was hoping to be able to do things
> natively using pure sqlalchemy constructs in order to share this
> predicate. Thanks, this confirms that what I wanted to do cannot be done
> at this time.
> 
> -- 
> 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.

Re: Batch Mode operations recreates existing indexes

[Mike Bayer]

this was kind of a big issue so that is fixed and in 0.8.3. today.



On 10/16/15 10:36 AM, Mike Bayer wrote:
> this is a bug,
> https://bitbucket.org/zzzeek/alembic/issues/333/batch-fails-on-tables-that-have-indexes
> is added.  thanks for reporting!
> 
> 
> 
> On 10/16/15 4:20 AM, Dheeraj Gupta wrote:
>> Hi,
>>
>> I have a single table in my sqlite database for which I am using alembic
>> for migration. After the initial revision (creating table and columns),
>> I needed to modify the models so that some of the columns allowed NULL.
>>
>> My original models.py looks like:
>>
>> # models.py ###33
>> from sqlalchemy import (BigInteger,
>> Column,
>> Integer,
>> SmallInteger,
>> String)
>> from sqlalchemy.ext.declarative import declarative_base
>> from sqlalchemy.schema import MetaData
>>
>>
>> convention = {
>> "ix": 'ix_%(column_0_label)s',
>> "uq": "uq_%(table_name)s_%(column_0_name)s",
>> "fk": "fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s",
>> "pk": "pk_%(table_name)s"
>> }
>>
>> mymetadata = MetaData(naming_convention=convention)
>> Base = declarative_base(metadata=mymetadata)
>>
>>
>> class MyBase(Base):
>> __abstract__ = True
>> __table_args__ = {'mysql_charset': 'utf8', 'mysql_engine': 'InnoDB',
>>   'sqlite_autoincrement': 'True'}
>>
>> class TRequest(MyBase):
>> __tablename__ = "trequests"
>> id_ = Column("id", BigInteger().with_variant(Integer, "sqlite"),
>>  primary_key=True)
>> req_uuid = Column(String(32), unique=True, index=True, nullable=False)
>> ip1 = Column(String(15), nullable=True)
>> ip2 = Column(String(15), nullable=True)
>> port1 = Column(Integer, nullable=True)
>> port2 = Column(Integer, nullable=True)
>> proto = Column(String(3), nullable=True)
>> tstamp = Column(Integer, nullable=True)
>> win = Column(SmallInteger, nullable=False)
>> status = Column(SmallInteger)
>> status_changed = Column(Integer)
>> accessed = Column(Integer)
>> data_file = Column(String(255))
>> pcap_file = Column(String(255))
>>
>> # End File##
>>
>> req_uuid has a uniuq index which is named 'ix_trequests_req_uuid' per
>> the naming convention
>>
>> In my base revision script, the upgrade() function is:
>> def upgrade():
>> ### commands auto generated by Alembic - please adjust! ###
>> op.create_table('trequests',
>> sa.Column('id', sa.BigInteger().with_variant(sa.Integer, "sqlite"),
>>   nullable=False),
>> sa.Column('req_uuid', sa.String(length=32), nullable=False),
>> sa.Column('ip1', sa.String(length=15), nullable=False),
>> sa.Column('ip2', sa.String(length=15), nullable=False),
>> sa.Column('port1', sa.Integer(), nullable=False),
>> sa.Column('port2', sa.Integer(), nullable=False),
>> sa.Column('tstamp', sa.Integer(), nullable=False),
>> sa.Column('proto', sa.String(length=3), nullable=False),
>> sa.Column('win', sa.SmallInteger(), nullable=False),
>> sa.Column('status', sa.SmallInteger(), nullable=True),
>> sa.Column('accessed', sa.Integer(), nullable=True),
>> sa.Column('status_changed', sa.Integer(), nullable=True),
>> sa.Column('data_file', sa.String(length=15), nullable=True),
>> sa.Column('pcap_file', sa.String(length=15), nullable=True),
>> sa.PrimaryKeyConstraint('id', name=op.f('pk_trequests')),
>> mysql_charset='utf8',
>> mysql_engine='InnoDB',
>> sqlite_autoincrement=True
>> )
>> op.create_index(op.f('ix_trequests_req_uuid'), 'trequests',
>> ['req_uuid'], unique=True)
>> # End ###
>>
>> Now, I am adding a new revision to remove nullable=True from some
>> columns. The relevant upgrade function is
>>
>>
>> from net_transcript.db.models import convention as naming_convention
>>
>> def upgrade():
>> ### commands auto generated by Alembic - please adjust! ###
>> with op.batch_alter_table('trequests', schema=None,
>>   naming_convention=naming_convention) as
>> batch_op:
>> batch_op.alter_column('port1',
>>

SQLAlchemy 1.0.9 Released

[Mike Bayer]

SQLAlchemy release 1.0.9 is now available.

Release 1.0.9 is a bugfix release, pushing out a collection of mostly
small fixes that have accumulated over the past few months. In
particular there is one small but important fix for users of the latest
cx_Oracle library (5.2 or greater) in conjunction with Python 3 that
corrects for an important issue in correctly detecting the version of
this DBAPI and its Unicode behavior.

Primary development of new features, architectural and behavioral
improvements continues within the 1.1 series targeted at early 2016 for
initial beta releases.

Changelog for 1.0.9 is at:
http://www.sqlalchemy.org/changelog/CHANGES_1_0_9


SQLAlchemy 1.0.9 is available on the Download Page at:
http://www.sqlalchemy.org/download.html

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

Re: 'AutogenContext' object has no attribute '__getitem__'

[Mike Bayer]

On 10/14/15 12:41 AM, Fayaz Yusuf Khan wrote:
> Hi,
> Looks like the autogen_context parameter has changed type recently.

indeed it has:

http://alembic.readthedocs.org/en/latest/api/autogenerate.html#alembic.autogenerate.api.AutogenContext




> 
> I'm getting this exception:
> 
> Traceback (most recent call last):
>   File "/home/fayaz/Programming/weaver-env/bin/weaver", line 9, in 
> load_entry_point('weaver==2.12.1', 'console_scripts', 'weaver')()
>   File
> "/home/fayaz/Programming/weaver-env/local/lib/python2.7/site-packages/click/core.py",
> line 700, in __call__
> return self.main(*args, **kwargs)
>   File
> "/home/fayaz/Programming/weaver-env/local/lib/python2.7/site-packages/click/core.py",
> line 680, in main
> rv = self.invoke(ctx)
>   File
> "/home/fayaz/Programming/weaver-env/local/lib/python2.7/site-packages/click/core.py",
> line 1027, in invoke
> return _process_result(sub_ctx.command.invoke(sub_ctx))
>   File
> "/home/fayaz/Programming/weaver-env/local/lib/python2.7/site-packages/click/core.py",
> line 1027, in invoke
> return _process_result(sub_ctx.command.invoke(sub_ctx))
>   File
> "/home/fayaz/Programming/weaver-env/local/lib/python2.7/site-packages/click/core.py",
> line 873, in invoke
> return ctx.invoke(self.callback, **ctx.params)
>   File
> "/home/fayaz/Programming/weaver-env/local/lib/python2.7/site-packages/click/core.py",
> line 508, in invoke
> return callback(*args, **kwargs)
>   File
> "/home/fayaz/Programming/weaver-env/local/lib/python2.7/site-packages/click/decorators.py",
> line 16, in new_func
> return f(get_current_context(), *args, **kwargs)
>   File "/home/fayaz/Programming/weaver-backend/weaver/cli.py", line 44,
> in script
> weaver.db.generate_migrate_script(url=ctx.obj, message=message)
>   File
> "/home/fayaz/Programming/weaver-backend/weaver/db/sql/__init__.py", line
> 132, in generate_migrate_script
> make_config(url), message, autogenerate=True)
>   File
> "/home/fayaz/Programming/weaver-env/local/lib/python2.7/site-packages/alembic/command.py",
> line 121, in revision
> revision_context.generate_scripts()
>   File
> "/home/fayaz/Programming/weaver-env/local/lib/python2.7/site-packages/alembic/autogenerate/api.py",
> line 412, in generate_scripts
> yield self._to_script(generated_revision)
>   File
> "/home/fayaz/Programming/weaver-env/local/lib/python2.7/site-packages/alembic/autogenerate/api.py",
> line 336, in _to_script
> autogen_context, migration_script, template_args
>   File
> "/home/fayaz/Programming/weaver-env/local/lib/python2.7/site-packages/alembic/autogenerate/render.py",
> line 40, in _render_python_into_templatevars
> _render_cmd_body(upgrade_ops, autogen_context))
>   File
> "/home/fayaz/Programming/weaver-env/local/lib/python2.7/site-packages/alembic/autogenerate/render.py",
> line 63, in _render_cmd_body
> lines = render_op(autogen_context, op)
>   File
> "/home/fayaz/Programming/weaver-env/local/lib/python2.7/site-packages/alembic/autogenerate/render.py",
> line 75, in render_op
> lines = util.to_list(renderer(autogen_context, op))
>   File
> "/home/fayaz/Programming/weaver-env/local/lib/python2.7/site-packages/alembic/autogenerate/render.py",
> line 102, in _render_modify_table
> t_lines = render_op(autogen_context, t_op)
>   File
> "/home/fayaz/Programming/weaver-env/local/lib/python2.7/site-packages/alembic/autogenerate/render.py",
> line 75, in render_op
> lines = util.to_list(renderer(autogen_context, op))
>   File
> "/home/fayaz/Programming/weaver-env/local/lib/python2.7/site-packages/alembic/autogenerate/render.py",
> line 299, in _add_column
> "column": _render_column(column, autogen_context),
>   File
> "/home/fayaz/Programming/weaver-env/local/lib/python2.7/site-packages/alembic/autogenerate/render.py",
> line 542, in _render_column
> 'type': _repr_type(column.type, autogen_context),
>   File
> "/home/fayaz/Programming/weaver-env/local/lib/python2.7/site-packages/alembic/autogenerate/render.py",
> line 565, in _repr_type
> rendered = _user_defined_render("type", type_, autogen_context)
>   File
> "/home/fayaz/Programming/weaver-env/local/lib/python2.7/site-packages/alembic/autogenerate/render.py",
> line 513, in _user_defined_render
> rendered = render(type_, object_, autogen_context)
>   File
> "/home/fayaz/Programming/weaver-backend/weaver/db/migrations/env.py",
> line 52, in render_item
> autogen_context['imports'].add(
> 
> 
> 
> Here's the code:
> 
> def render_item(type_, obj, autogen_context):
> from weaver.core.model import PriceType
> if type_ == 'type':
> if isinstance(obj, PriceType):
> print autogen_context
> autogen_context['imports'].add(
> 'from weaver.core.model import PriceType')
> return '%r' % obj
> return False
> 
> 
> -- 
> You received this message because you are subscribed to the Google
> Groups "sqlalchemy-alembic" group.
> To unsubscribe 

Re: alembic_version in separate schema

[Mike Bayer]

see the version_table_schema parameter :

http://alembic.readthedocs.org/en/latest/api/runtime.html?highlight=version_table_schema#alembic.runtime.environment.EnvironmentContext.configure.params.version_table_schema



On 10/9/15 9:30 AM, wwwald wrote:
> Hi,
>
> Sorry if this is basic - I just started playing with Alembic.
> My current project will run on a shared PostgreSQL database, in which
> I only have permission to a specific schema, without writing
> permissions to "public". Can I configure Alembic to store the
> "alembic_version" table in that specific schema?
>
> Thanks in advance, 
> wwwald
> -- 
> 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.

Re: Extending OpContainer?

[Mike Bayer]

On 11/12/2015 11:46 AM, Alyssa Kwan wrote:
> Thanks, Michael!
> 
> In terms of the ReplaceableObject, I was hoping for a composite
> operation that could use existing Alembic operations, without needing to
> generate the underlying SQL. 



For instance, the single composite
> operation would use Alembic's built-in create_table() in sequence with
> other operations, built-in or custom. Neither example addresses this.

ReplaceableObject is just an example. You'll note it uses
operations.execute() in its implementation. That's the Operation object;
in your own implementation methods, you can call
operations.alter_column(), operations.create_table(), whatever you want
on it.


> 
> It seems like in the Operations.implementation_for(), I get an
> operations object, which I could use to call operations.create_table(),
> followed by any other operations; to achieve the desired result? Am I on
> the right track?


yup

> 
> Thanks!
> Alyssa
> 
> On Thursday, November 12, 2015 at 6:43:37 AM UTC-8, Michael Bayer wrote:
> 
> I'd take a look at
> 
> 
> http://alembic.readthedocs.org/en/latest/api/operations.html#operation-plugins
> 
> 
> 
> and for a longer example
> http://alembic.readthedocs.org/en/latest/cookbook.html#replaceable-objects
> 
> .
> 
> 
> 
> 
> 
> 
> On 11/11/2015 04:26 PM, Alyssa Kwan wrote:
> > Hi all,
> >
> > I have a single logical Alembic operation that is best implemented
> as a
> > series of lower level operations (like create_sequence,
> create_table(s),
> > add_constraint(s), etc.). What's the best way to implement this? I
> > assume it would be to somehow extend OpContainer? I don't see any
> > examples, nor do I see how OpContainer is used.
> >
> > Thanks!
> > Alyssa
> >
> > --
> > 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.

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

Re: Not populating values when using offline mode

[Mike Bayer]

On 8/29/15 7:13 AM, Martin Marrese wrote:


On Fri, Aug 28, 2015 at 8:16 PM, ma...@tokbox.com 
mailto:ma...@tokbox.com wrote:


Hi,

I am trying to generate sql scripts in offline mode. But when
executing the delete in the following manner:

op.execute(
foo.delete().where(foo.c.key == 'bar')
)


generates sql as

DELETE FROM foo WHERE foo.`key` = %s;



How can i get the actual value 'bar' instead of %s? Is there a
better way to do this ? If I use raw sql statements I can get
around the problem although I am trying my best to not execute raw
sql statements in there.

That is ok, %s is later replaced with 'bar'.

For instance, this is the log I get when querying a table named role 
filtering by id. The python code is :


role = session.query(model.Role).get(id_)


for --sql mode you can add the literal_binds flag to your environment 
which will attempt to render those inline:


http://alembic.readthedocs.org/en/latest/api/runtime.html?highlight=literal_binds#alembic.runtime.environment.EnvironmentContext.configure.params.literal_binds

otherwise you can specify literal values using the inline_literal() 
construct: 
http://alembic.readthedocs.org/en/latest/ops.html?highlight=inline_literal#alembic.operations.Operations.inline_literal





And the log entries are :

2015-08-29 08:01:53,461 INFO sqlalchemy.engine.base.Engine SELECT 
role.id http://role.id AS role_id, role.name http://role.name AS 
role_name, role.is_admin AS role_is_admin

FROM role
WHERE role.id http://role.id = %s
2015-08-29 08:01:53,462 INFO sqlalchemy.engine.base.Engine (2L,)

The first line shows the query to be executed with %s as place holder 
for the parameters. The second line shows the parameters that will 
replace each %s.


Martín

--

Martín
--
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 
mailto: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.

Re: Global variables from inside render_item

[Mike Bayer]

On 9/2/15 3:13 AM, Fayaz Yusuf Khan wrote:

Hi,
I'm seeing this weird issue where several global variables in env.py 
are set as null when accessed from render_item.


Code: env.py
def render_item(type_, obj, autogen_context):
print globals()


I'm not deeply familiar with the vagaries of what globals() does other 
than I tend not to go near it except when doing eval / exec, so I don't 
have any immediate insight on this one, sorry (as always, I'd pdb it here).






Output:
{'mysql': None, 'with_statement': None, 'PasswordType': None, 
'include_symbol': None, 'PriceType': None, 'JSONEncodedDict': None, 
'compare_type': None, 'PhoneNumberType': None, 
'run_migrations_online': None, '__package__': None, 'render_item': 
None, 'target_metadata': None, 'Base': None, 'config': None, 
'__doc__': None, '__builtins__': {'bytearray': , 
'IndexError': , 'all': function all>, 'help': Type help() for interactive help, or 
help(object) for help about object., 'var.


Recently updated to:
alembic==0.8.2
SQLAlchemy==1.0.8

Any ideas why this might be happening?

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

Re: Drop followed by generation of Foreign Keys

[Mike Bayer]

On 9/22/15 7:38 PM, Sergi Pons Freixes wrote:
2015-09-22 16:24 GMT-07:00 Mike Bayer <mike...@zzzcomputing.com 
<mailto:mike...@zzzcomputing.com>>:



Revision runs fine, but now when I run the upgrade it does not
find the alembic_version table (attached logs)... I suspect that
the search_path is restored so that it takes notifications again
by default?


yes



Would this issue be solved just by $user!=$schema, or is there
something else behind (as you were saying, not even $user should
be on the search path)?


yes that would also solve it.


the issue is that PG doesn't give us the schema name of a foreign
key when that schema is in the search path. that's what the whole
explanation at

http://docs.sqlalchemy.org/en/rel_1_0/dialects/postgresql.html#remote-schema-table-introspection-and-postgresql-search-path
is about.


Setting the database search_path to 'public' permanently using pgAdmin 
solved the problem:


ALTER DATABASE notifications
  SET search_path = public;

Now it remains always as 'public', and everything seems to run fine. 
I'll now apply that to the other databases that were also having this 
issue. I guess this issue can be declared close, thank you very much 
for all your help!


well what I learned here is:

1. that $user thing in public_schema is important !  :)

2. how to better diagnose this for users b.c. you're not the first to 
stumble on this.


ideally I should add a documentation note re: postgresql on this.




Regards,
Sergi
--
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 
<mailto: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.

Re: Drop followed by generation of Foreign Keys

[Mike Bayer]

On 9/22/15 2:25 PM, Sergi Pons Freixes wrote:
2015-09-22 10:44 GMT-07:00 Mike Bayer <mike...@zzzcomputing.com 
<mailto:mike...@zzzcomputing.com>>:




On 9/22/15 1:41 PM, Sergi Pons Freixes wrote:

Additional update: If on the env.py I substitute "__table_args__
= {'schema': 'notifications'}" for "__table_args__ = {'schema':
'notifications'}" and rerun alembic twice again (assuming we
start on a clean database), alembic_version is created on the
'notifications' schema, t1 and t2 on 'notifications' schema, and
the issue is done (i.e., the second run of alembic does nothing).


does your PG database have some kind of replication or triggering
happening that is duplicating tables between the two schemas?  
Please run everything clean with full logging turned on as I

illustrated in my previous email, you should only see "CREATE
TABLE alembic_version" without any schema qualifier.


Just to be sure: I should create a new database (test) AND the 
'notifications' schema, right? Alembic shouldn't be able to create t1 
and t2 if the schema didn't exist previously.


yes, alembic doesnt create the schema for you.



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

Re: Drop followed by generation of Foreign Keys

[Mike Bayer]

On 9/21/15 6:09 PM, Sergi Pons Freixes wrote:



2015-09-21 14:18 GMT-07:00 Mike Bayer <mike...@zzzcomputing.com 
<mailto:mike...@zzzcomputing.com>>:





OK, I'm still not able to reproduce (see attached), as long as
include_schemas=True.  However, in your env.py, you have this:

def include_symbol(tablename, schema):
return schema == table_args['schema']

Which you are setting as your include_symbol option. I don't see
what "table_args" is here, however this would definitely have the
effect of omitting tables from the autogenerate.   I would suggest
removing this include_symbol callable, it does not appear to have
any purpose.


Oh, sorry, that was a mistake on the copy-pasting to the e-mail. On 
the tests I removed include_symbol, and it was not passed to 
context.configure().
I'm still suspecting something is up with your search_path.  Keeping 
mine on the default of "public", the alembic_version table cannot be 
created correctly because you have the "version_table_schema=public", 
which confuses SQLAlchemy because whatever names are in search_path are 
implicit (postgresql thing, not SQLAlchemy); when I test, it raises an 
exception.


Are you sure you don't have any in-application event handlers or similar 
manipulating search_path?   Can you put this in your env.py and tell me 
what it says?


with connectable.connect() as connection:

row = connection.execute("show search_path").fetchone()
print row


Alternatively, can you please remove the "version_table_schema" 
directive and test?  If your search_path is "public", it should not be 
needed.








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

Re: Drop followed by generation of Foreign Keys

[Mike Bayer]

On 9/22/15 1:41 PM, Sergi Pons Freixes wrote:
Additional update: If on the env.py I substitute "__table_args__ = 
{'schema': 'notifications'}" for "__table_args__ = {'schema': 
'notifications'}" and rerun alembic twice again (assuming we start on 
a clean database), alembic_version is created on the 'notifications' 
schema, t1 and t2 on 'notifications' schema, and the issue is done 
(i.e., the second run of alembic does nothing).


does your PG database have some kind of replication or triggering 
happening that is duplicating tables between the two schemas? Please run 
everything clean with full logging turned on as I illustrated in my 
previous email, you should only see "CREATE TABLE alembic_version" 
without any schema qualifier.





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

Re: Drop followed by generation of Foreign Keys

[Mike Bayer]

On 9/22/15 3:43 PM, Sergi Pons Freixes wrote:



2015-09-22 11:52 GMT-07:00 Sergi Pons Freixes <sachiel2...@gmail.com 
<mailto:sachiel2...@gmail.com>>:


2015-09-22 10:41 GMT-07:00 Mike Bayer <mike...@zzzcomputing.com
<mailto:mike...@zzzcomputing.com>>:


So one more time, with all detail possible; attached is an
env.py script and a full log of all SQL emitted and commands;
we have alembic_version is created in "public", the two tables
created only in "notifications", no redundant FK commands in
the next run.  Please start with a brand new, definitely
completely empty PG database, no dependencies, and run this
env.py as is without any of your application being imported,
then do a line-by-line on your logs vs. the logs (use logging
config in attached alembic.ini) here to see where they
diverge.  thanks!








Could you reproduce that?



Ok, I reproduced it on a new database I changed nothing except the
script location in the ini file, and the second revision was fine,
no foreign keys dropped. I'll now slowly change this "good" env.py
and .ini to make it more like my previous one, and see when the
issue starts triggering.


First of all, thank you for your patience all this time, Mike. Next, 
some more results. I just discovered that just switching between 
databases, not changing env.py or .ini at all (except to point to the 
new DB, and I added a print to the search_path), things start to 
differ. I attach the logs of the first revision, when the tables are 
created for the first time, being test_first_revision.log related to 
the new fresh database and notifications_first_revision.log related to 
the old database (but with all the previous tables dropped). Checking 
with pgAdmin, both have 'public' as the default schema, and the print 
of search_path shows only $user and public.



OK, the only possible way this would happen is if "SELECT 
current_schema()" were returning the name "notifications", and I went 
back through our emails and found:


The output of the script is:
$ python inspector_test.py
default schema: notifications
schema names: ['information_schema', 'notifications', 'public']
dflt table names: ['alembic_version', 't1', 't2']
notifications table names: ['alembic_version', 't1', 't2']

that is the issue.  If your search_path is set to only "public", then 
current_schema should *NOT* be reading "notifications"; per the docs 
(http://www.postgresql.org/docs/9.2/static/functions-info.html):


|current_schema| returns the name of the schema that is first in the 
search path (or a null value if the search path is empty). This is the 
schema that will be used for any tables or other named objects that are 
created without specifying a target schema.


So, is the username here the name "notifications"?since this $user 
default is finding its way in there.


I would do this when you connect:

with engine.connect() as conn:
conn.execute("SET search_path='public'")










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

Re: Drop followed by generation of Foreign Keys

[Mike Bayer]

On 9/22/15 6:49 PM, Sergi Pons Freixes wrote:
2015-09-22 15:06 GMT-07:00 Mike Bayer <mike...@zzzcomputing.com 
<mailto:mike...@zzzcomputing.com>>:


OK, the only possible way this would happen is if "SELECT
current_schema()" were returning the name "notifications", and I
went back through our emails and found:

The output of the script is:
$ python inspector_test.py
default schema: notifications
schema names: ['information_schema', 'notifications', 'public']
dflt table names: ['alembic_version', 't1', 't2']
notifications table names: ['alembic_version', 't1', 't2']

that is the issue.  If your search_path is set to only "public",
then current_schema should *NOT* be reading "notifications"; per
the docs
(http://www.postgresql.org/docs/9.2/static/functions-info.html):

|current_schema| returns the name of the schema that is first in
the search path (or a null value if the search path is empty).
This is the schema that will be used for any tables or other named
objects that are created without specifying a target schema.

So, is the username here the name "notifications"? since this
$user default is finding its way in there.

I would do this when you connect:

with engine.connect() as conn:
conn.execute("SET search_path='public'")


Yes, notifications is the name of the user AND the schema... not a 
recommended practice I guess?


I set manually the search_path to only public as suggested, and prints 
before and after show it during the revision:

...
INFO  [sqlalchemy.engine.base.Engine] show search_path
INFO  [sqlalchemy.engine.base.Engine] {}
('"$user",public',)
INFO  [sqlalchemy.engine.base.Engine] SET search_path='public'
INFO  [sqlalchemy.engine.base.Engine] {}
INFO  [sqlalchemy.engine.base.Engine] show search_path
INFO  [sqlalchemy.engine.base.Engine] {}
('public',)
...

Revision runs fine, but now when I run the upgrade it does not find 
the alembic_version table (attached logs)... I suspect that the 
search_path is restored so that it takes notifications again by default?


yes



Would this issue be solved just by $user!=$schema, or is there 
something else behind (as you were saying, not even $user should be on 
the search path)?


yes that would also solve it.


the issue is that PG doesn't give us the schema name of a foreign key 
when that schema is in the search path.   that's what the whole 
explanation at 
http://docs.sqlalchemy.org/en/rel_1_0/dialects/postgresql.html#remote-schema-table-introspection-and-postgresql-search-path 
is about.






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

Re: Drop followed by generation of Foreign Keys

[Mike Bayer]

On 9/21/15 8:14 PM, Sergi Pons Freixes wrote:


2015-09-21 16:55 GMT-07:00 Mike Bayer <mike...@zzzcomputing.com 
<mailto:mike...@zzzcomputing.com>>:


Can you run the above with logging enabled?  That is the [alembic]
logger turned on as in the default alembic.ini.


Sure thing, this was the output:

$ alembic -c development.ini revision -m "upgrade" --autogenerate
('"$user",public',)
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.ddl.postgresql] Detected sequence named 't2_id_seq' as 
owned by integer column 't2(id)', assuming SERIAL and omitting
INFO  [alembic.autogenerate.compare] Detected removed foreign key 
(t1id)(id) on table t2
INFO  [alembic.autogenerate.compare] Detected added foreign key 
(t1id)(id) on table notifications.t2

  Generating
XXX/notifications/scripts/alembic/versions/3e56f486dde_upgrade.py ... done


OK great, I can now show something similar happening. Your model has 
tables t1 and t2 in *both* the public and notifications schema and I'm 
assuming the same foreign key setup.


In many of your examples I've observed the explicit use of "public":

__table_args__ = {'schema': 'public'}

That has to be removed entirely.When I have all four tables and I 
use "public" explicitly, the reflection system cannot get enough 
information to make a decision, based on the information in the section 
I originally referred to at 
http://docs.sqlalchemy.org/en/rel_1_0/dialects/postgresql.html#remote-schema-table-introspection-and-postgresql-search-path 
- see the yellow sidebar box at the bottom of the section for a quick 
summary.


When I remove the redundant "public" schema from my table defs, the 
redundant FK defs go away.







With the logger config:

# Logging configuration
[loggers]
keys = root,sqlalchemy,alembic

[handlers]
keys = console

[formatters]
keys = generic

[logger_root]
level = WARN
handlers = console
qualname =

[logger_sqlalchemy]
level = WARN
handlers =
qualname = sqlalchemy.engine

[logger_alembic]
level = INFO
handlers =
qualname = alembic

[handler_console]
class = StreamHandler
args = (sys.stderr,)
level = NOTSET
formatter = generic

[formatter_generic]
format = %(levelname)-5.5s [%(name)s] %(message)s
datefmt = %H:%M:%S


Also, can you please run this script with your database URL and
send me the full output, with any sensitive information omitted:

from sqlalchemy import inspect, create_engine

e = create_engine("postgresql://scott:tiger@localhost/test")

inspector = inspect(e)

print "default schema:", inspector.bind.dialect.default_schema_name
print "schema names:", inspector.get_schema_names()
print "dflt table names:", inspector.get_table_names()
print "notifications table names:",
inspector.get_table_names('notifications')


The output of the script is:
$ python inspector_test.py
default schema: notifications
schema names: ['information_schema', 'notifications', 'public']
dflt table names: ['alembic_version', 't1', 't2']
notifications table names: ['alembic_version', 't1', 't2']
--
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 
<mailto: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.

Re: Drop followed by generation of Foreign Keys

[Mike Bayer]

On 9/21/15 7:32 PM, Sergi Pons Freixes wrote:
2015-09-21 16:13 GMT-07:00 Mike Bayer <mike...@zzzcomputing.com 
<mailto:mike...@zzzcomputing.com>>:



Are you sure you don't have any in-application event handlers or
similar manipulating search_path?   Can you put this in your
env.py and tell me what it says?

with connectable.connect() as connection:

row = connection.execute("show search_path").fetchone()
print row


Alternatively, can you please remove the "version_table_schema"
directive and test?  If your search_path is "public", it should
not be needed.



I attach the new env.py, based on yours (issue persists). Output of it 
is:


$ alembic -c development.ini revision -m "upgrade" --autogenerate
('"$user",public',)
  Generating
XXX/notifications/scripts/alembic/versions/519677dff97_upgrade.py ... done
Can you run the above with logging enabled?  That is the [alembic] 
logger turned on as in the default alembic.ini.


Also, can you please run this script with your database URL and send me 
the full output, with any sensitive information omitted:


from sqlalchemy import inspect, create_engine

e = create_engine("postgresql://scott:tiger@localhost/test")

inspector = inspect(e)

print "default schema:", inspector.bind.dialect.default_schema_name
print "schema names:", inspector.get_schema_names()
print "dflt table names:", inspector.get_table_names()
print "notifications table names:", 
inspector.get_table_names('notifications')








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

Re: alter table with sequence

[Mike Bayer]

On 9/21/15 10:18 AM, Ofir Herzas wrote:

rename_table operation does not rename sequences if they exist.
This causes a problem that the renamed table is unusable since the 
sequence is missing.


In Oracle, a simple rename works well (RENAME old_seq_name TO 
new_seq_name;) although it says "table renamed".


Is there a sequence rename operation in Alembic? (not "drop" and "create")


built-in operations for sequences are a TODO for now, you can emit the 
ALTER statement directly using op.execute():


op.execute("ALTER SEQUENCE foo RENAME TO bar")






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

Re: Drop followed by generation of Foreign Keys

[Mike Bayer]

On 9/18/15 7:43 PM, Sergi Pons Freixes wrote:

Hi everybody,

I am experiencing the same issue commented 
here: https://bitbucket.org/zzzeek/alembic/issues/293/alembic-autogenerate-creates-endless


In my case, I have the model:
---8<--
...
class EmailAttachment(Base):
__tablename__ = 'email_attachment'
__table_args__ = table_args

email_message_id = Column(ForeignKey(EmailMessage.id), nullable=False)
filestorage_file_id = Column(Integer, nullable=False)

email_message = relationship(EmailMessage, backref='email_attachments')

def __init__(self, email_message_id=None, email_message=None,
filestorage_file_id=None, fxt=None):
self.email_message_id = email_message_id
self.email_message = email_message
self.filestorage_file_id = filestorage_file_id
self.fxt = fxt
...
---8<--

Where:
---8<--
...
table_args = {'schema': 'notifications'}

class NotificationsBase:
@declared_attr
def id(cls):
return Column(Integer, primary_key=True)

@declared_attr
def fxt(cls):
return Column(String, nullable=True)

@classmethod
def get_one(cls, **kwargs):
query = DBSession.query(cls)
for k, v in kwargs.items():
query = query.filter(getattr(cls, k) == v)
return query.one()

def __repr__(self):
return "<%s id=%s>" % (self.__class__.__name__, self.id)


Base = declarative_base(cls=NotificationsBase)
...
---8<--


Running 'alembic -c development.ini revision -m "upgrade" 
--autogenerate' without any change to the model, alembic insists on 
dropping the foreign key and recreating it afterwards. It is happening 
to all the foreign keys of all the models of the project. The 
generated script looks like:

---8<--
...
def upgrade():
### commands auto generated by Alembic - please adjust! ###
op.drop_constraint('email_attachment_email_message_id_fkey', 
'email_attachment', type_='foreignkey')
op.create_foreign_key(None, 'email_attachment', 'email_message', 
['email_message_id'], ['id'], source_schema='notifications', 
referent_schema='notifications')

...
---8<--

For more information, my env.py has:
---8<--
...

target_metadata = Base.metadata

def include_symbol(tablename, schema):
return schema == table_args['schema']

...

def run_migrations_online():

connection = engine.connect()
context.configure(
connection=connection,
target_metadata=target_metadata,
include_schemas=True,
include_symbol=include_symbol,
version_table_schema='public'
)

try:
with context.begin_transaction():
context.run_migrations()
finally:
connection.close()

if context.is_offline_mode():
run_migrations_offline()
else:
run_migrations_online()
---8<--

And all this with SQLAlchemy 1.0.8, Alembic 0.8.2 and PostgreSQL 
9.3.7. Any clue of what could be the source of the problem?
what is your search_path set to ?   see 
http://docs.sqlalchemy.org/en/rel_1_0/dialects/postgresql.html#remote-schema-table-introspection-and-postgresql-search-path 
for background on this.It is highly recommended that search_path 
remain set on "public" and that you do *not* refer to the "public" name 
directly in any table definitions.   Feel free to share a model that 
includes all the tables and constraints in question as well.








Cheers,
Sergi



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

Re: Alter boolean column on Oracle

[Mike Bayer]

On 12/11/2015 04:39 PM, Ofir Herzas wrote:
> Hi Mike,
> I've just checked my code and I noticed that I already use existing_type
> (not sure what was wrong with the documentation in that aspect).
> 
> Can you elaborate a bit on specifying custom rules as you mentioned?
> I tried issuing a drop_constraint before calling the alter_column but
> that doesn't stop alter_column from trying to remove the check
> constraint by itself, leading to the same exception.
> On the other hand, I didn't see any parameter in the alter_column method
> that accepts a constraint name

You can call the drop_constraint(), then when you do the alter_column,
inside the existing_type put "create_constraint=False", so that it sees
it as an un-constrainted type.


drop_contraint('constraint_name')
alter_column('table', 'column', type=NewType(),
existing_type=Boolean(create_constraint=False))




> 
> Thanks,
> Ofir
> 
> 
> On Thursday, December 10, 2015 at 3:39:42 PM UTC+2, Ofir Herzas wrote:
> 
> I'm trying to run an alter_column operation from a Boolean type
> column to SmallInteger on Oracle.
> 
> According to the documentation: "...Type changes which are against
> the SQLAlchemy “schema” types |Boolean|
> 
> 
>  and |Enum|
> 
>  
> may
> also add or drop constraints which accompany those types on backends
> that don’t support them natively.
> The |existing_server_default| argument is used in this case as well
> to remove a previous constraint"
> 
> Does this mean that the alter column should work out-of-the-box or
> do I have to remove the constraint myself because I'm getting the
> following error on alembic 0.8.3 (sqlalchemy 1.0.9):
> 
> |
> sqlalchemy.exc.CompileError:Can't emit DROP CONSTRAINT for
> constraint CheckConstraint( object at 0x28a9b10>, name='_unnamed_', table=Table('t_message',
> MetaData(bind=None), Column('is_bulletin', Boolean(),
> table=), schema=None),
> _create_rule= object at 0x289ac68>, _type_bound=True); it has no name
> |
> 
> 
> And on a previous version (alembic 0.6.5, sqlalchemy 0.9.7) I got
> the following error:
> 
> |
> sqlalchemy.exc.DatabaseError:(DatabaseError)ORA-02443:Cannotdrop
> constraint  -nonexistent constraint
>  'ALTER TABLE t_message DROP CONSTRAINT None'{}
> |
> 
> 
> Your help will be appreciated...
> 
> -- 
> 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.

SQLAlchemy 1.0.10 Released

[Mike Bayer]

SQLAlchemy release 1.0.10 is now available.

Release 1.0.10 continues with maintenance fixes as we continue major
development on the 1.1 series. Fixes here include a handful of fairly
obscure ORM issues, as our userbase continues to use the new loader
option system introduced in 0.9 more deeply, some adjustments to
internals to work more smoothly with the latest Python 3.5 and upcoming
3.6 versions, an array of dialect-specific fixes, and a pretty important
fix to the new "baked query" extension. There's also one new feature
which is the ability to render an UPDATE statement with a specific
ordering of the SET clauses.

Changelog for 1.0.10 is at:

http://www.sqlalchemy.org/changelog/CHANGES_1_0_10

SQLAlchemy 1.0.10 is available on the Download Page at
http://www.sqlalchemy.org/download.html.

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

Re: Alter boolean column on Oracle

[Mike Bayer]

On 12/10/2015 08:39 AM, Ofir Herzas wrote:
> I'm trying to run an alter_column operation from a Boolean type column
> to SmallInteger on Oracle.
> 
> According to the documentation: "...Type changes which are against the
> SQLAlchemy “schema” types |Boolean|
> 
>  and |Enum|
>  
> may
> also add or drop constraints which accompany those types on backends
> that don’t support them natively. The |existing_server_default| argument
> is used in this case as well to remove a previous constraint"

OK that document is wrong.  At least in 0.8 and maybe previously, that
should be "existing_type", because the current source code is looking at
existing_type to determine what previous type-bound constraints need to
be dropped.  I've committed that and it should be on readthedocs shortly.


> 
> Does this mean that the alter column should work out-of-the-box or do I
> have to remove the constraint myself because I'm getting the following
> error on alembic 0.8.3 (sqlalchemy 1.0.9):

Well, the constraint needs to have a name.  At least in the
existing_type that you send, for Boolean you'd need to also include the
"name" field that is the name of the constraint.

If this is an existing production DB, the constraint already has some
name and on Oracle it will be something like SYS12345.  But
unfortunately that name is not portable so this depends on if you are
targeting a lot of databases, or just one.

If you're just working out how these migrations would run from start to
finish, the real way you'd want to address this is at the very least
ensuring that any Boolean or Enum type you use where you've also used
create_constraint=True (the default) also has a "name".If you want
that to be automatic, you can use naming conventions.  The whole story
here is documented at http://alembic.readthedocs.org/en/latest/naming.html.

In the worst case, you in fact have production DBs, there are multiple,
you didn't give the constraints a specific name up front, and this
constraint probably has a different name on each one.Your options
there are either to manually rename constraints to something predictable
on these DBs and then use the full naming approach, *or* you'd need to
write custom rules in your migrations that do a live search of the
Oracle catalog tables, locate these constraints dynamically, and drops them.





> |
> sqlalchemy.exc.CompileError:Can't emit DROP CONSTRAINT for constraint
> CheckConstraint( 0x28a9b10>, name='_unnamed_', table=Table('t_message',
> MetaData(bind=None), Column('is_bulletin', Boolean(),
> table=), schema=None),
> _create_rule= at 0x289ac68>, _type_bound=True); it has no name
> |
> 
> 
> And on a previous version (alembic 0.6.5, sqlalchemy 0.9.7) I got the
> following error:
> 
> |
> sqlalchemy.exc.DatabaseError:(DatabaseError)ORA-02443:Cannotdrop
> constraint  -nonexistent constraint
>  'ALTER TABLE t_message DROP CONSTRAINT None'{}
> |
> 
> 
> Your help will be appreciated...
> 
> -- 
> 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.

Re: Autogenerate ALWAYS assumes an empty schema with Oracle 11.2

[Mike Bayer]

On 01/02/2016 01:18 PM, Nana Okyere wrote:
> Disclosure: I'm a newbie :-)
> 
> Starting with an empty table, I autogenerate and then do upgrade and it
> works fine the first time. When I make a small change to the model(s)
> like adding a  new column, then I do autogenerate, alembic generates the
> upgrade and downgrade code as if starting from scratch. 

this would indicate it is seeing your Table objects (e.g. the "model"),
but is not seeing any tables in the database.

Are these tables located in explicitly named schemas?  If so, you'd need
to turn on the "include_schemas" option:

http://alembic.readthedocs.org/en/latest/api/runtime.html?highlight=include_schema#alembic.runtime.environment.EnvironmentContext.configure.params.include_schemas


otherwise, send along what logging is coming up with.   For detailed SQL
logging of the commands used to inspect your database set the SQLAlchemy
logger debug level to DEBUG inside of alembic.ini:

[logger_sqlalchemy]
level = DEBUG
handlers =
qualname = sqlalchemy.engine

this will emit all the queries used to look for tables in the oracle
database as well as what results come back.



It is supposed
> to only generate code for what has changed or the difference between the
> objects in the schema and the models. But subsequent attempted
> migrations always generates ALL tables again whether or not they already
> existed on the schema. Hence when I do 'upgrade', I get a message like:
> 
> name is already used by an existing object
> 
>  [SQL: '\nCREATE TABLE dut_departments (\n\tid INTEGER NOT NULL,
> \n\tname VARCHAR2(120 CHAR) NOT NULL, \n\tanother_col VARCHAR2(120
> CHAR), \n\tPRIMARY KEY (id), \n\tUNIQUE (another_col), \n\tUNIQUE
> (name)\n)\n\n']
> 
> 
> Clearly, the upgrade is issuing commands to create tables that already
> exist on the schema with the same name. I'm at a complete stop now. As a
> control test, I kept my models and did the autogenerate and upgrade on
> sqlite. It works as expected. Only changes from last migration are seen
> in the revision file. Is there something special about oracle I need to
> know to get autogenerate and upgrade to only pick up changes to models?
> Thanks.
> 
> -- 
> 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.

Re: Configure version_locations dynamically

[Mike Bayer]

the general idea here is to make your own Alembic command runner, create
a Config dynamically and then invoke the alembic commands via
alembic.command.foo() from that.

See http://alembic.readthedocs.org/en/latest/api/commands.html and
http://alembic.readthedocs.org/en/latest/api/config.html#alembic.config.Config
for background.



On 01/08/2016 05:18 PM, Martin Marrese wrote:
> Hi, 
> 
> I'm working with a rather large application that will allow different
> modules to have its own migrations using alembic branches and register
> themselves in the application. 
> 
> The problem I'm facing is that I didn't find a way to change
> version_locations value without changing ini file by hand.
> 
> I want to know if there is a way to do that without manually changing
> alembic configuration file.
> 
> Thanks, 
> 
> Martín
> -- 
> 
> Martín
> 
> +54 911 5112 9448
> Skype: kyheo_
> 
> -- 
> 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.

SQLAlchemy 1.0. 11 Released

[Mike Bayer]

SQLAlchemy release 1.0.11 is now available.

Release 1.0.11, only twelve days after 1.0.10's release, is expedited to
correct for one ORM-related regression introduced by a 1.0.10 fix; some
other fairly significant bugfixes involving more recently added
features, all reported subsequent to the release of 1.0.10, were also
fixed. Fixes include a major issue in the "baked lazy loader" extension
when used as a systemwide-replacement for lazy loading, a major series
of fixes to the "eager_defaults" mapper persistence option, and a
revision to 1.0.10's fix for a particular polymorphic joined-eager
loading issue.

Changelog for 1.0.11 is at:

http://www.sqlalchemy.org/changelog/CHANGES_1_0_11

SQLAlchemy 1.0.11 is available on the Download Page at:

http://www.sqlalchemy.org/download.html

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

Re: issue with --autogenerate and shortened identifiers

[Mike Bayer]

https://bitbucket.org/zzzeek/alembic/issues/351/autogen-with-constraint-names-that-were
is added



On 12/27/2015 07:57 PM, Mike Bayer wrote:
> that is kind of a bug for the time being.  You'd need to name those
> constructs explicitly or exclude them using an include_object routine
> (see
> http://alembic.readthedocs.org/en/latest/api/runtime.html#alembic.runtime.environment.EnvironmentContext.configure.params.include_object)
> 
> 
> 
> On 12/27/2015 02:12 PM, Jonathan Beluch wrote:
>> I'm having a problem with indexes whose name is longer than
>> max_identifier_length and the comparisons during --autogenerate.
>>
>> The index on the code/metadata side is being compared using its
>> auto-generated naming convention name, which doesn't take into account
>> the max_identifier_length since that happens during compilation.
>> However, the index pulled from the DB has the _ hash suffix in the
>> name. So auto generate will always output a drop_index/create_index pair.
>>
>> Any thoughts on how to solve this? Was thinking about something in
>> include_object and compiling things and/or a regex for the indexes
>> pulled from the db but both seem a bit hacky.
>>
>> Thanks.
>>
>> SQLAlchemy==1.0.11
>> alembic==0.8.4
>> postgres 9.4
>>
>> -- 
>> 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
>> <mailto: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.

Re: constraint names not as strict as expected

[Mike Bayer]

On 11/18/2015 06:41 PM, Chris Frey wrote:
> In the alembic documentation, there is an entire page devoted to naming
> constraints:
> 
>   http://alembic.readthedocs.org/en/latest/naming.html
> 
> So I assumed that if constraints had a naming scheme defined, then
> those names would be detected if changed.

if you change your naming convention, then that would show up as a bunch
of brand new constraints in the model and a whole bunch of constraints
removed in the model, so in theory would produce a lot of add constraint
/ drop constraint instructions.


> 
> My specific use case is as follows.  My naming scheme for foreign keys is:
> 
>   "fk": "fk_%(table_name)s_%(column_0_name)s",
> 
> 
>   1) I changed the __tablename__'s and class names of some tables
>   to add a prefix to each.

autogenerate does not detect a change in table name as that, it sees
this as a brand new table added and an old table dropped.  There is no
reasonable way to differentiate a drop/add of two tables from a name
change of a single table.  This limitation is documented at
http://alembic.readthedocs.org/en/latest/autogenerate.html#what-does-autogenerate-detect-and-what-does-it-not-detect.

> 
>   2) I updated the ForeignKey()'s to point to the new table names.

these will not generate add/drops as these FKs are local to the tables
that are being added / dropped (in reality just renamed).


> 
>   3) I then ran alembic autogenerate, which produced drop/create
>   commands for my tables, and some drop/creates for
>   constraints in non-renamed, but affected tables.
>   I manually converted these scripts to use op.rename_table()
>   and removed all the rest.

this is expected.

> 
>   4) I ran alembic upgrade head, which renamed the tables, but
>   not the constraints, as expected.

there is no "rename constraint" migration operation.  There is only add
and drop.   A constraint that changed name in place associated to a
table that did not change its name would produce distinct add/drop
instructions for the old and new constraint.

> 
>   5) Here I expected another alembic revision --autogenerate
>   to give me only the constraint changes, but it said
>   that nothing changed.

if you didn't change your model subsequent to the previous autogenerate
run then I don't see why a second run of autogenerate would do anything new.

> 
> During further experiments, if I removed a ForeignKey(), alembic
> produced an autogenerate script that used the old constraint name
> in the drop_constraint() command.

this is correct.  The database has a constraint named "X", your model
doesn't, it's a drop.

> 
> If I then added the foreign key back, it used the *new* constraint
> name based on the new table name.

also seems correct.   Your model has an FK with the name "Y", your
database doesn't, it's an add.

> 
> Obviously I need to be really careful with renames, but this behaviour
> was somewhat unexpected.  

All Alembic can do with constraints is:

1. get the list of names in the model
2. get the list of names in the DB
3. the names that are only in the DB and not the model are DROPs, the
names that are only in the model and not the DB are ADDs, the
intersection of those names are "do nothing". That's it.


It appears that alembic is already kinda smart
> about matching a table's constraint to the model, even if the names
> don't match.  

SQLAlchemy associates constraints with tables as part of the Python
definition of those models.  It has nothing to do with the names.

Could it also be made smart enough to detect when
> a constraint "rename" is needed? 

since a rename of a table can't be detected, you'd need to write these
out yourself as individual add/drops of constraints; there's not a
feature that automates this.  I can see how it might be useful but I'm
not coming up with what that would look like.  There'd have to be a hook
in the autogenerate process that allows the user to tell autogenerate
that a table is only being renamed, not an add/drop, and then to somehow
rebuild all the constraints with those names.  This would be kind of
intricate but it should be doable as a recipe using the autogenerate
extension system described at
http://alembic.readthedocs.org/en/latest/api/autogenerate.html#customizing-revision-generation.


 If I go to all the trouble to make sure
> my constraints are named properly, it would be nice if alembic helped
> me make sure those names were actually up to date. :-)

I can see this, but that's a big deal to implement.Getting an
autogenerate extension recipe to do it first would be the best way to start.




> 
> Thanks,
> - Chris
> 

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

Re: constraint names not as strict as expected

[Mike Bayer]

On 11/19/2015 04:01 AM, Chris Frey wrote:
> On Wed, Nov 18, 2015 at 11:23:11PM -0500, Mike Bayer wrote:
>> if you change your naming convention, then that would show up as a bunch
>> of brand new constraints in the model and a whole bunch of constraints
>> removed in the model, so in theory would produce a lot of add constraint
>> / drop constraint instructions.
> 
> Thanks for your reply Mike!
> 
> I probably didn't explain my steps very clearly, but what I was trying
> to achieve was basically what you state above.
> 
> And if I run a test, changing my foreign key naming convention from:
> 
>   "fk": "fk_%(table_name)s_%(column_0_name)s",
> 
> to
> 
>   "fk": "fkk_%(table_name)s_%(column_0_name)s",
>  ^^^
> 
> I get no changes in the autogenerate script at all.
> 
> And when I say changes, for constraints, I am indeed expecting drop/add,
> as you explained.

yup this would need to be some kind of custom autogenerate recipe for
starters, with the key being how to indicate a table that's been renamed
vs. added and dropped.


> 
> Thanks,
> - Chris
> 

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

Re: Tagging an Alembic revision

[Mike Bayer]

On 06/09/2016 05:11 AM, Søren Løvborg wrote:

On Wed, Jun 8, 2016 at 4:32 PM, Mike Bayer <mike...@zzzcomputing.com
<mailto:mike...@zzzcomputing.com>> wrote:

Branch labels are exactly what solves this?What's wrong with
using a branch label?  you put "v1.2.1" as a branch label in the
target revision and then your "alembic downgrade v1.2.1" command
works exactly.


Sorry, reading the documentation I was distracted by the @head notations
etc. and missed the part where a "bare" branch label does indeed work
like a tag.

From the docs, it still seems like branch labels were designed to solve
a different problem, and just incidentally happens to solve this too,
but if you say it's a proper use of branch_labels, I'm obviously not
gonna argue. ;-)


That is all true, it was designed for the branching issue and not as 
much "tag this revision", though these concepts are always very similar 
in version control systems.





There's a weird side-effect, in that our Alembic history is (so far)
linear, so all branch labels show up for every revision, e.g.:

Branch names: v1.2.3, v1.2.2, v1.2.1, v1.2.0

That was part of what confused me, and could get unwieldy eventually,
but I guess it's just a cosmetic issue.



OK, then that is actually an argument to add a new "tags" feature as 
well that is pretty much like a branch name but doesn't get associated 
with other revisions in the history view.




Yeah, but we can't depend on people having full VCS history available,
we have to support snapshot downloads too. :-/

So branch_labels it is. Thanks!


Yeah.  But yeah, as I mentioned in my other email having a "tags" 
collection next to "branch_labels" is OK for me as well with some 
different semantics in alembic history etc. (any other effects you can 
think of?)   Basically you're alpha testing this :).






Best,
Søren

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

Re: Getting the current dialect name in a migration

[Mike Bayer]

op.get_bind().name should do it



On 06/03/2016 03:25 AM, Michal Petrucha wrote:

Hi everyone,

I remember seeing a quick expression that extracts the name of the
dialect in use from alembic.op, but I can't find it anywhere now, and
I can't figure it out by myself either. Does anybody know the right
chain of attributes and/or method calls to follow from alembic.op to
get to this string?

Thanks,

Michal



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

Re: Getting the current dialect name in a migration

[Mike Bayer]

op.get_bind().engine.name



On 06/03/2016 10:21 AM, Michal Petrucha wrote:

On Fri, Jun 03, 2016 at 10:05:31AM -0400, Mike Bayer wrote:

op.get_bind().name should do it


Alas, I have already tried that:

if op.get_bind().name == 'postgresql':
AttributeError: 'Connection' object has no attribute 'name'

Michal



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

Re: Autogenerate does not detect Complex/Composite Custom Types

[Mike Bayer]

the "existing type" comes from what is reflected from the database and 
your custom type is not automatically reflectable.


The "existing type" for a PG alter_column is not important here because 
Postgresql's "ALTER COLUMN" doesn't require it.  It's mostly for MySQL 
and SQL Server that "existing type" is important.


If you want to get your type in there without just manually changing it 
(which is the usual approach for edge cases like these) you'd need to 
build an event handler that populates it into the column metadata (the 
SQLA column_reflect event usually is where this is).   SQLAlchemy 
doesn't yet have very good hooks for custom type reflection, though so 
this is not that straightforward (you'd need to re-load the type 
information for each column to detect if your type is there).


On 06/08/2016 03:21 AM, Brian Ogollah wrote:

I am creating custom types in sqlalchemy to handle postgresql composite
types. The first time i run the migrations, alembic discovers the types
but after adding changes e.g cardinality from nullable=True  >
nullable=False, alembic assigns a NullType to my custom types. what is
the issues here and how can i fix it?

def upgrade():
### commands auto generated by Alembic - please adjust! ##

op.alter_column('organization', 'active',
existing_type=sa.BOOLEAN(),
nullable=False)
op.alter_column('organization', 'identifier',
existing_type=sa.NullType(),
nullable=False)
op.alter_column('organization', 'name',
existing_type=sa.TEXT(),
nullable=False)
### end Alembic commands ###


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

Re: Tagging an Alembic revision

[Mike Bayer]

On 06/08/2016 09:21 AM, Søren Løvborg wrote:

Hi,

What's the best practice for "tagging" an Alembic revision, that is to
denote e.g. that 1ffbeb5179a5 is the database revision matching version
1.4.0 of the software?

I can do it in documentation, of course, but would prefer a method
allowing e.g. "alembic downgrade v1.2.1".

I guess I could simply rename the revision identifier to "v1.4.0", since
Alembic apparently doesn't strictly enforce that identifiers must be
hexadecimal? That does seem like quite a hack, though, and causes
problems with prefix-matching ("v1.1" could match "v1.12"). It's also
not clear whether it's safe to assume that Alembic will continue to
allow arbitrary strings as revision IDs.

Branch labels does not seem like a good fit for solving the problem either.


Branch labels are exactly what solves this?What's wrong with using a 
branch label?  you put "v1.2.1" as a branch label in the target revision 
and then your "alembic downgrade v1.2.1" command works exactly.


if this is just a matter of documentation update and perhaps adding 
another entry "tags" which just adds itself to "branch_labels", that's 
fine.


of course, if you actually git tag your project, the head revision file 
can be located from that git tag.That is, I can write a script right 
now to deliver the correct alembic revision given a git tag against the 
repo.   To make it seamless, a recipe that adds a new Alembic command 
would be fine however Alembic would need to have a nicer way to add new 
commands and/or to extend the existing commands.






Would there be interest in adding a dedicated tagging feature?

Best,
Søren

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

Re: auto-generating rename index and rename constraint

[Mike Bayer]

On 02/06/2016 05:26 PM, Jonathan Beluch wrote:

Thanks for clearing all that functionality up. Will give the traversal a
shot.

On 02/05/2016 01:10 PM, Jonathan Beluch wrote:
 > Just realized #4 isn't so simple either as the comparison is
always done
 > by name.

if this is a one-time migration why is autogenerate needed at all?
Just write out the migration for all the constraints either manually or
using your own one-off script.

There are about ~150 index/constraints that have to renamed so manual
could get annoying. Also, I want to hook into the alembic machinery to
compare indexes/constraints on column signatures/etc vs just the name.


If you can get those 150 names into a list, you can just generate a 
single "RENAME CONSTRAINT" script for all 150 names.  I'd just do this 
in a text editor with search and replace features.If you need the 
more careful comparison of col signatures in order to generate these 
names, then sure...but even then, you can work from an alembic diff 
structure directly.






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

Re: Changing the type from Boolean to Integer on SQLite

[Mike Bayer]

resending because my PGP tools totally got in the way

On 01/22/2016 09:05 AM, Michal Petrucha wrote:
> On Fri, Jan 22, 2016 at 01:44:38PM +0100, Michal Petrucha wrote:
>> Can anyone help me come up with the best way of renaming a Boolean
>> column on both backends? I'm about to try creating a new column with
>> the new name, copying the data from the old column, and dropping the
>> old one, but that's a really convoluted approach.
> 
> ...and just now I realized that won't work either, because there's a
> bug in the batch implementation of drop_column. Funnily enough, it was
> only yesterday that I submitted a PR for that particular bug
> (https://bitbucket.org/zzzeek/alembic/pull-requests/53/ for those who
> are interested). Which means even my brute-force fallback solution is
> a no go, at least for the time being.
> 
> In light of that, can anyone come up with a solution that does not
> involve any of the following:
> - a straight-up rename of a Boolean column,
> - changing Boolean to Integer, and
> - dropping a Boolean column?
> 
> Changing the type from Boolean() to Boolean(create_constraint=False)
> has the same effect as changing to Integer().
> 
> Or maybe -- can I drop the CHECK constraint manually? What I mean is,
> is it guaranteed that the constraint will be there for all backends,
> even those with a native boolean type? (That appears to be the case
> for MS SQL, but I'm curious if it holds for other backends as well.)
> If the only condition for creating the constraint is
> create_constraint=True (regardless of the backend's capabilities),
> this might be a way forward.


I see in

 batch_op.alter_column(name, type_=sa.Integer(),
existing_type=sa.Boolean())

you aren't giving it the constraint name in the existing_type, that's
actually where the
"_unnamed_" is coming from.

I just added a test for this and it seems that this existing_type is
where the problem comes from, not the batch migration.  So setting
create_constraint=False allows it to recreate (which is wrong, but gets
us to the next step):

def _boolean_fixture(self):
t = Table(
'hasbool', self.metadata,
Column('x', Boolean(create_constraint=True, name='ck1'))
)
t.create(self.conn)

def test_bool_change(self):
self._boolean_fixture()
with self.op.batch_alter_table("hasbool") as batch_op:
batch_op.alter_column(
'x', type_=Integer, existing_type=Boolean(
create_constraint=False, name='ck1'))

however, it's still copying the constraint, and adding a DROP is not
working so I have to just cerate an issue for this, sorry.

https://bitbucket.org/zzzeek/alembic/issues/354/cant-change-type-of-bool
ean-w-batch




> 
> Cheers,
> 
> Michal
> 

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

Re: Changing the type from Boolean to Integer on SQLite

[Mike Bayer]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA256



On 01/22/2016 07:44 AM, Michal Petrucha wrote:
> Hello people,
> 
> I'm having difficluty changing the type of a column from Boolean
> to Integer on SQLite. Boolean generates an integer column with a
> CHECK constraint; changing the type should just drop the constraint
> (on SQLite at least).
> 
> The problem is, when batch_op processes a command like this:
> 
> batch_op.alter_column(name, type_=sa.Integer(),
> existing_type=sa.Boolean())
> 
> errors out with errors looking something like this:
> 
> Traceback (most recent call last): File
> "/home/konk/expermients/alembic-sqlite-boolint/env-alembic-boolint
/lib/python3.4/site-packages/alembic/operations/batch.py",
> line 314, in drop_constraint del
> self.named_constraints[const.name] KeyError: '_unnamed_'
> 
> During handling of the above exception, another exception
> occurred:
> 
> Traceback (most recent call last): File
> "/home/konk/expermients/alembic-sqlite-boolint/env-alembic-boolint
/bin/alembic",
> line 11, in  sys.exit(main()) File
> "/home/konk/expermients/alembic-sqlite-boolint/env-alembic-boolint
/lib/python3.4/site-packages/alembic/config.py",
> line 471, in main CommandLine(prog=prog).main(argv=argv) File
> "/home/konk/expermients/alembic-sqlite-boolint/env-alembic-boolint
/lib/python3.4/site-packages/alembic/config.py",
> line 465, in main self.run_cmd(cfg, options) File
> "/home/konk/expermients/alembic-sqlite-boolint/env-alembic-boolint
/lib/python3.4/site-packages/alembic/config.py",
> line 448, in run_cmd **dict((k, getattr(options, k)) for k in
> kwarg) File
> "/home/konk/expermients/alembic-sqlite-boolint/env-alembic-boolint
/lib/python3.4/site-packages/alembic/command.py",
> line 174, in upgrade script.run_env() File
> "/home/konk/expermients/alembic-sqlite-boolint/env-alembic-boolint
/lib/python3.4/site-packages/alembic/script/base.py",
> line 397, in run_env util.load_python_file(self.dir, 'env.py') File
> "/home/konk/expermients/alembic-sqlite-boolint/env-alembic-boolint
/lib/python3.4/site-packages/alembic/util/pyfiles.py",
> line 81, in load_python_file module = load_module_py(module_id,
> path) File
> "/home/konk/expermients/alembic-sqlite-boolint/env-alembic-boolint
/lib/python3.4/site-packages/alembic/util/compat.py",
> line 68, in load_module_py module_id, path).load_module(module_id) 
> File "", line 539, in
> _check_name_wrapper File "", line
> 1614, in load_module File "", line
> 596, in _load_module_shim File "",
> line 1220, in load File "", line 1200,
> in _load_unlocked File "", line 1129,
> in _exec File "", line 1471, in
> exec_module File "", line 321, in
> _call_with_frames_removed File "migrations/env.py", line 78, in
>  run_migrations_online() File "migrations/env.py", line 73,
> in run_migrations_online context.run_migrations() File "",
> line 8, in run_migrations File
> "/home/konk/expermients/alembic-sqlite-boolint/env-alembic-boolint
/lib/python3.4/site-packages/alembic/runtime/environment.py",
> line 797, in run_migrations 
> self.get_context().run_migrations(**kw) File
> "/home/konk/expermients/alembic-sqlite-boolint/env-alembic-boolint
/lib/python3.4/site-packages/alembic/runtime/migration.py",
> line 312, in run_migrations step.migration_fn(**kw) File
> "/home/konk/expermients/alembic-sqlite-boolint/migrations/versions
/eaab6fc3ef59_.py",
> line 25, in upgrade batch_op.alter_column('c1', type_=sa.Integer(),
> existing_type=sa.Boolean()) File
> "/usr/lib64/python3.4/contextlib.py", line 66, in __exit__ 
> next(self.gen) File
> "/home/konk/expermients/alembic-sqlite-boolint/env-alembic-boolint
/lib/python3.4/site-packages/alembic/operations/base.py",
> line 299, in batch_alter_table impl.flush() File
> "/home/konk/expermients/alembic-sqlite-boolint/env-alembic-boolint
/lib/python3.4/site-packages/alembic/operations/batch.py",
> line 76, in flush fn(*arg, **kw) File
> "/home/konk/expermients/alembic-sqlite-boolint/env-alembic-boolint
/lib/python3.4/site-packages/alembic/operations/batch.py",
> line 316, in drop_constraint raise ValueError("No such constraint:
> '%s'" % const.name) ValueError: No such constraint: '_unnamed_'
> 
> We're using a naming convention, and it does apply correctly when
> the table is created. I also tried passing the naming convention as
> an argument to batch_alter_table, but it doesn't seem to make any 
> difference.
> 
> '_unnamed_' appears to be a sentinel value that the Boolean type
> sets if you don't pass it an explicit name for the CHECK
> constraint, and it seems that for some reason, it doesn't get
> properly replaced with the name provided by the naming convention
> before batch_op starts processing queued operations.

that should not be the case, but typically a naming convention for a
CHECK constraint includes the "name" given to the constraint itself,
which is the "constraint_name" token in the string.

I see in

 batch_op.alter_column(name, 

test message, please ignore

[Mike Bayer]

test

--
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 1.0.12 Released

[Mike Bayer]

SQLAlchemy release 1.0.12 is now available.

Release 1.0.12 is a bug fix release resolving a handful of issues 
reported in the past few weeks. Primary development continues on the 1.1 
series which is slated for initial releases in the spring.


1.0.12 includes relatively minor fixes, including a revision to a fix 
made for version 1.0.10 where we're attempting to report on the "cause" 
of an error that is interrupted by a later failure to emit a ROLLBACK on 
the MySQL platform. While Python 3 solves this problem using exception 
chaining and cause reporting, on Python 2 we emit a warning so that the 
cause isn't totally concealed. The condition has been expanded to emit 
this warning in a much wider range of scenarios.


Changelog for 1.0.12 is at: 
http://www.sqlalchemy.org/changelog/CHANGES_1_0_12



SQLAlchemy 1.0.12 is available on the Download Page at 
http://www.sqlalchemy.org/download.html.



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

Alembic 0.8.5 Released

[Mike Bayer]

Alembic 0.8.5 is now available.

Version 0.8.5 includes a short set of bugfixes.   See 
http://alembic.readthedocs.org/en/latest/changelog.html#change-0.8.5.


Download Alembic 0.8.5 at:

https://pypi.python.org/pypi/alembic

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

Re: 'relation “public.alembic_version” does not exist' when using `version_table_schema` (X-Post Stackoverflow)

[Mike Bayer]

On 04/11/2016 12:34 PM, Zack S wrote:

(X-Post from Stackoverflow: http://stackoverflow.com/q/36511941/703040)


I'm writing some custom code for Alembic to keep my database always
updated in my dev environment for a project. The project involves a
database with the following:

  * A |public| schema for shared data
  * A single schema per client "database"
  * One schema that acts as a |prototype| for all of the client schemas
(orgs)

At this moment, I'm not worried about the multiple client schemas, only
keeping the |public| and |prototype| schemas up-to-date. My env.py
script works great for the |public| schema, but not |prototype| because
alembic is trying to use the version table from |public| when working
with |prototype|.

So, I thought I could use the |version_table_schema|

 option
to maintain one version table in the |public| schema and one in the
|prototype| schema. However, as soon as I start using that, I get a
'/relation "public.alembic_version" does not exist/' error when I
attempt to do the upgrade.

The only difference that I see is that, when I use
|version_table_schema| set to the appropriate schema, the generated
revision scripts actually contain a line to
|op.drop_table('alembic_version')|. The line ONLY exists when
|version_table_schema| is in use.

I'm hoping that I'm just missing something minor.



you're likely hitting the very confusing schema rules that apply to 
Postgresql.  See 
http://docs.sqlalchemy.org/en/rel_1_0/dialects/postgresql.html#remote-schema-table-introspection-and-postgresql-search-path 
for details.  Short answer is that schema of "blank" and schema of 
"public" are two different things on the Python side, leading to a lot 
of confusion.


In order to convince autogenerate to not affect alembic_version at all 
no matter where it pops up, you probably need to create an exclusion 
rule using include_object: 
http://alembic.readthedocs.org/en/latest/api/runtime.html?highlight=include_object#alembic.runtime.environment.EnvironmentContext.configure.params.include_object


def include_object(object, name, type_, reflected, compare_to):
if (type_ == "table" and name == 'alembic_version'):
 return False
else:
return True






I've posted the source files on SO already
 if they are helpful.


Thanks!

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

Re: Running Migrations Simulataneously

[Mike Bayer]

On 04/26/2016 01:22 AM, Amit Saha wrote:

On Tue, Apr 26, 2016 at 12:33 PM, Mike Bayer <mike...@zzzcomputing.com> wrote:



On 04/25/2016 08:30 PM, Amit Saha wrote:


Hi all,

In my scenario, DB migrations (via alembic) will be run as part of the
app deployment and multiple app deployments will happen near
simultaneously (multiple EC2 instances talking to the same MySQL DB).
Let's, for simplicity's sake assume two instances:

Instance #1: Alembic sees alembic_version table and finds that we do
need the migrations to happen, and starts the migration.

Instance #2: Alembic sees alembic_version table and finds that we do
need the migrations to happen, and starts the migration.  This doesn't
obviously happen since MySQL will lock the table during the migration.



this scenario is entirely unsupported.   MySQL does not support
transactional DDL and migration #1 will be hitting some tables and migration
#2 another.The version table is not updated until after a particular
migration takes place so concurrent processes will both be doing the same
migration at the same time.



Now, let's say instance #1 finishes the migration first and updates the
alembic_version table. Then, the migration triggered by instance #2
starts to run, and errors out because the schema changes are already in
place.

Is this what will happen or does alembic get a read/write lock on
alembic_version table for the entire migration?



there are no "read/write locks" on the alembic_version table.  What you're
doing will not work at all.   Only one process should be running migrations
against a single target database at a time.


Thanks for the reply, Mike. I am wondering what are my options -
should I just have to ensure that I don't run migrations in more than
one process? Which would mean, I cannot really use alembic for my DB
migrations (I wonder how people would usually do it).


How this is usually done is that the decision to "migrate" is initiated 
manually.  I'm guessing this is a web application that wants to just 
automigrate when it starts.Usually what apps like that do is check 
that the DB is not up to date, and present a web form with a button to 
"upgrade" - so the user pushes the button only once.Or, if this is 
some kind of service that spawns multiple processes, the lead process 
does the check against the table and does the upgrade.


The thing about schema upgrades is that they require code changes to 
have any upgrades to apply.  So therefore, you're starting *something* 
to first run that new code; that's where the migration step should 
happen, before everything else starts up.









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

Re: Running Migrations Simulataneously

[Mike Bayer]

Yes, you can implement that inside your env.py if you wanted.All the
other processes would wait and once inside the lock they all need to
re-check the version.Not something alembic would include, though.

On Tuesday, April 26, 2016, Amit Saha <amitsaha...@gmail.com> wrote:

> On Wed, Apr 27, 2016 at 12:37 AM, Mike Bayer <mike...@zzzcomputing.com
> <javascript:;>> wrote:
> >
> >
> > On 04/26/2016 01:22 AM, Amit Saha wrote:
> >>
> >> On Tue, Apr 26, 2016 at 12:33 PM, Mike Bayer <mike...@zzzcomputing.com
> <javascript:;>>
> >> wrote:
> >>>
> >>>
> >>>
> >>> On 04/25/2016 08:30 PM, Amit Saha wrote:
> >>>>
> >>>>
> >>>> Hi all,
> >>>>
> >>>> In my scenario, DB migrations (via alembic) will be run as part of the
> >>>> app deployment and multiple app deployments will happen near
> >>>> simultaneously (multiple EC2 instances talking to the same MySQL DB).
> >>>> Let's, for simplicity's sake assume two instances:
> >>>>
> >>>> Instance #1: Alembic sees alembic_version table and finds that we do
> >>>> need the migrations to happen, and starts the migration.
> >>>>
> >>>> Instance #2: Alembic sees alembic_version table and finds that we do
> >>>> need the migrations to happen, and starts the migration.  This doesn't
> >>>> obviously happen since MySQL will lock the table during the migration.
> >>>
> >>>
> >>>
> >>> this scenario is entirely unsupported.   MySQL does not support
> >>> transactional DDL and migration #1 will be hitting some tables and
> >>> migration
> >>> #2 another.The version table is not updated until after a
> particular
> >>> migration takes place so concurrent processes will both be doing the
> same
> >>> migration at the same time.
> >>>
> >>>>
> >>>> Now, let's say instance #1 finishes the migration first and updates
> the
> >>>> alembic_version table. Then, the migration triggered by instance #2
> >>>> starts to run, and errors out because the schema changes are already
> in
> >>>> place.
> >>>>
> >>>> Is this what will happen or does alembic get a read/write lock on
> >>>> alembic_version table for the entire migration?
> >>>
> >>>
> >>>
> >>> there are no "read/write locks" on the alembic_version table.  What
> >>> you're
> >>> doing will not work at all.   Only one process should be running
> >>> migrations
> >>> against a single target database at a time.
> >>
> >>
> >> Thanks for the reply, Mike. I am wondering what are my options -
> >> should I just have to ensure that I don't run migrations in more than
> >> one process? Which would mean, I cannot really use alembic for my DB
> >> migrations (I wonder how people would usually do it).
> >
> >
> > How this is usually done is that the decision to "migrate" is initiated
> > manually.  I'm guessing this is a web application that wants to just
> > automigrate when it starts.Usually what apps like that do is check
> that
> > the DB is not up to date, and present a web form with a button to
> "upgrade"
> > - so the user pushes the button only once.Or, if this is some kind of
> > service that spawns multiple processes, the lead process does the check
> > against the table and does the upgrade.
> >
> > The thing about schema upgrades is that they require code changes to have
> > any upgrades to apply.  So therefore, you're starting *something* to
> first
> > run that new code; that's where the migration step should happen, before
> > everything else starts up.
>
> Thanks again Mike. So, I think the manual separate step to change the
> schema has to be in there.
>
> For my curiosity's sake (and please bear with my noobishness), what if
> alembic had a lock (provided the RDMS allows) for the entire time:
>
> 1. Get RW lock on alembic_version
> 2. Check if we need a migration
> 3. Yes, next step, no- step 5
> 4. Run migrations
> 5. Update alembic_version
> 5. Release lock on alembic_version
>
> That would at least prevent multiple migrations stepping on each
> other's feet? Whichever gets to alembic_version first performs the
> entire migration and when the other process gets to alembic_version,
>

Re: Running Migrations Simulataneously

[Mike Bayer]

On 04/25/2016 08:30 PM, Amit Saha wrote:

Hi all,

In my scenario, DB migrations (via alembic) will be run as part of the
app deployment and multiple app deployments will happen near
simultaneously (multiple EC2 instances talking to the same MySQL DB).
Let's, for simplicity's sake assume two instances:

Instance #1: Alembic sees alembic_version table and finds that we do
need the migrations to happen, and starts the migration.

Instance #2: Alembic sees alembic_version table and finds that we do
need the migrations to happen, and starts the migration.  This doesn't
obviously happen since MySQL will lock the table during the migration.


this scenario is entirely unsupported.   MySQL does not support 
transactional DDL and migration #1 will be hitting some tables and 
migration #2 another.The version table is not updated until after a 
particular migration takes place so concurrent processes will both be 
doing the same migration at the same time.




Now, let's say instance #1 finishes the migration first and updates the
alembic_version table. Then, the migration triggered by instance #2
starts to run, and errors out because the schema changes are already in
place.

Is this what will happen or does alembic get a read/write lock on
alembic_version table for the entire migration?


there are no "read/write locks" on the alembic_version table.  What 
you're doing will not work at all.   Only one process should be running 
migrations against a single target database at a time.






(And hence instance #2

never will need to run the migration since when it reads
alembic_version, the DB already has the migrated schema).

Thanks for your insights.

Best Wishes,
Amit.





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

Re: FAILED: No 'script_location' key found in configuration

[Mike Bayer]

This depends on how you are sending Alembic its configuration. If you 
are running with a plain alembic.ini file, "script_location" should be 
present in the file itself, see the example in 
http://alembic.readthedocs.io/en/latest/tutorial.html#editing-the-ini-file


Otherwise if your setup is not using a simple .ini file and is instead 
providing configuration to Alembic in some other way, this key must be 
present in the config object.  If you were using the Config object 
directly, details on how that looks is here: 
http://alembic.readthedocs.io/en/latest/api/config.html .  There's a 
simple programmatic example a few paragraphs down which illustrates 
script_location being applied.




On 05/21/2016 01:59 PM, Drake wrote:

I've had alembic running, but now when I try and add some updates to my
database I run

alembic revision -m "Add permissions table"

I'm getting the following error

FAILED: No 'script_location' key found in configuration

Do you know how I can fix this?

Thanks.

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

Alembic 0.8.6 released

[Mike Bayer]

Alembic 0.8.6 is now available.

Release 0.8.6 fixes a small handful of bugs including in the area of 
batch migrations and Postgresql server default comparison.


It also enhances the behavior of the "alembic revision" command, such 
that if the revision fails due to a compile-time or runtime error in 
rendering the Mako template for script.py.mako, the Mako-translated 
traceback is saved inside of a tempfile and reported at the commandline 
for the failure.


For full changelog see:

http://alembic.readthedocs.org/en/latest/changelog.html#change-0.8.6

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

Re: alembic upgrade not idempotent?

[Mike Bayer]

On 04/18/2016 10:56 AM, bertr...@cryptosense.com wrote:

Hi,

Here's something quite fundamental I don't understand about alembic.
I've initialized my database and created a revision meant to be executed
online.  I went like this:
|
alembic init app/alembic
# edit configuration
alembic revision -m 'Data modification'
# edit revision script
alembic upgrade
|

If I run alembic upgrade a second time, it runs the upgradefunction
again, which results in an error because my function should not be run
on the new database (it's not idempotent). Why doesn't alembic figure
out that the upgrade has already been applied? This is especially
annoying in production where I just want to make sure the database is up
to date without having to guess which revision script to execute.


I'm not understanding what you are actually doing.  It is an error to 
run "alembic upgrade" without a target:


$ alembic upgrade
usage: alembic upgrade [-h] [--sql] [--tag TAG] revision
alembic upgrade: error: too few arguments


When you specify a target, say "head", it updates the version table:

$ .venv/bin/alembic upgrade head
INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade  -> e12f6fb70dcc, rev 1

and then the next time, it will do nothing:

$ .venv/bin/alembic upgrade head
INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.


so it appears like you have some entirely customized setup going on, 
which itself would be where your issue lies.










Best,

--
Bertrand

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

SQLAlchemy 1.0.17 Released

[mike bayer]

SQLAlchemy release 1.0.17 is now available.

The 1.0.x series is well into maintenance mode and most fixes and 
enhancements are targeted only at the 1.1.x series. This release 
includes only one ORM related fix as well as repairs to deprecation 
warnings that are more prevalent with the Python 3.6 interpreter.


Changelog for 1.0.17 is at: 
http://www.sqlalchemy.org/changelog/CHANGES_1_0_17


SQLAlchemy 1.0.17 is available on the Download Page at:

http://www.sqlalchemy.org/download.html

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

Re: modifying a primary key

[Mike Bayer]

On 09/07/2016 10:18 AM, Chris Withers wrote:

Hi All,

I have a migration which adds a column to a table where the added column
forms part of the primary key.


woe be unto you

:)





As a result, I need to alter the primary key of the table. Autogenerate
doesn't appear to spot this, so what do I need to write manually to get
this to work?



you need to drop the PK constraint and then re-create it.   Which means 
for the "drop" you need to know its name for drop_constraint("my_pk_name").


Here's the create:

http://alembic.zzzcomputing.com/en/latest/ops.html#alembic.operations.Operations.create_primary_key








cheers,

Chris



--
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 1.0.15 Released

[Mike Bayer]

SQLAlchemy release 1.0.15 is now available.

Release 1.0.15 features a small number of bug fixes that have been made 
over the past two months since the previous 1.0.x release.


Users should review the changelog for 1.0.15 at 
http://www.sqlalchemy.org/changelog/CHANGES_1_0_15; we'd like to thank 
the many contributors who helped with this release.


SQLAlchemy 1.0.15 is available on the Download Page at:
http://www.sqlalchemy.org/download.html

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

Re: Is there a way to generate DDL of current state as of a revision?

[Mike Bayer]

On 09/22/2016 02:51 AM, Tom Walter wrote:

I work in a team of people collaborating on database development and
we'd like to start version controlling and deploy our changes with Alembic.

I was wondering though if there is a way to generate the DDL for the
current state of the whole schema as of a given revision... something
like the 'offline' SQL generation, however rather than every
intermediate revision, just output the CREATE statements of the final
states of all the objects.

You might ask why anyone would want to do this... the reason is that we
generally like to do code reviews or pull requests when someone deploys
a change. If the only artifacts stored in source control are alembic
migrations, it can be difficult for a reviewer to get an idea of the
overall context and impact of a given migration or set of migrations.


this is also a common use case because of the desire to "squash" all the 
migrations up to a certain point that are no longer needed individually.




If we could use alembic to update a file or set of files representing
the current state of the entire DB, then that could be checked into
version control along with the migrations. Granted it would be useless
for migrating a given instance of a database from one state to another,
but it would be a useful and more readable way for a dev to understand
all the objects in a database, short of logging into a live instance of
one and poking around.


If the purpose of the file here is strictly one of documentation, I 
would first note that a traditional SQLAlchemy application has the 
tables also expressed in Python, either as Table objects or as ORM 
mapped objects that will show off the table names, columns, and general 
constraints.   Just from that level, there are tools that can generate 
ER diagrams from a SQLAlchemy model.   As well as, you can take any 
collection of SQLAlchemy tables and just use metadata.create_all() to 
emit the CREATE statements, which can be directed to string output.   So 
even without a database, the structure of your schema is apparent just 
from the Python code in the normal case.It would not be hard to 
write a short script to generate this from the state of the model.


To document the current state of the database itself without consulting 
the Python structure, IMO that's not even an Alembic thing, most simply 
you can use your database's dump feature to just dump out the CREATE 
statements to a text file.  All relational databases have such a tool 
available, such as pg_dump or mysqldump.   Then there are also an 
endless number of both free and commercial database inspection / 
diagramming tools as well that can generate all kinds of views of a 
relational database schema.


If, as a third case, you want to generate from the database schema 
itself, *and* use only SQLAlchemy / Alembic, you would create a MetaData 
object, do a metadata.reflect() of that engine, then run a 
metadata.create_all() into a string buffer.  This would not be nearly as 
complete as that of using the database dumper tools, however you would 
see the basic table structures as well as foreign key constraints.


The create_all() into a string buffer is described at 
http://docs.sqlalchemy.org/en/latest/faq/metadata_schema.html#how-can-i-get-the-create-table-drop-table-output-as-a-string.


Usually, people want this "state of the database" to be squashed into 
one big Alembic migration, which would be an additional step to the 
techniques above, but if you just want a readable text file, running 
metadata.create_all() into a text file is the most expedient way, it's 
just that you won't see any special directives or constraints that are 
present in your migrations only.   To get a fully accurate view I


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

Re: Using alembic with two projects, one with foreign keys referencing the other

[Mike Bayer]

On 09/29/2016 07:52 PM, Wesley Weber wrote:

I have an uncommon use case that might or might not be supported (or
even a good idea):

I have a main, independent project with its own tables. I would like to
keep track of migrations of its database. I will also have several
"plugin" projects, which can add functionality to the main software.
These may create and manage their own tables, and the main project need
not know about them. They may not even share the same source tree.


this is actually a common use case.


However, I would like to support referential integrity in the tables
created by the plugin projects (e.g., there might be a "users" table in
the main project, and the plugin might add its own "users_extra_info"
table referencing the "users" table).

I know how to have the plugin projects use their own alembic folder and
use a different alembic_version table, but what happens when the main
project undergoes a migration that breaks referential integrity with the
plugin projects? (the "users" table changes, violating foreign keys in
"users_extra_info" from another project, for example). Is there a way to
handle this? Alternatively, is this not a good design pattern?


that's actually up to how you advertise your model for plugin authors. 
A foreign key constraint typically refers to the primary key of a target 
table, or in less common cases towards columns that are inside of a 
UNIQUE constraint.   You'd not want to change around those columns if 
your plugin authors are making FKs towards them.  or if you want to 
change them, your plugin authors would have to know this.   You might 
want to use a semantic versioning scheme where you don't make any such 
changes within minor versions, and have your plugin authors target 
towards a ceiling version so that their plugins aren't broken by such 
changes.







Thanks

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

Alembic 0.8.9 Released

[mike bayer]

Alembic 0.8.9 is now available.

The release includes seven bug fixes.Changelog is available at:

http://alembic.zzzcomputing.com/en/latest/changelog.html#change-0.8.9

Download alembic 0.8.9 at https://pypi.python.org/pypi/alembic.

--
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 1.1.4 Released

[mike bayer]

SQLAlchemy release 1.1.4 is now available.

Release 1.1.4 includes a variety of bug fixes, some backported to the 
1.0 series and others which repair minor regressions in the 1.1 series. 
There is also an important fix to the new Postgresql ON CONFLICT 
feature, as well as a new feature allowing server side cursor 
functionality to be usable with MySQL.


Changelog for 1.1.4 is at:

http://www.sqlalchemy.org/changelog/CHANGES_1_1_4.

SQLAlchemy 1.1.4 is available on the Download Page at:

http://www.sqlalchemy.org/download.html

--
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 1.0.16 Released

[mike bayer]

SQLAlchemy release 1.0.16 is now available.

Release 1.0.16 includes a series backported bug fixes from SQLAlchemy 
versions 1.1.0 to 1.1.4. These changes include a handful of relatively 
minor ORM and MS SQL Server fixes.


Changelog for 1.0.16 is at:

https://www.sqlalchemy.org/changelog/CHANGES_1_0_16

SQLAlchemy 1.0.16 is available on the Download Page at:


https://www.sqlalchemy.org/download.html

--
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 1.1.2 Released

[Mike Bayer]

SQLAlchemy release 1.1.2 is now available.

Release 1.1.2 includes a small handful of bug fixes, including some 
small regressions from the 1.0 series, some ORM loading improvements, 
and a fix to the new PostgreSQL ON CONFLICT feature.


Changelog for 1.1.2 is at: http://www.sqlalchemy.org/changelog/CHANGES_1_1_2

SQLAlchemy 1.1.2 is available on the Download Page at: 
http://www.sqlalchemy.org/download.html


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

Re: MySQL ForeignKey indexes being removed

[mike bayer]

MyISAM does not actually record foreign key definitions.   Autogenerate 
is not compatible with foreign keys that are declared in the model yet 
are not actually present in the database due to MyISAM.   To keep these 
FK definitions in Python but work around the autogenerate issue, create 
an include_object rule that skips foreign_key_constraint detection:


http://alembic.zzzcomputing.com/en/latest/api/runtime.html?highlight=include_object#alembic.runtime.environment.EnvironmentContext.configure.params.include_object

On 12/22/2016 06:08 PM, Adam Patt wrote:

I'm using mysql 5.1.73 and alembic 0.8.6 and even when the model has not
changed.  Below I have the schema migration which is getting auto
generated as well as the models defs. This is using MyISAM and when I
look at the Foreign Keys tab in MySQL Workbench, it says foreign keys
can only be used in certain engines. I only recently added the Foreign
Key and migrations worked exactly as expected before this change.

What am I doing wrong?

### generated schema migration

def upgrade():
### commands auto generated by Alembic - please adjust! ###
op.drop_index('fk_command_task_id_task', table_name='command')
op.create_foreign_key(op.f('fk_command_task_id_task'), 'command',
'task', ['task_id'], ['id'])
op.drop_index('fk_task_job_id_job', table_name='task')
op.create_foreign_key(op.f('fk_task_job_id_job'), 'task', 'job',
['job_id'], ['id'])
### end Alembic commands ###


def downgrade():
### commands auto generated by Alembic - please adjust! ###
op.drop_constraint(op.f('fk_task_job_id_job'), 'task',
type_='foreignkey')
op.create_index('fk_task_job_id_job', 'task', ['job_id'], unique=False)
op.drop_constraint(op.f('fk_command_task_id_task'), 'command',
type_='foreignkey')
op.create_index('fk_command_task_id_task', 'command', ['task_id'],
unique=False)
### end Alembic commands ###

### object definitions

Base = declarative_base()

# make it so constraints have a name so that backward migrations work
correctly
Base.metadata = MetaData(naming_convention={
"ix": 'ix_%(column_0_label)s',
"uq": "uq_%(table_name)s_%(column_0_name)s",
"ck": "ck_%(table_name)s_%(constraint_name)s",
"fk": "fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s",
"pk": "pk_%(table_name)s"
  })


class SnapBase(object):
"""
base for all SNAP sqlalchemy objects
does the following things:
- adds a default id field to all objects
- automatically generate a table name based on the class name.
 Turns ClassName to class_name
- creates a nice human readable version of any object
- utility method to get the list of fields available
"""
@declared_attr
def __tablename__(cls):
"""
turn things like MyTable into my_table in the database
"""
tablename = ''
for c in cls.__name__:
if c.isupper() and tablename:
tablename += '_'
tablename += c.lower()
return tablename

def _field_names(self):
return [str(c).split('.')[1] for c in self.__table__.columns]

def __repr__(self):
display = ""
for column in self.__table__.columns:
name = str(column).split('.')[1]
if display:
display += ', '
value = getattr(self, name)
column_type = str(column.type)
if (column_type.startswith('VARCHAR') or
column_type.startswith('CHAR') or column_type in ['DATETIME', 'DATE'])
and value is not None:
val = "%s='%s'"
else:
val = "%s=%s"
display += val % (name, value)
return '%s(%s)' % (self.__class__.__name__, display)

id = Column(Integer, primary_key=True)


class Job(SnapBase, Base):
user = Column(String(50), nullable=False)
status = Column(String(10), default='running', nullable=False)  #
running/complete
created = Column(DateTime, default=datetime.datetime.utcnow,
nullable=False)
updated = Column(DateTime, default=datetime.datetime.utcnow,
onupdate=datetime.datetime.utcnow, nullable=False)

tasks = relationship('Task', backref='job')


class Task(SnapBase, Base):
serial = Column(String(50), nullable=False)
name = Column(String(50), nullable=False)
user = Column(String(50), nullable=False)
status = Column(String(10), default='running', nullable=False)  #
running/complete
result = Column(String(50))
message = Column(Text)
created = Column(DateTime, default=datetime.datetime.utcnow,
nullable=False)
updated = Column(DateTime, default=datetime.datetime.utcnow,
onupdate=datetime.datetime.utcnow, nullable=False)

job_id = Column(Integer, ForeignKey('job.id'))
commands = relationship('Command', backref='task')

class Command(SnapBase, Base):
serial = Column(String(50), nullable=False)
agent = Column(String(20), nullable=False)  # snap/oob/cobbler/chef

SQLAlchemy 1.1.7 Released

[mike bayer]

SQLAlchemy release 1.1.7 is now available.

Release 1.1.7 includes several bugfixes, one of which was introduced in 
1.1.6 as a result of performance enhancements for joined eager loading 
which presents as a race condition under certain multithreaded 
scenarios. Fixes also include an important adjustment in the cx_Oracle 
dialect to adjust for changes in cx_Oracle release 5.3, resolution of an 
extremely small memory leak which could occur in the relatively unusual 
case that an arbitrarily high number of savepoints were established on a 
single Connection object, as well as an important fix in the "schema 
translate" feature.


Changelog for 1.1.7 is at: http://www.sqlalchemy.org/changelog/CHANGES_1_1_7

SQLAlchemy 1.1.7 is available on the Download Page at: 
http://www.sqlalchemy.org/download.html


--
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 1.1.8 Released

[mike bayer]

SQLAlchemy release 1.1.8 is now available.

Release 1.1.8 is only a few days past 1.1.7, however is being released 
early in order to deliver a few fixes requested by downstream projects. 
In particular, a regression from the 1.0.x series involving the 
sqlalchemy.ext.mutable extension is fixed.


Changelog for 1.1.8 is at: http://www.sqlalchemy.org/changelog/CHANGES_1_1_8

SQLAlchemy 1.1.8 is available on the Download Page at: 
http://www.sqlalchemy.org/download.html


--
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 1.0.19 released

[Mike Bayer]

SQLAlchemy release 1.0.19 is now available.

This release modifies the just-released fix for Oracle "WITH_UNICODE"
mode based on feedback from cx_Oracle developers, to resolve a
performance regression caused by this change.

Changelog for 1.0.19 is at:  https://www.sqlalchemy.org/changelog/CHANGES_1_0_19

SQLAlchemy 1.0.19 is available on the Download Page at:
https://www.sqlalchemy.org/download.html

-- 
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 1.1.13 released

[Mike Bayer]

SQLAlchemy release 1.1.13 is now available.

This release modifies the just-released fix for Oracle "WITH_UNICODE"
mode based on feedback from cx_Oracle developers, to resolve a
performance regression caused by this change.

Changelog for 1.1.13 is at: https://www.sqlalchemy.org/changelog/CHANGES_1_1_13

SQLAlchemy 1.1.13 is available on the Download Page at:
https://www.sqlalchemy.org/download.html

-- 
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 1.0.18 released

[Mike Bayer]

SQLAlchemy release 1.0.18 is now available.

This release repairs an issue in the test suite that would prevent the
suite from passing on Python version 3.6.2. The release is made at the
same time as that of release 1.1.12 and 1.2.0b2. Additionally, a
backport from release 1.1.7 allowing cx_Oracle 5.3 to work correctly
is included in this release.

Changelog for 1.0.18 is at: http://www.sqlalchemy.org/changelog/CHANGES_1_0_18

SQLAlchemy 1.0.18 is available on the Download Page at:
http://www.sqlalchemy.org/download.html

-- 
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 1.1.12 released

[Mike Bayer]

SQLAlchemy release 1.1.12 is now available.

This release repairs an issue in the test suite that would prevent the
suite from passing on Python version 3.6.2. The release is made at the
same time as that of release 1.0.18 and 1.2.0b2. Also included are
fixes related to ORM use of JSON NULL values, subquery eager loading,
and a stability enhancement involving the identity map when used under
high concurrency.

Changelog for 1.1.12 is at: https://www.sqlalchemy.org/changelog/CHANGES_1_1_12

SQLAlchemy 1.1.12 is available on the Download Page at:
https://www.sqlalchemy.org/download.html

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

Re: Altering the behavior of AddColumn

[Mike Bayer]

more straightforward would be a pull request that adds "mysql_after"
keyword to both op.add_column() and op.modify_column().

On Fri, Jul 7, 2017 at 1:33 AM,   wrote:
>
>
> On Tuesday, August 27, 2013 at 9:31:29 AM UTC-5, Michael Bayer wrote:
>>
>>
>> On Aug 26, 2013, at 11:49 PM, Samer Atiani  wrote:
>>
>> Hello,
>>
>> All my tables have create_date and update_date columns in them, and I like
>> to keep these columns as the last two columns in any table for convention
>> reasons. However, when you use alembic autogenerate to generate schema
>> migrations to add columns, the columns are always added to the end of the
>> table. With time, the create_date and update_date columns in my tables will
>> end up being in the middle of the column list in my MySQL database.
>>
>> So I was trying to alter this behavior by making alembic use MySQL's
>> "ALTER TABLE x ADD COLUMN y AFTER z" feature to always try to add columns
>> before create_date or update_date. The only way I could find out how is to
>> override the visit_add_column method after EnvironmentContext configuration.
>> I achieved this by changing the "run_migrations_online()" method in
>> alembic/env.py so that it looks like:
>>
>>
>> However, this feels quite brittle and is probably touching internal stuff
>> that it shouldn't touch. My question to you is: is there a better way to
>> achieve this? I looked at SQLAlchemy Core Events, but I couldn't find an
>> event that could correspond to adding columns, nor is the alembic code
>> firing any events I could see when it add columns (unlike, for example, when
>> it emits a CREATE TABLE statements).
>>
>>
>>
>> AddColumn is a SQL expression element so the standard way we want to
>> override those is to use @compiles
>> (http://docs.sqlalchemy.org/en/rel_0_8/core/compiler.html#changing-the-default-compilation-of-existing-constructs).
>> As you might have noticed, Alembic is already using that system internally
>> in order to provide compile rules for constructs.   So we want to override
>> that, however the roadblock is that the @compiles system currently doesn't
>> have a nice way of letting us override the @compiles of an existing
>> @compiles (should be added as a feature).  So there's a slight bit of
>> internals we need to get at Alembic's already present @compiles rule for
>> now, but the rest is straightforward:
>>
>> from sqlalchemy.ext.compiler import compiles
>> from alembic.ddl.base import AddColumn
>>
>> # ideally, the @compiles system would have some way of getting
>> # us the "existing" @compiles decorator, so this part is the
>> # hack
>> specs = AddColumn.__dict__.get('_compiler_dispatcher').specs
>> existing_dispatch = specs.get('mysql', specs['default'])
>>
>> @compiles(AddColumn, "mysql")
>> def add_column(element, compiler, **kw):
>> text = existing_dispatch(element, compiler, **kw)
>> if "after" in element.column.info:
>> text += " AFTER %s" % element.column.info['after']
>> return text
>>
>> from sqlalchemy import Column, Integer
>> from alembic.migration import MigrationContext
>> from alembic.operations import Operations
>>
>> ctx = MigrationContext.configure(dialect_name="mysql", opts={"as_sql":
>> True})
>> op = Operations(ctx)
>>
>> op.add_column("t", Column('y', Integer))
>>
>> op.add_column("t", Column('x', Integer, info={"after": "y"}))
>
>
>
> I'm interested in applying a similar solution to handle alter columns.
>
> The problem I'm seeing is that  MySQLImpl.alter_column() does not pass *kw
> to MySQLChangeColumn or  MySQLModifyColumn. So there doesn't seem to be a
> simple way that I can write a @compiles(MySQLChangeColumn, 'mysql')
> decorated function, and the interface would have to be something like
> element.info  instead of element.column.info too.   Also, can you explain
> why I have to use MySQLChangeColumn/MySQLModifyColumn instead of AlterColumn
> in the @compiles decorator?
>
> Would it be possible to get support for this into alembic proper?  I can get
> it to work, but I have to monkey patch MySQLImpl.
>
> --
> 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.

Re: first time migration with existing database ?

[Mike Bayer]

On Tue, Aug 8, 2017 at 6:14 AM, Sandeep Srinivasa  wrote:
> hi guys,
> I'm just taking over a legacy piece of code where tables were hand created
> (SQL).
>
> I'm trying to migrate this over to alembic. I dont have (want to use) any
> python models, so this is alembic by itself.
>
> I'm unsure of two things:
> 1. How do I create the first migration - for my first migration, I want to
> snapshot the existing structure of the database. Most of the autogenerate
> takes place only if there is an existing set of python models. How do I do
> this in my case

that is a fantastic question and I don't think anyone has asked that
before.So I worked up an example, we should add it to the
"Cookbook" documentation.   This will print out just the python code
you'd stick inside of upgrade() by hand, with some more it could write
the whole migration file out at once but I think this will do what you
need:

from sqlalchemy import Column, Integer, ForeignKey, create_engine, \
String, Index, MetaData
from sqlalchemy.ext.declarative import declarative_base
from alembic.operations import ops
from alembic.autogenerate import render_python_code


def make_a_database(engine):
Base = declarative_base()

class A1(Base):
__tablename__ = 'a1'
id = Column(Integer, primary_key=True)

class A2(Base):
__tablename__ = 'a2'
id = Column(Integer, primary_key=True)

class B(Base):
__tablename__ = 'b'
id = Column(Integer, primary_key=True)
a1_id = Column(ForeignKey('a1.id'))
a2_id = Column(ForeignKey('a2.id'))

class C(Base):
__tablename__ = 'c'

foo = Column(String(50), primary_key=True)
bar = Column(String(10))

Index("c_bar", C.bar)

Base.metadata.create_all(engine)


def metadata_to_migration(metadata):
migration_script = ops.MigrationScript(
'initial_rev',
ops.UpgradeOps(
ops=[
ops.CreateTableOp.from_table(table)
for table in reflected_metadata.sorted_tables
]
+ [
ops.CreateIndexOp.from_index(index)
for table in reflected_metadata.sorted_tables
for index in table.indexes
]
),
ops.DowngradeOps(),
)

print(render_python_code(migration_script.upgrade_ops))


engine = create_engine("sqlite://")
make_a_database(engine)

reflected_metadata = MetaData()
reflected_metadata.reflect(engine)
metadata_to_migration(reflected_metadata)




> 2. How do I apply the migration ... without applying the migration. Since I
> need alembic to start managing my db, I realize I need to get it to create a
> "migrate_version" . However, I cant really apply the migration. How do I do
> this ?

For this use case you use the "alembic stamp" command, the section at
http://alembic.zzzcomputing.com/en/latest/cookbook.html#building-an-up-to-date-database-from-scratch
talks about this command.




>
> Thanks guys!
> regards
> sandeep
>
> --
> 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.

Re: Bypass current version check when stamping.

[Mike Bayer]

On Aug 5, 2017 8:40 AM, "Philippe Lavoie" 
wrote:

Let's say you're on branch and you have a migration, you test it out, it
works: everything is cool. The version in `public.alembic_version` is your
latest migration.
Before you merge it you have to switch to a different branch to work on
something else and you also have a migration there.

You want to use `alembic stamp` to move back to the previous state, but
`alembic` will complain that it can't find the revision stored in
`public.alembic_version`, since it was in your other branch.

The work around is to just drop the table and redo the stamp. I think it
would be convenient for `alembic` to bypass this check, what do you think?



It's unfortunately not possible in the general case because a stamp may or
may not affect existing rows in the table depending on their relationship
to the stamp you are specifying.  Without the version files present, it
doesn't know anything about them.  If you're sure about the rows which go
away, you can DELETE them (no need for DROP).


I suppose a "purge-all" option to "stamp" could add this delete as a
convenience but it would need to carry appropriate warnings.

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

Re: Bypass current version check when stamping.

[Mike Bayer]

On Aug 5, 2017 9:05 AM, "Philippe Lavoie" <philippe.lavo...@gmail.com>
wrote:

Ah ok, for sure you're thinking about covering more cases than I am.


consider if you have multiple, independent branches (eg are not connected
down to the root ) and you only want to stamp for one of them.   If it sees
unfamiliar identifiers in the table it doesn't know whether or not to
delete it or leave it alone.



Thanks for your reply :)

On Sat, Aug 5, 2017 at 9:02 AM, Mike Bayer <clas...@zzzcomputing.com> wrote:

>
>
> On Aug 5, 2017 8:40 AM, "Philippe Lavoie" <philippe.lavo...@gmail.com>
> wrote:
>
> Let's say you're on branch and you have a migration, you test it out, it
> works: everything is cool. The version in `public.alembic_version` is your
> latest migration.
> Before you merge it you have to switch to a different branch to work on
> something else and you also have a migration there.
>
> You want to use `alembic stamp` to move back to the previous state, but
> `alembic` will complain that it can't find the revision stored in
> `public.alembic_version`, since it was in your other branch.
>
> The work around is to just drop the table and redo the stamp. I think it
> would be convenient for `alembic` to bypass this check, what do you think?
>
>
>
> It's unfortunately not possible in the general case because a stamp may or
> may not affect existing rows in the table depending on their relationship
> to the stamp you are specifying.  Without the version files present, it
> doesn't know anything about them.  If you're sure about the rows which go
> away, you can DELETE them (no need for DROP).
>
>
> I suppose a "purge-all" option to "stamp" could add this delete as a
> convenience but it would need to carry appropriate warnings.
>
> --
> 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 a topic in the
> Google Groups "sqlalchemy-alembic" group.
> To unsubscribe from this topic, visit https://groups.google.com/d/to
> pic/sqlalchemy-alembic/Qum7T9aZTaA/unsubscribe.
> To unsubscribe from this group and all its topics, 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.

-- 
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 1.1.11 Released

[mike bayer]

SQLAlchemy release 1.1.11 is now available.

Release 1.1.11 includes a series of fixes providing 
forwards-compatibility with a variety new behaviors in supported 
databases. A few other core fixes and one ORM-related fix is also included.


Changelog for 1.1.11 is at: 
http://www.sqlalchemy.org/changelog/CHANGES_1_1_11


SQLAlchemy 1.1.11 is available on the Download Page at: 
http://www.sqlalchemy.org/download.html


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

Re: Migration fails when changing to single table inheritance

[mike bayer]

On 06/07/2017 04:44 PM, Michael wrote:
Hi all, I have a class called MediaChapter(Base), which I've refactored 
into MediaBase(Base) and MediaChapter(MediaBase) When I run the 
migration, I see:


|
psycopg2.IntegrityError:insert orupdate on table "mediachapter"violates 
foreign key constraint "fk_mediachapter_id_mediabase"

DETAIL:Key(id)=(570)isnotpresent intable "mediabase".



here's the real error with the SQL:

sqlalchemy.exc.IntegrityError: (psycopg2.IntegrityError) insert or 
update on table "mediachapter" violates foreign key constraint 
"fk_mediachapter_id_mediabase"

DETAIL:  Key (id)=(570) is not present in table "mediabase".
 [SQL: 'ALTER TABLE mediachapter ADD CONSTRAINT 
fk_mediachapter_id_mediabase FOREIGN KEY(id) REFERENCES mediabase (id)']


the error means that your "mediachapter" table contains an id, "570", 
which is not present in the "mediabase" table.


it looks like you are starting with a populated "mediachapter" table 
then adding a new table "mediabase".  Before you create the constraint, 
you need to run an INSERT on "mediabase" that selects from 
"mediachapter", like:


INSERT INTO mediabase (id, col1, col2, ...) SELECT id, col1, col2, .. 
FROM mediachapter






|



I'm not sure exactly what this means because I create a mediabase.id.

I did
|
ws-alembic -c kjvrvg/conf/development.ini -x packages=all revision 
--auto-m "MediaBase-MediaChapter subclassing"

|


-- SUCCESS

then
|
ws-alembic -c kjvrvg/conf/development.ini -x packages=all upgrade head
|


-- FAILED


Here are my models, MediaBase:

|
classMediaBase(Base):
#: The table in the database
 __tablename__ ="mediabase"

#: Database primary key for the row (running counter)
 id =Column(Integer,autoincrement=True,primary_key=True)

# table inheritance
 media_type =Column(String(32),nullable=False)

#: Publicly exposed non-guessable
 uuid =Column(UUID(as_uuid=True),default=uuid4)

 localizedname =Column(Unicode(128),default=None)

#: url
 url =Column(Unicode(384),default=None)

# full iso language-locale identifier i.e. zh-Hans-US
 language_id =Column(String(16),default=None)

# name of the person presenting the material
 presenter_name =Column(Unicode(64),default=None)
 source_material =Column(Unicode(128),default=None)

# table inheritance
 __mapper_args__ ={'polymorphic_on':media_type}

def__repr__(self):
"""Shell and debugger presentation."""
return'{} ({}) {} 
<{}>'.format(self.localizedname,self.language_id,str(self.uuid),self.url)


def__str__(self):
"""Python default and admin UI string presentation."""
return'{} ({}) presenter: {} source: {} 
  <{}>'.format(self.localizedname,self.language_id,self.presenter_name,self.source_material,self.url)



|


and MediaChapter:

|
classMediaChapter(MediaBase):

#: The table in the database
 __tablename__ ="mediachapter"

 __mapper_args__ ={'polymorphic_identity':'chapter'}

 id =Column(Integer,ForeignKey('mediabase.id'),primary_key=True)

#: Which chapter this media is part of
 chapter_id =Column(Integer,ForeignKey('chapter.id'))
 chapter =relationship("Chapter",back_populates="mediachapter")
|




and finally here is the auto-generated migration. I put all files in a 
github Gist.


|
https://gist.github.com/mazz/7d63e521316859f4ae852e5cea5d84eb
|



Any suggestions?
Mazz

|

"""MediaBase-MediaChapter subclassing

Revision ID: a00980918d75
Revises: e74ba4203098
Create Date: 2017-06-07 16:10:29.807437

"""

# revision identifiers, used by Alembic.
revision ='a00980918d75'
down_revision ='e74ba4203098'
branch_labels =None
depends_on =None

importdatetime
importwebsauna.system.model.columns
fromsqlalchemy.types importText# Needed from proper creation of JSON 
fields as Alembic inserts astext_type=Text() row


fromalembic importop
importsqlalchemy assa
fromsqlalchemy.dialects importpostgresql

defupgrade():
# ### commands auto generated by Alembic - please adjust! ###
 op.create_table('mediabase',
 sa.Column('id',sa.Integer(),nullable=False),
 sa.Column('media_type',sa.String(length=32),nullable=False),
 sa.Column('uuid',postgresql.UUID(as_uuid=True),nullable=True),
 sa.Column('localizedname',sa.Unicode(length=128),nullable=True),
 sa.Column('url',sa.Unicode(length=384),nullable=True),
 sa.Column('language_id',sa.String(length=16),nullable=True),
 sa.Column('presenter_name',sa.Unicode(length=64),nullable=True),
 sa.Column('source_material',sa.Unicode(length=128),nullable=True),
 sa.PrimaryKeyConstraint('id',name=op.f('pk_mediabase'))
)
 op.alter_column('group','created_at',
existing_type=postgresql.TIMESTAMP(timezone=True),
type_=websauna.system.model.columns.UTCDateTime(),
existing_nullable=True)
 op.alter_column('group','updated_at',
existing_type=postgresql.TIMESTAMP(timezone=True),
type_=websauna.system.model.columns.UTCDateTime(),
existing_nullable=True)
 

Re: Alembic autogenerate ignore non mapped tables.

[Mike Bayer]

On Sat, Oct 14, 2017 at 11:16 PM, Srikanth Bemineni
 wrote:
> Hi,
>
> When I auto generate a migration using alembic, it automatically drops some
> dynamically created tables which are in the DB schema. These tables are
> dynamically created by my application. These class don't inherit declarative
> base. They are added to declarative base by the application during run time
> , but not during migration creation. How can alembic ignore these tables.?

check out the include_object parameter
http://alembic.zzzcomputing.com/en/latest/api/runtime.html#alembic.runtime.environment.EnvironmentContext.configure.params.include_object
which is the catchall hook for "ignore item" rules.




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

SQLAlchemy 1.2.0b3 Released

[Mike Bayer]

SQLAlchemy release 1.2.0b3 is now available.

Release 1.2.0b3 is possibly the last beta release before 1.2.0 final
is released. The release of 1.2.0b3 was delayed by two major factors.
One was the release of cx_Oracle 6.0, which is a complete rework of
the underlying client library for cx_Oracle that in turn required a
lot of work on the SQLAlchemy side to accommodate, which resulted in
SQLAlchemy's cx_Oracle dialect itself being reworked and modernized to
make more complete use of patterns that were introduced in the 5.x
series of cx_Oracle. The other was made possible by the advent of
Microsoft SQL Server for Linux, which has allowed SQL Server testing
to finally be part of of SQLAlchemy's continuous integration
environment; as a result of this testing effort the library and test
suite have had many long-standing SQL Server issues repaired.

On top of those two efforts were the usual stream of issue fixes that
are backported into the 1.1.x series as well as additional behavioral
adjustments new to the 1.2 series.

The "What's New in SQLAlchemy 1.2?" [1] document as well as the
Changelog [2] will detail these changes as well as the changes since
the 1.1 series overall. Users should carefully review these documents
when testing applications against the 1.2 series, including for the
move from 1.2.0b2 to 1.2.0b3. We'd like to thank the many contributors
who helped with this release.

SQLAlchemy 1.2.0b3 is available on the Download Page. [3]

[1] http://www.sqlalchemy.org/docs/latest/changelog/migration_12.html
[2] http://www.sqlalchemy.org/changelog/CHANGES_1_2_0b3
[3] http://www.sqlalchemy.org/download.html

-- 
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 1.1.14 released

[Mike Bayer]

SQLAlchemy release 1.1.14 is now available.

This release includes a variety of fixes for the ORM. Two of the
issues are critical stability issues involving garbage-collection
related bugs that are more likely to be seen within the Pypy
interpreter however are not necessarily limited to this platform.

Changelog for 1.1.14 is at: http://www.sqlalchemy.org/changelog/CHANGES_1_1_14

SQLAlchemy 1.1.14 is available on the Download Page at:
http://www.sqlalchemy.org/download.html

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

Re: Modeling One-to-One relationships and Unique constraints

[Mike Bayer]

On Wed, Sep 6, 2017 at 3:12 AM,   wrote:
> Hello,
>
> I am a bit puzzled over modeling One-to-One relationships. The example in
> the documentation says to use uselist flag on the relationship declaration.
> That does make sense to ensure scalars on both sides of the relationship.
> However, that's just an ORM flag and does not necessarily translate to the
> DB (e.g. using Alembic).
>
> In this context I miss the mention of a unique constraint. Could the uselist
> flag not be derived if a unique constraint was specified on the foreign key
> column? For example:
>
> class Parent(Base):
> __tablename__ = 'parent'
> id = Column(Integer, primary_key=True)
> child_id = Column(Integer, ForeignKey('child.id'), unique=True)
> child = relationship("Child", back_populates="parent")
>
> class Child(Base):
> __tablename__ = 'child'
> id = Column(Integer, primary_key=True)
> parent = relationship("Parent", back_populates="child") # uselist=False
> redundant?
>
> Here, the Parent.child_id column has a unique constraint which narrows the
> Many-to-One to a One-to-One relationship. Would this not make the uselist
> flag redundant?
>
> Or am I missing something?

One can build a one-to-one relationship *without* a unique constraint
being present, hence the uselist flag as a public accessor still has a
purpose.   Additionally, if your Parent class linked to Child using a
composite foreign key where only some of the columns had unique=True,
that again means having uselist as explicit is useful.Add to that,
sometimes the table has been reflected, and it's only in the last few
years that SQLAlchemy has had the ability to reflect unique
constraints, and even then, not on all backends.

So there's many cases where a unique constraint may or may not be
present, yet we still need the uselist flag.   None of these cases are
the very specific case you have here.   The precedent for uselist
setting itself to false implicitly would be that a standard
many-to-one, which involves foreign keys that link directly to a
primary key, and the relationship() construct performs a lot of
heuristics when it is configured in order to figure this out.  These
heuristics took years to get right.

Overall, it is optional from an ORM point of view to set unique
constraints on Table metadata, even if the backing database does have
those constraints in place.   But this is not the case for a primary
key, which is explicitly required by the ORM.   So it is easier for
the ORM to rely upon making decisions for uselist based on a primary
key constraint, which is a very reliable source of information, versus
the presence of a unique constraint, which is an unreliable source of
information (in that it is optional, might not be set, might not be
reflected, might be present in the database and/or the model and not
actually on the other side).

So to minimize confusion and try to stick to the principle of "There
should be one-- and preferably only one --obvious way to do it.",
there is no feature right now to attempt to auto-determine uselist
based on the combination of the presence of a unique constraint
combined with the correct primaryjoin geometry.




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

Re: Recommended logger for migration scripts

[Mike Bayer]

On Sun, Oct 1, 2017 at 7:41 PM,   wrote:
> I've used in my generated migration script:
>
> log = logging.getLogger(__name__)
> log.setLevel(logging.INFO)
>
> for now and that seems to work; quick glance at Alembic’s code indicates
> that’s what Alembic uses as well. The util/messaging.py is used in other
> contexts.

inside your environment's env.py, Alembic places this:

# Interpret the config file for Python logging.
# This line sets up loggers basically.
fileConfig(config.config_file_name)

that's reading the [loggers] and other logging sections from your
alembic.ini file.   You can add your own logger name to that file so
that your own logger is usable and also configurable without changing
any source code, such as where you added log.setLevel(logging.INFO).

You can use any logger by name, like alembic's is
logging.getLogger("alembic"), and as you noted you can get one with
your own namespace using logging.getLogger(__name__).




>
> Jens
>
>
>
> On Sunday, October 1, 2017 at 5:03:31 PM UTC+10, jens.t...@gmail.com wrote:
>>
>> Hi,
>>
>> I’d like to output some logging info from my migration script, which was
>> generated by Alembic. Other than Python’s own logging module, what is the
>> recommended way to hook into Alembic’s logger to use it’s formatting?
>>
>> 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.

Re: importing from myapp

[Mike Bayer]

On Mon, Oct 2, 2017 at 9:06 PM, hatch  wrote:
> from myapp.mymodel import Base
> target_metadata = Base.metadata
>
> http://alembic.zzzcomputing.com/en/latest/autogenerate.html
>
> What is the preferred way to access myapp? As far as I can tell it's not
> normally available using this structure (doing the above gives ImportError),
> without altering sys.path. Is it expected that "myapp" is installed? Or
> another approach?

it's expected that "myapp" is installed in some way, for development I
usually just set PYTHONPATH or set up a virtualenv with pip install
-e.


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

Re: Rename an existing constraint

[Mike Bayer]

On Wed, Aug 30, 2017 at 7:18 AM,   wrote:
> Given an Alembic migration, would you recommend the following code to rename
> constraints?
>
> from alembic import op
> import sqlalchemy as sa
> from srv.orm.meta import NAMING_CONVENTION # as per Pylons cookiecutter
> template
>
> def upgrade():
>
> connection = op.get_bind()
> engine = connection.engine
> metadata = sa.MetaData(naming_convention=NAMING_CONVENTION)
>
> for table_name in engine.table_names():
> table = sa.Table(table_name, metadata, autoload_with=connection)
> for fk in table.foreign_keys:
> op.drop_constraint(fk.name, table_name, type_="foreignkey")
> fk.name = None
> op.invoke(CreateForeignKeyOp.from_constraint(fk))
>
> For downgrade() we'd create a metadata without naming_convention, thus
> falling back to the db's naming (which is where we're coming from).
>
> However, how would I go about iterating over indexes, unique constraints,
> and check constraints of a table?

same idea, the table has table.indexes for the Index objects,
table.constraints in fact has all of ForeignKeyConstraint,
UniqueConstraint, CheckConstraint so you could iterate just that and
do an isinstance() to take the right course of action.


>
> Thank you!
>
>
> On Wednesday, August 30, 2017 at 7:20:32 AM UTC+10, jens.t...@gmail.com
> wrote:
>>
>> 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
>
>
>
> --
> 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.

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.

Re: unit testing migration code

[Mike Bayer]

don't think so but haven't looked. We have a fixture in Openstack
that does that kind of thing, but it's not non-openstack friendly.   I
think you've found your next project!


On Fri, Dec 15, 2017 at 1:42 AM, Chris Withers <ch...@withers.org> wrote:
> Has anyone done anything like an equivalent of the following but for
> Alembic?
>
> https://github.com/plumdog/django_migration_testcase
>
> cheers,
>
> Chris
>
>
> On 01/12/2017 15:06, Mike Bayer wrote:
>>
>> it's kind of a PITA but in Openstack we have fixtures which actually
>> run all the alembic (or sqlalchemy-migrate) migrations on a new
>> database.  Some of the more ambitious projects even write unit tests
>> in between each migration that use inspect() to check that the
>> database state is what's expected.
>>
>> so to do things like that, you need a fixture which can:
>>
>> 1. create a new database (you probably need to produce a randomized
>> name for concurrency)
>> 2. invoke alembic to each revision individually (you can do this
>> through alembic API:
>> http://alembic.zzzcomputing.com/en/latest/api/commands.html
>> 3. have a dispatch which can call upon test cases linked to that rev,
>> like "def test_aabbccddee_does_thing_one()"
>> 4. drops the database
>>
>>
>> and...that's how you do it !
>>
>>
>>
>> On Thu, Nov 30, 2017 at 1:54 PM, Chris Withers <ch...@simplistix.co.uk>
>> wrote:
>>>
>>> Hi All,
>>>
>>> How would I add test coverage for this sort of code?
>>>
>>>
>>> https://coveralls.io/builds/14408741/source?filename=mortar_mixins%2Fmigrations.py
>>>
>>> cheers,
>>>
>>> Chris
>>>
>>> --
>>> 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.