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