Sweet, Thanks!
On Friday, October 25, 2013 3:56:59 PM UTC-4, Michael Bayer wrote:
>
> that answer is a little overkill, you can share the tables already
> reflected in a MetaData with a SQLSoup object like this:
>
> from sqlalchemy import create_engine
>
> # table with no PK
> e = create_engine("sqlite://", echo=True)
> e.execute("""
> create table no_pk(
> id integer,
> data varchar(30)
> )
> """)
>
> # test data
> e.execute("insert into no_pk (id, data) values (?, ?)", [(1, 'd1'), (2,
> 'd2')])
>
>
> from sqlalchemy import Table, MetaData, Column, Integer
>
> m = MetaData(bind=e)
>
> # preload "no_pk" with primary key
> Table('no_pk', m, Column('id', Integer, primary_key=True), autoload=True)
>
> # do this for other no-pk tables...
> # Table("some_other_no_pk_table", ...)
>
> # now link that MetaData to the SQLSoup
> from sqlsoup import SQLSoup
>
> db = SQLSoup(m)
> no_pk = db.no_pk
>
> print no_pk.all()
>
>
>
>
>
> On Oct 25, 2013, at 3:33 PM, Tim Pierson <[email protected] <javascript:>>
> wrote:
>
> Hi everyone,
>
> I'm new to SQLSoup and only have a little sqlalchemy experience and I'm
> wondering if anyone can give me some direction on how to use the subclassed
> sqlsoup object outlined in previous posts. I also have more than a few
> talbes with no primary keys that I need dynamically mapped to objects.
>
> Could anyone point me to a basic query and connection structure that
> utilizes the sub-classed object? Thanks!
>
>
>
> On Monday, March 4, 2013 3:32:44 PM UTC-5, brent wrote:
>>
>>
>>
>> On Monday, 4 March 2013 12:31:51 UTC-7, Michael Bayer wrote:
>>>
>>> you can control the whole thing using map_to():
>>> https://sqlsoup.readthedocs.org/en/latest/api.html#sqlsoup.SQLSoup.map_to,
>>> however that would mean you'd need to build the Table reflection outside
>>> of calling that in any case.
>>>
>>> Another approach might be just to subclass the SQLSoup object and
>>> override the map_to() method, so that you reflect "tablename" ahead of
>>> time, then pass it in as "selectable":
>>>
>>>
>>> class MySoup(SQLSoup):
>>> def map_to(self, attrname, tablename=None, …):
>>> table = Table(tablename, self._metadata, Column('name', String,
>>> primary_key=True), autoload=True, autoload_with=self.bind)
>>> return super(MySoup, self).map_to(attrname, selectable=table, …)
>>>
>>> There should be some more event hooks in SQLAlchemy for intercepting the
>>> primary key. We currently have hooks to intercept columns as they are
>>> reflected, but not the actual PK column collection.
>>>
>>>
>>>
>> ok, that's simpler than what I did.
>>
>>
>>>
>>>
>>>
>>>
>>>
>>> On Mar 4, 2013, at 2:16 PM, brent <[email protected]> wrote:
>>>
>>>
>>>
>>> On Monday, 4 March 2013 11:57:01 UTC-7, Michael Bayer wrote:
>>>>
>>>> Have you looked at SQLSoup ? This library already does exactly what
>>>> you're looking for.
>>>>
>>>> https://sqlsoup.readthedocs.org/en/latest/
>>>>
>>>>
>>> wow! yeah that does do what I'm looking for.
>>> However, I'm mapping to tables that do not have primary keys defined. So
>>> with SQLSoup, I get:
>>>
>>> sqlsoup.SQLSoupError: table 'cpgIslandExt' does not have a primary
>>> key defined
>>>
>>> I got the same in sqlalchemy if I don't explicitly add the name column
>>> to the db. Any way around this?
>>>
>>>
>>>> For the most part, I have this working. However, the example in the
>>>> gist shows that:
>>>>
>>>> len(g.cpgIslandExt.all()) != g.cpgIslandExt.count()
>>>>
>>>>
>>>> What does your SQL echo output say? Looking at the queries (and the
>>>> rows returned, if you use echo='debug') will illustrate what's being sent.
>>>>
>>>> A typical reason why all() returns fewer rows is when the query returns
>>>> duplicate primary key identities - returned objects are uniqued on
>>>> identity
>>>> as they are received. The fact that the "name" column is being hardcoded
>>>> in your base model as the sole "primary key" for all mappings is the
>>>> likely
>>>> cause of this even being possible. The reflection process already knows
>>>> how to yield the primary key constraints defined on each table so you'd
>>>> best rely upon that.
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>> --
>>> 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 [email protected].
>>> To post to this group, send email to [email protected].
>>> Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
>>> For more options, visit https://groups.google.com/groups/opt_out.
>>>
>>>
>>>
>>>
>>>
> --
> 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 [email protected] <javascript:>.
> To post to this group, send email to [email protected]<javascript:>
> .
> Visit this group at http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/groups/opt_out.
>
>
>
--
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 [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
