[sqlalchemy] Number of Open ConnectionPool connections

[Dave Ekhaus]

Hi All

Is there a way to determine the exact number of open db connections in 
SQLAlchemy's connection pool ?

Thanks
Dave

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

Re: [sqlalchemy] how can I search rows containing jsonb data on the basis of it's key>

[Jonathan Vanasco]

To add to Simon's resposne: SqlAlchemy has some postgres specific 
extensions to the dialect.

http://docs.sqlalchemy.org/en/latest/dialects/postgresql.html#sqlalchemy.dialects.postgresql.JSONB

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

Re: [sqlalchemy] Modeling single FK to multiple tables

[Mike Bayer]

On 03/17/2016 03:11 PM, Alex Hall wrote:

Hello all,
It seems like I can't go a day without running into some kind of wall.
This one is a conceptual one regarding foreign keys. I have to somehow
get the same FK column in table A pointing to IDs in tables B and C.


So a real foreign key constraint is not capable of this.  Repurposing a 
single column to occasionally point to one table or another is a famous 
anti-pattern I've spoke of much (warning, this is *extremely* old, but 
the general idea still holds):


http://techspot.zzzeek.org/2007/05/29/polymorphic-associations-with-sqlalchemy/


I have an updated version of all the various "polymoprhic association" 
examples in SQLAlchemy itself at 
http://docs.sqlalchemy.org/en/rel_1_0/orm/examples.html#module-examples.generic_associations. 
 This includes the "single column pointing to multiple tables" hack, as 
well as three other versions of the same business object geometry which 
preserve relational integrity within the schema design.




At one person's suggestion, I'm making classes for my tables, even
though I'm using automap. This is to let me stop doing a ton of joins,
making querying much easier... I hope! I'm defining all the foreign
keys between my tables manually. For instance:

class item(base):
  __tablename__ = "item"
  itm_id = Column(Integer, primary_key=True)
  vendornum = Column(String, ForeignKey(VENDR.PVVNNO))

class vendorTable(base):
  __tablename__ = "VENDR"
  PVVNNO = Column(String, primary_key=True)

If I've understood correctly, I'll now be able to say
item.vendornum.vendor_full_name
to get the vendor's full name for any item.

Here's the problem. Items have attachments, and attached text,
respectively held in attach and attach_text tables. Binding them to
items is a table called assignment. Assignment is pretty
straightforward, with an itm_id and an attachment id (att_id). The
trouble is that this att_id occurs in both attach and attach_text. I
can make att_id a foreign key to one table or the other, but I'm not
sure how to make it go to both tables.


the "generic_fk" example illustrates a pattern for working with this.

Getting this all to work with automap is another layer of complexity, 
you certainly want all of this part of it laid out before you reflect 
the rest of the database columns.





class assignmentTable(base):
  __tablename__ = "assignment"
  itm_id = Column(Integer, ForeignKey(item.itm_id))
  #the following column has to point to attach_text.att_id AS WELL
   att_id = Column(Integer, ForeignKey(attachment.att_id))
  seq_num = Column(Integer)
  asn_primary = Column(Integer, nullable=True)

class attachmentTable(base):
  __tablename__ = "attachment"
  att_id = Column(Integer, primary_key=True)

class attachmentTextTable(base):
  __tablename__ = "attach_text"
  att_id = Column(Integer, primary_key=True)



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

[sqlalchemy] How make update in Association Objects

[Mário Idival]

Hi,

I have a problema to make updates on tables with Association Objects


class Type(PrimaryKey, Base):
__tablename__ = "types"

name = Column(Unicode, nullable=False)
description = Column(Unicode)

users = relationship(User, secondary="type_users")

class TypeUser(Base):
__tablename__ = "type_users"

type_id = Column(Integer, ForeignKey("types.pk"), primary_key=True)
user_id = Column(Integer, ForeignKey("users.pk"), primary_key=True)

type = relationship(Type, backref=backref("type_users", lazy="dynamic"))
user = relationship(User, backref=backref("type", uselist=False))

class User(PrimaryKey, Base):
__tablename__ = "users"
# some irrelevant datas.

To save is easy...

user = User.query.get(pk)
type = Type.query.get(other_pk)

type.users.append(user)



How I can update o `type` of user??

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

Re: [sqlalchemy] Re: Database synchronisation

[Mike Bayer]

these are all documented functions and I didn't check the exact API.

Here is a full example:

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()


class A(Base):
__tablename__ = 'a'
id = Column(Integer, primary_key=True)
a = Column(Integer)
b = Column(Integer)

e = create_engine("sqlite://", echo=True)
Base.metadata.create_all(e)

session = Session(e)

session.add(A(a=1, b=2))
session.commit()

a1 = session.query(A).first()

mapper = inspect(a1).mapper

session.expire(a1, [col.key for col in mapper.primary_key])
make_transient(a1)

session.add(a1)
session.commit()



On 03/18/2016 03:54 AM, milan53064...@gmail.com wrote:

Hi Mike,
thanks for idea.
I am getting this exception :
session.expire(test_run, [col.key for col in mapper.primary_keys])
AttributeError: 'InstanceState' object has no attribute 'primary_keys'

Do I need setup something on the schema objects in order to get this
primary_keys?

right now I have this parameters on every relation in the objects in the
schema  :

lazy='joined',cascade="save-update, merge, delete, expunge")



Dňa štvrtok, 17. marca 2016 16:56:05 UTC+1 Mike Bayer napísal(-a):


how about:


from sqlalchemy import inspect

mapper = inspect(obj)

session.expire(obj, [col.key for col in mapper.primary_keys])
make_transient(obj)



On 03/17/2016 10:54 AM, milan5...@gmail.com  wrote:
 > Right now I am taking  test run object from local database,
then I
 > call expunge on that object (cascade is set to expunge).
 >
 >   then I put this object into this python method :
 >
 > def _reset_primary_keys(self, test_run):
 > make_transient(test_run)
 > test_run.ID= None
 >  if test_run.OrderNumber:
 > make_transient(test_run.OrderNumber)
 > test_run.OrderNumber.ID = None
 >
 >  for equipmentin test_run.TestEquipments:
 > make_transient(equipment)
 > equipment.ID= None
 >
 >  for trdin test_run.TestRunToDevs:
 > make_transient(trd)
 > trd.ID= None
 >
 >  if trd.TestedDut:
 > make_transient(trd.TestedDut)
 > trd.TestedDut.ID = None
 >
 >  for test_stepin trd.TestSteps:
 > make_transient(test_step)
 > test_step.ID= None
 >
 >  for test_resultin test_step.TestResults:
 > make_transient(test_result)
 > test_result.ID= None
 >
 >
 > This method reset all primary keys, so I can merge it into master
 > database which will generate new primary keys. Is there a better or
 > easier way how to do it?
 >
 > --
 > 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 https://groups.google.com/group/sqlalchemy
.
 > For more options, visit https://groups.google.com/d/optout
.

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


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

Re: [sqlalchemy] Re: Database synchronisation

[Mike Bayer]

how about:


from sqlalchemy import inspect

mapper = inspect(obj)

session.expire(obj, [col.key for col in mapper.primary_keys])
make_transient(obj)



On 03/17/2016 10:54 AM, milan53064...@gmail.com wrote:

Right now I am taking  test run object from local database, then I
call expunge on that object (cascade is set to expunge).

  then I put this object into this python method :

def _reset_primary_keys(self, test_run):
make_transient(test_run)
test_run.ID= None
 if test_run.OrderNumber:
make_transient(test_run.OrderNumber)
test_run.OrderNumber.ID= None

 for equipmentin test_run.TestEquipments:
make_transient(equipment)
equipment.ID= None

 for trdin test_run.TestRunToDevs:
make_transient(trd)
trd.ID= None

 if trd.TestedDut:
make_transient(trd.TestedDut)
trd.TestedDut.ID= None

 for test_stepin trd.TestSteps:
make_transient(test_step)
test_step.ID= None

 for test_resultin test_step.TestResults:
make_transient(test_result)
test_result.ID= None


This method reset all primary keys, so I can merge it into master
database which will generate new primary keys. Is there a better or
easier way how to do it?

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


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

Re: [sqlalchemy] how can I search rows containing jsonb data on the basis of it's key>

[Jonathan Vanasco]

On Friday, March 18, 2016 at 8:48:48 AM UTC-4, Simon King wrote:

I think you should be able to use something like:
>

 I find it easier to run raw sql to grab data like 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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Defining relationships (was: joins instead of filters remove attributes of results)

[Alex Hall]

If I define all the relationships as suggested, I could do
result.itm_id
or
result.attribute_value
and it would all work? Would I still need to specify, in my initial
query, things like
.filter(itemTable.itm_id = attachmentAssignmentTable.itm_id\
.filter(attachmentTable.att_id = attachmentAssignmentTable.att_id)

to get all attachments assigned to a given item? I'll read more about
this and play with it, but I wanted to ask here as well in case
someone sees that the design of this database will cause problems with
relationships.

On 3/16/16, Simon King  wrote:
> On Wed, Mar 16, 2016 at 1:07 PM, Alex Hall  wrote:
>
>> Hello all,
>> I'm running a different query than yesterday. Before, I had something
>> like:
>>
>> items = session.query(itemTable, attachmentTable, attachmentTextTable,
>> assignmentTable, attributeTable, attributeValueTable,
>> attributeValueAssignmentTable, vendorTable)\
>> .filter(attachmentTable.itm_id == itemTable.itm_id)\
>> #and so on, a bunch of .filter calls
>>
>> Then, in the loop iterating over the results, I could do this:
>>
>> for result in queryResults:
>>  itemID = result.item.itm_id
>>
>
> Because you wrote "session.query(itemTable, attachmentTable,
> attachmentTextTable)", the results that you get back from the query are
> like a tuple with 3 items corresponding to the 3 tables that you queries.
> result[0] would be the data from itemTable, result[1] is from
> attachmentTable, and result[2] is from attachmentTextTable. It also
> supports name-based access, which is why you were able to refer to
> "result.item" and "result.attach_text".
>
>
>>
>> Now that I'm using a bunch of outer left joins, that code is suddenly
>> not working. I get an error when I say
>> result.item.itm_id
>> AttributeError: 'item' object has no attribute 'item'
>>
>> The problem is that my query starts out with only one table passed to
>> session.query(), not all of them. Thus my result is of type 'item',
>> which is the table passed in. That would be okay, except that I need
>> to access values of other tables in the result, so even if I change
>> id = result.item.itm_id
>> to
>> id = result.itm_id
>> When I then say
>> description = result.attach_text.att_value
>> AttributeError: 'item' object has no attribute 'attach_text'
>>
>>
> First, it helps to be precise about your terminology. SQLAlchemy
> distinguishes between the object representing a table, and a class that you
> are mapping to that table. You've talked about using automapper in the
> past, so I assume you are passing a mapped class, not a table, to
> session.query().
>
> When you pass a single mapped class, the results you get back are instances
> of that class.
>
>
>
>> I know why it doesn't. What I don't know is how to get my query
>> results to hold all the information from all the tables, or how to
>> access it if they do already, but in a different way than before. My
>> new query is this:
>>
>> items = session.query(itemTable)\
>> .outerjoin(vendorTable, vendorTable.PVVNNO == itemTable.itm_vendornum)\
>> .outerjoin(assignmentTable, assignmentTable.itm_id == itemTable.itm_id)\
>> .filter(assignmentTable.att_id == attachmentTable.att_id)\
>> .outerjoin(attachmentTextTable, assignmentTable.att_id ==
>> attachmentTextTable.att_id)\
>> .outerjoin(attributeValueAssignmentTable,
>> attributeValueAssignmentTable.itm_id == itemTable.itm_id)\
>> .outerjoin(attributeTable, attributeTable.attr_id ==
>> attributeValueAssignmentTable.attr_id)\
>> .filter(attributeValueTable.attr_value_id ==
>> attributeValueAssignmentTable.attr_value_id)\
>> .yield_per(1000)
>>
>> I've also tried the same query, but with the first line changed to:
>> items = session.query(itemTable, attachmentTable, attachmentTextTable,
>> assignmentTable, attributeTable, attributeValueTable,
>> attributeValueAssignmentTable, vendorTable)\
>>
>> The problem here is that, while result.item.* works as expected, other
>> tables don't. For instance, result.attach_text.att_value yields an
>> AttributeError, 'None' type object has no attribute att_value.
>> Clearly, the other tables are in the result, but they're all None. I
>> expected something like that, and only added them back in to see if it
>> might help, but since I call query().outerjoin() I didn't think it
>> would work.
>>
>> I should note that I renamed most of the tables by assigning variables
>> to base.classes.tableName, which is why I'm using "itemTable" here,
>> but in getting attributes of results I use just "item". The 'item'
>> table is called 'item', but I assigned it to a variable called
>> 'itemTable', just for clarity in the script.
>>
>> Is there a way to access the values of a query like this? At the very
>> least, is there a way I can print out all the objects the result
>> object has, so I can work out what to do? Thanks for any help!
>>
>>
> Normally you would do this by setting up relationships between your mapped
> classes, so that when you access 

Re: [sqlalchemy] Re: Can I make bulk update through association proxy?

[Piotr Dobrogost]

On Saturday, February 27, 2016 at 10:26:53 PM UTC+1, Mike Bayer wrote:
>
>
> If I can just wrap this up, do i understand correctly that this would 
> the functionality of "joined eager loading", except applied to something 
> like an UPDATE..FROM ? 
>

I think so although I can't say I'm 100% sure as I'm not fluent in neither 
SA nor in SQL.

Jonathan Vanasco wrote earlier: 
>For this general task, I `flush` the session, use the `update` command on 
the target class -- filtering the >WHERE based on the parent object and 
 join conditions --  then I `expire_all` (because that update may have 
>affected in-memory object relations).  There is a small hit on reloading 
all the data, but I've found the `update` >to run considerably faster and 
make it worth-it.

This explains how to do this starting from the "opposite end". I mean the 
situation is that there's some parent_class <-relationship-> target_class 
and I want to update objects of target_class having initially objects of 
parent_class without explicitly selecting objects of target_class first as 
in Jonathan's solution.

Regards,
Piotr

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