Re: [sqlalchemy] How Can I Build a ForeignKey to a Table Which Has Multiple Primary Keys?

[Randall Degges]

Simon,

Thanks man! This works perfectly, can't believe I didn't see that.

This was actually a really frustrating experience, you guys have been
extremely helpful. Thank you all so much!

Best,

-Randall


On Thu, Mar 7, 2013 at 5:15 PM, Simon King  wrote:

> According to the pastie log, your table is called "exchanges", not
> "exchange", so the target columns should be called exchanges.exchange and
> exchanges.area_code_pk.
>
> Simon
>
> On 8 Mar 2013, at 00:27, Randall Degges  wrote:
>
> > Hi Simon,
> >
> > Ok cool. So, I updated that, but now I'm getting the following error:
> >
> > sqlalchemy.exc.NoReferencedTableError: Foreign key associated with
> column 'phonenumbers.exchange_exchange' could not find table 'exchange'
> with which to generate a foreign key to target column 'exchange'
> >
> > It looks like SQLa is trying to create the tables out of order (I'm
> guessing?). Is there a way to force this to execute in a specific order?
> >
> > -Randall
> >
> >
> > On Thu, Mar 7, 2013 at 4:06 PM, Simon King 
> wrote:
> > You have to put your ForeignKeyConstraint in the __table_args__ for the
> PhoneNumber class - see
> http://docs.sqlalchemy.org/en/rel_0_8/orm/extensions/declarative.html#table-configurationfor
>  details. Something like:
> >
> > class PhoneNumber(db.Model):
> > __tablename__ = 'phonenumbers'
> >
> > phone_number = db.Column(db.Numeric(precision=4, scale=0),
> >  primary_key=True)
> > exchange_exchange = db.Column(db.Integer, primary_key=True)
> > exchange_area_code_pk = db.Column(db.Integer, primary_key=True)
> >
> > __table_args__ = (db.ForeignKeyConstraint(
> >  ['exchange_exchange', 'exchange_area_code_pk'],
> >  ['exchange.exchange', 'exchange.area_code_pk'],
> >  ),
> >   )
> >
> > Simon
> >
> >
> > On 7 Mar 2013, at 23:49, Randall Degges  wrote:
> >
> > > Hi Lloyd,
> > >
> > > Thank you! I believe this is what I was trying to figure out, although
> I am having further issues now. Here's a recent pastie with my improved
> models, along with the errors I'm now having, http://pastie.org/6417080
> > >
> > > What I've done (as you can probably see) is I've used the
> ForeignKeyConstraint on my PhoneNumber table, to ensure that I'm able to
> link to my exchanges table properly. Unfortunately, this looks like it is
> not being picked up by SQLAlchemy, since it's complaining that I have no
> Foreign Keys for that table.
> > >
> > > Also, just for reference, I've totally wiped / recreated my DBs to
> test this, so I'm sure it isn't a result of migrations or anything like
> that.
> > >
> > > If I run \d phonenumbers inside of psql, I get the following output (
> http://pastie.org/6417088), thought that might also be of help.
> > >
> > > Thank you all for your help so far, I can't wait to get this working!
> > >
> > > Best,
> > >
> > > -Randall
> > >
> > >
> > > On Thu, Mar 7, 2013 at 5:41 AM, Lloyd Kvam  wrote:
> > > While primary_key is specified twice, once for each column, there is
> only ONE primary key which is a composite.
> > >
> > > You need to use ForeignKeyConstraint at the Table level to specify a
> composite foreign key.
> > >
> > > You need to provide two lists, the local table columns, and the
> corresponding foreign table columns.
> > > Now you know what to search for if  you need more information.
> > >
> > > from the schema definition language docs
> > > It’s important to note that the ForeignKeyConstraint is the only way
> to define a composite foreign key. While we could also have placed
> individual ForeignKey objects on both theinvoice_item.invoice_id and
> invoice_item.ref_num columns, SQLAlchemy would not be aware that these two
> values should be paired together - it would be two individual foreign key
> constraints instead of a single composite foreign key referencing two
> columns.
> > >
> > >
> > > On Wednesday, March 6, 2013 9:00:56 PM UTC-5, Randall Degges wrote:
> > > Hi Mike,
> > >
> > > Sorry about that, I actually had a typo there. I've got a correct code
> sample available here
> http://stackoverflow.com/questions/15260842/how-can-i-build-a-foreignkey-to-a-table-which-has-multiple-primary-keys(just
>  posted it).
> > >
> > > Thank you,
> > >
> > > -Randall
> > >
> > >
> > > On Wed, Mar 6, 2013 at 5:54 PM, Michael Bayer 
> wrote:
> > > a database table can only have one primary key (hence "primary"), but
> that key can contain more than one column (a "composite" primary key).
> > >
> > > the model you have here is a little unclear, did you mean for the
> primary key of Exchange to be "exchange" , and the primary key of
> PhoneNumber to be the composite of "exchange" and "phone number" ?   that
> would be my guess as to what you're looking for.
> > >
> > >
> > >
> > > On Mar 6, 2013, at 6:05 PM, Randall Degges  wrote:
> > >
> > >> Hi all,
> > >>
> > >> I'm having a lot of trouble figuring out how to properly build my
> For

Re: [sqlalchemy] How Can I Build a ForeignKey to a Table Which Has Multiple Primary Keys?

[Simon King]

According to the pastie log, your table is called "exchanges", not "exchange", 
so the target columns should be called exchanges.exchange and 
exchanges.area_code_pk.

Simon

On 8 Mar 2013, at 00:27, Randall Degges  wrote:

> Hi Simon,
> 
> Ok cool. So, I updated that, but now I'm getting the following error:
> 
> sqlalchemy.exc.NoReferencedTableError: Foreign key associated with column 
> 'phonenumbers.exchange_exchange' could not find table 'exchange' with which 
> to generate a foreign key to target column 'exchange'
> 
> It looks like SQLa is trying to create the tables out of order (I'm 
> guessing?). Is there a way to force this to execute in a specific order?
> 
> -Randall
> 
> 
> On Thu, Mar 7, 2013 at 4:06 PM, Simon King  wrote:
> You have to put your ForeignKeyConstraint in the __table_args__ for the 
> PhoneNumber class - see 
> http://docs.sqlalchemy.org/en/rel_0_8/orm/extensions/declarative.html#table-configuration
>  for details. Something like:
> 
> class PhoneNumber(db.Model):
> __tablename__ = 'phonenumbers'
> 
> phone_number = db.Column(db.Numeric(precision=4, scale=0),
>  primary_key=True)
> exchange_exchange = db.Column(db.Integer, primary_key=True)
> exchange_area_code_pk = db.Column(db.Integer, primary_key=True)
> 
> __table_args__ = (db.ForeignKeyConstraint(
>  ['exchange_exchange', 'exchange_area_code_pk'],
>  ['exchange.exchange', 'exchange.area_code_pk'],
>  ),
>   )
> 
> Simon
> 
> 
> On 7 Mar 2013, at 23:49, Randall Degges  wrote:
> 
> > Hi Lloyd,
> >
> > Thank you! I believe this is what I was trying to figure out, although I am 
> > having further issues now. Here's a recent pastie with my improved models, 
> > along with the errors I'm now having, http://pastie.org/6417080
> >
> > What I've done (as you can probably see) is I've used the 
> > ForeignKeyConstraint on my PhoneNumber table, to ensure that I'm able to 
> > link to my exchanges table properly. Unfortunately, this looks like it is 
> > not being picked up by SQLAlchemy, since it's complaining that I have no 
> > Foreign Keys for that table.
> >
> > Also, just for reference, I've totally wiped / recreated my DBs to test 
> > this, so I'm sure it isn't a result of migrations or anything like that.
> >
> > If I run \d phonenumbers inside of psql, I get the following output 
> > (http://pastie.org/6417088), thought that might also be of help.
> >
> > Thank you all for your help so far, I can't wait to get this working!
> >
> > Best,
> >
> > -Randall
> >
> >
> > On Thu, Mar 7, 2013 at 5:41 AM, Lloyd Kvam  wrote:
> > While primary_key is specified twice, once for each column, there is only 
> > ONE primary key which is a composite.
> >
> > You need to use ForeignKeyConstraint at the Table level to specify a 
> > composite foreign key.
> >
> > You need to provide two lists, the local table columns, and the 
> > corresponding foreign table columns.
> > Now you know what to search for if  you need more information.
> >
> > from the schema definition language docs
> > It’s important to note that the ForeignKeyConstraint is the only way to 
> > define a composite foreign key. While we could also have placed individual 
> > ForeignKey objects on both theinvoice_item.invoice_id and 
> > invoice_item.ref_num columns, SQLAlchemy would not be aware that these two 
> > values should be paired together - it would be two individual foreign key 
> > constraints instead of a single composite foreign key referencing two 
> > columns.
> >
> >
> > On Wednesday, March 6, 2013 9:00:56 PM UTC-5, Randall Degges wrote:
> > Hi Mike,
> >
> > Sorry about that, I actually had a typo there. I've got a correct code 
> > sample available here 
> > http://stackoverflow.com/questions/15260842/how-can-i-build-a-foreignkey-to-a-table-which-has-multiple-primary-keys
> >  (just posted it).
> >
> > Thank you,
> >
> > -Randall
> >
> >
> > On Wed, Mar 6, 2013 at 5:54 PM, Michael Bayer  
> > wrote:
> > a database table can only have one primary key (hence "primary"), but that 
> > key can contain more than one column (a "composite" primary key).
> >
> > the model you have here is a little unclear, did you mean for the primary 
> > key of Exchange to be "exchange" , and the primary key of PhoneNumber to be 
> > the composite of "exchange" and "phone number" ?   that would be my guess 
> > as to what you're looking for.
> >
> >
> >
> > On Mar 6, 2013, at 6:05 PM, Randall Degges  wrote:
> >
> >> Hi all,
> >>
> >> I'm having a lot of trouble figuring out how to properly build my 
> >> ForeignKey column for a table I'm defining. I've outlined my models here: 
> >> http://pastie.org/6407419# (and put a comment next to the problematic line 
> >> in my PhoneNumber model).
> >>
> >> Here's what's happening:
> >>
> >> My Exchange table has two primary keys. This is required for my use case.
> >>
> >> The PhoneNumber table I'm trying

Re: [sqlalchemy] Updating a table using sqlalchemy.sql.expression update

[Mauricio de Abreu Antunes]

Thanks.
I'm gonna read the communication to watch the difference between Core and
ORM.
I changed that process_files description to Process_Files(Base): #mapping
fields

It worked!

2013/3/7 Simon King 

> SQLAlchemy is broadly separated into 2 parts, "core" and "ORM" (which you
> can see as the left and right-hand sides on the  front page of the
> documentation, http://docs.sqlalchemy.org/en/rel_0_8/)
>
> Table objects like the one you have below are part of the Core API.
> Columns of Table objects are accessible via the "c" property:
>
>   process_files.c.process_id
>
> The ORM is built on top of the core API. When you do something like:
>
>   class Files(Base):
>   __tablename__ = 'files'
>   id = sa.Column('id', sa.Integer, primary_key=True)
>
> …you are creating a "mapped class". You can access the columns of these
> directly like "Files.id". If you ever need access to the underlying Table
> object, you can get it from Files.__table__.
>
> When using things like session.query(), you can often use tables or mapped
> classes interchangeably.
>
> Hope that helps,
>
> Simon
>
> On 7 Mar 2013, at 20:57, Mauricio de Abreu Antunes <
> mauricio.abr...@gmail.com> wrote:
>
> > Simon,
> >
> > Here is the table (not sure if the code to create a relationship table
> is correct):
> >
> >
> > # Many to Many - Process x Files
> >
> >
> >
> > process_files = Table("process_files", Base.metadata,
> >
> >
> >
> > Column("process_id", Integer, ForeignKey("process.id")),
> >
> >
> >
> > Column("files_id", Integer, ForeignKey("files.id"))
> >
> >
> >
> > )
> >
> >
> >
> >
> > The error:
> >
> > AttributeError: 'Table' object has no attribute 'process_id'
> >
> >
> > Why can not I access process_id?
> >
> > 2013/3/7 Simon King 
> > The ORM tutorial covers querying with joins:
> >
> >
> http://docs.sqlalchemy.org/en/rel_0_8/orm/tutorial.html#querying-with-joins
> >
> > In this case, you probably want something like this (if ProcessFiles is
> a class mapped to the process_files table):
> >
> >   result =
> session.query(Files).join(ProcessFiles).filter(ProcessFiles.process_id==1)
> >
> > Hope that helps,
> >
> > Simon
> >
> > On 7 Mar 2013, at 20:35, Mauricio de Abreu Antunes <
> mauricio.abr...@gmail.com> wrote:
> >
> > > I wanna perform a query on process_files and hereafter a update/join
> like this:
> > >
> > > SELECT files.id AS files_id, files.name AS files_name,
> files.directory AS files_directory, files.active AS files_active,
> files.connection_id AS files_connection_id
> > > FROM files JOIN process_files ON files.id = process_files.files_id
> > >
> > > Actually the piece os select above is a result from:
> > >
> > > result = session.query(Files).join(process_files)
> > >
> > > It is ok, but i wanna filter it with process_files.process_id = 1 for
> example.
> > >
> > > Reading the docs i could not find the proper way to do this:
> > >
> http://docs.sqlalchemy.org/en/rel_0_7/orm/query.html#sqlalchemy.orm.query.Query.join
> > >
> > > Am i reading the wrong part of the docs?
> > >
> > > 2013/3/7 Mauricio de Abreu Antunes 
> > > These tips are veeery good!
> > > I sometimes get lost about the best way to use the best ORM library in
> the world.
> > >
> > >
> > > 2013/3/7 Simon King 
> > > This is an unusual way to update an object that you've already
> retrieved:
> > >
> > > result = session.query(Executions). \
> > > filter_by(id=execution_id).first()
> > > if result.end_date is None:
> > > e =
> > > update(Executions).where(Executions.id==bindparam("execution_id")). \
> > > values(end_date=bindparam("now"))
> > > self.connection.execute(e, execution_id=execution_id,
> > > now=datetime.datetime.now())
> > >
> > > It would be more natural to write it like this:
> > >
> > >   if result.end_date is None:
> > >   result.end_date = datetime.datetime.now()
> > >   session.flush()
> > >
> > > Also, if "id" is the primary key on your Executions class, you can
> > > write the first line as:
> > >
> > >   result = session.query(Executions).get(execution_id)
> > >
> > >
> > > On Thu, Mar 7, 2013 at 1:58 AM, Michael Bayer <
> mike...@zzzcomputing.com> wrote:
> > > > in the 0.7 series, you can't pass an ORM mapped class as the subject
> of the
> > > > core update() construct:
> > > >
> > > > e =
> update(Executions).where(Executions.id==bindparam("execution_id")). \
> > > > values(end_date=bindparam("now"))
> > > >
> > > > that statement will work as is if you just refer to the Table:
> > > >
> > > > e =
> > > >
> update(Executions.__table__).where(Executions.id==bindparam("execution_id")).
> > > > \
> > > > values(end_date=bindparam("now"))
> > > >
> > > > also note that the indirection between bindparam("foo") and
> > > > connection.execute(stmt, foo="some value") is not needed; you can
> embed
> > > > literal values directly in the statement, and the Core will convert
> them to
> > > > bound parameters (just use echo=True to see it in 

Re: [sqlalchemy] How Can I Build a ForeignKey to a Table Which Has Multiple Primary Keys?

[Randall Degges]

Hi Simon,

Ok cool. So, I updated that, but now I'm getting the following error:

sqlalchemy.exc.NoReferencedTableError: Foreign key associated with column
'phonenumbers.exchange_exchange' could not find table 'exchange' with which
to generate a foreign key to target column 'exchange'

It looks like SQLa is trying to create the tables out of order (I'm
guessing?). Is there a way to force this to execute in a specific order?

-Randall


On Thu, Mar 7, 2013 at 4:06 PM, Simon King  wrote:

> You have to put your ForeignKeyConstraint in the __table_args__ for the
> PhoneNumber class - see
> http://docs.sqlalchemy.org/en/rel_0_8/orm/extensions/declarative.html#table-configurationfor
>  details. Something like:
>
> class PhoneNumber(db.Model):
> __tablename__ = 'phonenumbers'
>
> phone_number = db.Column(db.Numeric(precision=4, scale=0),
>  primary_key=True)
> exchange_exchange = db.Column(db.Integer, primary_key=True)
> exchange_area_code_pk = db.Column(db.Integer, primary_key=True)
>
> __table_args__ = (db.ForeignKeyConstraint(
>  ['exchange_exchange', 'exchange_area_code_pk'],
>  ['exchange.exchange', 'exchange.area_code_pk'],
>  ),
>   )
>
> Simon
>
>
> On 7 Mar 2013, at 23:49, Randall Degges  wrote:
>
> > Hi Lloyd,
> >
> > Thank you! I believe this is what I was trying to figure out, although I
> am having further issues now. Here's a recent pastie with my improved
> models, along with the errors I'm now having, http://pastie.org/6417080
> >
> > What I've done (as you can probably see) is I've used the
> ForeignKeyConstraint on my PhoneNumber table, to ensure that I'm able to
> link to my exchanges table properly. Unfortunately, this looks like it is
> not being picked up by SQLAlchemy, since it's complaining that I have no
> Foreign Keys for that table.
> >
> > Also, just for reference, I've totally wiped / recreated my DBs to test
> this, so I'm sure it isn't a result of migrations or anything like that.
> >
> > If I run \d phonenumbers inside of psql, I get the following output (
> http://pastie.org/6417088), thought that might also be of help.
> >
> > Thank you all for your help so far, I can't wait to get this working!
> >
> > Best,
> >
> > -Randall
> >
> >
> > On Thu, Mar 7, 2013 at 5:41 AM, Lloyd Kvam  wrote:
> > While primary_key is specified twice, once for each column, there is
> only ONE primary key which is a composite.
> >
> > You need to use ForeignKeyConstraint at the Table level to specify a
> composite foreign key.
> >
> > You need to provide two lists, the local table columns, and the
> corresponding foreign table columns.
> > Now you know what to search for if  you need more information.
> >
> > from the schema definition language docs
> > It’s important to note that the ForeignKeyConstraint is the only way to
> define a composite foreign key. While we could also have placed individual
> ForeignKey objects on both theinvoice_item.invoice_id and
> invoice_item.ref_num columns, SQLAlchemy would not be aware that these two
> values should be paired together - it would be two individual foreign key
> constraints instead of a single composite foreign key referencing two
> columns.
> >
> >
> > On Wednesday, March 6, 2013 9:00:56 PM UTC-5, Randall Degges wrote:
> > Hi Mike,
> >
> > Sorry about that, I actually had a typo there. I've got a correct code
> sample available here
> http://stackoverflow.com/questions/15260842/how-can-i-build-a-foreignkey-to-a-table-which-has-multiple-primary-keys(just
>  posted it).
> >
> > Thank you,
> >
> > -Randall
> >
> >
> > On Wed, Mar 6, 2013 at 5:54 PM, Michael Bayer 
> wrote:
> > a database table can only have one primary key (hence "primary"), but
> that key can contain more than one column (a "composite" primary key).
> >
> > the model you have here is a little unclear, did you mean for the
> primary key of Exchange to be "exchange" , and the primary key of
> PhoneNumber to be the composite of "exchange" and "phone number" ?   that
> would be my guess as to what you're looking for.
> >
> >
> >
> > On Mar 6, 2013, at 6:05 PM, Randall Degges  wrote:
> >
> >> Hi all,
> >>
> >> I'm having a lot of trouble figuring out how to properly build my
> ForeignKey column for a table I'm defining. I've outlined my models here:
> http://pastie.org/6407419# (and put a comment next to the problematic
> line in my PhoneNumber model).
> >>
> >> Here's what's happening:
> >>
> >> My Exchange table has two primary keys. This is required for my use
> case.
> >>
> >> The PhoneNumber table I'm trying to define needs a ForeignKey to the
> Exchange table, but since the Exchange table has two primary keys, I can't
> figure out how to make the relationship work.
> >>
> >> Any guidance would be appreciated. Thank you.
> >>
> >> --
> >> You received this message because you are subscribed to the Google
> Groups "sqlalchemy" group.
> >> To unsubscrib

Re: [sqlalchemy] How Can I Build a ForeignKey to a Table Which Has Multiple Primary Keys?

[Simon King]

You have to put your ForeignKeyConstraint in the __table_args__ for the 
PhoneNumber class - see 
http://docs.sqlalchemy.org/en/rel_0_8/orm/extensions/declarative.html#table-configuration
 for details. Something like:

class PhoneNumber(db.Model):
__tablename__ = 'phonenumbers'

phone_number = db.Column(db.Numeric(precision=4, scale=0),
 primary_key=True)
exchange_exchange = db.Column(db.Integer, primary_key=True)
exchange_area_code_pk = db.Column(db.Integer, primary_key=True)

__table_args__ = (db.ForeignKeyConstraint(
 ['exchange_exchange', 'exchange_area_code_pk'],
 ['exchange.exchange', 'exchange.area_code_pk'],
 ),
  )

Simon


On 7 Mar 2013, at 23:49, Randall Degges  wrote:

> Hi Lloyd,
> 
> Thank you! I believe this is what I was trying to figure out, although I am 
> having further issues now. Here's a recent pastie with my improved models, 
> along with the errors I'm now having, http://pastie.org/6417080
> 
> What I've done (as you can probably see) is I've used the 
> ForeignKeyConstraint on my PhoneNumber table, to ensure that I'm able to link 
> to my exchanges table properly. Unfortunately, this looks like it is not 
> being picked up by SQLAlchemy, since it's complaining that I have no Foreign 
> Keys for that table.
> 
> Also, just for reference, I've totally wiped / recreated my DBs to test this, 
> so I'm sure it isn't a result of migrations or anything like that.
> 
> If I run \d phonenumbers inside of psql, I get the following output 
> (http://pastie.org/6417088), thought that might also be of help.
> 
> Thank you all for your help so far, I can't wait to get this working!
> 
> Best,
> 
> -Randall
> 
> 
> On Thu, Mar 7, 2013 at 5:41 AM, Lloyd Kvam  wrote:
> While primary_key is specified twice, once for each column, there is only ONE 
> primary key which is a composite.
> 
> You need to use ForeignKeyConstraint at the Table level to specify a 
> composite foreign key.
> 
> You need to provide two lists, the local table columns, and the corresponding 
> foreign table columns.
> Now you know what to search for if  you need more information.
> 
> from the schema definition language docs
> It’s important to note that the ForeignKeyConstraint is the only way to 
> define a composite foreign key. While we could also have placed individual 
> ForeignKey objects on both theinvoice_item.invoice_id and 
> invoice_item.ref_num columns, SQLAlchemy would not be aware that these two 
> values should be paired together - it would be two individual foreign key 
> constraints instead of a single composite foreign key referencing two columns.
> 
> 
> On Wednesday, March 6, 2013 9:00:56 PM UTC-5, Randall Degges wrote:
> Hi Mike,
> 
> Sorry about that, I actually had a typo there. I've got a correct code sample 
> available here 
> http://stackoverflow.com/questions/15260842/how-can-i-build-a-foreignkey-to-a-table-which-has-multiple-primary-keys
>  (just posted it).
> 
> Thank you,
> 
> -Randall
> 
> 
> On Wed, Mar 6, 2013 at 5:54 PM, Michael Bayer  wrote:
> a database table can only have one primary key (hence "primary"), but that 
> key can contain more than one column (a "composite" primary key).
> 
> the model you have here is a little unclear, did you mean for the primary key 
> of Exchange to be "exchange" , and the primary key of PhoneNumber to be the 
> composite of "exchange" and "phone number" ?   that would be my guess as to 
> what you're looking for.
> 
> 
> 
> On Mar 6, 2013, at 6:05 PM, Randall Degges  wrote:
> 
>> Hi all,
>> 
>> I'm having a lot of trouble figuring out how to properly build my ForeignKey 
>> column for a table I'm defining. I've outlined my models here: 
>> http://pastie.org/6407419# (and put a comment next to the problematic line 
>> in my PhoneNumber model).
>> 
>> Here's what's happening:
>> 
>> My Exchange table has two primary keys. This is required for my use case.
>> 
>> The PhoneNumber table I'm trying to define needs a ForeignKey to the 
>> Exchange table, but since the Exchange table has two primary keys, I can't 
>> figure out how to make the relationship work.
>> 
>> Any guidance would be appreciated. Thank you.
>> 
>> -- 
>> 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+...@googlegroups.com.
>> 
>> To post to this group, send email to sqlal...@googlegroups.com.
>> 
>> 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 sqlalchemy+...@googlegroups.com.
> 
> To post to this group, send

Re: [sqlalchemy] Updating a table using sqlalchemy.sql.expression update

[Simon King]

SQLAlchemy is broadly separated into 2 parts, "core" and "ORM" (which you can 
see as the left and right-hand sides on the  front page of the documentation, 
http://docs.sqlalchemy.org/en/rel_0_8/)

Table objects like the one you have below are part of the Core API. Columns of 
Table objects are accessible via the "c" property:

  process_files.c.process_id

The ORM is built on top of the core API. When you do something like:

  class Files(Base):
  __tablename__ = 'files'
  id = sa.Column('id', sa.Integer, primary_key=True)

…you are creating a "mapped class". You can access the columns of these 
directly like "Files.id". If you ever need access to the underlying Table 
object, you can get it from Files.__table__.

When using things like session.query(), you can often use tables or mapped 
classes interchangeably.

Hope that helps,

Simon

On 7 Mar 2013, at 20:57, Mauricio de Abreu Antunes  
wrote:

> Simon,
> 
> Here is the table (not sure if the code to create a relationship table is 
> correct): 
> 
> 
> # Many to Many - Process x Files
> 
> 
> 
> process_files = Table("process_files", Base.metadata,
> 
> 
> 
> Column("process_id", Integer, ForeignKey("process.id")),
> 
> 
> 
> Column("files_id", Integer, ForeignKey("files.id"))
> 
> 
> 
> )
> 
> 
> 
> 
> The error:
> 
> AttributeError: 'Table' object has no attribute 'process_id'
> 
> 
> Why can not I access process_id?
> 
> 2013/3/7 Simon King 
> The ORM tutorial covers querying with joins:
> 
>   http://docs.sqlalchemy.org/en/rel_0_8/orm/tutorial.html#querying-with-joins
> 
> In this case, you probably want something like this (if ProcessFiles is a 
> class mapped to the process_files table):
> 
>   result = 
> session.query(Files).join(ProcessFiles).filter(ProcessFiles.process_id==1)
> 
> Hope that helps,
> 
> Simon
> 
> On 7 Mar 2013, at 20:35, Mauricio de Abreu Antunes 
>  wrote:
> 
> > I wanna perform a query on process_files and hereafter a update/join like 
> > this:
> >
> > SELECT files.id AS files_id, files.name AS files_name, files.directory AS 
> > files_directory, files.active AS files_active, files.connection_id AS 
> > files_connection_id
> > FROM files JOIN process_files ON files.id = process_files.files_id
> >
> > Actually the piece os select above is a result from:
> >
> > result = session.query(Files).join(process_files)
> >
> > It is ok, but i wanna filter it with process_files.process_id = 1 for 
> > example.
> >
> > Reading the docs i could not find the proper way to do this:
> > http://docs.sqlalchemy.org/en/rel_0_7/orm/query.html#sqlalchemy.orm.query.Query.join
> >
> > Am i reading the wrong part of the docs?
> >
> > 2013/3/7 Mauricio de Abreu Antunes 
> > These tips are veeery good!
> > I sometimes get lost about the best way to use the best ORM library in the 
> > world.
> >
> >
> > 2013/3/7 Simon King 
> > This is an unusual way to update an object that you've already retrieved:
> >
> > result = session.query(Executions). \
> > filter_by(id=execution_id).first()
> > if result.end_date is None:
> > e =
> > update(Executions).where(Executions.id==bindparam("execution_id")). \
> > values(end_date=bindparam("now"))
> > self.connection.execute(e, execution_id=execution_id,
> > now=datetime.datetime.now())
> >
> > It would be more natural to write it like this:
> >
> >   if result.end_date is None:
> >   result.end_date = datetime.datetime.now()
> >   session.flush()
> >
> > Also, if "id" is the primary key on your Executions class, you can
> > write the first line as:
> >
> >   result = session.query(Executions).get(execution_id)
> >
> >
> > On Thu, Mar 7, 2013 at 1:58 AM, Michael Bayer  
> > wrote:
> > > in the 0.7 series, you can't pass an ORM mapped class as the subject of 
> > > the
> > > core update() construct:
> > >
> > > e = update(Executions).where(Executions.id==bindparam("execution_id")). \
> > > values(end_date=bindparam("now"))
> > >
> > > that statement will work as is if you just refer to the Table:
> > >
> > > e =
> > > update(Executions.__table__).where(Executions.id==bindparam("execution_id")).
> > > \
> > > values(end_date=bindparam("now"))
> > >
> > > also note that the indirection between bindparam("foo") and
> > > connection.execute(stmt, foo="some value") is not needed; you can embed
> > > literal values directly in the statement, and the Core will convert them 
> > > to
> > > bound parameters (just use echo=True to see it in action):
> > >
> > > e = update(Executions).where(Executions.id==execution_id). \
> > > values(end_date=datetime.datetime.now())
> > >
> > > At the ORM level,  you can use query.update():
> > >
> > > session.query(Executions).filter(Executions.id==execution_id).update({"end_date":datetime.now()},
> > > synchronize_session=False)
> > >
> > >
> > > On Mar 6, 2013, at 8:06 PM, Mauricio de Abreu Antunes
> > >  wrote:
> > >
> > > So, i have read @StackOverflow some tips.
> > > There is a lot of p

Re: [sqlalchemy] How Can I Build a ForeignKey to a Table Which Has Multiple Primary Keys?

[Randall Degges]

Hi Lloyd,

Thank you! I believe this is what I was trying to figure out, although I am
having further issues now. Here's a recent pastie with my improved models,
along with the errors I'm now having, http://pastie.org/6417080

What I've done (as you can probably see) is I've used the
ForeignKeyConstraint on my PhoneNumber table, to ensure that I'm able to
link to my exchanges table properly. Unfortunately, this looks like it is
not being picked up by SQLAlchemy, since it's complaining that I have no
Foreign Keys for that table.

Also, just for reference, I've totally wiped / recreated my DBs to test
this, so I'm sure it isn't a result of migrations or anything like that.

If I run \d phonenumbers inside of psql, I get the following output (
http://pastie.org/6417088), thought that might also be of help.

Thank you all for your help so far, I can't wait to get this working!

Best,

-Randall


On Thu, Mar 7, 2013 at 5:41 AM, Lloyd Kvam  wrote:

> While primary_key is specified twice, once for each column, there is only
> ONE primary key which is a composite.
>
> You need to use ForeignKeyConstraint at the Table level to specify a
> composite foreign key.
>
> You need to provide two lists, the local table columns, and the
> corresponding foreign table columns.
> Now you know what to search for if  you need more information.
>
> from the schema definition language docs
>
> It’s important to note that the 
> ForeignKeyConstraint
>  is
> the only way to define a composite foreign key. While we could also have
> placed individual 
> ForeignKey
>  objects
> on both theinvoice_item.invoice_id and invoice_item.ref_num columns,
> SQLAlchemy would not be aware that these two values should be paired
> together - it would be two individual foreign key constraints instead of a
> single composite foreign key referencing two columns.
>
>
> On Wednesday, March 6, 2013 9:00:56 PM UTC-5, Randall Degges wrote:
>
>> Hi Mike,
>>
>> Sorry about that, I actually had a typo there. I've got a correct code
>> sample available here http://stackoverflow.com/**
>> questions/15260842/how-can-i-**build-a-foreignkey-to-a-table-**
>> which-has-multiple-primary-**keys
>>  (just
>> posted it).
>>
>> Thank you,
>>
>> -Randall
>>
>>
>> On Wed, Mar 6, 2013 at 5:54 PM, Michael Bayer wrote:
>>
>>> a database table can only have one primary key (hence "primary"), but
>>> that key can contain more than one column (a "composite" primary key).
>>>
>>> the model you have here is a little unclear, did you mean for the
>>> primary key of Exchange to be "exchange" , and the primary key of
>>> PhoneNumber to be the composite of "exchange" and "phone number" ?   that
>>> would be my guess as to what you're looking for.
>>>
>>>
>>>
>>> On Mar 6, 2013, at 6:05 PM, Randall Degges  wrote:
>>>
>>> Hi all,
>>>
>>> I'm having a lot of trouble figuring out how to properly build my
>>> ForeignKey column for a table I'm defining. I've outlined my models here:
>>> http://pastie.org/**6407419#  (and put a
>>> comment next to the problematic line in my PhoneNumber model).
>>>
>>> Here's what's happening:
>>>
>>> My Exchange table has two primary keys. This is required for my use case.
>>>
>>> The PhoneNumber table I'm trying to define needs a ForeignKey to the
>>> Exchange table, but since the Exchange table has two primary keys, I can't
>>> figure out how to make the relationship work.
>>>
>>> Any guidance would be appreciated. Thank you.
>>>
>>> --
>>> 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+...@**googlegroups.com.
>>>
>>> To post to this group, send email to sqlal...@googlegroups.com.
>>>
>>> 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 sqlalchemy+...@**googlegroups.com.
>>>
>>> To post to this group, send email to sqlal...@googlegroups.com.
>>>
>>> Visit this group at 
>>> http://groups.google.com/**group/sqlalchemy?hl=en
>>> .
>>> For more options, visit 
>>> https://groups.google.com/**groups/opt_out
>>> .
>>>
>>>
>>>
>>
>>
>>
>> --
>> Randall Degges
>> *http://rdegges.com/*
>>
>  

Re: [sqlalchemy] Updating a table using sqlalchemy.sql.expression update

[Mauricio de Abreu Antunes]

Simon,

Here is the table (not sure if the code to create a relationship table is
correct):

# Many to Many - Process x Files
process_files = Table("process_files", Base.metadata,
Column("process_id", Integer, ForeignKey("process.id")),
Column("files_id", Integer, ForeignKey("files.id"))
)

The error:


AttributeError: 'Table' object has no attribute 'process_id'


Why can not I access process_id?


2013/3/7 Simon King 

> The ORM tutorial covers querying with joins:
>
>
> http://docs.sqlalchemy.org/en/rel_0_8/orm/tutorial.html#querying-with-joins
>
> In this case, you probably want something like this (if ProcessFiles is a
> class mapped to the process_files table):
>
>   result =
> session.query(Files).join(ProcessFiles).filter(ProcessFiles.process_id==1)
>
> Hope that helps,
>
> Simon
>
> On 7 Mar 2013, at 20:35, Mauricio de Abreu Antunes <
> mauricio.abr...@gmail.com> wrote:
>
> > I wanna perform a query on process_files and hereafter a update/join
> like this:
> >
> > SELECT files.id AS files_id, files.name AS files_name, files.directory
> AS files_directory, files.active AS files_active, files.connection_id AS
> files_connection_id
> > FROM files JOIN process_files ON files.id = process_files.files_id
> >
> > Actually the piece os select above is a result from:
> >
> > result = session.query(Files).join(process_files)
> >
> > It is ok, but i wanna filter it with process_files.process_id = 1 for
> example.
> >
> > Reading the docs i could not find the proper way to do this:
> >
> http://docs.sqlalchemy.org/en/rel_0_7/orm/query.html#sqlalchemy.orm.query.Query.join
> >
> > Am i reading the wrong part of the docs?
> >
> > 2013/3/7 Mauricio de Abreu Antunes 
> > These tips are veeery good!
> > I sometimes get lost about the best way to use the best ORM library in
> the world.
> >
> >
> > 2013/3/7 Simon King 
> > This is an unusual way to update an object that you've already retrieved:
> >
> > result = session.query(Executions). \
> > filter_by(id=execution_id).first()
> > if result.end_date is None:
> > e =
> > update(Executions).where(Executions.id==bindparam("execution_id")). \
> > values(end_date=bindparam("now"))
> > self.connection.execute(e, execution_id=execution_id,
> > now=datetime.datetime.now())
> >
> > It would be more natural to write it like this:
> >
> >   if result.end_date is None:
> >   result.end_date = datetime.datetime.now()
> >   session.flush()
> >
> > Also, if "id" is the primary key on your Executions class, you can
> > write the first line as:
> >
> >   result = session.query(Executions).get(execution_id)
> >
> >
> > On Thu, Mar 7, 2013 at 1:58 AM, Michael Bayer 
> wrote:
> > > in the 0.7 series, you can't pass an ORM mapped class as the subject
> of the
> > > core update() construct:
> > >
> > > e =
> update(Executions).where(Executions.id==bindparam("execution_id")). \
> > > values(end_date=bindparam("now"))
> > >
> > > that statement will work as is if you just refer to the Table:
> > >
> > > e =
> > >
> update(Executions.__table__).where(Executions.id==bindparam("execution_id")).
> > > \
> > > values(end_date=bindparam("now"))
> > >
> > > also note that the indirection between bindparam("foo") and
> > > connection.execute(stmt, foo="some value") is not needed; you can embed
> > > literal values directly in the statement, and the Core will convert
> them to
> > > bound parameters (just use echo=True to see it in action):
> > >
> > > e = update(Executions).where(Executions.id==execution_id). \
> > > values(end_date=datetime.datetime.now())
> > >
> > > At the ORM level,  you can use query.update():
> > >
> > >
> session.query(Executions).filter(Executions.id==execution_id).update({"end_date":datetime.now()},
> > > synchronize_session=False)
> > >
> > >
> > > On Mar 6, 2013, at 8:06 PM, Mauricio de Abreu Antunes
> > >  wrote:
> > >
> > > So, i have read @StackOverflow some tips.
> > > There is a lot of people saying they have to make a query on the table
> and
> > > then update it. there is no way to upgrade without performing a query?!
> > >
> > > On Wednesday, March 6, 2013 6:17:35 PM UTC-3, Mauricio de Abreu Antunes
> > > wrote:
> > >>
> > >> Hello,
> > >>
> > >> I'm new to SQLAlchemy. Currently I'm using SQLAlchemy 0.7.1.
> > >> Reading the tutorial, I tried to write my codes like those examples
> but I
> > >> had no success working on it.
> > >>
> > >> Code is here:
> > >> https://gist.github.com/mauricioabreu/5103163
> > >>
> > >> Do I need to map the table Executions to execute an update expression
> on
> > >> it?
> > >>
> > >> Sorry if this is a very noob question.
> > >>
> > >> If you need more info about the problem let me know.
> > >
> > >
> > > --
> > > 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...@googlegrou

Re: [sqlalchemy] Updating a table using sqlalchemy.sql.expression update

[Simon King]

The ORM tutorial covers querying with joins:

  http://docs.sqlalchemy.org/en/rel_0_8/orm/tutorial.html#querying-with-joins

In this case, you probably want something like this (if ProcessFiles is a class 
mapped to the process_files table):

  result = 
session.query(Files).join(ProcessFiles).filter(ProcessFiles.process_id==1)

Hope that helps,

Simon

On 7 Mar 2013, at 20:35, Mauricio de Abreu Antunes  
wrote:

> I wanna perform a query on process_files and hereafter a update/join like 
> this:
> 
> SELECT files.id AS files_id, files.name AS files_name, files.directory AS 
> files_directory, files.active AS files_active, files.connection_id AS 
> files_connection_id
> FROM files JOIN process_files ON files.id = process_files.files_id
> 
> Actually the piece os select above is a result from:
> 
> result = session.query(Files).join(process_files)
> 
> It is ok, but i wanna filter it with process_files.process_id = 1 for example.
> 
> Reading the docs i could not find the proper way to do this:
> http://docs.sqlalchemy.org/en/rel_0_7/orm/query.html#sqlalchemy.orm.query.Query.join
> 
> Am i reading the wrong part of the docs?
> 
> 2013/3/7 Mauricio de Abreu Antunes 
> These tips are veeery good!
> I sometimes get lost about the best way to use the best ORM library in the 
> world.
> 
> 
> 2013/3/7 Simon King 
> This is an unusual way to update an object that you've already retrieved:
> 
> result = session.query(Executions). \
> filter_by(id=execution_id).first()
> if result.end_date is None:
> e =
> update(Executions).where(Executions.id==bindparam("execution_id")). \
> values(end_date=bindparam("now"))
> self.connection.execute(e, execution_id=execution_id,
> now=datetime.datetime.now())
> 
> It would be more natural to write it like this:
> 
>   if result.end_date is None:
>   result.end_date = datetime.datetime.now()
>   session.flush()
> 
> Also, if "id" is the primary key on your Executions class, you can
> write the first line as:
> 
>   result = session.query(Executions).get(execution_id)
> 
> 
> On Thu, Mar 7, 2013 at 1:58 AM, Michael Bayer  
> wrote:
> > in the 0.7 series, you can't pass an ORM mapped class as the subject of the
> > core update() construct:
> >
> > e = update(Executions).where(Executions.id==bindparam("execution_id")). \
> > values(end_date=bindparam("now"))
> >
> > that statement will work as is if you just refer to the Table:
> >
> > e =
> > update(Executions.__table__).where(Executions.id==bindparam("execution_id")).
> > \
> > values(end_date=bindparam("now"))
> >
> > also note that the indirection between bindparam("foo") and
> > connection.execute(stmt, foo="some value") is not needed; you can embed
> > literal values directly in the statement, and the Core will convert them to
> > bound parameters (just use echo=True to see it in action):
> >
> > e = update(Executions).where(Executions.id==execution_id). \
> > values(end_date=datetime.datetime.now())
> >
> > At the ORM level,  you can use query.update():
> >
> > session.query(Executions).filter(Executions.id==execution_id).update({"end_date":datetime.now()},
> > synchronize_session=False)
> >
> >
> > On Mar 6, 2013, at 8:06 PM, Mauricio de Abreu Antunes
> >  wrote:
> >
> > So, i have read @StackOverflow some tips.
> > There is a lot of people saying they have to make a query on the table and
> > then update it. there is no way to upgrade without performing a query?!
> >
> > On Wednesday, March 6, 2013 6:17:35 PM UTC-3, Mauricio de Abreu Antunes
> > wrote:
> >>
> >> Hello,
> >>
> >> I'm new to SQLAlchemy. Currently I'm using SQLAlchemy 0.7.1.
> >> Reading the tutorial, I tried to write my codes like those examples but I
> >> had no success working on it.
> >>
> >> Code is here:
> >> https://gist.github.com/mauricioabreu/5103163
> >>
> >> Do I need to map the table Executions to execute an update expression on
> >> it?
> >>
> >> Sorry if this is a very noob question.
> >>
> >> If you need more info about the problem let me know.
> >
> >
> > --
> > 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 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 sqlalchemy+unsubscr...@googlegroups.com.
> > To post to this group, send email to sqlalchemy@googlegroups.com.
> > Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
> > For more options, visit https://groups.google.com/groups/opt_out.
> >
> >

Re: [sqlalchemy] Updating a table using sqlalchemy.sql.expression update

[Mauricio de Abreu Antunes]

I wanna perform a query on process_files and hereafter a update/join like
this:

SELECT files.id AS files_id, files.name AS files_name, files.directory AS
files_directory, files.active AS files_active, files.connection_id AS
files_connection_id
FROM files JOIN process_files ON files.id = process_files.files_id

Actually the piece os select above is a result from:

result = session.query(Files).join(process_files)

It is ok, but i wanna filter it with process_files.process_id = 1 for
example.

Reading the docs i could not find the proper way to do this:
http://docs.sqlalchemy.org/en/rel_0_7/orm/query.html#sqlalchemy.orm.query.Query.join

Am i reading the wrong part of the docs?

2013/3/7 Mauricio de Abreu Antunes 

> These tips are veeery good!
> I sometimes get lost about the best way to use the best ORM library in the
> world.
>
>
> 2013/3/7 Simon King 
>
>> This is an unusual way to update an object that you've already retrieved:
>>
>> result = session.query(Executions). \
>> filter_by(id=execution_id).first()
>> if result.end_date is None:
>> e =
>> update(Executions).where(Executions.id==bindparam("execution_id")). \
>> values(end_date=bindparam("now"))
>> self.connection.execute(e, execution_id=execution_id,
>> now=datetime.datetime.now())
>>
>> It would be more natural to write it like this:
>>
>>   if result.end_date is None:
>>   result.end_date = datetime.datetime.now()
>>   session.flush()
>>
>> Also, if "id" is the primary key on your Executions class, you can
>> write the first line as:
>>
>>   result = session.query(Executions).get(execution_id)
>>
>>
>> On Thu, Mar 7, 2013 at 1:58 AM, Michael Bayer 
>> wrote:
>> > in the 0.7 series, you can't pass an ORM mapped class as the subject of
>> the
>> > core update() construct:
>> >
>> > e = update(Executions).where(Executions.id==bindparam("execution_id")).
>> \
>> > values(end_date=bindparam("now"))
>> >
>> > that statement will work as is if you just refer to the Table:
>> >
>> > e =
>> >
>> update(Executions.__table__).where(Executions.id==bindparam("execution_id")).
>> > \
>> > values(end_date=bindparam("now"))
>> >
>> > also note that the indirection between bindparam("foo") and
>> > connection.execute(stmt, foo="some value") is not needed; you can embed
>> > literal values directly in the statement, and the Core will convert
>> them to
>> > bound parameters (just use echo=True to see it in action):
>> >
>> > e = update(Executions).where(Executions.id==execution_id). \
>> > values(end_date=datetime.datetime.now())
>> >
>> > At the ORM level,  you can use query.update():
>> >
>> >
>> session.query(Executions).filter(Executions.id==execution_id).update({"end_date":datetime.now()},
>> > synchronize_session=False)
>> >
>> >
>> > On Mar 6, 2013, at 8:06 PM, Mauricio de Abreu Antunes
>> >  wrote:
>> >
>> > So, i have read @StackOverflow some tips.
>> > There is a lot of people saying they have to make a query on the table
>> and
>> > then update it. there is no way to upgrade without performing a query?!
>> >
>> > On Wednesday, March 6, 2013 6:17:35 PM UTC-3, Mauricio de Abreu Antunes
>> > wrote:
>> >>
>> >> Hello,
>> >>
>> >> I'm new to SQLAlchemy. Currently I'm using SQLAlchemy 0.7.1.
>> >> Reading the tutorial, I tried to write my codes like those examples
>> but I
>> >> had no success working on it.
>> >>
>> >> Code is here:
>> >> https://gist.github.com/mauricioabreu/5103163
>> >>
>> >> Do I need to map the table Executions to execute an update expression
>> on
>> >> it?
>> >>
>> >> Sorry if this is a very noob question.
>> >>
>> >> If you need more info about the problem let me know.
>> >
>> >
>> > --
>> > 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 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 sqlalchemy+unsubscr...@googlegroups.com.
>> > To post to this group, send email to sqlalchemy@googlegroups.com.
>> > 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 sqlalchemy+unsubscr...@googlegroups.com.
>> To post to this group, send email to sqlalchemy@googlegroups.com.
>> Visit this group at

Re: [sqlalchemy] 0.8.0b Postgres Array Issue

[Jason]

On Thursday, March 7, 2013 2:16:54 PM UTC-5, Michael Bayer wrote:
>
>
> On Mar 7, 2013, at 2:12 PM, Jason > 
> wrote:
>
>
>
> On Thursday, March 7, 2013 1:40:46 PM UTC-5, Michael Bayer wrote:
>>
>> Can you pass along more specifics here?  I don't see where this named 
>> tuple is being created.  It's true that the ARRAY type by default doesn't 
>> know how deep it should be unwrapping arrays, if you pass the "dimensions" 
>> argument then it will be fixed.   But it's not clear why you didn't see 
>> this issue in 0.7 as well, perhaps we check for a wider range of 
>> array/tuple types at this point but I don't recall.
>>
>>
>>
>> A namedtuple is returned by Psycopg2 directly (using its 
> register_composite mechanism). The result processing changed in this 
> commit: 
> https://bitbucket.org/sqlalchemy/sqlalchemy/commits/0ba8b4fd402f1c187b80310fc838dcdea27e0af8,
>  
> so I will take a look to see if I can figure out what causes the new 
> behaviour. I'm guessing that it sees the namedtuple instance and figures 
> that it is a tuple instance so it can be converted into a list.
>
>
>
> yeah.  if you set the ARRAY with the number of expected dimensions (likely 
> "1" here) it shouldn't do any of that.
>
>
Oh great, that works fine.  Thanks!

-- Jason

-- 
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 http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.

Re: [sqlalchemy] 0.8.0b Postgres Array Issue

[Michael Bayer]

On Mar 7, 2013, at 2:12 PM, Jason  wrote:

> 
> 
> On Thursday, March 7, 2013 1:40:46 PM UTC-5, Michael Bayer wrote:
> Can you pass along more specifics here?  I don't see where this named tuple 
> is being created.  It's true that the ARRAY type by default doesn't know how 
> deep it should be unwrapping arrays, if you pass the "dimensions" argument 
> then it will be fixed.   But it's not clear why you didn't see this issue in 
> 0.7 as well, perhaps we check for a wider range of array/tuple types at this 
> point but I don't recall.
> 
> 
> 
> A namedtuple is returned by Psycopg2 directly (using its register_composite 
> mechanism). The result processing changed in this commit: 
> https://bitbucket.org/sqlalchemy/sqlalchemy/commits/0ba8b4fd402f1c187b80310fc838dcdea27e0af8,
>  so I will take a look to see if I can figure out what causes the new 
> behaviour. I'm guessing that it sees the namedtuple instance and figures that 
> it is a tuple instance so it can be converted into a list.


yeah.  if you set the ARRAY with the number of expected dimensions (likely "1" 
here) it shouldn't do any of 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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.

Re: [sqlalchemy] 0.8.0b Postgres Array Issue

[Jason]

On Thursday, March 7, 2013 1:40:46 PM UTC-5, Michael Bayer wrote:
>
> Can you pass along more specifics here?  I don't see where this named 
> tuple is being created.  It's true that the ARRAY type by default doesn't 
> know how deep it should be unwrapping arrays, if you pass the "dimensions" 
> argument then it will be fixed.   But it's not clear why you didn't see 
> this issue in 0.7 as well, perhaps we check for a wider range of 
> array/tuple types at this point but I don't recall.
>
>
>
> A namedtuple is returned by Psycopg2 directly (using its 
register_composite mechanism). The result processing changed in this 
commit: 
https://bitbucket.org/sqlalchemy/sqlalchemy/commits/0ba8b4fd402f1c187b80310fc838dcdea27e0af8,
 
so I will take a look to see if I can figure out what causes the new 
behaviour. I'm guessing that it sees the namedtuple instance and figures 
that it is a tuple instance so it can be converted into a list.


--
Jason

-- 
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 http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.

Re: [sqlalchemy] Connecting to ms sql db on Windows Server 2008 with pyodbc and python 3.3

[Michael Bayer]

its Python 3 related.  that particular ticket refers to how bad of an 
experience I have when trying to get pyodbc to run well on OSX especially in 
Python 3.  if things have improved, I can try working on it at least in a Linux 
VM (I still have low hopes for OSX).


On Mar 7, 2013, at 1:41 PM, Daniel Kraus  wrote:

> Hi,
> 
> when I try to connect with sqlalchemy and mssql+pyodbc I get this exeption:
> "TypeError: The first argument to execute must be a string or unicode query".
> 
> It works if I only use pyodbc.
> 
> E.g.
> >>> conn = pyodbc.connect('DRIVER={SQL 
> >>> Server};Server=127.0.0.1;Database=BOM;UID=guest;PWD=guest')
> >>> cursor = conn.cursor()
> >>> cursor.execute("select * from Objects")
> >>> result = cursor.fetchone()
> 
> -> now `result` is a result from the database as it should be.
> 
> When I try to connect with sqlalchemy:
> 
> >>> engine = 
> >>> sqlalchemy.create_engine('mssql+pyodbc://guest:guest@127.0.0.1/BOM')
> >>> engine.execute("select * from Objects")
> 
> -> The above TypeError exception (complete traceback: 
> http://pastebin.com/PHxbynFt )
> 
> Not sure if this bug is related: http://www.sqlalchemy.org/trac/ticket/2355
> 
> Any ideas what could be wrong and how to fix it?
> I would really like to stick with python3 ;)
> 
> Thanks,
>   Daniel
> 
> -- 
> 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 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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.

[sqlalchemy] Connecting to ms sql db on Windows Server 2008 with pyodbc and python 3.3

[Daniel Kraus]

Hi,

when I try to connect with sqlalchemy and mssql+pyodbc I get this exeption:
"TypeError: The first argument to execute must be a string or unicode 
query".

It works if I only use pyodbc.

E.g.
>>> conn = pyodbc.connect('DRIVER={SQL 
Server};Server=127.0.0.1;Database=BOM;UID=guest;PWD=guest')
>>> cursor = conn.cursor()
>>> cursor.execute("select * from Objects")
>>> result = cursor.fetchone()

-> now `result` is a result from the database as it should be.

When I try to connect with sqlalchemy:

>>> engine = 
sqlalchemy.create_engine('mssql+pyodbc://guest:guest@127.0.0.1/BOM')
>>> engine.execute("select * from Objects")

-> The above TypeError exception (complete 
traceback: http://pastebin.com/PHxbynFt )

Not sure if this bug is related: http://www.sqlalchemy.org/trac/ticket/2355

Any ideas what could be wrong and how to fix it?
I would really like to stick with python3 ;)

Thanks,
  Daniel

-- 
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 http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.

Re: [sqlalchemy] 0.8.0b Postgres Array Issue

[Michael Bayer]

Can you pass along more specifics here?  I don't see where this named tuple is 
being created.  It's true that the ARRAY type by default doesn't know how deep 
it should be unwrapping arrays, if you pass the "dimensions" argument then it 
will be fixed.   But it's not clear why you didn't see this issue in 0.7 as 
well, perhaps we check for a wider range of array/tuple types at this point but 
I don't recall.



On Mar 7, 2013, at 1:21 PM, Jason  wrote:

> Hello,
> 
> I'm using a column defined as:
> discounts = Column(ARRAY(Discount))
> 
> Where Discount is a UserDefinedType that just passes the value through 
> to/from Psycopg2 (which uses a namedtuple for the discounts value):
> 
> class Discount(UserDefinedType):
> """ SQLAlchemy type that passes through values to be handled by a psycopg2
> extension type.
> """
> type_name = 'Discount'
> 
> def get_col_spec(self):
> return self.type_name
> 
> def bind_processor(self, dialect):
> return None
> 
> def result_processor(self, dialect, coltype):
> return None
> 
> def is_mutable(self):
> return True
> 
> def copy_value(self, value):
> return copy.copy(value)
> 
> Previously (in 0.7.9) the discounts value on a model instance would be an 
> list of discount types (which are namedtuples). Now SQLAlchemy seems to 
> convert the Discount type into a list resulting in a two-dimensional list for 
> the discounts value instead of a list of namedtuples. The documentation 
> doesn't mention this side-effect of the array improvements, is this a bug in 
> 0.8.0b? 
> 
> Thanks,
> 
> Jason
> 
> -- 
> 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 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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.

[sqlalchemy] 0.8.0b Postgres Array Issue

[Jason]

Hello,

I'm using a column defined as:

discounts = Column(ARRAY(Discount))

Where Discount is a UserDefinedType that just passes the value through 
to/from Psycopg2 (which uses a namedtuple for the discounts value):

class Discount(UserDefinedType):
""" SQLAlchemy type that passes through values to be handled by a 
psycopg2
extension type.
"""
type_name = 'Discount'

def get_col_spec(self):
return self.type_name

def bind_processor(self, dialect):
return None

def result_processor(self, dialect, coltype):
return None

def is_mutable(self):
return True

def copy_value(self, value):
return copy.copy(value)


Previously (in 0.7.9) the discounts value on a model instance would be an 
list of discount types (which are namedtuples). Now SQLAlchemy seems to 
convert the Discount type into a list resulting in a two-dimensional list 
for the discounts value instead of a list of namedtuples. The documentation 
doesn't mention this side-effect of the array improvements, is this a bug 
in 0.8.0b? 

Thanks,

Jason

-- 
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 http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.

Re: [sqlalchemy] Undefer Hybrid Attributes

[Kent Bower]

That makes sense,
Thanks,
Kent

On Mar 7, 2013, at 12:09 PM, Michael Bayer  wrote:

> 
> The hybrid attribute is a Python function that invokes when it's called.  So 
> it doesn't make sense for it to be a column property since there is no 
> attribute to be populated.  "Undeferred" also doesn't make any sense because 
> the hybrid already calls a local in-Python function when accessed at the 
> instance level, not a database call.
> 
> A traditional column_property() can be deferred or undeterred, and when 
> called at the instance level will emit SQL to the database.
> 
> If you have an attribute that should only populate via SQL, then you need to 
> just use a column_property().
> 
> 
> 
> On Mar 7, 2013, at 11:42 AM, Kent  wrote:
> 
>> I suppose what I'm really after is a column_property (for class level) and 
>> plain descriptor (for instance level), which is exactly what Hybrid 
>> attributes are meant to be, but I wanted them to be part of the mapper and 
>> undeferred in some cases.
>> 
>> On Thursday, March 7, 2013 11:36:37 AM UTC-5, Kent wrote:
>>> 
>>> I notice that Hybrid Attributes don't show up as mapper properties (since 
>>> they are class wide instead of mapper specific, I suppose).  I couldn't 
>>> find documentation on whether I can "undefer" these?  Or can I create a 
>>> synonym or column_property from a hybrid attribute in the mapper?
>> 
>> -- 
>> 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 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 a topic in the Google 
> Groups "sqlalchemy" group.
> To unsubscribe from this topic, visit 
> https://groups.google.com/d/topic/sqlalchemy/kO6KS88-2xU/unsubscribe?hl=en.
> To unsubscribe from this group and all its topics, send an email to 
> sqlalchemy+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> 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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.

Re: [sqlalchemy] Undefer Hybrid Attributes

[Michael Bayer]

The hybrid attribute is a Python function that invokes when it's called.  So it 
doesn't make sense for it to be a column property since there is no attribute 
to be populated.  "Undeferred" also doesn't make any sense because the hybrid 
already calls a local in-Python function when accessed at the instance level, 
not a database call.

A traditional column_property() can be deferred or undeterred, and when called 
at the instance level will emit SQL to the database.

If you have an attribute that should only populate via SQL, then you need to 
just use a column_property().



On Mar 7, 2013, at 11:42 AM, Kent  wrote:

> I suppose what I'm really after is a column_property (for class level) and 
> plain descriptor (for instance level), which is exactly what Hybrid 
> attributes are meant to be, but I wanted them to be part of the mapper and 
> undeferred in some cases.
> 
> On Thursday, March 7, 2013 11:36:37 AM UTC-5, Kent wrote:
> I notice that Hybrid Attributes don't show up as mapper properties (since 
> they are class wide instead of mapper specific, I suppose).  I couldn't find 
> documentation on whether I can "undefer" these?  Or can I create a synonym or 
> column_property from a hybrid attribute in the mapper?
> 
> -- 
> 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 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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.

[sqlalchemy] Re: Undefer Hybrid Attributes

[Kent]

I suppose what I'm really after is a column_property (for class level) and 
plain descriptor (for instance level), which is exactly what Hybrid 
attributes are meant to be, but I wanted them to be part of the mapper and 
undeferred in some cases.

On Thursday, March 7, 2013 11:36:37 AM UTC-5, Kent wrote:
>
> I notice that Hybrid Attributes don't show up as mapper properties (since 
> they are class wide instead of mapper specific, I suppose).  I couldn't 
> find documentation on whether I can "undefer" these?  Or can I create a 
> synonym or column_property from a hybrid attribute in the mapper?
>

-- 
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 http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.

[sqlalchemy] Undefer Hybrid Attributes

[Kent]

I notice that Hybrid Attributes don't show up as mapper properties (since 
they are class wide instead of mapper specific, I suppose).  I couldn't 
find documentation on whether I can "undefer" these?  Or can I create a 
synonym or column_property from a hybrid attribute in the mapper?

-- 
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 http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.

Re: [sqlalchemy] after_configured event and multiple engines

[Michael Bayer]

On Mar 7, 2013, at 8:29 AM, jefro...@gmail.com wrote:

> Hello,
> 
> We are testing the new  __declare_last__ feature with 0.8. We face an issue 
> with the after_configured event in an application that use multiple engines.
> after_configured event is triggered multiple times (by 
> sqlalchemy.orm.configure_mappers), once for each engine.
> 
> The problem with this -as far as I understand it- is that the mapper registry 
> is shared and that the after_configured subscribers can be called multiple 
> times on the same mapper. We end up with exception like this:
> 
> ArgumentError: Error creating backref 'herd' on relationship 'Herd.animals': 
> property of that name exists on mapper 'Mapper|Animal|animal'
> 
> caused by the fact that __declare_last__ is called multiple times on the same 
> mappers ... relation being already defined.
> 
> Is there a way to prevent this in sqlalchemy ? Or should I prevent this using 
> state in my class ?


there's no connection between Engine objects and Mapper objects, so there's no 
implicit linkage between creating an engine and mappers being configured.   If 
there are multiple mapper configured events happening, that indicates your 
application is adding new mappers after the existing set of mappers are being 
used in some way, or you're calling configure_mappers() explicitly.Sounds 
like you have some kind of multi-tenancy going on (multiple applications 
running in the same Python process), but if not it might be worth it to 
organize things such that configure_mappers() isn't called as much.

As for __declare_last__, it's a simple link to this event, which necessarily 
needs to be called each time newly configured mappers become available as it 
can't assume which mappings the event wishes to deal with.   So if there is 
state being established within __declare_last__, you'd want to check for that 
state already being present, and yes a class-level flag is often a simple way 
to deal with 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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.

Re: [sqlalchemy] How Can I Build a ForeignKey to a Table Which Has Multiple Primary Keys?

[Simon King]

I don't understand your model. Can you have multiple rows in the
Exchange table which all have the same value for Exchange.exchange?

If so, and if you want PhoneNumber to be able to point to a single one
of those rows, then it needs 2 columns to do that (one to point to
Exchange.exchange and one to point at Exchange.area_code_pk).

If not (ie. Exchange.exchange uniquely identifies a row in the
Exchange table), then you probably don't want to make
Exchange.area_code_pk as a primary key. Note: it doesn't actually
matter whether it is declared in the database as a primary key.
SQLAlchemy only requires that the primary key uniquely identifies the
row in the database.

Simon

On Thu, Mar 7, 2013 at 2:00 AM, Randall Degges  wrote:
> Hi Mike,
>
> Sorry about that, I actually had a typo there. I've got a correct code
> sample available here
> http://stackoverflow.com/questions/15260842/how-can-i-build-a-foreignkey-to-a-table-which-has-multiple-primary-keys
> (just posted it).
>
> Thank you,
>
> -Randall
>
>
> On Wed, Mar 6, 2013 at 5:54 PM, Michael Bayer 
> wrote:
>>
>> a database table can only have one primary key (hence "primary"), but that
>> key can contain more than one column (a "composite" primary key).
>>
>> the model you have here is a little unclear, did you mean for the primary
>> key of Exchange to be "exchange" , and the primary key of PhoneNumber to be
>> the composite of "exchange" and "phone number" ?   that would be my guess as
>> to what you're looking for.
>>
>>
>>
>> On Mar 6, 2013, at 6:05 PM, Randall Degges  wrote:
>>
>> Hi all,
>>
>> I'm having a lot of trouble figuring out how to properly build my
>> ForeignKey column for a table I'm defining. I've outlined my models here:
>> http://pastie.org/6407419# (and put a comment next to the problematic line
>> in my PhoneNumber model).
>>
>> Here's what's happening:
>>
>> My Exchange table has two primary keys. This is required for my use case.
>>
>> The PhoneNumber table I'm trying to define needs a ForeignKey to the
>> Exchange table, but since the Exchange table has two primary keys, I can't
>> figure out how to make the relationship work.
>>
>> Any guidance would be appreciated. Thank you.
>>
>> --
>> 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 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 sqlalchemy+unsubscr...@googlegroups.com.
>>
>> To post to this group, send email to sqlalchemy@googlegroups.com.
>> Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
>> For more options, visit https://groups.google.com/groups/opt_out.
>>
>>
>
>
>
>
> --
> Randall Degges
> http://rdegges.com/
>
> --
> 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 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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.

Re: [sqlalchemy] How Can I Build a ForeignKey to a Table Which Has Multiple Primary Keys?

[Lloyd Kvam]

While primary_key is specified twice, once for each column, there is only 
ONE primary key which is a composite.

You need to use ForeignKeyConstraint at the Table level to specify a 
composite foreign key.

You need to provide two lists, the local table columns, and the 
corresponding foreign table columns.
Now you know what to search for if  you need more information.

from the schema definition language docs

It’s important to note that the 
ForeignKeyConstraint
 is 
the only way to define a composite foreign key. While we could also have 
placed individual 
ForeignKey
 objects 
on both theinvoice_item.invoice_id and invoice_item.ref_num columns, 
SQLAlchemy would not be aware that these two values should be paired 
together - it would be two individual foreign key constraints instead of a 
single composite foreign key referencing two columns.


On Wednesday, March 6, 2013 9:00:56 PM UTC-5, Randall Degges wrote:
>
> Hi Mike,
>
> Sorry about that, I actually had a typo there. I've got a correct code 
> sample available here 
> http://stackoverflow.com/questions/15260842/how-can-i-build-a-foreignkey-to-a-table-which-has-multiple-primary-keys
>  (just 
> posted it).
>
> Thank you,
>
> -Randall
>
>
> On Wed, Mar 6, 2013 at 5:54 PM, Michael Bayer 
> 
> > wrote:
>
>> a database table can only have one primary key (hence "primary"), but 
>> that key can contain more than one column (a "composite" primary key).
>>
>> the model you have here is a little unclear, did you mean for the primary 
>> key of Exchange to be "exchange" , and the primary key of PhoneNumber to be 
>> the composite of "exchange" and "phone number" ?   that would be my guess 
>> as to what you're looking for.
>>
>>
>>
>> On Mar 6, 2013, at 6:05 PM, Randall Degges > 
>> wrote:
>>
>> Hi all,
>>
>> I'm having a lot of trouble figuring out how to properly build my 
>> ForeignKey column for a table I'm defining. I've outlined my models here: 
>> http://pastie.org/6407419# (and put a comment next to the problematic 
>> line in my PhoneNumber model).
>>
>> Here's what's happening:
>>
>> My Exchange table has two primary keys. This is required for my use case.
>>
>> The PhoneNumber table I'm trying to define needs a ForeignKey to the 
>> Exchange table, but since the Exchange table has two primary keys, I can't 
>> figure out how to make the relationship work.
>>
>> Any guidance would be appreciated. Thank you.
>>
>> -- 
>> 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+...@googlegroups.com .
>>
>> To post to this group, send email to sqlal...@googlegroups.com
>> .
>> 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 sqlalchemy+...@googlegroups.com .
>>
>> To post to this group, send email to sqlal...@googlegroups.com
>> .
>> Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
>> For more options, visit https://groups.google.com/groups/opt_out.
>>  
>>  
>>
>
>
>
> -- 
> Randall Degges
> *http://rdegges.com/*
>  

-- 
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 http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.

[sqlalchemy] after_configured event and multiple engines

[jefroche]

Hello,

We are testing the new  __declare_last__ feature with 0.8. We face an issue 
with the after_configured event in an application that use multiple engines.
after_configured event is triggered multiple times (by 
sqlalchemy.orm.configure_mappers), once for each engine.

The problem with this -as far as I understand it- is that the mapper 
registry is shared and that the after_configured subscribers can be called 
multiple times on the same mapper. We end up with exception like this:

ArgumentError: Error creating backref 'herd' on relationship 
'Herd.animals': property of that name exists on mapper 
'Mapper|Animal|animal'

caused by the fact that __declare_last__ is called multiple times on the 
same mappers ... relation being already defined.

Is there a way to prevent this in sqlalchemy ? Or should I prevent this 
using state in my class ?

Thank you for your help.

Jeff

-- 
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 http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.

Re: [sqlalchemy] Updating a table using sqlalchemy.sql.expression update

[Mauricio de Abreu Antunes]

These tips are veeery good!
I sometimes get lost about the best way to use the best ORM library in the
world.

2013/3/7 Simon King 

> This is an unusual way to update an object that you've already retrieved:
>
> result = session.query(Executions). \
> filter_by(id=execution_id).first()
> if result.end_date is None:
> e =
> update(Executions).where(Executions.id==bindparam("execution_id")). \
> values(end_date=bindparam("now"))
> self.connection.execute(e, execution_id=execution_id,
> now=datetime.datetime.now())
>
> It would be more natural to write it like this:
>
>   if result.end_date is None:
>   result.end_date = datetime.datetime.now()
>   session.flush()
>
> Also, if "id" is the primary key on your Executions class, you can
> write the first line as:
>
>   result = session.query(Executions).get(execution_id)
>
>
> On Thu, Mar 7, 2013 at 1:58 AM, Michael Bayer 
> wrote:
> > in the 0.7 series, you can't pass an ORM mapped class as the subject of
> the
> > core update() construct:
> >
> > e = update(Executions).where(Executions.id==bindparam("execution_id")). \
> > values(end_date=bindparam("now"))
> >
> > that statement will work as is if you just refer to the Table:
> >
> > e =
> >
> update(Executions.__table__).where(Executions.id==bindparam("execution_id")).
> > \
> > values(end_date=bindparam("now"))
> >
> > also note that the indirection between bindparam("foo") and
> > connection.execute(stmt, foo="some value") is not needed; you can embed
> > literal values directly in the statement, and the Core will convert them
> to
> > bound parameters (just use echo=True to see it in action):
> >
> > e = update(Executions).where(Executions.id==execution_id). \
> > values(end_date=datetime.datetime.now())
> >
> > At the ORM level,  you can use query.update():
> >
> >
> session.query(Executions).filter(Executions.id==execution_id).update({"end_date":datetime.now()},
> > synchronize_session=False)
> >
> >
> > On Mar 6, 2013, at 8:06 PM, Mauricio de Abreu Antunes
> >  wrote:
> >
> > So, i have read @StackOverflow some tips.
> > There is a lot of people saying they have to make a query on the table
> and
> > then update it. there is no way to upgrade without performing a query?!
> >
> > On Wednesday, March 6, 2013 6:17:35 PM UTC-3, Mauricio de Abreu Antunes
> > wrote:
> >>
> >> Hello,
> >>
> >> I'm new to SQLAlchemy. Currently I'm using SQLAlchemy 0.7.1.
> >> Reading the tutorial, I tried to write my codes like those examples but
> I
> >> had no success working on it.
> >>
> >> Code is here:
> >> https://gist.github.com/mauricioabreu/5103163
> >>
> >> Do I need to map the table Executions to execute an update expression on
> >> it?
> >>
> >> Sorry if this is a very noob question.
> >>
> >> If you need more info about the problem let me know.
> >
> >
> > --
> > 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 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 sqlalchemy+unsubscr...@googlegroups.com.
> > To post to this group, send email to sqlalchemy@googlegroups.com.
> > 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 sqlalchemy+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
> For more options, visit https://groups.google.com/groups/opt_out.
>
>
>


-- 
*Mauricio de Abreu Antunes*
Mobile: (51)930-74-525
Skype: mauricio.abreua

-- 
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 http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.

Re: [sqlalchemy] Updating a table using sqlalchemy.sql.expression update

[Simon King]

This is an unusual way to update an object that you've already retrieved:

result = session.query(Executions). \
filter_by(id=execution_id).first()
if result.end_date is None:
e =
update(Executions).where(Executions.id==bindparam("execution_id")). \
values(end_date=bindparam("now"))
self.connection.execute(e, execution_id=execution_id,
now=datetime.datetime.now())

It would be more natural to write it like this:

  if result.end_date is None:
  result.end_date = datetime.datetime.now()
  session.flush()

Also, if "id" is the primary key on your Executions class, you can
write the first line as:

  result = session.query(Executions).get(execution_id)


On Thu, Mar 7, 2013 at 1:58 AM, Michael Bayer  wrote:
> in the 0.7 series, you can't pass an ORM mapped class as the subject of the
> core update() construct:
>
> e = update(Executions).where(Executions.id==bindparam("execution_id")). \
> values(end_date=bindparam("now"))
>
> that statement will work as is if you just refer to the Table:
>
> e =
> update(Executions.__table__).where(Executions.id==bindparam("execution_id")).
> \
> values(end_date=bindparam("now"))
>
> also note that the indirection between bindparam("foo") and
> connection.execute(stmt, foo="some value") is not needed; you can embed
> literal values directly in the statement, and the Core will convert them to
> bound parameters (just use echo=True to see it in action):
>
> e = update(Executions).where(Executions.id==execution_id). \
> values(end_date=datetime.datetime.now())
>
> At the ORM level,  you can use query.update():
>
> session.query(Executions).filter(Executions.id==execution_id).update({"end_date":datetime.now()},
> synchronize_session=False)
>
>
> On Mar 6, 2013, at 8:06 PM, Mauricio de Abreu Antunes
>  wrote:
>
> So, i have read @StackOverflow some tips.
> There is a lot of people saying they have to make a query on the table and
> then update it. there is no way to upgrade without performing a query?!
>
> On Wednesday, March 6, 2013 6:17:35 PM UTC-3, Mauricio de Abreu Antunes
> wrote:
>>
>> Hello,
>>
>> I'm new to SQLAlchemy. Currently I'm using SQLAlchemy 0.7.1.
>> Reading the tutorial, I tried to write my codes like those examples but I
>> had no success working on it.
>>
>> Code is here:
>> https://gist.github.com/mauricioabreu/5103163
>>
>> Do I need to map the table Executions to execute an update expression on
>> it?
>>
>> Sorry if this is a very noob question.
>>
>> If you need more info about the problem let me know.
>
>
> --
> 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 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 sqlalchemy+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> 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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.

[sqlalchemy] Re: Many to many relationship with a composite key

[Christian]

Finally figured out how to do it : 

molecule2atom = Table(
  'molecule2atom',
  Base.metadata, 
  Column('molecule_db', Integer),
  Column('molecule_id', Integer),
  Column('atom_id', Integer, ForeignKey('atom.id')),
  ForeignKeyConstraint( 
('molecule_db', 'molecule_id'),
('molecule.db', 'molecule.id')  ),)

And add the relatiohship to one of the models as usual, for example, in 
Class Atom add:

molecules = relationship("Molecule", secondary=molecule2atom, backref="atoms")


On Thursday, March 7, 2013 10:16:24 AM UTC+1, Christian wrote:
>
> Let's say I have the following model:
>
> class Molecule(Base):
>db = Column(Integer, primary_key=True)
>id = Column(Integer, primary_key=True)
>data = Column(Integer)
> class Atom(Base):
>id = Column(Integer, primary_key=True)
>weight = Column(Integer)
>
> And I want to establish a *many-to-many* relationship between Molecule 
> and Atom, what would be the best way to do it? Notice that the *primary 
> key* of Molecule is *composite*.
>
> Thanks
>

-- 
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 http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.

[sqlalchemy] Many to many relationship with a composite key

[Christian]

Let's say I have the following model:

class Molecule(Base):
   db = Column(Integer, primary_key=True)
   id = Column(Integer, primary_key=True)
   data = Column(Integer)
class Atom(Base):
   id = Column(Integer, primary_key=True)
   weight = Column(Integer)

And I want to establish a *many-to-many* relationship between Molecule and 
Atom, what would be the best way to do it? Notice that the *primary key* of 
Molecule is *composite*.

Thanks

-- 
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 http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.