gmane.comp.python.sqlalchemy.user

Re: Foreign key reflection error?

Michael Bayer — 2012-05-22T01:37:20

On May 21, 2012, at 8:05 PM, Demitri Muna wrote:


should be able to execute with a cursor:

cursor = dbapi_con.cursor()
cursor.execute('SET search_path TO "$user"')


OK table not found would be affected based on the search path and whether or not you have "schema" on your table.

Re: Foreign key reflection error?

Demitri Muna — 2012-05-22T00:05:38

Hi Michael,

As a very quick test to see if I could make things work, I created a new postgres user and set that user's search_path to just '"$user"' (since it can't be empty, but as there are no tables with the user's name, that's effectively what it is). My toy example worked.

On May 18, 2012, at 5:10 PM, Michael Bayer wrote:


I'm trying to avoid creating a separate user just for scripts that use SA and it sounds like your suggestion above should address this, but I'm a little stuck on how that will work. This is what I have:

def my_on_connect(dbapi_con, connection_record):
    print "New DBAPI connection:", dbapi_con
    # ??? execute query 'SET search_path TO "$user"'

I call

listen(Pool, 'connect', my_on_connect)

very early in my code, which seems to be properly called. Can you help me fill in the missing line? If I understand things correctly, then this hook will set the search path for the lifetime of any connection made to the database, regardless of what the user has set otherwise.

I also trie

Re: Re: PostgreSQL hstore custom type?

Adam Tauno Williams — 2012-05-21T15:25:29

On Tue, 2012-05-15 at 00:24 -0700, Kyle Schaffrick wrote: 


FYI, for anyone looking for hstore.py in the future I've put a copy in
my SourceForge Hg repository (to have a not-in-list-archive copy).

<https://sourceforge.net/u/whitemice/whitemicehg/ci/b6fb4d699f513d53ac058dcbb932d47cad0ddafe/tree/Scraps/SQLAlchemy/hstore.py>

I'll probably hack on it a bit.

Re: pyodbc+mssql and implicit_returning: False: Is this a bug? Any work arounds?

Domas — 2012-05-21T09:51:06

I had this problem. I solved it using this:
http://docs.sqlalchemy.org/en/latest/dialects/mssql.html#triggers

On 3 Bal, 00:27, Derek Litz <litzoma...< at >gmail.com> wrote:

azure & sqlalchemy

Damian Dimmich — 2012-05-21T13:09:12

Hi,

Has anyone used sqlalchemy and azure at any point?  I may need to work 
with azure shortly...

Thanks!

Damian

Re: How should I do inheritance using DeclarativeReflectedBase?

Ignas Mikalajunas — 2012-05-20T16:29:53

Tried, everything works. Awesome!

Ignas

On Sun, May 20, 2012 at 2:34 AM, Michael Bayer <mike_mp< at >zzzcomputing.com> wrote:

Re: How should I do inheritance using DeclarativeReflectedBase?

Michael Bayer — 2012-05-19T23:34:28

thanks.  try r896d404af747.


On May 19, 2012, at 12:09 PM, Ignas Mikalajunas wrote:

Re: How should I do inheritance using DeclarativeReflectedBase?

Ignas Mikalajunas — 2012-05-19T16:09:24

CREATE TABLE users (
    id bigserial NOT NULL,
    primary key (id)
);

CREATE TABLE wall_posts (
    id bigserial not null,
    user_id integer,
    foreign key (user_id) references users on delete cascade,
    primary key (id)
);;

CREATE TABLE wall_post_stars (
    wall_post_id integer NOT NULL,
    user_id integer NOT NULL,
    foreign key (wall_post_id) references wall_posts on delete cascade,
    foreign key (user_id) references users on delete cascade,
    primary key (user_id, wall_post_id)
);


On Sat, May 19, 2012 at 3:41 PM, Michael Bayer <mike_mp< at >zzzcomputing.com> wrote:

Re: How should I do inheritance using DeclarativeReflectedBase?

Michael Bayer — 2012-05-19T12:41:04

What's the fk setup there, do you have an FK column that is also a PK?

Sent from my iPhone

On May 19, 2012, at 7:24 AM, Ignas Mikalajunas <ignas.mikalajunas< at >gmail.com> wrote:

Re: How should I do inheritance using DeclarativeReflectedBase?

Ignas Mikalajunas — 2012-05-19T11:24:01

Hi,


sorry forgot the error message for the traceback:

NoReferencedColumnError: Could not create ForeignKey 'wall_posts.id'
on table 'wall_post_stars': table 'wall_posts' has no column named
'id'

Ignas

Re: How should I do inheritance using DeclarativeReflectedBase?

Ignas Mikalajunas — 2012-05-19T11:22:04

Hi, thanks for all your work :)


Oh, Indeed that works.


Thanks for solving this.


I must have been unclear in my ramblings, sorry. I know I can silence
the warnings, but in reflected schema arrangement
I could silence warnings for just one one table:

        warnings.simplefilter('ignore', SAWarning)
        Table("items", metadata, autoload=True, autoload_with=engine,
extend_existing=True)
        warnings.simplefilter('default', SAWarning)

If I want to do the same for the *normal* declarative I just surround
the class definition with same code.
With Deferred declarative I can must turn off all warnings by doing:

        warnings.simplefilter('ignore', SAWarning)
        Base.prepare()
        warnings.simplefilter('default', SAWarning)

Which hides warnings for all the tables, instead of just the 1 table I
know has something fishy. Though it is just a minor issue.
The perfect solution for me would be being able to tell sqlalchemy to
ignore some columns or indexes on the class explicitly. Like:

clas

Re: Foreign key reflection error?

Michael Bayer — 2012-05-18T21:10:12

On May 18, 2012, at 3:38 PM, Demitri Muna wrote:


If you've created all your Table objects with an explicit "schema" definition, you shouldn't need anything in your search path - technically not even "public".  If the issue is that your database connection defaults to a certain search path, change the search path just within your SQLAlchemy application to not include defaults.   A "connect" event listener can achieve this.


It's Postgresql's pg_catalog.pg_get_constraintdef() function.



Consider that an application which *does* want to use a custom search path, and does *not* specify "schema" inside of each Table, would want SQLAlchemy to *disregard* the schema information from the foreign key.   The behavior of PG here, omitting the schema information if that schema is in fact already in the search path, is quite natural.   The choice here is simple - either your tablenames are unique across schemas, in which case you can use a composite search path to place them all into one namespace and you don't use

Re: Error "object has no attribute '_sa_instance_state'" - one to many (with fixtures)

Michael Bayer — 2012-05-18T21:03:11

On May 18, 2012, at 12:08 PM, Vinz wrote:


OK just for future reference, the "bla bla" part here is actually quite important in revealing what the issue is.   In this case it is likely that you're appending a list  somewhere, where a mapped object is expected, such as:

myobject.some_attribute = []

or 

myobject.some_collection.append([])

but there might be configurational issues with the testing package you're using that's leading to this (so perhaps check with them).


yeah unfortunate python issue here, this is SQLAlchemy attempting to procure an internal tracking object present on all mapped objects called "InstanceState".   We use an "attribute getter" to get this, which is because it's very fast compared to a plain function, but the downside is in a lot of areas of the code we aren't transforming this attribute error into a nicely descriptive message (something which can be improved).    The error means, "'list' instance is not a mapped object".


this isn't a SQLAlchemy question.  Contact the auth

Re: Foreign key reflection error?

Demitri Muna — 2012-05-18T19:38:12

Hi Michael,

On May 18, 2012, at 9:19 AM, Michael Bayer wrote:


Thanks for the explanation. This unfortunately makes things difficult for me as I have at least two schema that have nearly the same tables, the schema model providing a clean separation of the data. Removing the schema from search_path introduces ambiguities.

Where would be the best place to try to find a solution? Is it psycopg2 that's not returning the schema information? The information is of course in the database, so it sounds like maybe the SQL query to get the foreign keys could be updated to explicitly include the schema? I imagine it's not at the database level since, again, the information is there.

Also, given the Python classes in my example code, how do I print out the foreign keys from a table object?

Cheers,
Demitri

Error "object has no attribute '_sa_instance_state'" - one to many (with fixtures)

Vinz — 2012-05-18T16:08:03

Hi all,
I'm writing some tests for my SqlAlchemy DB using the fixtures module,
and I'm currently trying to populate a one to many relationship.
http://farmdev.com/projects/fixture/using-dataset.html#referencing-foreign-dataset-classes

We have a relationship between Author and Address : (very much like
the SA doc)
------------ model.py ---------------
class Address(Base):
    author_id = Column(Integer, ForeignKey('author.id'))
    author = relationship("Author")
------------------------------------

Then I defined some data (fixture specific) :
------------ modelData.py --------------
class AuthorDate(DataSet):
    class foo:
        name = "foo"

class AddressData(DataSet):
    class my_address:
        name = "my address"
        author_id = AuthorData.foo.ref('id')
        author = [AuthorData.foo,] # this is handy with the fixtures
-------------

Then we have a file with the tests :
-------- testAuthor.py
import model
from modelData import *
# call to __init__.py and Base.metadata.bind = engine, session

Re: How should I do inheritance using DeclarativeReflectedBase?

Michael Bayer — 2012-05-18T14:57:17

On May 18, 2012, at 9:41 AM, Michael Bayer wrote:


the latest tip ensures that __mapper_args__ is not invoked until after the table is reflected and available via __table__, so you can say:

        class User(Base):
            __tablename__ = 'users'

            < at >declared_attr
            def __mapper_args__(cls):
                return {
                    "order_by":cls.__table__.c.name
                }

Re: How should I do inheritance using DeclarativeReflectedBase?

Michael Bayer — 2012-05-18T13:41:10

On May 17, 2012, at 6:58 PM, Ignas Mikalajunas wrote:


set this using a string - polymorphic_on = 'type'.



order_by?   OK, I'm not a huge fan of mapper order_by but we can come up with something on that....


you can silence warnings using the warnings filter in Python.    There's lots of things SQLA doesn't know how to reflect in PG, particularly functional indexes, so the options are it ignores them silently, raises an error, or warns+ignores.



Dependencies between mapper tables come up at mapper creation time only with joined inheritance - these mappers should already be created in the same order.   Dependencies between mappers due to relationship() don't attempt to resolve at this stage, so that wouldn't be the issue either.    A dependency due to column_property() with some related table, maybe, but that requires explicit access to the tables anyway.

A stack trace here would be extremely helpful.

Re: Foreign key reflection error?

Michael Bayer — 2012-05-18T13:19:06

On May 17, 2012, at 7:05 PM, Demitri Muna wrote:


very nice !   Though Im sorry I put you through this as I just remembered a key caveat with Postgresql, and in fact it's even documented here:

http://docs.sqlalchemy.org/en/rel_0_7/dialects/postgresql.html#remote-cross-schema-table-introspection


When using cross-schema reflection, you have the option of either using only "public" in your schema search path, or *not* schema-qualifying the tables.  This is because when you have the alternate schemas in your search path, Postgresql does not tell SQLAlchemy about the schema name when it returns foreign key information - it returns just the tablename, columnnname, but not the schema.  Therefore when you have your Table objects which schema names in them, SQLAlchemy can't match them up and instead makes another Table that you aren't seeing as the target of each cross-schema foreign key, which has no schema name.

That's likely what's happening here, evidenced by the fact that your test case works for me (I lea

Re: Foreign key reflection error?

Demitri Muna — 2012-05-17T23:05:50

On May 17, 2012, at 6:33 PM, Michael Bayer wrote:


I've refactored the example to the simplest case. We can see if the fix addresses many-to-many relationships as well.

Demitri

Re: How should I do inheritance using DeclarativeReflectedBase?

Ignas Mikalajunas — 2012-05-17T22:58:53

Thanks, everything seems to be working very well.

Small issues so far:

I can't set neither polymorphic_on nor sort_on columns in mapper
properties without defining columns explicitly and that overrides the
reflected columns.

There is no way to ignore just SAWarnings just on some tables now,
only either turn them off for all tables (around prepare call) or
reflect the tables explicitly before calling prepare. (otherwise I
keep getting the warnings for indexes/columns sqlalchemy does not
understand)

the third one required a patch to the prepare method:

      < at >classmethod
      def prepare(cls, engine):
          """Reflect all :class:`.Table` objects for all current
          :class:`.DeferredReflection` subclasses"""
          to_map = [m for m in _MapperConfig.configs.values()
                      if issubclass(m.cls, cls)]
          for thingy in to_map:
              cls.__prepare__(thingy.args, engine)
+
+         for thingy in to_map:
              thingy.map()


so that all the tables would get d

Re: Foreign key reflection error?

Michael Bayer — 2012-05-17T22:33:00

On May 17, 2012, at 6:18 PM, Demitri Muna wrote:


OK this is way too much verbiage.  If the error is as you describe, a bug involving reflection of two tables across two schemas, I'd need a test which illustrates this alone, no ORM or full database dumps, with a simple failure to identify a foreign key constraint between them.   I've attached a sample of exactly what I'd like to see.

If the issue is not as simple as this, then we will have to dig into multiple tables, mappings, etc. but you should be able to reproduce the specific case here with just two tables.