Re: [sqlalchemy] Turning SAWarnings into exceptions

[Michal Petrucha]

On Thu, Sep 15, 2016 at 03:09:44PM +0100, Simon King wrote:
> According to https://docs.python.org/2/using/cmdline.html#cmdoption-W,
> the full form of -W (and PYTHONWARNINGS) is:
> 
> action:message:category:module:line
> 
> Empty fields are ignored, and unused trailing fields can be left out,
> so maybe "error::SAWarning" would work?

The problem with this is that -W and PYTHONWARNINGS are evaluated
*before* any user code gets imported, which means there is no
SAWarning at that time, so this doesn't work. At least it didn't work
for me when I was trying to do this myself a few months ago, and I
found this as an explanation. (I didn't save a reference, though,
unfortunately.)

What I ended up doing was to enable all warnings, and then whitelist
some of them one by one (IIRC, I only had to restore the default
behavior for ImportError and DeprecationWarning, but I'm not entirely
certain anymore – I'm pretty sure it wasn't more than a small handful,
though, of course, this will also depend on the packages you use).

Good luck,

Michal

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


signature.asc
Description: Digital signature

Re: Getting the current dialect name in a migration

[Michal Petrucha]

On Fri, Jun 03, 2016 at 10:24:23AM -0400, Mike Bayer wrote:
> 
> 
> op.get_bind().engine.name

/me hangs his head in shame.

Thanks, I probably should have tried that.

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


signature.asc
Description: Digital signature

Re: Getting the current dialect name in a migration

[Michal Petrucha]

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.


signature.asc
Description: Digital signature

Getting the current dialect name in a migration

[Michal Petrucha]

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.


signature.asc
Description: Digital signature

Re: [sqlalchemy] Support for Oracle 12c auto increment (IDENTITY) columns?

[Michal Petrucha]

On Wed, Apr 13, 2016 at 07:15:27AM -0400, Mike Bayer wrote:
> We've not started supporting new oracle 12c features as of yet, in this
> case it might be possible to get it working with some dialect flags since
> we already use "returning" to get at the newly generated primary key,
> although testing would be needed and other assumptions in the dialect might
> get in the way.
> 
> Is there a free XE download for 12c yet?   I can do nothing until I can at
> least install that, unless you were able to work up patches on your end
> (which would be great anyway because I have no time these days).

There is a pre-installed VM with 12c available from
http://www.oracle.com/technetwork/database/enterprise-edition/databaseappdev-vm-161299.html

It's ridiculously slow (orders of magnitude slower than PG/MySQL
running on bare metal), but it works...

Cheers,

Michal

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


signature.asc
Description: Digital signature

Re: [sqlalchemy] Re: order by child object's field

[Michal Petrucha]

On Mon, Mar 21, 2016 at 05:21:21PM +0100, c.bu...@posteo.jp wrote:
> > ReferenceAuthor is an instance of sqlalchemy.Table, so you can refer
> > to its columns as ReferenceAuthor.c.Index.
> 
> Ah nice. But something is still wrong.
> Part of the query:
> 
>   .join(ReferenceAuthor,
>   ReferenceAuthor.c.Index=0) \
> 
> result in
> ReferenceAuthor.c.Index=0) \
> ^
> 
> SyntaxError: keyword can't be an expression
> 
> Not sure what is wrong here. But I think c.Index doesn't exist?

That's not it, try:

q.join(ReferenceAuthor, ReferenceAuthor.c.Index == 0)

If you use a single equals sign, Python understands that as an attempt
to use a keyword argument; keyword arguments need to be valid Python
identifiers, not arbitrary expressions. That's why it gave you a
SyntaxError.

In this case, you do not want to use a keyword argument, but an
ordinary expression that gets translated into SQL by SQLAlchemy.

Good luck,

Michal

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


signature.asc
Description: Digital signature

Re: [sqlalchemy] Re: reflection taking a very long time?

[Michal Petrucha]

On Tue, Feb 16, 2016 at 04:02:08PM -0500, Alex Hall wrote:
> Great; I was hoping you wouldn't say that. :) I've been through them
> many, many times, trying to get the connection working. I've gone from
> error to error, and thought I had it all working when I finally got
> the create_engine line to run with no problem. Apparently I'm not as
> far along as I thought I was. Back to the drawing board.

Hi Alex,

I just want to reiterate my earlier suggestion – before you try to use
any SQLAlchemy machinery at all, first try to create a connection from
your Python runtime directly, using whichever DBAPI driver you want to
use (most likely you want to create a ibm_db connection object -- do
not import anything related to SQLAlchemy at this point, neither
sqlalchemy, nor ibm_db_sa), make sure you are able to execute SQL
statements using that, and only once you get this to work correctly,
try to figure out how to make it work with SQLAlchemy.

And, of course, you shouldn't try to get SQLAlchemy to work all at
once either. First, create an Engine with a connection string, but do
not try to run any fancy introspection or anything before you make
sure that you can execute raw SQL queries using that engine. After you
get *that* out of the way, you can start trying out more advanced
features of SQLAlchemy.

Baby steps, you know. Divide and conquer. Do not try to solve this
entire huge problem all at once. (And yes, as you are probably aware
by now, successfully connecting to an enterprise database server *is*
a huge problem.) That way you'll avoid false leads like this one.

Good luck!

Michal

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


signature.asc
Description: Digital signature

Re: [sqlalchemy] Pyodbc.Connection has no attribute 'dbms_ver'?

[Michal Petrucha]

On Tue, Feb 16, 2016 at 10:27:40AM -0500, Alex Hall wrote:
> I have pyodbc 3.0.10, ibm_db_sa 0.3.2, and ibm_db 2.0.6. I'm also
> talking to people on the ibm_db list, and they suggested I re-install
> ibm_db and ibm_db_sa according to the official tutorial, which uses
> easy_install. I did so, but there was no change.
> 
> As to pyodbc, I'm fine with not using it. Thus far, from the two lists
> I'm on and more research, I thought I *had to* use it to get things
> working right. Indeed, when I remove "+pyodbc" from my SA connection
> string, the dbms_ver error goes away. However, it's replaced by an
> error that the driver can't find the DSN name I give it, even though I
> can see that DSN right in the IBM ODBC manager on this computer.
> Someone mentioned 64-bit versus 32-bit; I'm using the 64-bit version
> of the ODBC manager, and 64-bit Python. I'm not sure how else to tell
> if the name of the DSN itself is in the correct format.
> 
> The traceback is very long, but here it is in full:
> 
> c:\python27\python.exe DBInterface2.py
> Traceback (most recent call last):
[...]
>   File 
> "c:\python27\lib\site-packages\ibm_db_sa-0.3.2-py2.7.egg\ibm_db_sa\base.p
> y", line 666, in initialize
> self.dbms_ver = connection.connection.dbms_ver
> AttributeError: 'pyodbc.Connection' object has no attribute 'dbms_ver'

This traceback is still the dbms_ver thing, did you mean to post the
other one?

In any case, when you're using a URI in the form of
"ibm_db_sa://user:pass@host/db_name", at least based on the example in
the IBM docs [1], I'm guessing that you shouldn't use the ODBC DSN you
have defined, but rather the server hostname or IP address directly.
In this case it should be using the IBM DBAPI driver directly, without
going through ODBC.

Cheers,

Michal


[1]: 
https://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.swg.im.dbclient.python.doc/doc/t0060891.html

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


signature.asc
Description: Digital signature

Re: [sqlalchemy] Use order_by when using relationship() with secondary-parameter in a many-to-many?

[Michal Petrucha]

On Sun, Feb 14, 2016 at 03:23:59PM +0100, c.bu...@posteo.jp wrote:
> On 2016-02-14 08:38 Michael Bayer  wrote:
> > order_by, you spelled it wrong.
> 
> Ah, I see the misstake. Was a copy thing. Sorry.
> 
> What is this c? I havend found that "topic" in the docs. Where should I
> look for?

I believe what you are looking for is
http://docs.sqlalchemy.org/en/rel_1_0/core/metadata.html#sqlalchemy.schema.Table.c,
i.e. an alias for the “columns” attribute.

Cheers,

Michal

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


signature.asc
Description: Digital signature

Re: [sqlalchemy] Connecting to AS400 with SQLAlchemy fails

[Michal Petrucha]

On Thu, Feb 11, 2016 at 01:16:03PM -0500, Alex Hall wrote:
> I've done more research on this topic. There's a lot out there about
> using MSSQL with SA, but next to nothing about using ibm_db_sa or
> specifying drivers.
> 
> I have pyodbc installed. I downloaded IBM's ODBC zip file, and I've
> put db2odbc64.dll in my project folder, but don't know how to point SA
> or pyodbc to it. I've tried several versions of
> "?driver="db2odbc64.dll"" appended to my connection string, but I keep
> getting an error: "data source not found and no default driver
> specified". It doesn't even time out anymore, it just errors out
> immediately. I've also tried "ibm_db_sa+pyodbc://" to start the
> string, but that fails too.
> 
> This *must* be a simple thing, but I can't work out what to do, and
> Google is failing me. If anyone has any ideas, I'd greatly appreciate
> hearing them. Thanks, and sorry to keep bugging the list about this. I
> just have no other options at the moment and I need to get this
> working soon.

Hi Alex,

Unfortunately, I can't offer you any specific help with IBM DB, but
judging by the number of replies, it seems nobody on this list can, so
I only have some stab-in-the-dark suggestions.

In my experience with enterprise software, *nothing* is ever a simple
thing, not even seemingly trivial operations, such as connecting to a
database.

You can try using either pyodbc, or the ibm_db driver – in both cases,
those are just the Python DBAPI drivers which take in textual SQL
statements, send them to the database in the low-level network
protocol, and present the results as dumb Python objects. SQLAlchemy
is a layer on top of them. That means, the first step would be to get
your Python runtime to open a raw pyodbc, or ibm_db connection to the
server, and be able to execute raw SQL statements there. Only after
you confirm this works you can move on to getting SQLAlchemy to work
with the DBAPI driver.


In my understanding, pyodbc is a wrapper around the library unixodbc.
I'm not sure how it's implemented on Windows – whether it's a port of
unixodbc, or it uses a different ODBC implementation there. Whatever
the case, though, on Linux with unixodbc, when I wanted to connect to
MS SQL, I had to register a low-level driver with the unixodbc
library. I had to edit a system-wide configuration file
(/etc/unixODBC/odbcinst.ini), and create a new driver definition in
there to make unixodbc recognize the FreeTDS driver I'm using as the
low-level protocol implementation.

I have no idea what low-level ODBC driver is required to connect to
IBM DB, I'm afraid you'll have to figure that out on your own. The
official IBM docs at
https://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.apdv.cli.doc/doc/c0007944.html?cp=SSEPGG_9.7.0%2F4-0-4
seem to imply that IBM provides their own low-level ODBC driver which
you'll need to have in place in order to be able to connect to the
server using ODBC.

In any case, I would expect that the ODBC machinery would expect to
have the db2odbc64.dll registered somehow with a symbolic name in some
configuration file, registry, or whatever, and that would be the
string you're expected to pass as the driver name in the ODBC
connection string.

Actually, I think with ODBC, you're expected to define all database
servers in a system-wide configuration file or some such, give each
one of them a nickname (“DSN”), and just use that to connect to the
database.


The other option is to use the ibm_db Python DBAPI driver. I expect
you have already seen the official docs:
https://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.swg.im.dbclient.python.doc/doc/c0054366.html
Have you tried following the set-up steps in that section there? Try
to first get it into a state where you can connect to the database
with ``ibm_db.connect()``, and successfully execute SQL statements
from the Python shell.

Once you get that to work, you can try to install ibm_db_sa, and try
to call
``sqlalchemy.create_engine('ibm_db_sa://user:passw...@host.name.com:5/database')``

If that fails, you still have the option to turn to the official IBM
support channels – you're using an expensive enterprise database,
which should come with a support contract. The ibm_db drivers seem to
be officially supported by IBM, which would mean, if it doesn't work
for you, you should be able to request help from IBM to fix it.

Good luck!

Michal

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


signature.asc
Description: Digital signature

[sqlalchemy] Update containing a JOIN inside an EXISTS condition in its WHERE clause

[Michal Petrucha]

Hi folks,

I'm trying to write a data migration for Alembic, and I can't seem to
figure out the correct chain of calls to express it. The tables are as
follows::

users = sa.table('users',
sa.Column('id', sa.Integer(), primary_key=True),
sa.Column('is_admin', sa.Boolean(), default=False, nullable=False),
)

roles = sa.table('roles',
sa.Column('id', sa.Integer(), primary_key=True),
sa.Column('name', sa.Unicode(length=50), unique=True),
)

user_roles = sa.table('user_roles',
sa.Column('id', sa.Integer(), primary_key=True),
sa.Column('user_id', sa.Integer(), sa.ForeignKey('users.id'), 
nullable=True),
sa.Column('role_id', sa.Integer(), sa.ForeignKey('roles.id'), 
nullable=True),
)

Now, the query I'm trying to express in terms of the SQLAlchemy API is
something like::

UPDATE users
SET is_admin = 1
WHERE EXISTS (
SELECT *
FROM user_roles INNER JOIN roles ON (user_roles.role_id = roles.id)
WHERE roles.name = "administrator"
)

My first stab was this::

users.update().where(
sa.exists(
sa.select([user_roles.c.id]).\
join(roles, user_roles.c.role_id == roles.c.id).\
where(sa.and_(
user_roles.c.user_id == users.c.id,
roles.c.name == 'administrator'
))
)
).values({'is_admin': True})

...but the result of join() does not have a where method.

Am I at least going in the right direction? Am I better off just
dumping a chunk of literal SQL into the migration?

Next up, I'll try to do do the opposite migration, too, i.e. something
like::

INSERT INTO user_roles (user_id, role_id)
SELECT id, ?
FROM user
WHERE is_admin = 1

...where the second column in that SELECT would be a bound parameter.
I'm not even sure where to begin on this one.

Thanks for any nudge in the right direction.

Regards,

Michal

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


signature.asc
Description: Digital signature

Re: [sqlalchemy] Update containing a JOIN inside an EXISTS condition in its WHERE clause

[Michal Petrucha]

Phew, took me some time, but I figured it out eventually. I'll post my
solution just in case someone else finds this thread while struggling
with a similar problem.

On Mon, Feb 08, 2016 at 11:23:54AM +0100, Michal Petrucha wrote:
> Hi folks,
> 
> I'm trying to write a data migration for Alembic, and I can't seem to
> figure out the correct chain of calls to express it. The tables are as
> follows::
> 
> users = sa.table('users',
> sa.Column('id', sa.Integer(), primary_key=True),
> sa.Column('is_admin', sa.Boolean(), default=False, nullable=False),
> )
> 
> roles = sa.table('roles',
> sa.Column('id', sa.Integer(), primary_key=True),
> sa.Column('name', sa.Unicode(length=50), unique=True),
> )
> 
> user_roles = sa.table('user_roles',
> sa.Column('id', sa.Integer(), primary_key=True),
> sa.Column('user_id', sa.Integer(), sa.ForeignKey('users.id'), 
> nullable=True),
> sa.Column('role_id', sa.Integer(), sa.ForeignKey('roles.id'), 
> nullable=True),
> )
> 
> Now, the query I'm trying to express in terms of the SQLAlchemy API is
> something like::
> 
> UPDATE users
> SET is_admin = 1
> WHERE EXISTS (
> SELECT *
> FROM user_roles INNER JOIN roles ON (user_roles.role_id = roles.id)
> WHERE roles.name = "administrator"
> )

This seems to produce the right query::

op.execute(users.update().where(
sa.exists(
sa.select([user_roles.c.id]).\
select_from(
user_roles.join(roles, user_roles.c.role_id == roles.c.id)
).\
where(sa.and_(
user_roles.c.user_id == users.c.id,
roles.c.name == 'administrator',
))
)
).values({'is_admin': True}))


> Next up, I'll try to do do the opposite migration, too, i.e. something
> like::
> 
> INSERT INTO user_roles (user_id, role_id)
> SELECT id, ?
> FROM user
> WHERE is_admin = 1

Here, I came up with the following::

op.execute(
sa.insert(user_roles).\
from_select(
['user_id', 'role_id'],
sa.select([users.c.id, sa.literal(47)],
  whereclause=users.c.is_admin == True),
)
)

Have a nice day,

Michal

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


signature.asc
Description: Digital signature

Re: [sqlalchemy] Best practice for restricting input to columns?

[Michal Petrucha]

On Fri, Feb 05, 2016 at 08:24:46AM -0500, Alex Hall wrote:
> Hi all,
> What's the recommended way to restrict input? For instance, I store a
> phone number as an integer, and I need it to have 7, 10, or 11 digits.
> In its getter, I format it so it looks good as a string, and in its
> setter, I take the string the user inputs, strip only the integers,
> and store those.
> 
> To inforce my length restriction, I have it set up something like this:
> 
> class Customer(base):
>  _phone = Column("phone", Integer)
> 
>  @property
>  def phone(self):
>   #return pretty string
> 
>  @phone.setter
>  def phone(self, value):
>   #intsInPhone is a list of the integers in "value"
>   if len(intsInPhone) not in [7, 10, 11]: #invalid length
>raise ValueError, "Phone numbers must be 7, 10, or 11 digits"
> 
> Is there a way I should do this instead? I had to make properties
> anyway, since user-inputted values are coming from a dialog and will
> always be strings--I had to convert them to the proper types, and
> output formatted strings in some cases. I figured this was a good
> place for a little validation while I was at it.

Hi Alex,

First of all, personally, I would avoid storing phone numbers as
integers. Not sure about other parts of the world, but at least here
in Europe, most local phone numbers (i.e. without the international
routing prefix) start with a zero. Sure, many applications expect
phone numbers without leading zeroes, but I don't think that adds to
the user experience, really.

Therefore I'd store them as strings instead. Which might also make it
easier to parse out things like area prefixes and such, should you
have a need to do that kind of thing.

As for the validation, it depends on your requirements. I usually try
to enforce as many constraints as I can in the database itself (as
long as it does not make things disproportionately complicated).
Specifically in the case of enforcing length constraints, you could go
with CHECK constraints on your database table. You could also use
those to make sure that all characters of a phone number are digits.
Things like making sure that the phone number has a valid area prefix
might also be possible in a CHECK constraint, but for me this would be
past the boundary of disproportionate complexity.

However, if you know that the database will never be written into by
anything else than your Python application using your SQLAlchemy
declarative classes, it might be just as reasonable to perform your
validation in Python code, like in the setter you posted above.

Cheers,

Michal

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


signature.asc
Description: Digital signature

Re: Changing the type from Boolean to Integer on SQLite

[Michal Petrucha]

On Fri, Jan 22, 2016 at 09:59:39AM -0500, Mike Bayer wrote:
> 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.

Yeah, I've been able to trace the value that far, but... I'm afraid I
don't understand at which point the naming convention gets into play,
or what the exact rules are.

For the record, in the naming convention I'm using, I have
"uq": "uq_%(table_name)s_%(column_0_name)s"
i.e. no %(constraint_name)s. Somehow, during a op.create_table, the
naming convention does apply to the auto-generated constraint, but for
some reason, it does not seem to in this case.

Actually, I'm not even certain how the naming convention gets picked
up in a regular migration during a create_table – I don't pass it as
an argument there, and I don't pass the resulting constraint name to
Boolean() either; the only place where the naming convention is set is
the target_metadata, which, in my understanding, was only used for
auto-generation...?

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.


signature.asc
Description: Digital signature

Re: [sqlalchemy] sqlalchemy.exc.InvalidRequestError: Mapper properties (i.e. deferred,column_property(), relationship(), etc.) must be declared as @declared_attr callables on declarative mixin classes

[Michal Petrucha]

Hi Andreas,

On Fri, Jan 15, 2016 at 02:56:39AM -0800, Andreas Jung wrote:
> Hi,
> 
> I currently have the following mix-in class construction (based on the 
> documentation 
> http://docs.sqlalchemy.org/en/latest/orm/extensions/declarative/mixins.html
> )
> 
> Base = declarative_base() 
> 
> class BaseCountry(object): 
>""" Managed through Kotti """ 
> 
>@declared_attr 
>def __tablename__(cls): 
>return cls.__name__.lower() 
> 
>dpis_id = Column(Integer, primary_key=True) 
>title =  Column(Unicode(80)) 
>regions = relationship("Region", 
> primaryjoin="Country.dpis_id==Region.country_id") 

As the error message in the exception says, this attribute needs to be
declared as a declared_attr, like this:

@declared_attr
def regions(cls):
return relationship("Region", 
primaryjoin="Country.dpis_id==Region.country_id") 

This is stated in the following section of the docs:
http://docs.sqlalchemy.org/en/latest/orm/extensions/declarative/mixins.html#mixing-in-relationships

Good luck,

Michal

>"Mapper properties (i.e. deferred," 
> sqlalchemy.exc.InvalidRequestError: Mapper properties (i.e. 
> deferred,column_property(), relationship(), etc.) must be declared as 
> @declared_attr callables on declarative mixin
> classes.

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


signature.asc
Description: Digital signature

Re: [sqlalchemy] sqlalchemy.exc.InvalidRequestError: Mapper properties (i.e. deferred,column_property(), relationship(), etc.) must be declared as @declared_attr callables on declarative mixin classes

[Michal Petrucha]

On Fri, Jan 15, 2016 at 02:26:37PM +0100, Michal Petrucha wrote:
> Hi Andreas,
> 
> On Fri, Jan 15, 2016 at 02:56:39AM -0800, Andreas Jung wrote:
> > Hi,
> > 
> > I currently have the following mix-in class construction (based on the 
> > documentation 
> > http://docs.sqlalchemy.org/en/latest/orm/extensions/declarative/mixins.html
> > )
> > 
> > Base = declarative_base() 
> > 
> > class BaseCountry(object): 
> >""" Managed through Kotti """ 
> > 
> >@declared_attr 
> >def __tablename__(cls): 
> >return cls.__name__.lower() 
> > 
> >dpis_id = Column(Integer, primary_key=True) 
> >title =  Column(Unicode(80)) 
> >regions = relationship("Region", 
> > 
> > primaryjoin="Country.dpis_id==Region.country_id") 
> 
> As the error message in the exception says, this attribute needs to be
> declared as a declared_attr, like this:
> 
> @declared_attr
> def regions(cls):
> return relationship("Region", 
> primaryjoin="Country.dpis_id==Region.country_id") 

...and just after I sent the email I realized this wouldn't work
either, because it hardcodes a fixed string as the originating table
name. Something like the following should work, though (according to
the docs I linked):

@declared_attr
def regions(cls):
return relationship("Region",
primaryjoin=lambda: cls.dpis_id == Region.country_id
)

...or perhaps this one, if you prefer:

@declared_attr
def regions(cls):
return relationship("Region",
primaryjoin="%s.dpis_id==Region.country_id" % cls.__name__
)

> This is stated in the following section of the docs:
> http://docs.sqlalchemy.org/en/latest/orm/extensions/declarative/mixins.html#mixing-in-relationships
> 
> Good luck,
> 
> Michal
> 
> >"Mapper properties (i.e. deferred," 
> > sqlalchemy.exc.InvalidRequestError: Mapper properties (i.e. 
> > deferred,column_property(), relationship(), etc.) must be declared as 
> > @declared_attr callables on declarative mixin
> > classes.
> 
> -- 
> You received this message because you are subscribed to the Google Groups 
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an 
> email to sqlalchemy+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.


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


signature.asc
Description: Digital signature

Re: [sqlalchemy] table_per_relation ORM example and declarative objects

[Michal Petrucha]

On Thu, Dec 10, 2015 at 12:31:33PM -0500, Mike Bayer wrote:
> 
> 
> On 12/10/2015 10:21 AM, Michal Petrucha wrote:
> > Hello alchemists,
> > 
> > There's something that's been bugging me for a while now. I even 
> > asked about it on [stackoverflow][1], but it didn't get much 
> > attention there. It's been suggested to me on IRC that this
> > mailing list might be a better place for this question.
> > 
> > When you take a look at the [table_per_relation ORM example][2], 
> > you can see that the argument passed to `ForeignKey` [here][3] is 
> > the “raw” “tablename.column”. However, since the entire point of 
> > declarative is to use higher-level constructs to abstract away
> > some of the lower-level SQL details, it would make sense to me to
> > use `cls.id` here instead. (I want this here declarative field to
> > point to this other declarative field, instead of saying, I want
> > this declarative field to point to this SQL column of that SQL
> > table.)
> 
> the first thing I don't understand is that the code you refer to is
> locating the correct name dynamically.  So why does it matter?   You
> aren't typing in the name of the table or class yourself.

Why does it matter? Let's say it's a matter of personal taste and
consistency -- I like to use the ForeignKey(OtherClass.id) API rather
than ForeignKey('other_table.id'), and I like to be consistent about
which APIs I use.

Not to mention that in this particular example, typing
ForeignKey(cls.id) would be a lot shorter and require less brainpower
to parse than ForeignKey("%s.id" % cls.__tablename__).

> Second thing is, you can pass the class-bound column to the
> ForeignKey, sure, just as an object, not a string:
> 
> ForeignKey(WhateverClass.id)
> 
> it just requires that you have WhateverClass already available.

Exactly -- and the point of my question was that this apparently
doesn't work in that example; I assume it is because the class is not
fully initialized at that point, and I'm wondering if there's anything
that can be done about it.

Cheers,
Michal

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


signature.asc
Description: Digital signature

[sqlalchemy] table_per_relation ORM example and declarative objects

[Michal Petrucha]

Hello alchemists,

There's something that's been bugging me for a while now. I even asked
about it on [stackoverflow][1], but it didn't get much attention
there. It's been suggested to me on IRC that this mailing list might
be a better place for this question.

When you take a look at the [table_per_relation ORM example][2], you
can see that the argument passed to `ForeignKey` [here][3] is the
“raw” “tablename.column”. However, since the entire point of
declarative is to use higher-level constructs to abstract away some of
the lower-level SQL details, it would make sense to me to use `cls.id`
here instead. (I want this here declarative field to point to this
other declarative field, instead of saying, I want this declarative
field to point to this SQL column of that SQL table.)

The problem is, when you try to do that, you'll get the following
error:

(env-tmp)konk@parahippus /tmp $  python table_per_related.py 
2015-10-20 13:05:44,366 INFO sqlalchemy.engine.base.Engine SELECT 
CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2015-10-20 13:05:44,366 INFO sqlalchemy.engine.base.Engine ()
2015-10-20 13:05:44,367 INFO sqlalchemy.engine.base.Engine SELECT 
CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2015-10-20 13:05:44,367 INFO sqlalchemy.engine.base.Engine ()
2015-10-20 13:05:44,367 INFO sqlalchemy.engine.base.Engine PRAGMA 
table_info("supplier_address")
2015-10-20 13:05:44,367 INFO sqlalchemy.engine.base.Engine ()
2015-10-20 13:05:44,368 INFO sqlalchemy.engine.base.Engine PRAGMA 
table_info("customer")
2015-10-20 13:05:44,368 INFO sqlalchemy.engine.base.Engine ()
2015-10-20 13:05:44,368 INFO sqlalchemy.engine.base.Engine PRAGMA 
table_info("supplier")
2015-10-20 13:05:44,368 INFO sqlalchemy.engine.base.Engine ()
2015-10-20 13:05:44,368 INFO sqlalchemy.engine.base.Engine PRAGMA 
table_info("customer_address")
2015-10-20 13:05:44,368 INFO sqlalchemy.engine.base.Engine ()
Traceback (most recent call last):
  File "table_per_related.py", line 81, in 
Base.metadata.create_all(engine)
  File "/tmp/env-tmp/lib/python3.4/site-packages/sqlalchemy/sql/schema.py", 
line 3431, in create_all
tables=tables)
  File 
"/tmp/env-tmp/lib/python3.4/site-packages/sqlalchemy/engine/base.py", line 
1726, in _run_visitor
conn._run_visitor(visitorcallable, element, **kwargs)
  File 
"/tmp/env-tmp/lib/python3.4/site-packages/sqlalchemy/engine/base.py", line 
1357, in _run_visitor
**kwargs).traverse_single(element)
  File 
"/tmp/env-tmp/lib/python3.4/site-packages/sqlalchemy/sql/visitors.py", line 
120, in traverse_single
return meth(obj, **kw)
  File "/tmp/env-tmp/lib/python3.4/site-packages/sqlalchemy/sql/ddl.py", 
line 713, in visit_metadata
self.traverse_single(table, create_ok=True)
  File 
"/tmp/env-tmp/lib/python3.4/site-packages/sqlalchemy/sql/visitors.py", line 
120, in traverse_single
return meth(obj, **kw)
  File "/tmp/env-tmp/lib/python3.4/site-packages/sqlalchemy/sql/ddl.py", 
line 732, in visit_table
self.connection.execute(CreateTable(table))
  File 
"/tmp/env-tmp/lib/python3.4/site-packages/sqlalchemy/engine/base.py", line 841, 
in execute
return meth(self, multiparams, params)
  File "/tmp/env-tmp/lib/python3.4/site-packages/sqlalchemy/sql/ddl.py", 
line 69, in _execute_on_connection
return connection._execute_ddl(self, multiparams, params)
  File 
"/tmp/env-tmp/lib/python3.4/site-packages/sqlalchemy/engine/base.py", line 889, 
in _execute_ddl
compiled = ddl.compile(dialect=dialect)
  File "", line 1, in 
  File 
"/tmp/env-tmp/lib/python3.4/site-packages/sqlalchemy/sql/elements.py", line 
493, in compile
return self._compiler(dialect, bind=bind, **kw)
  File "/tmp/env-tmp/lib/python3.4/site-packages/sqlalchemy/sql/ddl.py", 
line 27, in _compiler
return dialect.ddl_compiler(dialect, self, **kw)
  File 
"/tmp/env-tmp/lib/python3.4/site-packages/sqlalchemy/sql/compiler.py", line 
199, in __init__
self.string = self.process(self.statement, **compile_kwargs)
  File 
"/tmp/env-tmp/lib/python3.4/site-packages/sqlalchemy/sql/compiler.py", line 
222, in process
return obj._compiler_dispatch(self, **kwargs)
  File 
"/tmp/env-tmp/lib/python3.4/site-packages/sqlalchemy/sql/visitors.py", line 80, 
in _compiler_dispatch
return meth(self, **kw)
  File 
"/tmp/env-tmp/lib/python3.4/site-packages/sqlalchemy/sql/compiler.py", line 
2415, in visit_create_table
const = self.create_table_constraints(table)
  File 
"/tmp/env-tmp/lib/python3.4/site-packages/sqlalchemy/sql/compiler.py", line 
2452, in create_table_constraints
for constraint in constraints
  File 
"/tmp/env-tmp/lib/python3.4/site-packages/sqlalchemy/sql/compiler.py", line 
2450, in 
return ", \n\t".join(p for p in
  File 

Re: Select a subset of declarative classes to migrate

[Michal Petrucha]

On Wed, Nov 18, 2015 at 08:07:09AM -0500, Jon Rosebaugh wrote:
> Sure, but declarative classes have a table.
> 
> MyModel.__table__.info['exclude_from_autogen']=True
> 
> (you'll probably want to write a class decorator that sets this,
> actually.)

Oh, of course. Thanks a lot for the help!

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.


signature.asc
Description: Digital signature

Select a subset of declarative classes to migrate

[Michal Petrucha]

Hi everyone,

In short, is it possible to use migrations for some declarative
classes, but not others?

A bit more background: Certain parts of our project are relatively
stable, and we want to use a migration tool for those. However, there
are also some components that are highly experimental prototypes, and
using migrations for those would just introduce extra overhead, and
simply dropping their tables and create_all-ing them is often easier.

I'm aware that we could use separate declarative bases with separate
metadata, and only tell Alembic about one of them, but that way we'd
lose the ability to create foreign key references from the
experimental classes into those migrated by Alembic. (We don't have
any references in the other direction.)

Has anyone tried doing something like this before? Or is this a wrong
way of approaching this problem, and is there a better “standard”
solution for it?

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


signature.asc
Description: Digital signature