[sqlalchemy] Using ibm_db to talk to AS400?

[Alex Hall]

Hi list,
Thus far, I've been using very basic database actions on a local
SQLite database as I've written the application. Now, though, I have
my GUI able to drive all CRUD operations, and I'm nearing the time
when I'll switch over to the actual system for which I've written the
app: the company's AS400, over a network.

I've installed ibm_db, but I also found something called ibm_db_sa,
claiming to be a package specifically for SQLAlchemy:
https://pypi.python.org/pypi/ibm_db_sa

The problem is, when I unpacked the file, it was just the ibm_db
package I'd already installed. I found an older version of ibm_db_sa,
but it was for Python 2.5 and is from 2013. What, if anything, more do
I need to get SQLAlchemy talking to a DB2 database than the ibm_db
package?

I'll also be talking to the database over a network; will that be a
problem? Right now it's within the same building, but eventually this
app will run on a virtual server hosted by Google and will still need
to be in constant contact with the 400. What should I know, if
anything, about remote servers with SQLAlchemy? Thanks in advance for
any help. I know DB2 isn't as popular as Oracle or MySQL, but I'm
hoping a few people here have used 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.

Re: [sqlalchemy] Recommended way to delete record given instance?

[Alex Hall]

Thanks for the suggestion, that seems to work perfectly! I'm not sure
why the docs made deletions sound more complex than that. Of course, I
may have just missed this.

On 2/6/16, Thierry Florac  wrote:
> Hi !
>
> Did you try "session.delete(myrecord)" ?
>
> Best regards,
> Thierry
>
>
> 2016-02-06 5:16 GMT+01:00 Alex Hall :
>
>> Hello all,
>> Another basic question: given an instance of a record, can I somehow
>> delete the record from a table? The longer story is this.
>>
>> I have my app, which lists tables on the left and the selected table's
>> rows on the right. Adding and editing rows both now work correctly
>> (though
>> I have yet to do serious validation--thanks for all your thoughts on
>> that,
>> by the way). Now I just need to get deletion working. However, because
>> the
>> user could be looking at any table, I can't do
>> table.query.filter(id==x).delete()
>> because I can't know what field to use as the search field in that query.
>> One table might have an ID field, another something different. I do,
>> however, have the entire object representing the row selected for
>> deletion.
>> Could I somehow issue a delete statement using that whole object, and
>> know
>> that this will work for any table? I hope I'm making sense--it's been a
>> long day. Thanks!
>>
>>
>> --
>> Alex Hall
>> Automatic Distributors, IT Department
>> ah...@autodist.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 https://groups.google.com/group/sqlalchemy.
>> For more options, visit https://groups.google.com/d/optout.
>>
>
>
>
> --
> http://www.imagesdusport.com -- http://www.ztfy.org
>
> --
> 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.

[sqlalchemy] Recommended way to delete record given instance?

[Alex Hall]

Hello all,
Another basic question: given an instance of a record, can I somehow delete the 
record from a table? The longer story is this.

I have my app, which lists tables on the left and the selected table's rows on 
the right. Adding and editing rows both now work correctly (though I have yet 
to do serious validation--thanks for all your thoughts on that, by the way). 
Now I just need to get deletion working. However, because the user could be 
looking at any table, I can't do
table.query.filter(id==x).delete()
because I can't know what field to use as the search field in that query. One 
table might have an ID field, another something different. I do, however, have 
the entire object representing the row selected for deletion. Could I somehow 
issue a delete statement using that whole object, and know that this will work 
for any table? I hope I'm making sense--it's been a long day. Thanks!


--
Alex Hall
Automatic Distributors, IT Department
ah...@autodist.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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Best practice for restricting input to columns?

[Alex Hall]

Hi all,
What's the recommended way to restrict input? For instance, I store a
phone number as an integer, and I need it to have 7, 10, or 11 digits.
In its getter, I format it so it looks good as a string, and in its
setter, I take the string the user inputs, strip only the integers,
and store those.

To inforce my length restriction, I have it set up something like this:

class Customer(base):
 _phone = Column("phone", Integer)

 @property
 def phone(self):
  #return pretty string

 @phone.setter
 def phone(self, value):
  #intsInPhone is a list of the integers in "value"
  if len(intsInPhone) not in [7, 10, 11]: #invalid length
   raise ValueError, "Phone numbers must be 7, 10, or 11 digits"

Is there a way I should do this instead? I had to make properties
anyway, since user-inputted values are coming from a dialog and will
always be strings--I had to convert them to the proper types, and
output formatted strings in some cases. I figured this was a good
place for a little validation while I was at 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.

Re: [sqlalchemy] Itterating over database row?

[Alex Hall]

Ah, getattr()… I forgot that one! It's a bit messy, but my tables now fill 
correctly:
recordData = getattr(record, table.columns.keys()[i].__str__()).__str__()

Maybe one day I'll make my table schemas (is that the right term for them?) 
itterable, but this works for now. Thanks for the help. I assumed subclasses of 
base would be iterable so one could iterate over individual rows, but now I 
know they aren't, it'll be easy enough to add that in the future as I get more 
complex tables.
> On Feb 4, 2016, at 18:51, Simon King  wrote:
> 
> 
>> On 4 Feb 2016, at 18:19, Alex Hall  wrote:
>> 
>> Hello all,
>> I'm setting my application up the way Simon suggested. I still use the
>> table object so I can get its name for displaying in one list, but the
>> other list (which holds the actual rows of the selected table) is
>> using the relevant subclass of base.
>> 
>> I use wx.ListCtrl to display everything, and when I load the rows into
>> their list, I first grab the column names from the table and set the
>> list to have those columns. Then, I want to itterate over each row,
>> extracting the value for the given column and displaying it. That's
>> the problem: these objects are not itterable. I tried to use
>> value(columnName), but that didn't work, even though the rows are
>> returned from a query. Here's my attempt thus far.
>> 
>> def updateSelectedIndex(self, evt):
>> """When the index changes in the list of tables, the list of records
>> is automatically populated with the newly-selected table's records."""
>> super(DBTablesListManager, self).updateSelectedIndex(evt)
>> self.records_listControl.ClearAll() #to avoid appending items--we
>> want each table to display only its own data
>> table = self.choices[self.selectedIndex][0] #this is the
>> sql.schema.Table object
>> self.records =
>> DBInterface.session.query(self.schemas[self.selectedIndex]).all()
>> #self.schema is the list of actual subclasses of base, not table
>> objects
>> #set the column names in the records list
>> i = 0
>> for column in table.columns:
>>  self.records_listControl.InsertColumn(i, column.name)
>>  i += 1
>> #add the data
>> i = 0
>> for record in self.records:
>>  recordData = record.value(table.columns[i]).__str__() #this line errors
>>  self.records_listControl.InsertStringItem(i, recordData)
>>  for j in range(1, len(record)):
>>   self.records_listControl.SetStringItem(i, j, record[j].__str__())
>> #this line would fail too
>>  i += 1
>> 
>> I'm either missing something obvious, or thinking about this all
>> wrong, because itterating over a row has to be a pretty common need.
>> Thanks in advance for any suggestions anyone has.
> 
> “record” is an instance of your mapped class. It doesn’t have any public 
> methods other than those which you define yourself (so for instance it 
> doesn’t have a “value()” method or an __iter__ method unless you define 
> them). If you want to get a named attribute from it, use the standard python 
> getattr function:
> 
>  recordData = getattr(record, colname)
> 
> Hope that helps,
> 
> Simon
> 
> -- 
> 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.



--
Alex Hall
Automatic Distributors, IT Department
942-6769, Ext. 629
ah...@autodist.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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Itterating over database row?

[Alex Hall]

Hello all,
I'm setting my application up the way Simon suggested. I still use the
table object so I can get its name for displaying in one list, but the
other list (which holds the actual rows of the selected table) is
using the relevant subclass of base.

I use wx.ListCtrl to display everything, and when I load the rows into
their list, I first grab the column names from the table and set the
list to have those columns. Then, I want to itterate over each row,
extracting the value for the given column and displaying it. That's
the problem: these objects are not itterable. I tried to use
value(columnName), but that didn't work, even though the rows are
returned from a query. Here's my attempt thus far.

 def updateSelectedIndex(self, evt):
  """When the index changes in the list of tables, the list of records
is automatically populated with the newly-selected table's records."""
  super(DBTablesListManager, self).updateSelectedIndex(evt)
  self.records_listControl.ClearAll() #to avoid appending items--we
want each table to display only its own data
  table = self.choices[self.selectedIndex][0] #this is the
sql.schema.Table object
  self.records =
DBInterface.session.query(self.schemas[self.selectedIndex]).all()
#self.schema is the list of actual subclasses of base, not table
objects
  #set the column names in the records list
  i = 0
  for column in table.columns:
   self.records_listControl.InsertColumn(i, column.name)
   i += 1
  #add the data
  i = 0
  for record in self.records:
   recordData = record.value(table.columns[i]).__str__() #this line errors
   self.records_listControl.InsertStringItem(i, recordData)
   for j in range(1, len(record)):
self.records_listControl.SetStringItem(i, j, record[j].__str__())
#this line would fail too
   i += 1

I'm either missing something obvious, or thinking about this all
wrong, because itterating over a row has to be a pretty common need.
Thanks in advance for any suggestions anyone has.

-- 
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] Modifying records across multiple modules?

[Alex Hall]

Oh! Okay, I think I get you now. The only reason I pass a list of
table objects is that I need the name, and I thought I needed the
table to query. If I can obtain the table name from the class
(.__tablename__), then this should work, with no need to mess with
table objects at all. I'll try it. Thanks for the help!

On 2/4/16, Simon King  wrote:
> You need to use the class when querying the database. SQLAlchemy will then
> return an instance of that class for each matching row:
>
>   customer = session.query(Customer).filter_by(name=u'Powersports
> Etc').first()
>   customer.name = u'New Name'
>   session.flush()
>
> The getAllClasses function I showed was meant to be a direct analog to your
> getAllTables function, which was returning the tables themselves. You were
> then passing those to session.query() to get a whole load of rows back, but
> those rows were not update-able. I was trying to suggest that if you
> replaced those Table objects with mapped classes (such as Customer), then
> when those classes were passed to session.query(), what you get back will
> be Customer *instances*.
>
> session.query(someTable) returns glorified tuples that can't be updated
> session.query(someMappedClass) returns instances of someMappedClass, that
> *can* be updated.
>
> Hope that makes sense,
>
> Simon
>
> On Thu, Feb 4, 2016 at 2:07 PM, Alex Hall  wrote:
>
>> This is where sqlalchemy gets murky for me. If I return all classes,
>> I'm not returning all *instances* of those classes. How, then, would I
>> update a given customer's record? The objects (c1 and c2, for
>> instance) were instantiated elsewhere, and my GUIManager module has no
>> knowledge of them. More generally, when I switch this over to our
>> AS400 and start querying, I won't have created any records at all.
>> Every record will be pulled from a table on the 400, and while I will
>> have a schema, I won't have any instances of that schema.
>>
>> As I think about this, it occurs to me that I should *create* the
>> record objects from the records. That is:
>>
>> for record in recordsList: #an array of session.query(someTable).all()
>>  myTempRecord = mySchemaClass(record)
>>  myTempRecord.property = newValue
>> #update the database
>>
>> Assuming GUIManager knows about mySchemaClass, would that approach
>> work? How I'd pass in a record and get back an instance of
>> mySchemaClass I'm not yet sure, but is this worth looking into more,
>> or am I on the wrong track?
>>
>> On 2/4/16, Simon King  wrote:
>> > getAllTables is returning Core Table objects. I suppose you could have
>> > a
>> > similar function which returns all mapped classes, something like this:
>> >
>> > def getAllClasses():
>> > return base.__subclasses__()
>> >
>> > (If your class hierarchy is more complicated you'd need a more
>> > sophisticated function there)
>> >
>> > Simon
>> >
>> > On Thu, Feb 4, 2016 at 12:32 PM, Alex Hall  wrote:
>> >
>> >> Yes, I'm using Declarative. I was following a great tutorial, then
>> >> realized it was way out of date. The one recommended on the forum I
>> >> found said to use Declarative, so I did.
>> >>
>> >> In DBInterface, I have this little function:
>> >>
>> >> def getAllTables():
>> >>  return base.metadata.tables
>> >> #end def getAllTables
>> >>
>> >> I then loop over that array, grabbing the value only and ignoring the
>> >> key. I just tried printing the type, as in:
>> >>
>> >> def getAllTables():
>> >>  for table in base.metadata.tables.values():
>> >>   print type(table)
>> >>  return base.metadata.tables
>> >> #end def getAllTables
>> >>
>> >> I got  Here's the loop that
>> >> generates the table list passed to my GUI (remember that this must be
>> >> an array of arrays):
>> >>
>> >>   tables = DBInterface.getAllTables()
>> >>   tablesList = []
>> >>   for table in tables.values():
>> >>tablesList.append([table])
>> >>   #end for
>> >>
>> >> Just to be sure, I stuck a "print type(table)" statement in that for
>> >> loop, and the objects are still sqlalchemy.sql.schema.Table objects.
>> >>
>> >> On 2/4/16, Simon King  wrote:
>> >> > SQLAlchemy has 2 main layers, the "Core" layer which deals 

Re: [sqlalchemy] Modifying records across multiple modules?

[Alex Hall]

This is where sqlalchemy gets murky for me. If I return all classes,
I'm not returning all *instances* of those classes. How, then, would I
update a given customer's record? The objects (c1 and c2, for
instance) were instantiated elsewhere, and my GUIManager module has no
knowledge of them. More generally, when I switch this over to our
AS400 and start querying, I won't have created any records at all.
Every record will be pulled from a table on the 400, and while I will
have a schema, I won't have any instances of that schema.

As I think about this, it occurs to me that I should *create* the
record objects from the records. That is:

for record in recordsList: #an array of session.query(someTable).all()
 myTempRecord = mySchemaClass(record)
 myTempRecord.property = newValue
#update the database

Assuming GUIManager knows about mySchemaClass, would that approach
work? How I'd pass in a record and get back an instance of
mySchemaClass I'm not yet sure, but is this worth looking into more,
or am I on the wrong track?

On 2/4/16, Simon King  wrote:
> getAllTables is returning Core Table objects. I suppose you could have a
> similar function which returns all mapped classes, something like this:
>
> def getAllClasses():
> return base.__subclasses__()
>
> (If your class hierarchy is more complicated you'd need a more
> sophisticated function there)
>
> Simon
>
> On Thu, Feb 4, 2016 at 12:32 PM, Alex Hall  wrote:
>
>> Yes, I'm using Declarative. I was following a great tutorial, then
>> realized it was way out of date. The one recommended on the forum I
>> found said to use Declarative, so I did.
>>
>> In DBInterface, I have this little function:
>>
>> def getAllTables():
>>  return base.metadata.tables
>> #end def getAllTables
>>
>> I then loop over that array, grabbing the value only and ignoring the
>> key. I just tried printing the type, as in:
>>
>> def getAllTables():
>>  for table in base.metadata.tables.values():
>>   print type(table)
>>  return base.metadata.tables
>> #end def getAllTables
>>
>> I got  Here's the loop that
>> generates the table list passed to my GUI (remember that this must be
>> an array of arrays):
>>
>>   tables = DBInterface.getAllTables()
>>   tablesList = []
>>   for table in tables.values():
>>tablesList.append([table])
>>   #end for
>>
>> Just to be sure, I stuck a "print type(table)" statement in that for
>> loop, and the objects are still sqlalchemy.sql.schema.Table objects.
>>
>> On 2/4/16, Simon King  wrote:
>> > SQLAlchemy has 2 main layers, the "Core" layer which deals with mostly
>> > database-level constructs, such as Tables, and the ORM layer, which is
>> > built on top of Core. With the ORM, you map your own classes onto the
>> > lower-level Tables, then work with instances of those classes. You
>> > appear
>> > to be using the declarative system to define your classes, so
>> > SQLAlchemy
>> > will be creating the associated Table instances for you under the hood.
>> >
>> > In your example, Customer is an ORM-level class. Somewhere, there will
>> be a
>> > construct representing the Core-level Table that the Customer class is
>> > mapped to. (It's probably available at Customer.__table__, but also in
>> > other places).
>> >
>> > When you say that "self.choices" contains table objects, I suspect that
>> > those are the Core-level Table instances. When you query using those,
>> > you
>> > don't get Customer objects back. You need to query using the Customer
>> class
>> > instead. Can you get the Customer class into your self.choices array,
>> > either instead of the table, or as well as? Where are you getting the
>> > object that you are putting in the first element of each of the
>> > self.choices list?
>> >
>> > Simon
>> >
>> > On Thu, Feb 4, 2016 at 11:34 AM, Alex Hall  wrote:
>> >
>> >> It's all from a GUI, so it's something like this (my code isn't in
>> >> front
>> >> of me):
>> >> DBInterface.session"query(self.choices[self.selectedIndex][0]).all()
>> >> Choices is a 2D array where the first (0th) element of each sub-array
>> >> is
>> >> a
>> >> table object. The query works, because I get the records as expected
>> >> and
>> >> can display them or inspect them. I just can't modify them for some
>> >> reason.
>> >> A

Re: [sqlalchemy] Modifying records across multiple modules?

[Alex Hall]

I was re-reading your email, and realized I forgot to answer something
(sorry, it's early here). It looks like I am indeed passing the table
to session.query, which works, but then I'm trying to change
attributes of the table, which doesn't (of course). Could I do this?

#DBDefinitions.py
import sqlalchemy
from sqlalchemy.ext.declarative import declarative_base
base = declarative_base()

class Customer(base):
 ..

class MyOtherTable(base):
 ..

#DDInterface.py
import DBDefinitions
import sqlalchemy

#set up the database--session, engine, etc

DBDefinitions.base.metadata.create_all(myEngine) #should create the
empty tables, right?

#main.py
from DBDefinitions import *
import DBInterface

c1 = Customer(...)
c2 = Customer(...)
mt1 = MyOtherTable(...)
if DBInterface.session.query(DBDefinitions.Customer).count == 0:
DBInterface.session.add_all([c1, c2])


On 2/4/16, Alex Hall  wrote:
> Yes, I'm using Declarative. I was following a great tutorial, then
> realized it was way out of date. The one recommended on the forum I
> found said to use Declarative, so I did.
>
> In DBInterface, I have this little function:
>
> def getAllTables():
>  return base.metadata.tables
> #end def getAllTables
>
> I then loop over that array, grabbing the value only and ignoring the
> key. I just tried printing the type, as in:
>
> def getAllTables():
>  for table in base.metadata.tables.values():
>   print type(table)
>  return base.metadata.tables
> #end def getAllTables
>
> I got  Here's the loop that
> generates the table list passed to my GUI (remember that this must be
> an array of arrays):
>
>   tables = DBInterface.getAllTables()
>   tablesList = []
>   for table in tables.values():
>tablesList.append([table])
>   #end for
>
> Just to be sure, I stuck a "print type(table)" statement in that for
> loop, and the objects are still sqlalchemy.sql.schema.Table objects.
>
> On 2/4/16, Simon King  wrote:
>> SQLAlchemy has 2 main layers, the "Core" layer which deals with mostly
>> database-level constructs, such as Tables, and the ORM layer, which is
>> built on top of Core. With the ORM, you map your own classes onto the
>> lower-level Tables, then work with instances of those classes. You appear
>> to be using the declarative system to define your classes, so SQLAlchemy
>> will be creating the associated Table instances for you under the hood.
>>
>> In your example, Customer is an ORM-level class. Somewhere, there will be
>> a
>> construct representing the Core-level Table that the Customer class is
>> mapped to. (It's probably available at Customer.__table__, but also in
>> other places).
>>
>> When you say that "self.choices" contains table objects, I suspect that
>> those are the Core-level Table instances. When you query using those, you
>> don't get Customer objects back. You need to query using the Customer
>> class
>> instead. Can you get the Customer class into your self.choices array,
>> either instead of the table, or as well as? Where are you getting the
>> object that you are putting in the first element of each of the
>> self.choices list?
>>
>> Simon
>>
>> On Thu, Feb 4, 2016 at 11:34 AM, Alex Hall  wrote:
>>
>>> It's all from a GUI, so it's something like this (my code isn't in front
>>> of me):
>>> DBInterface.session"query(self.choices[self.selectedIndex][0]).all()
>>> Choices is a 2D array where the first (0th) element of each sub-array is
>>> a
>>> table object. The query works, because I get the records as expected and
>>> can display them or inspect them. I just can't modify them for some
>>> reason.
>>> As I said, though, I'm new to this package so am likely missing an
>>> obvious
>>> step, or have something set up very wrong.
>>>
>>> Sent from my iPhone
>>>
>>> > On Feb 3, 2016, at 16:18, Simon King  wrote:
>>> >
>>> > OK, so you’re not actually getting Customer objects back from your
>>> query. What does your call to session.query() look like? For this to
>>> work,
>>> it really ought to be something like “session.query(Customer)”. I
>>> suspect
>>> you are doing something like “session.query(Customer.id, Customer.name,
>>> …)”
>>> instead.
>>> >
>>> > Simon
>>> >
>>> >> On 3 Feb 2016, at 17:43, Alex Hall  wrote:
>>> >>
>>> >> I'm on the Gmail site, so am not sure I can reply in-line. Sorry.
>>> >>
>>> >> This 

Re: [sqlalchemy] Modifying records across multiple modules?

[Alex Hall]

Yes, I'm using Declarative. I was following a great tutorial, then
realized it was way out of date. The one recommended on the forum I
found said to use Declarative, so I did.

In DBInterface, I have this little function:

def getAllTables():
 return base.metadata.tables
#end def getAllTables

I then loop over that array, grabbing the value only and ignoring the
key. I just tried printing the type, as in:

def getAllTables():
 for table in base.metadata.tables.values():
  print type(table)
 return base.metadata.tables
#end def getAllTables

I got  Here's the loop that
generates the table list passed to my GUI (remember that this must be
an array of arrays):

  tables = DBInterface.getAllTables()
  tablesList = []
  for table in tables.values():
   tablesList.append([table])
  #end for

Just to be sure, I stuck a "print type(table)" statement in that for
loop, and the objects are still sqlalchemy.sql.schema.Table objects.

On 2/4/16, Simon King  wrote:
> SQLAlchemy has 2 main layers, the "Core" layer which deals with mostly
> database-level constructs, such as Tables, and the ORM layer, which is
> built on top of Core. With the ORM, you map your own classes onto the
> lower-level Tables, then work with instances of those classes. You appear
> to be using the declarative system to define your classes, so SQLAlchemy
> will be creating the associated Table instances for you under the hood.
>
> In your example, Customer is an ORM-level class. Somewhere, there will be a
> construct representing the Core-level Table that the Customer class is
> mapped to. (It's probably available at Customer.__table__, but also in
> other places).
>
> When you say that "self.choices" contains table objects, I suspect that
> those are the Core-level Table instances. When you query using those, you
> don't get Customer objects back. You need to query using the Customer class
> instead. Can you get the Customer class into your self.choices array,
> either instead of the table, or as well as? Where are you getting the
> object that you are putting in the first element of each of the
> self.choices list?
>
> Simon
>
> On Thu, Feb 4, 2016 at 11:34 AM, Alex Hall  wrote:
>
>> It's all from a GUI, so it's something like this (my code isn't in front
>> of me):
>> DBInterface.session"query(self.choices[self.selectedIndex][0]).all()
>> Choices is a 2D array where the first (0th) element of each sub-array is
>> a
>> table object. The query works, because I get the records as expected and
>> can display them or inspect them. I just can't modify them for some
>> reason.
>> As I said, though, I'm new to this package so am likely missing an
>> obvious
>> step, or have something set up very wrong.
>>
>> Sent from my iPhone
>>
>> > On Feb 3, 2016, at 16:18, Simon King  wrote:
>> >
>> > OK, so you’re not actually getting Customer objects back from your
>> query. What does your call to session.query() look like? For this to
>> work,
>> it really ought to be something like “session.query(Customer)”. I suspect
>> you are doing something like “session.query(Customer.id, Customer.name,
>> …)”
>> instead.
>> >
>> > Simon
>> >
>> >> On 3 Feb 2016, at 17:43, Alex Hall  wrote:
>> >>
>> >> I'm on the Gmail site, so am not sure I can reply in-line. Sorry.
>> >>
>> >> This is a basic table class, like
>> >> class Customer(base):
>> >> __tablename__ = "customers"
>> >> name = Column(String(50)),
>> >> ...
>> >>
>> >> When I print the type:
>> >> 
>> >> And repr():
>> >> (2, u'Powersports Etc', 5554443210L, u'ahall+dbte...@autodist.com',
>> True)
>> >>
>> >>
>> >>> On 2/3/16, Simon King  wrote:
>> >>>> On Wed, Feb 3, 2016 at 3:54 PM, Alex Hall 
>> >>>> wrote:
>> >>>>
>> >>>> Hello list,
>> >>>> I'm new to SQLAlchemy, but not to Python. I have an application
>> >>>> that's
>> >>>> coming together, and relies on SQLAlchemy to talk to a database for
>> >>>> many of the app's functions. Listing tables, listing records,
>> >>>> updating
>> >>>> records, pulling records for internal use, and so on.
>> >>>>
>> >>>> My app is working, but so far I've been writing the framework and
>> >>>> GUI
>> >>>> with a bit of SQLite just

Re: [sqlalchemy] Modifying records across multiple modules?

[Alex Hall]

It's all from a GUI, so it's something like this (my code isn't in front of me):
DBInterface.session"query(self.choices[self.selectedIndex][0]).all()
Choices is a 2D array where the first (0th) element of each sub-array is a 
table object. The query works, because I get the records as expected and can 
display them or inspect them. I just can't modify them for some reason. As I 
said, though, I'm new to this package so am likely missing an obvious step, or 
have something set up very wrong. 

Sent from my iPhone

> On Feb 3, 2016, at 16:18, Simon King  wrote:
> 
> OK, so you’re not actually getting Customer objects back from your query. 
> What does your call to session.query() look like? For this to work, it really 
> ought to be something like “session.query(Customer)”. I suspect you are doing 
> something like “session.query(Customer.id, Customer.name, …)” instead.
> 
> Simon
> 
>> On 3 Feb 2016, at 17:43, Alex Hall  wrote:
>> 
>> I'm on the Gmail site, so am not sure I can reply in-line. Sorry.
>> 
>> This is a basic table class, like
>> class Customer(base):
>> __tablename__ = "customers"
>> name = Column(String(50)),
>> ...
>> 
>> When I print the type:
>> 
>> And repr():
>> (2, u'Powersports Etc', 5554443210L, u'ahall+dbte...@autodist.com', True)
>> 
>> 
>>> On 2/3/16, Simon King  wrote:
>>>> On Wed, Feb 3, 2016 at 3:54 PM, Alex Hall  wrote:
>>>> 
>>>> Hello list,
>>>> I'm new to SQLAlchemy, but not to Python. I have an application that's
>>>> coming together, and relies on SQLAlchemy to talk to a database for
>>>> many of the app's functions. Listing tables, listing records, updating
>>>> records, pulling records for internal use, and so on.
>>>> 
>>>> My app is working, but so far I've been writing the framework and GUI
>>>> with a bit of SQLite just to check that things are working how I want.
>>>> Now, though, I'm getting into my first "real" user-facing database
>>>> task: taking the values from a dialog and updating a record according
>>>> to those values. Thus far, I'm having no luck.
>>>> 
>>>> My organization for now is DBInterface.py, which holds all my table
>>>> definitions, database details, and my base, session, and engine
>>>> objects. I can hear the groans from here; I do plan to move the table
>>>> definitions into a module of their own at some point, there simply
>>>> hasn't been a need yet. GUIManager.py imports DBInterface, and handles
>>>> all the GUI stuff, as the name suggests. It's where, eventually, I'll
>>>> take user input and use it to update records by calling functions from
>>>> DBInterface. That's the problem, though. In GUIManager, I have a
>>>> simple test:
>>>> 
>>>> self.records[self.selectedRecordIndex].name="test name" #records is
>>>> the list of objects returned by querying the current table
>>>> 
>>>> Which errors out every time:
>>>> AttributeError: can't set attribute
>>>> 
>>>> (Yes, "name" is an attribute name of my Customer class.) From what
>>>> I've read thus far, updating records is as easy as modifying their
>>>> properties and calling session.commit(). That isn't working, though. I
>>>> imagine the problem is that the records in a query aren't the same as
>>>> the records originally created, and modify/commit only works on those
>>>> originals. I'm not sure if that's right, though. If it is, how could I
>>>> modify the originals, given that I run a new query each time the user
>>>> selects a table name in my GUI's list of names? If I'm wrong, how
>>>> would I update the record attributes and save the changes back to the
>>>> database? I think I'm picturing this whole thing wrong, to be honest.
>>>> Thanks for any help, and please let me know if I need to provide more
>>>> code or context.
>>> Is your query against a single mapped class, or is it against some set of
>>> columns? What do you get if you write:
>>> 
>>> print type(self.records[self.selectedRecordIndex])
>>> print repr(self.records[self.selectedRecordIndex])
>>> 
>>> ...at the point where you are trying to set the name?
>>> 
>>> Simon
>>> 
>>> --
>>> You received this

Re: [sqlalchemy] Modifying records across multiple modules?

[Alex Hall]

I'm on the Gmail site, so am not sure I can reply in-line. Sorry.

This is a basic table class, like
class Customer(base):
 __tablename__ = "customers"
 name = Column(String(50)),
 ...

When I print the type:

And repr():
(2, u'Powersports Etc', 5554443210L, u'ahall+dbte...@autodist.com', True)


On 2/3/16, Simon King  wrote:
> On Wed, Feb 3, 2016 at 3:54 PM, Alex Hall  wrote:
>
>> Hello list,
>> I'm new to SQLAlchemy, but not to Python. I have an application that's
>> coming together, and relies on SQLAlchemy to talk to a database for
>> many of the app's functions. Listing tables, listing records, updating
>> records, pulling records for internal use, and so on.
>>
>> My app is working, but so far I've been writing the framework and GUI
>> with a bit of SQLite just to check that things are working how I want.
>> Now, though, I'm getting into my first "real" user-facing database
>> task: taking the values from a dialog and updating a record according
>> to those values. Thus far, I'm having no luck.
>>
>> My organization for now is DBInterface.py, which holds all my table
>> definitions, database details, and my base, session, and engine
>> objects. I can hear the groans from here; I do plan to move the table
>> definitions into a module of their own at some point, there simply
>> hasn't been a need yet. GUIManager.py imports DBInterface, and handles
>> all the GUI stuff, as the name suggests. It's where, eventually, I'll
>> take user input and use it to update records by calling functions from
>> DBInterface. That's the problem, though. In GUIManager, I have a
>> simple test:
>>
>> self.records[self.selectedRecordIndex].name="test name" #records is
>> the list of objects returned by querying the current table
>>
>> Which errors out every time:
>> AttributeError: can't set attribute
>>
>> (Yes, "name" is an attribute name of my Customer class.) From what
>> I've read thus far, updating records is as easy as modifying their
>> properties and calling session.commit(). That isn't working, though. I
>> imagine the problem is that the records in a query aren't the same as
>> the records originally created, and modify/commit only works on those
>> originals. I'm not sure if that's right, though. If it is, how could I
>> modify the originals, given that I run a new query each time the user
>> selects a table name in my GUI's list of names? If I'm wrong, how
>> would I update the record attributes and save the changes back to the
>> database? I think I'm picturing this whole thing wrong, to be honest.
>> Thanks for any help, and please let me know if I need to provide more
>> code or context.
>>
>>
> Is your query against a single mapped class, or is it against some set of
> columns? What do you get if you write:
>
>   print type(self.records[self.selectedRecordIndex])
>   print repr(self.records[self.selectedRecordIndex])
>
> ...at the point where you are trying to set the name?
>
> Simon
>
> --
> 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.

[sqlalchemy] Modifying records across multiple modules?

[Alex Hall]

Hello list,
I'm new to SQLAlchemy, but not to Python. I have an application that's
coming together, and relies on SQLAlchemy to talk to a database for
many of the app's functions. Listing tables, listing records, updating
records, pulling records for internal use, and so on.

My app is working, but so far I've been writing the framework and GUI
with a bit of SQLite just to check that things are working how I want.
Now, though, I'm getting into my first "real" user-facing database
task: taking the values from a dialog and updating a record according
to those values. Thus far, I'm having no luck.

My organization for now is DBInterface.py, which holds all my table
definitions, database details, and my base, session, and engine
objects. I can hear the groans from here; I do plan to move the table
definitions into a module of their own at some point, there simply
hasn't been a need yet. GUIManager.py imports DBInterface, and handles
all the GUI stuff, as the name suggests. It's where, eventually, I'll
take user input and use it to update records by calling functions from
DBInterface. That's the problem, though. In GUIManager, I have a
simple test:

self.records[self.selectedRecordIndex].name="test name" #records is
the list of objects returned by querying the current table

Which errors out every time:
AttributeError: can't set attribute

(Yes, "name" is an attribute name of my Customer class.) From what
I've read thus far, updating records is as easy as modifying their
properties and calling session.commit(). That isn't working, though. I
imagine the problem is that the records in a query aren't the same as
the records originally created, and modify/commit only works on those
originals. I'm not sure if that's right, though. If it is, how could I
modify the originals, given that I run a new query each time the user
selects a table name in my GUI's list of names? If I'm wrong, how
would I update the record attributes and save the changes back to the
database? I think I'm picturing this whole thing wrong, to be honest.
Thanks for any help, and please let me know if I need to provide more
code or context.

-- 
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] Re: history_meta.py: foreign keys in history table, and relationships

[Alex Fraser]

On Tuesday, 25 August 2015 11:23:51 UTC+10, Alex Fraser wrote:
>
> Is there a declarative way to add foreign keys to the history table when 
> using history_meta.py? In the app I'm making the user can view old versions 
> of a document, and I want to make sure e.g. the user that created the old 
> version can't be deleted while their old versions exist.
>

For what it's worth, we have solved this by checking for a 'version' flag 
in the info attribute of foreign key constraints:

diff --git a/history_meta.py b/history_meta.py
index f1e308e..da72f27 100644
--- a/history_meta.py
+++ b/history_meta.py
@@ -117,6 +117,16 @@ def _history_mapper(local_mapper):
 *cols,
 schema=local_mapper.local_table.schema
 )
+
+for fk in local_mapper.local_table.foreign_key_constraints:
+log.debug(
+"Duplicating foreign key for history table: %s, fk: %s",
+local_mapper.local_table, fk)
+if 'version' in fk.info and fk.info['version']:
+fk_new = fk.copy()
+fk_new.info['history_copy'] = fk
+table.append_constraint(fk_new)
+
 else:
 # single table inheritance.  take any additional columns that may 
have
 # been added and add them to the history table.

Let me know if this is too dodgy :)

Cheers,
Alex

-- 
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.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] history_meta.py: foreign keys in history table, and relationships

[Alex Fraser]

Hi Mike,

Thanks for your help with my other problem. I have a couple of other 
questions / a wishlist:

Is there a declarative way to add foreign keys to the history table when 
using history_meta.py? In the app I'm making the user can view old versions 
of a document, and I want to make sure e.g. the user that created the old 
version can't be deleted while their old versions exist.

Finally, it would be nice if other attributes could be carried over to the 
history mapper. If they had a similar API, the history objects could be 
worked with (e.g. serialised to JSON) in the same way as the non-history 
objects. Perhaps this could be done by carrying over methods decorated with 
@declared_attr? Something like this (untested):

def test_history_relationships(self):
class User(self.Base):
__tablename__ = 'appuser'
id = Column(Integer, primary_key=True)
name = Column(String)

class Document(Versioned, self.Base):
__tablename__ = 'document'
id = Column(Integer, primary_key=True)
user_id = Column(Integer, ForeignKey("appuser.id"))
contents = Column(String)

@declared_attr
def user(cls):
return relationship(AppUser)

self.create_tables()

user = User(name="Fred")
self.session.flush()

document = Document(user_id=user.id)
document.contents = "foo"
self.session.flush()

document.contents = "bar"
self.session.flush()

DocumentHistory = Document.__history_mapper__.class_
v2 = self.session.query(Document).one()
v1 = self.session.query(DocumentHistory).one()
self.assertEqual(v2.user.name, "Fred")
self.assertTrue(hasattr(v1, 'user'))
self.assertEqual(v1.user.name, "Fred")

Although it would be nice if it worked with other attributes too, such as 
hybrid properties. Maybe they would need to be specially decorated for use 
only by the history mapper, so that relationships weren't made 
bidirectional.

Currently I'm doing this by manually querying the related tables - but I 
feel this is prone to error, because code for doing the join is duplicated. 
E.g. instead of doing *user_name = v1.user.name*, I do:

user = self.session.query(User).filter_by(id=v1.user_id).one()
user_name = user.name

I feel like I'm doing something wrong; any suggestions for improving this 
would be appreciated.

Cheers,
Alex

SQLAlchemy 1.0.8, Python 3, PostgreSQL 9.4

-- 
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.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] history_meta.py: IndexError when creating a new object

[Alex Fraser]

Hi Mike,

On Tuesday, 25 August 2015 00:56:03 UTC+10, Michael Bayer wrote:
>
> great, thank you, these issues are both repaired as of 
> d57e5edbcdf915168c613, the diff for his section is:
>

Thanks very much! That works perfectly. By the way, you asked if I had 
changed *history_meta.py* at all: I added some properties on *Versioned* to 
allow conditional versioning:

--- src/app/server/history_meta_orig.py 2015-08-25 10:21:57.555888454 +1000
+++ src/app/server/history_meta.py  2015-08-25 10:23:08.342461090 +1000
@@ -157,6 +157,28 @@
 return mp
 return map
 
+@property
+def version_on_update(self):
+try:
+return self._version_on_update
+except AttributeError:
+return True
+
+@version_on_update.setter
+def version_on_update(self, enabled):
+self._version_on_update = enabled
+
+@property
+def version_on_delete(self):
+try:
+return self._version_on_delete
+except AttributeError:
+return True
+
+@version_on_delete.setter
+def version_on_delete(self, enabled):
+self._version_on_delete = enabled
+
 
 def versioned_objects(iter):
 for obj in iter:
@@ -249,7 +271,8 @@
 @event.listens_for(session, 'before_flush')
 def before_flush(session, flush_context, instances):
 for obj in versioned_objects(session.dirty):
-create_version(obj, session)
+if obj.version_on_update:
+create_version(obj, session)
 for obj in versioned_objects(session.deleted):
-create_version(obj, session, deleted=True)
-
+if obj.version_on_delete:
+create_version(obj, session, deleted=True)

The use case being that I only want to create a new version if the user 
saving an object is different from the user who last saved it. I also 
thought about adding an explicit *Versioned.create_version* method, but 
this suffices for now.

Cheers,
Alex

-- 
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.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] history_meta.py: IndexError when creating a new object

[Alex Fraser]

Hi Michael,

On Monday, 24 August 2015 12:31:20 UTC+10, Michael Bayer wrote:
>
> yes and no.  Yes, if there's no "added" history, that should be skipped as 
> you're doing, but no in that that particular line of code is not called if 
> an object is being saved for the first time, only on an update, and then 
> only if that attribute actually had a change, which you are saying this 
> attribute did not.
>
> so if you can please share: 1. a mapping and an exact usage that 
> illustrates how this is happening 2. what exact version of SQLAlchemy are 
> you using and 3. have you modified the history_meta.py recipe in any way?
>

Oops, sorry for leaving that out the first time. I'm using SQLAlchemy 1.0.8 
on Python 3. I have modified *history_meta.py* for my app, but if I revert 
the changes the problem is still there.

It turns out that the problem happens when the session gets flushed twice. 
For example:

document = Document()
self.session.add(document)
self.session.flush()
document.name = 'Foo'
self.session.flush()
# IndexError: tuple index out of range

In my app I think I need to call flush several times, because I'm building 
a tree and I need to know the parent IDs. Perhaps I could rearrange my code 
to not need to do this.

Additionally, if I set a different name for a column than the attribute 
name (as shown below), the value doesn't get propagated to the history 
table.

description_ = Column('description', String, nullable=True)

See here for unit tests for both of these issues. It uses an unmodified 
*history_meta.py*.

https://github.com/z0u/satest/blob/master/test_versioned.py

Cheers,
Alex

-- 
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.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] history_meta.py: IndexError when creating a new object

[Alex Fraser]

I'm using the separate table versioning code from *history_meta.py*. When I 
try to save a new object, I get this:

  [...]
  File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/orm/session.py", 
line 1282, in _autoflush
  self.flush()
  File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/orm/session.py", 
line 2004, in flush
self._flush(objects)
  File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/orm/session.py", 
line 2031, in _flush
self.dispatch.before_flush(self, flush_context, objects)
  File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/event/attr.py", 
line 218, in __call__
fn(*args, **kw)
  File "/usr/share/foo/app/server/history_meta.py", line 290, in 
before_flush
create_version(obj, session)
  File "/usr/share/foo/app/server/history_meta.py", line 256, in 
create_version
attr[hist_col.key] = a[0]
IndexError: tuple index out of range

I added some logging, and it seems that this happens for attributes that I 
haven't assigned a value to, and which have a default value of *None*. - 
because in this line in *create_version*, *a*, *u* and *d* are all empty:

a, u, d = attributes.get_history(obj, prop.key)

If I actually assign *None* to the attribute before saving, then it works 
fine. It also works if I apply this patch to *history_meta.py*:

diff --git a/src/app/server/history_meta.py b/src/app/server/history_meta.py
index 02b54e0..17cd87f 100644
--- a/src/app/server/history_meta.py
+++ b/src/app/server/history_meta.py
@@ -240,10 +240,13 @@ def create_version(obj, session, deleted=False):
 obj_changed = True
 elif u:
 attr[hist_col.key] = u[0]
-else:
+elif a:
 # if the attribute had no value.
 attr[hist_col.key] = a[0]
 obj_changed = True
+else:
+# The attribute has never had a value
+attr[hist_col.key] = None
 
 if not obj_changed:
 # not changed, but we have relationships.  OK

Is this a reasonable thing to do?

Cheers,
Alex

-- 
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.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Trouble with AbstractConcreteBase and aliased columns

[Alex Grönholm]

Thanks for the response! The solution looks too hairy for my tastes, and 
I can manage with what I currently have. Good to know it's possible though!


10.07.2015, 02:25, Mike Bayer kirjoitti:



On 7/9/15 12:17 PM, Alex Grönholm wrote:
Thanks. What about my other question? Is it possible to have two 
layers of classes (Document and ContactDocument) mapped to 
polymorphic unions?
OK.   So, AbstractConcreteBase struggles hard against Declarative 
wanting to map things.   So as far as how to get it to take effect 
multiple times in a hierarchy, with ABC itself it would require more 
weird class tricks, of the kind where we always have to see, "does 
class A declare "_x" or is it inheriting it?" which is why declarative 
has gotten so crazy compared to its innocent beginnings. This 
might be something that can be added but I'd have to think about it, 
ABC is still pretty brittle overall.


I can have you just use the API that ABC uses internally. Concrete 
mappings in classical SQLA were really easy, because we had those 
Table objects up front before we did anything with the classes.  With 
declarative we don't have that because it makes the table/mapper at 
the same time.   This architecture has opened up a lot in 1.0 but 
still doesn't make this kind of thing that simple. But the main thing 
that was added probably in 0.8 or 0.9 to make this possible was a way 
to attach the "base" underneath a concrete mapper after the subclass 
is set up.  Instead of ABC doing that for us, we can do it the "old" 
way manually, using polymophic_union() in the old way and calling 
mapper(), just using one newish API function so that we can still use 
declarative for the subclasses.  It's one private API function at the 
moment. It maps and creates the queries, so that should be pretty much 
it - we can try to make these API patterns more accessible.  Let me 
know if this works more fully.


from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.ext.declarative.api import declared_attr
from sqlalchemy.orm import configure_mappers, mapper, Session
from sqlalchemy.sql.schema import Column, ForeignKey
from sqlalchemy.sql.sqltypes import Date, String, Integer

Base = declarative_base()


class Company(Base):
__tablename__ = 'companies'
id = Column(Integer, primary_key=True)


class Document(Base):
date = Column(Date)
documentType = Column(String)

__abstract__ = True
__mapper_args__ = {"concrete": True}


class SomeDocument(Document):
"""extends Document but not ContactDocument """
__tablename__ = 'some_document'

id = Column(Integer, primary_key=True)


class ContactDocument(Document):
contactPersonName = Column(String)
salesPersonName = Column(String)
sendMethod = Column(String)

@declared_attr
def company_id(self):
return Column(ForeignKey('companies.id'))

__abstract__ = True


class Offer(ContactDocument):
__tablename__ = 'offers'

id = Column(Integer, primary_key=True)


class SalesOrder(ContactDocument):
__tablename__ = 'orders'

id = Column(Integer, primary_key=True)


from sqlalchemy.orm.util import polymorphic_union

document_pjoin = polymorphic_union({
'offer': Offer.__table__,
'orders': SalesOrder.__table__,
'somedocument': SomeDocument.__table__
}, 'type', 'd_pjoin'
)

contact_document_pjoin = polymorphic_union({
'offer': Offer.__table__,
'orders': SalesOrder.__table__,
}, 'type', 'cd_pjoin'
)

md = mapper(
Document,
document_pjoin,
polymorphic_on=document_pjoin.c.type,
concrete=True)
mcd = mapper(
ContactDocument,
contact_document_pjoin,
inherits=md,
polymorphic_on=contact_document_pjoin.c.type,
concrete=True)

# AbstractConcreteBase does this part by looking at cls.__subclasses__()
Offer.__mapper__._set_concrete_base(mcd)
SalesOrder.__mapper__._set_concrete_base(mcd)
SomeDocument.__mapper__._set_concrete_base(md)

configure_mappers()
session = Session()
print "---"
print session.query(Document)
print "---"
print session.query(ContactDocument)






torstai 9. heinäkuuta 2015 18.31.36 UTC+3 Michael Bayer kirjoitti:

Thanks for reporting.   Issue

https://bitbucket.org/zzzeek/sqlalchemy/issues/3480/abstractconcretebase-regression-with
is created, create the Column objects with an explicit key for now:


class Document(object):
date = Column(Date)
documentType = Column('documenttype', String, key="documentType")


class ContactDocument(AbstractConcreteBase, Base, Document):
contactPersonName = Column('contactpersonname', String,
key="contactPersonName")
salesPersonName = Column(String)

Re: [sqlalchemy] Trouble with AbstractConcreteBase and aliased columns

[Alex Grönholm]

Thanks. What about my other question? Is it possible to have two layers of 
classes (Document and ContactDocument) mapped to polymorphic unions?

torstai 9. heinäkuuta 2015 18.31.36 UTC+3 Michael Bayer kirjoitti:
>
>  Thanks for reporting.   Issue 
> https://bitbucket.org/zzzeek/sqlalchemy/issues/3480/abstractconcretebase-regression-with
>  
> is created, create the Column objects with an explicit key for now:
>
>
> class Document(object):
> date = Column(Date)
> documentType = Column('documenttype', String, key="documentType")
>
>
> class ContactDocument(AbstractConcreteBase, Base, Document):
> contactPersonName = Column('contactpersonname', String, 
> key="contactPersonName")
> salesPersonName = Column(String)
> sendMethod = Column('sendmethod', String, key="sendMethod")
>
> @declared_attr
> def company_id(self):
> return Column(ForeignKey('companies.id'))
>
>
>
> On 7/9/15 11:18 AM, Alex Grönholm wrote:
>  
> The following script no longer works in 1.0.6, but does in 0.9.9:
>
>
> from sqlalchemy.ext.declarative import declarative_base, 
> AbstractConcreteBasefrom sqlalchemy.ext.declarative.api import 
> declared_attrfrom sqlalchemy.orm.mapper import configure_mappersfrom 
> sqlalchemy.orm.session import Sessionfrom sqlalchemy.sql.schema import 
> Column, ForeignKeyfrom sqlalchemy.sql.sqltypes import Date, String, Integer
>
> Base = declarative_base()
>
> class Company(Base):
> __tablename__ = 'companies'id = Column(Integer, primary_key=True)
>
> class Document(object):
> date = Column(Date)
> documentType = Column('documenttype', String)
>
> class ContactDocument(AbstractConcreteBase, Base, Document):
> contactPersonName = Column('contactpersonname', String)
> salesPersonName = Column(String)
> sendMethod = Column('sendmethod', String)
>
> @declared_attrdef company_id(self):
> return Column(ForeignKey('companies.id'))
>
> class Offer(ContactDocument):
> __tablename__ = 'offers'id = Column(Integer, primary_key=True)
>
> class SalesOrder(ContactDocument):
> __tablename__ = 'orders'id = Column(Integer, primary_key=True)
>
>
> configure_mappers()
> session = Session()
> query = session.query(ContactDocument)print(query)
>
>
>
> On 1.0.6, I get an error: sqlalchemy.exc.ArgumentError: When configuring 
> property 'documentType' on Mapper|ContactDocument|pjoin, column 
> 'documenttype' is not represented in the mapper's table. Use the 
> `column_property()` function to force this column to be mapped as a 
> read-only attribute.
> Why am I getting this? Is this a bug or am I not understanding something?
>
> Also, is it possible to have both Document and ContactDocument as abstract 
> concrete base classes (ie. I want the union from Document to include both 
> the direct concrete subclasses of Document and all concrete subclasses of 
> ContactDocument as well)?
>  -- 
> 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.
> 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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Trouble with AbstractConcreteBase and aliased columns

[Alex Grönholm]

The following script no longer works in 1.0.6, but does in 0.9.9:


from sqlalchemy.ext.declarative import declarative_base, AbstractConcreteBase
from sqlalchemy.ext.declarative.api import declared_attr
from sqlalchemy.orm.mapper import configure_mappers
from sqlalchemy.orm.session import Session
from sqlalchemy.sql.schema import Column, ForeignKey
from sqlalchemy.sql.sqltypes import Date, String, Integer

Base = declarative_base()


class Company(Base):
__tablename__ = 'companies'
id = Column(Integer, primary_key=True)


class Document(object):
date = Column(Date)
documentType = Column('documenttype', String)


class ContactDocument(AbstractConcreteBase, Base, Document):
contactPersonName = Column('contactpersonname', String)
salesPersonName = Column(String)
sendMethod = Column('sendmethod', String)

@declared_attr
def company_id(self):
return Column(ForeignKey('companies.id'))


class Offer(ContactDocument):
__tablename__ = 'offers'

id = Column(Integer, primary_key=True)


class SalesOrder(ContactDocument):
__tablename__ = 'orders'

id = Column(Integer, primary_key=True)


configure_mappers()
session = Session()
query = session.query(ContactDocument)
print(query)



On 1.0.6, I get an error: sqlalchemy.exc.ArgumentError: When configuring 
property 'documentType' on Mapper|ContactDocument|pjoin, column 
'documenttype' is not represented in the mapper's table. Use the 
`column_property()` function to force this column to be mapped as a 
read-only attribute.
Why am I getting this? Is this a bug or am I not understanding something?

Also, is it possible to have both Document and ContactDocument as abstract 
concrete base classes (ie. I want the union from Document to include both 
the direct concrete subclasses of Document and all concrete subclasses of 
ContactDocument as well)?

-- 
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.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] contains_eager does not load relation data if related model appears twice in query

[Alex S]

Thank you very much for your work, Mike.

-- 
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.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] contains_eager does not load relation data if related model appears twice in query

[Alex S]

Hi everyone!

I've found that contains_eager() does not load relation objects from query 
in case when related model appears twice in query. The working code with 
example models and steps to reproduce is listed in the attached file, I'll 
briefly describe the issue here.

There are four models:

Model = declarative_base()


M2M = Table('...')


class User(Model):
accounts = relation(Account, secondary=M2M)
preferences = relation(UserPreferences, uselist=False)


class Account(Model):
users = relation(User, secondary=M2M),

preferences = relation(AccountPreferences, uselist=False)


class UserPreferences(Model):
user = relation(User, uselist=False)
current_account = relation(Account, uselist=False)



class AccountPreferences(Model):
account = relation(Account, uselist=False)




I want to fetch user with all relations filled via single request. The 
query I'm using is:

aM2M = aliased(M2M_User_Account, name='mm')
aUser = aliased(User, name='users')
aAccount = aliased(Account, name='accounts')
aCurrentAccount = aliased(Account, name='currents')
aUserPreferences = aliased(UserPreferences, name='upx')
aAccountPreferences = aliased(AccountPreferences, name='apx')




q = (
s.query(aUser)
.outerjoin(
aUserPreferences,
aUserPreferences.uid == aUser.id)
.outerjoin(
aCurrentAccount,
aCurrentAccount.id == aUserPreferences.aid)
.outerjoin(
aAccountPreferences,
aAccountPreferences.aid == aCurrentAccount.id)
.options(
contains_eager(aUser.preferences, alias=aUserPreferences)
.contains_eager(aUserPreferences.current_account, alias=
aCurrentAccount)
.contains_eager(aCurrentAccount.preferences, alias=
aAccountPreferences)
)


.outerjoin(
aM2M,
aM2M.c.uid == aUser.id)
.outerjoin(
aAccount,
aAccount.id == aM2M.c.aid)
.options(
contains_eager(aUser.accounts, alias=aAccount)
)
)


r = q.all()
u = r[0]



When I start printing some user's relations (u.preferences or u.accounts), 
I've found that all data was load via q.all() call and there are no 
additional queries in database. All data are loaded except for 
u.preferences.current_account.preferences. Account preferences for current 
account weren't loaded on q.all() stage and the additional sql query was 
issued when printing u.preferences.current_account.preferences. This 
behaviour is not suitable for me. Could anyone please tell me where I'm 
wrong? Are there any ways to resolve the issue?


-- 
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.
For more options, visit https://groups.google.com/d/optout.
from contextlib import contextmanager
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Table, Column, String, Integer, ForeignKey
from sqlalchemy import PrimaryKeyConstraint, ForeignKeyConstraint
from sqlalchemy.orm import relation, contains_eager, aliased


engine = create_engine('sqlite:///:memory:', echo=True)
Model = declarative_base()


# = Models 


M2M_User_Account = Table(
'm2m', Model.metadata,
Column('uid', Integer, nullable=False),
Column('aid', Integer, nullable=False),

PrimaryKeyConstraint(
'uid', 'aid',
name='m2m_pk',
),
ForeignKeyConstraint(
['uid'],
['u.id'],
onupdate='CASCADE',
ondelete='CASCADE',
),
ForeignKeyConstraint(
['aid'],
['a.id'],
onupdate='CASCADE',
ondelete='CASCADE',
),
)


class User(Model):
__tablename__ = 'u'
id = Column(Integer, primary_key=True, autoincrement=True)
email = Column(String, nullable=False)

accounts = relation(
'Account', back_populates='users',
secondary=M2M_User_Account,
)

preferences = relation(
'UserPreferences', back_populates='user',
uselist=False,
)

def __repr__(self):
return (
'User(id={uid}, email="{email}")'
.format(
id=self.id,
email=self.email,
)
)


class Account(Model):
__tablename__ = 'a'
id = Column(Integer, primary_key=True, autoincrement=True)
name = Column(String, nullable=False)

users = relation(
'User', back_populates='accounts',
secondary=M2M_User_Account,
)

preferences = relation(
'AccountPreferences', back_populates='account',
uselist=False,
)

users_preferences = relation(
'UserPreferences',
back_populates='current

[sqlalchemy] sqlacodegen 1.1.6 released.

[Alex Grönholm]

This update only fixes compatibility with SQLAlchemy 1.0.

-- 
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.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Sending queue messages/emails after model commit

[Alex Michael]

Hey Jason,

Thanks for your reply. That makes sense I guess. It just feels like this is 
something most webapps will need at some point and it's not as 
straightforward as someone would imagine. 

-- alex

On Monday, September 22, 2014 6:22:14 PM UTC+2, jason kirtland wrote:
>
> Hi Alex,
>
> I have a similar use case, and fixed it by buffering the signals until the 
> session transaction completes. On rollback, the buffered signals are 
> discarded; on successful commit, the signals are truly emitted.
>
> Cheers,
> Jason
>
>
> On Mon, Sep 22, 2014 at 2:20 AM, Alex Michael  > wrote:
>
>> Hey, 
>>
>> From my understanding it's recommended that the business logic does not 
>> commit the session and that the application itself handles the session 
>> lifecycle. Following that, I have all the session handling logic in my 
>> controllers so the business logic just changes the objects as necessary and 
>> then the controllers call .commit() when needed. When a model is committed 
>> and say X property has changed, I need to send a queue message. My problem 
>> is that I'm not sure where the logic for emitting such signals should live 
>> in order to avoid duplicating logic all over the place. An example:
>>
>> I have an order which I take a payment for. If the payment is successful, 
>> I mark the order as paid. At this point I need to emit a signal. If the 
>> order is pending, I wait for a notification to come in from the payment 
>> gateway and then mark the order as paid. My business logic has a 
>> `mark_as_paid` function which changes the status of the order. Ideally I 
>> would like to emit the signal in the `mark_as_paid` method but I don't know 
>> at that point in time if the session commit will succeed or not. The 
>> alternative would be to emit the signal manually after the session was 
>> committed but that would (1) lead to duplicated logic since `mark_as_paid` 
>> can be triggered from many code paths (2) not always work since the status 
>> of the order is determined dynamically so the caller doesn't actually know 
>> what "changed" in order to emit the correct signal. 
>>
>> Am I missing something here? I'd appreciate any help.
>>
>> Thanks!
>>
>> -- alex
>>
>> -- 
>> 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.
>> 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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Sending queue messages/emails after model commit

[Alex Michael]

Hey, 

>From my understanding it's recommended that the business logic does not 
commit the session and that the application itself handles the session 
lifecycle. Following that, I have all the session handling logic in my 
controllers so the business logic just changes the objects as necessary and 
then the controllers call .commit() when needed. When a model is committed 
and say X property has changed, I need to send a queue message. My problem 
is that I'm not sure where the logic for emitting such signals should live 
in order to avoid duplicating logic all over the place. An example:

I have an order which I take a payment for. If the payment is successful, I 
mark the order as paid. At this point I need to emit a signal. If the order 
is pending, I wait for a notification to come in from the payment gateway 
and then mark the order as paid. My business logic has a `mark_as_paid` 
function which changes the status of the order. Ideally I would like to 
emit the signal in the `mark_as_paid` method but I don't know at that point 
in time if the session commit will succeed or not. The alternative would be 
to emit the signal manually after the session was committed but that would 
(1) lead to duplicated logic since `mark_as_paid` can be triggered from 
many code paths (2) not always work since the status of the order is 
determined dynamically so the caller doesn't actually know what "changed" 
in order to emit the correct signal. 

Am I missing something here? I'd appreciate any help.

Thanks!

-- alex

-- 
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.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] More than one level of abstract base classes doesn't work

[Alex Grönholm]

Sorry to be asking more questions, but the docs on inheritance don't get 
into much details on how the properties are supposed to work.
The following code produces unexpected results:

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

Base = declarative_base()


class Document(Base, AbstractConcreteBase):
doctype = Column('doc_type', Unicode, nullable=False)


class ActualDocument(Document):
__tablename__ = 'actual_documents'
__mapper_args__ = {'concrete': True, 'polymorphic_identity': 'actual'}

id = Column(Integer, primary_key=True)
name = Column('name_', Unicode)

configure_mappers()

for prop in class_mapper(Document).column_attrs:
print('%s (%s)' % (prop, prop.__class__.__name__))

for prop in class_mapper(ActualDocument).column_attrs:
print('%s (%s)' % (prop, prop.__class__.__name__))

Which gives me:

Document.doc_type (ColumnProperty)
Document.id (ColumnProperty)
Document.name_ (ColumnProperty)
Document.type (ColumnProperty)
ActualDocument.doctype (ColumnProperty)
ActualDocument.name (ColumnProperty)
ActualDocument.id (ColumnProperty)

I can understand Document.id (which comes from ActualDocument) and 
Document.type (which I assume is the polymorphic identity) but doc_type 
seems wrong to me. Why are doc_type and name_ not named doctype and name 
respectively, like they are on ActualDocument? Is this a bug? Am I right in 
guessing that the polymorphic union simply lacks the proper labels where 
the column name differs from the attribute name?

-- 
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.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] More than one level of abstract base classes doesn't work

[Alex Grönholm]

Hm not so dumb actually, because __subclasses__ only lists immediate 
subclasses. Getting all the mapped classes would involve more work that 
way. So my preferred method remains this:

def get_all_mapped_classes():
return [cls for cls in Base._decl_class_registry.values() if isinstance(
cls, type)]


perjantai, 5. syyskuuta 2014 17.19.21 UTC+3 Alex Grönholm kirjoitti:
>
> You're right, I'm dumb. I should've just used __subclasses__ and be done 
> with it.
> The use case is that I have a client-server app and I build a list of all 
> classes for the client so the client knows which column types to use in 
> tables. It's also used for automatically generating the column/relationship 
> list on the client in the search dialog. So I won't have to make code 
> changes to the client when I alter the model on the server.
>
> perjantai, 5. syyskuuta 2014 17.13.10 UTC+3 Michael Bayer kirjoitti:
>>
>> well it’s not set to be mapped until after the setup part of it.   
>>
>> if you want to get every class whether mapped or not, maybe use 
>> Base.__subclasses__() ?
>>
>> What’s the use case where you need the abstract base in the decl class 
>> registry?  it’s not really something you’d want to refer to in a 
>> relationship().
>>
>>
>>
>> On Sep 5, 2014, at 9:56 AM, Alex Grönholm  wrote:
>>
>> Thanks. I've filed an issue 
>> <https://bitbucket.org/zzzeek/sqlalchemy/issue/3185/more-than-one-level-of-abstract-concrete>
>>  
>> at Bitbucket.
>>
>> A follow-up question: Why are abstract base classes not present in the 
>> declarative class registry? Or is there another way to get all the mapped 
>> classes besides iterating over Base._decl_class_registry?
>>
>> -- 
>> 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.
>> 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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] More than one level of abstract base classes doesn't work

[Alex Grönholm]

You're right, I'm dumb. I should've just used __subclasses__ and be done 
with it.
The use case is that I have a client-server app and I build a list of all 
classes for the client so the client knows which column types to use in 
tables. It's also used for automatically generating the column/relationship 
list on the client in the search dialog. So I won't have to make code 
changes to the client when I alter the model on the server.

perjantai, 5. syyskuuta 2014 17.13.10 UTC+3 Michael Bayer kirjoitti:
>
> well it’s not set to be mapped until after the setup part of it.   
>
> if you want to get every class whether mapped or not, maybe use 
> Base.__subclasses__() ?
>
> What’s the use case where you need the abstract base in the decl class 
> registry?  it’s not really something you’d want to refer to in a 
> relationship().
>
>
>
> On Sep 5, 2014, at 9:56 AM, Alex Grönholm  > wrote:
>
> Thanks. I've filed an issue 
> <https://bitbucket.org/zzzeek/sqlalchemy/issue/3185/more-than-one-level-of-abstract-concrete>
>  
> at Bitbucket.
>
> A follow-up question: Why are abstract base classes not present in the 
> declarative class registry? Or is there another way to get all the mapped 
> classes besides iterating over Base._decl_class_registry?
>
> -- 
> 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.
> 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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] More than one level of abstract base classes doesn't work

[Alex Grönholm]

Thanks. I've filed an issue 

 
at Bitbucket.

A follow-up question: Why are abstract base classes not present in the 
declarative class registry? Or is there another way to get all the mapped 
classes besides iterating over Base._decl_class_registry?

-- 
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.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] More than one level of abstract base classes doesn't work

[Alex Grönholm]

The following code fails with AttributeError: 'NoneType' object has no 
attribute 'concrete':

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

Base = declarative_base()


class Document(Base, AbstractConcreteBase):
type = Column(Unicode, nullable=False)


class ContactDocument(Document):
__abstract__ = True

send_method = Column('sendmethod', Unicode)


class ActualDocument(ContactDocument):
__tablename__ = 'actual_documents'
__mapper_args__ = {'concrete': True, 'polymorphic_identity': 'actual'}

id = Column(Integer, primary_key=True)

configure_mappers()


Am I not supposed to have more than one level of abstract base classes? Or 
am I doing something else wrong?
This is with SQLAlchemy 0.9.7.

-- 
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.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Encapsulating insert/update logic of mapped classes in the class itself

[Alex Grönholm]

02.06.2014 16:44, Michael Bayer kirjoitti:
the ORM can't persist any value for "delivery_method" unless there is 
an actual attribute set event.


If you are saying session.add(DeliveryAddress()) and flushing, these 
are the options for "deliveryaddress.delivery_method":


1. the value has no setting and is not present in the INSERT statement 
- it gets set as NULL in the DB.


2. the value has no setting, but you have a python or server side 
default set up on it.  At before_insert() time, that value is still 
None so that can't be what you're doing.


3. The DeliveryAddress() constructor sets delivery_method, so there's 
an attribute set event.


So I can only guess that you're looking for the None here? I guess 
there's some odd relationship to this Settings object such that it 
already exists in the DB with some other value such that you actually 
need to positively set None.  In any case, to have something 
happen upon DeliveryAddress(), you can either place that logic as part 
of __init__() (regular Python!) or if that bothers you, you can also 
set up the init() event for the DeliveryAddress class.
Settings is a singleton (just one row with ID 1). It contains the 
default values for a number of things.


I will have a look at __declare_last__, thanks!

That said, sometimes I need to react to inserts and insert other rows 
then. Attribute events won't help me there.
So far I've ended up making a module for these listeners and I just had 
a single listener for each event (before_insert or before_flush) with a 
lot of if..elif...branching.


If all of these options don't suit you and you absolutely insist on 
the before_insert event, it is very easy to add to your declarative 
base a mixin that sets these up for you (see 
http://docs.sqlalchemy.org/en/rel_0_9/orm/extensions/declarative.html#declare-last):


class BeforeInsertMixin(object):
@classmethod
def __declare_last__(cls):
if hasattr(cls, 'before_insert'):
event.listen(cls, 'before_insert', cls.before_insert)

Base = declarative_base(cls=BeforeInsertMixin)

class DeliveryAddress(Base):
# ...

@classmethod
def before_insert(cls, mapper, connection, target):
#...


if you want to change the signature, no problem:

class BeforeInsertMixin(object):
@classmethod
def __declare_last__(cls):
if hasattr(cls, 'before_insert'):
@event.listens_for(cls, 'before_insert')
def before_insert(mapper, connection, target):
target.before_insert(object_session(target))


class DeliveryAddress(Base):
# ...

def before_insert(self, session):
#...
These are "frameworky" types of hooks that SQLAlchemy would prefer to 
remain agnostic of, but it provides for you all the components you 
need to create whatever system of hooks you'd like.






On Jun 2, 2014, at 8:04 AM, Alex Grönholm <mailto:alex.gronh...@nextday.fi>> wrote:


That's the first thing I tried, but validators don't get called 
unless you explicitly set a value to the column.


So for something like session.add(DeliveryAddress()), the validator 
doesn't get called.


maanantai, 2. kesäkuuta 2014 14.27.47 UTC+3 Michael Bayer kirjoitti:


On Jun 2, 2014, at 1:47 AM, Alex Grönholm 
wrote:

> This has been a problem for me for years.
>
> class DeliveryAddress(Base, Address):
> ...
> delivery_method = Column(String)
> ...
>
> @event.listens_for(DeliveryAddress, 'before_insert')
> def before_insert_deliveryaddress(mapper, connection, target):
> settings = object_session(target).query(Settings).one()
> target.delivery_method = settings.default_delivery_method
>
> Is there absolutely no way to encapsulate this logic cleanly in
the actual class?


I use an attribute event for that, and more often just a simple
@validates.

@validates("default_delivery_method")
def _set_delivery_method(self, key, value):
self.settings.delivery_method = value
return value




--
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 tosqlalchemy+unsubscr...@googlegroups.com 
<mailto:sqlalchemy+unsubscr...@googlegroups.com>.
To post to this group, send email tosqlalch...@googlegroups.com 
<mailto:sqlalchemy@googlegroups.com>.

Visit this group athttp://groups.google.com/group/sqlalchemy.
For more options, visithttps://groups.google.com/d/optout.


--
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/-s9GWB3hfso/un

Re: [sqlalchemy] Encapsulating insert/update logic of mapped classes in the class itself

[Alex Grönholm]

That's the first thing I tried, but validators don't get called unless you 
explicitly set a value to the column.

So for something like session.add(DeliveryAddress()), the validator doesn't 
get called.

maanantai, 2. kesäkuuta 2014 14.27.47 UTC+3 Michael Bayer kirjoitti:
>
>
> On Jun 2, 2014, at 1:47 AM, Alex Grönholm  > wrote: 
>
> > This has been a problem for me for years. 
> > 
> > class DeliveryAddress(Base, Address): 
> > ... 
> > delivery_method = Column(String) 
> > ... 
> > 
> > @event.listens_for(DeliveryAddress, 'before_insert') 
> > def before_insert_deliveryaddress(mapper, connection, target): 
> > settings = object_session(target).query(Settings).one() 
> > target.delivery_method = settings.default_delivery_method 
> > 
> > Is there absolutely no way to encapsulate this logic cleanly in the 
> actual class? 
>
>
> I use an attribute event for that, and more often just a simple 
> @validates.   
>
> @validates(“default_delivery_method”) 
> def _set_delivery_method(self, key, value): 
> self.settings.delivery_method = value 
> return value 
>
>
>
>

-- 
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.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Encapsulating insert/update logic of mapped classes in the class itself

[Alex Grönholm]

This has been a problem for me for years.

class DeliveryAddress(Base, Address):
...
delivery_method = Column(String)
...

@event.listens_for(DeliveryAddress, 'before_insert')
def before_insert_deliveryaddress(mapper, connection, target):
settings = object_session(target).query(Settings).one()
target.delivery_method = settings.default_delivery_method

Is there absolutely no way to encapsulate this logic cleanly in the actual 
class?

-- 
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.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Mutable ARRAY

[Alex Grönholm]

03.05.2014 16:04, Michael Bayer kirjoitti:
I'd like to provide mutablearray though, so if someone can roll it 
with some tests it can go right in.



I'll look into it.

Sent from my iPhone

On May 3, 2014, at 9:02 AM, Alex Grönholm <mailto:alex.gronh...@nextday.fi>> wrote:


There doesn't seem to be a class like MutableDict that provides 
equivalent functionality for the ARRAY column type. Any particular 
reason why? I'd like to be able to do .append() and .remove() on an 
array column. Do I have to roll my own?

--
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 
<mailto:sqlalchemy+unsubscr...@googlegroups.com>.
To post to this group, send email to sqlalchemy@googlegroups.com 
<mailto:sqlalchemy@googlegroups.com>.

Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

--
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/QP4IBjzk9i8/unsubscribe.
To unsubscribe from this group and all its topics, send an email to 
sqlalchemy+unsubscr...@googlegroups.com 
<mailto:sqlalchemy+unsubscr...@googlegroups.com>.
To post to this group, send email to sqlalchemy@googlegroups.com 
<mailto:sqlalchemy@googlegroups.com>.

Visit this group at http://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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Mutable ARRAY

[Alex Grönholm]

There doesn't seem to be a class like MutableDict that provides equivalent 
functionality for the ARRAY column type. Any particular reason why? I'd 
like to be able to do .append() and .remove() on an array column. Do I have 
to roll my own?

-- 
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.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Bitwise Flag Type

[Alex]

Yeah, its a very frustrating aspect of SQL Server. Anyway, a query that
works is the following:

SELECT testmodel.id AS testmodel_id, testmodel.flags AS testmodel_flags
FROM testmodel
WHERE (testmodel.flags & 1) > 0

I can get sqlalchemy to emit this like so:

session.query(TestModel).filter(TestModel.flag_one)

And the negation of it:

session.query(TestModel).filter(not_(TestModel.flag_one))

I can't figure out how to emit the required SQL on comparison with a
boolean value though.

Alex



On Tue, Apr 1, 2014 at 1:54 PM, Michael Bayer wrote:

>
> On Apr 1, 2014, at 6:34 AM, Alex  wrote:
>
> Hmm, looks like I spoke too soon. Testing against a SQLite database the
> hybrid attribute approach works fine but I'm having some trouble with SQL
> Server. Basically, given the structure that Michael laid out, the following
> query:
>
> model = TestModel(
> flags=1
> )
> session.add(model)
> session.commit()
> result = session.query(TestModel).filter(TestModel.flag_one ==
> True).first()
>
> Resullts in this exception:
>
> ProgrammingError: (ProgrammingError) (102, "Incorrect syntax near
> '='.DB-Lib error message 102, severity 15:
> General SQL Server error: Check messages from the SQL Server
> ") 'SELECT TOP 1 testmodel.id AS testmodel_id, testmodel.flags AS
> testmodel_flags
> FROM testmodel
> WHERE ((testmodel.flags & %(flags_1)s) > %(param_1)s) = 1' {'flags_1': 1,
> 'param_1': 0}
>
> So it looks like the equality comparison is coercing True to 1, I can't
> figure out which hook I need to use to change this. I've tried to use
> coerce_compared_value with no effect.
>
>
> SQL server doesn't have a "boolean" type, you can only use one and zero.
> the issue there is more likely the bitwise comparison operators or the
> nesting of the parenthesis.   get that query to work first at the SQL
> server console to figure out the syntax it wants.
>
>
>  --
> 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/Mu9m1dVU1Gw/unsubscribe.
> 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.
> 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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Re: Bitwise Flag Type

[Alex]

Hmm, looks like I spoke too soon. Testing against a SQLite database the
hybrid attribute approach works fine but I'm having some trouble with SQL
Server. Basically, given the structure that Michael laid out, the following
query:

model = TestModel(
flags=1
)
session.add(model)
session.commit()
result = session.query(TestModel).filter(TestModel.flag_one ==
True).first()

Resullts in this exception:

ProgrammingError: (ProgrammingError) (102, "Incorrect syntax near
'='.DB-Lib error message 102, severity 15:
General SQL Server error: Check messages from the SQL Server
") 'SELECT TOP 1 testmodel.id AS testmodel_id, testmodel.flags AS
testmodel_flags
FROM testmodel
WHERE ((testmodel.flags & %(flags_1)s) > %(param_1)s) = 1' {'flags_1': 1,
'param_1': 0}

So it looks like the equality comparison is coercing True to 1, I can't
figure out which hook I need to use to change this. I've tried to use
coerce_compared_value with no effect.

Alex


On Mon, Mar 31, 2014 at 8:49 PM, Alex  wrote:

> The hybrid property and custom comparator approach works like a charm, I
> had an inkling that hybrid propertyies were the correct approach but hadn't
> really thought about subclassing hybrid_property, this has helped me
> understand the custom types architecture a lot, thank very much.
>
> Alex
>
>
> On Mon, Mar 31, 2014 at 4:13 PM, Jonathan Vanasco 
> wrote:
>
>> I'm interested in what you find. I know TypeDecorator is the right
>> solution, I was looking at that for this exact same situation a few weeks
>> ago (
>> https://groups.google.com/forum/#!searchin/sqlalchemy/vanasco%7Csort:date/sqlalchemy/sQtOYxSUiqI/5ns2vWMFaGAJ)
>>
>> I have a similar situation.  I wrote a generic bitwise wrapper class that
>> I was ashamed of, but I guess I should eventually release it.  i can toss
>> it on github under the MIT if you'd like.
>>
>> The way I have my bitwise stuff working is this:
>>
>> * I create an class that inherits from my `BitwiseSet` class.  That
>> subclass stores a mapping of the bitwise values, the parent class has
>> functions for common bitwise operations ( add, remove, has_any, has_all --
>> both by string and integer ).   The parent class has an 'encode' and
>> 'decode' function, which returns an int or list of elements (as int or
>> string ).
>>
>> class BitwiseClassFieldA(BitwiseSet):
>> set_ = {
>>'flag_a' << 1,
>>'flag_b' << 2,
>>'flag_c' << 3,
>> }
>>
>> * I have a property on each of my sqlalchemy objects that works something
>> like this...
>>
>> class Foo(SqlAlchemyObject):
>>  bitwise_field_a = sa.Column( sa.Integer, default=0 )
>>
>>  @property
>>  def bitwise_manager_field_a(self):
>> if self._bitwise_manager_field_a is None:
>>   self. bitwise_manager_field_a =
>> BitwiseClassFieldA(self.bitwise_field_a)
>> return self.bitwise_manager_field_a
>>  _ bitwise_manager_field_a = None
>>
>> * when i do saves , i call the manager's `encode` function
>>
>>   instance.bitwise_field_a = instance.bitwise_manager_field_a.encode()
>>
>>
>> anyways, when I was trying to get a TypeDecorator working, I was focused
>> on the Integer field having a decorator and managing my object -- not on
>> defining many column attributes like you are.  i think that might be an
>> easier avenue, because you want to affect the column itself for these
>> comparisons.
>>
>> --
>> 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/Mu9m1dVU1Gw/unsubscribe.
>> 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.
>> 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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Re: Bitwise Flag Type

[Alex]

The hybrid property and custom comparator approach works like a charm, I
had an inkling that hybrid propertyies were the correct approach but hadn't
really thought about subclassing hybrid_property, this has helped me
understand the custom types architecture a lot, thank very much.

Alex


On Mon, Mar 31, 2014 at 4:13 PM, Jonathan Vanasco wrote:

> I'm interested in what you find. I know TypeDecorator is the right
> solution, I was looking at that for this exact same situation a few weeks
> ago (
> https://groups.google.com/forum/#!searchin/sqlalchemy/vanasco%7Csort:date/sqlalchemy/sQtOYxSUiqI/5ns2vWMFaGAJ)
>
> I have a similar situation.  I wrote a generic bitwise wrapper class that
> I was ashamed of, but I guess I should eventually release it.  i can toss
> it on github under the MIT if you'd like.
>
> The way I have my bitwise stuff working is this:
>
> * I create an class that inherits from my `BitwiseSet` class.  That
> subclass stores a mapping of the bitwise values, the parent class has
> functions for common bitwise operations ( add, remove, has_any, has_all --
> both by string and integer ).   The parent class has an 'encode' and
> 'decode' function, which returns an int or list of elements (as int or
> string ).
>
> class BitwiseClassFieldA(BitwiseSet):
> set_ = {
>'flag_a' << 1,
>'flag_b' << 2,
>'flag_c' << 3,
> }
>
> * I have a property on each of my sqlalchemy objects that works something
> like this...
>
> class Foo(SqlAlchemyObject):
>  bitwise_field_a = sa.Column( sa.Integer, default=0 )
>
>  @property
>  def bitwise_manager_field_a(self):
> if self._bitwise_manager_field_a is None:
>   self. bitwise_manager_field_a =
> BitwiseClassFieldA(self.bitwise_field_a)
> return self.bitwise_manager_field_a
>  _ bitwise_manager_field_a = None
>
> * when i do saves , i call the manager's `encode` function
>
>   instance.bitwise_field_a = instance.bitwise_manager_field_a.encode()
>
>
> anyways, when I was trying to get a TypeDecorator working, I was focused
> on the Integer field having a decorator and managing my object -- not on
> defining many column attributes like you are.  i think that might be an
> easier avenue, because you want to affect the column itself for these
> comparisons.
>
> --
> 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/Mu9m1dVU1Gw/unsubscribe.
> 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.
> 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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Bitwise Flag Type

[Alex Good]

Hi there,

I'm working with a database where the original designer has chosen to 
represent a large number of values as bitflags. Nearly every table in the 
database has a 'flags' integer which is bitwise ORred or ANDed with 
constants stored in the application code to extract the relevant options. 
I'm trying to wrap this in a custom type which will allow me to refer to 
these values individually as booleans and I can't figure out the correct 
approach. Here's an example:

class TestFlags(object):
flag_value_one = 1
flag_value_two = 2
flag_value_three = 4
flag_value_four = 8

class TestModel(TestBase):
__tablename__  = "testmodel"
id = Column("id", Integer, primary_key=True)
flags = Column("flags", Integer)
flag_one = column_property(Column("flags", Flag(TestFlags, 
"flag_value_one")))
flag_two = column_property(Column("flags", Flag(TestFlags, 
"flag_value_two")))
flag_three = column_property(Column("flags", Flag(TestFlags, 
"flag_value_three")))
flag_four = column_property(Column("flags", Flag(TestFlags, 
"flag_value_four")))

So what I want to be able to do is these kind of interactions

m = session.query(TestModel).filter(TestModel.flag_one == True).first()
m.flag_one = False
session.add(m.flag_one)
session.commit()
assert((m.flags & 1) == 0)

I can see how one would accomplish this with hybrid attributes but with 
every table having 5 to 10 flags defined on it that would lead to a lot of 
typing, hence the custom type approach. I attempted the following:

class Flag(TypeDecorator):
impl = Integer

def __init__(self, flag_constants, flag_name, *args, **kwargs):
super(Flag, self).__init__(*args, **kwargs)
self.flag_constants = flag_constants
self.flag_name = flag_name

def column_expression(self, col):
return col.op("&")(getattr(self.flag_constants, self.flag_name))

Which works great for querying values but not for updating or inserting 
them. Another point which I can't figure out is what to do if updating 
multiple flags in the same step. It seems to me that it would make more 
sense to do this in process_bind_param but then obviously there is no 
current value of the flag integer for me to operate against. So, I'm a bit 
lost, any ideas on how to implement this would be greatly appreciated.

Thanks
Alex



-- 
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.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] HSTORE intermittent bug

[alex]

Hi there,

I've been hitting an intermittent bug with SQLAlchemy/PostgreSQL using an 
HSTORE column.  Some times, when I restart my process (pyramid over gevent 
over chaussette), I get a spew of such errors... some other time, 
everything goes just fine.

It seems this bug hasn't been documented anywhere, or at least the error I 
get wasn't reported.

If you guys have any insight at all that would help me out in my search for 
the bug, I'd be eternally grateful.  Here is the traceback:

  File "/home/abourget/build/bitc/Bitc/bitc/api.py", line 126, in 
website_from_origin
website = Website.get_by_host(origin)
  File "/home/abourget/build/bitc/Bitc/bitc/models.py", line 299, in 
get_by_host
Website.deleted == False).first()
  File 
"/home/abourget/build/bitc/env/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py",
 
line 2320, in first
ret = list(self[0:1])
  File 
"/home/abourget/build/bitc/env/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py",
 
line 2187, in __getitem__
return list(res)
  File 
"/home/abourget/build/bitc/env/local/lib/python2.7/site-packages/sqlalchemy/orm/loading.py",
 
line 72, in instances
rows = [process[0](row, None) for row in fetch]
  File 
"/home/abourget/build/bitc/env/local/lib/python2.7/site-packages/sqlalchemy/orm/loading.py",
 
line 451, in _instance
populate_state(state, dict_, row, isnew, only_load_props)
  File 
"/home/abourget/build/bitc/env/local/lib/python2.7/site-packages/sqlalchemy/orm/loading.py",
 
line 305, in populate_state
populator(state, dict_, row)
  File 
"/home/abourget/build/bitc/env/local/lib/python2.7/site-packages/sqlalchemy/orm/strategies.py",
 
line 154, in fetch_col
dict_[key] = row[col]
  File 
"/home/abourget/build/bitc/env/local/lib/python2.7/site-packages/sqlalchemy/dialects/postgresql/hstore.py",
 
line 291, in process
return _parse_hstore(value.decode(encoding))
AttributeError: 'dict' object has no attribute 'decode'

Just for little context, models.py:299 in full is:
website = db.query(Website).filter(Website.domain == host,
Website.deleted == 
False).first()

There is one field on Website, declared as such:

data_apis = Column(MutableDict.as_mutable(HSTORE), default=dict)

perhaps that last default=dict would induce such a problem ?  I'll try 
investigating that path.

thanks for any help!

Alexandre


-- 
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.
For more options, visit https://groups.google.com/groups/opt_out.

[sqlalchemy] Build Query Without Objects

[Alex Meadows]

Greetings,

I've been reading through the docs and am still very new to SQLAlchemy but 
haven't found an answer to my question.  I am trying to write a query with 
SQLAlchemy but don't want to build any objects if it can be helped.  Is 
this possible?  I've read about reflection, but I'm still not clear how it 
works.

If it is possible, how would I write the following query in SQLAlchemy 
parlance?

SELECT col1, col3
FROM table1
WHERE col1 = 'a';

-- 
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.
For more options, visit https://groups.google.com/groups/opt_out.

[sqlalchemy] sqlacodegen 1.1.4 released.

[Alex Grönholm]

This release fixes compatibility with the just released SQLAlchemy 0.9.0 
final.

-- 
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.
For more options, visit https://groups.google.com/groups/opt_out.

Re: [sqlalchemy] How can I use the OVERLAPS operator?

[Alex Grönholm]

Ah, I was completely unaware of tuple_(). That's what I was looking for. 
Thanks!

keskiviikko, 18. joulukuuta 2013 18.31.42 UTC+2 Michael Bayer kirjoitti:
>
>
> On Dec 17, 2013, at 8:39 PM, Alex Grönholm 
> > 
> wrote:
>
> I would like to check if two date ranges overlap. This is done using the 
> OVERLAPS 
> operator<http://www.postgresql.org/docs/9.2/static/functions-datetime.html>
> .
> For example:
>
> SELECT (DATE '2001-02-16', DATE '2001-12-21') OVERLAPS
>(DATE '2001-10-30', DATE '2002-10-30');
>
>
> How do I do this in SQLAlchemy? I have no clue how to produce the 
> parentheses here.
>
>
> I think what we’re really looking at are two tuples, so might as well use 
> that:
>
> >>> from sqlalchemy import create_engine, tuple_, select
> >>> import datetime
> >>> e = create_engine("postgresql://scott:tiger@localhost/test", 
> echo=True)
> >>> s = select([
> ...   tuple_(datetime.date(2001, 2, 6), datetime.date(2001, 12, 
> 21)).op('overlaps')(
> ...tuple_(datetime.date(2001, 10, 30), datetime.date(2002, 
> 10, 30)))
> ...  ])
> >>> e.scalar(s)
> 2013-12-18 11:30:05,232 INFO sqlalchemy.engine.base.Engine SELECT 
> (%(param_1)s, %(param_2)s) overlaps (%(param_3)s, %(param_4)s) AS anon_1
> 2013-12-18 11:30:05,232 INFO sqlalchemy.engine.base.Engine {'param_4': 
> datetime.date(2002, 10, 30), 'param_1': datetime.date(2001, 2, 6), 
> 'param_3': datetime.date(2001, 10, 30), 'param_2': datetime.date(2001, 12, 
> 21)}
> 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+...@googlegroups.com .
> To post to this group, send email to sqlal...@googlegroups.com
> .
> Visit this group at http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/groups/opt_out.
>
>
>

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to 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.
For more options, visit https://groups.google.com/groups/opt_out.

[sqlalchemy] How can I use the OVERLAPS operator?

[Alex Grönholm]

I would like to check if two date ranges overlap. This is done using the 
OVERLAPS 
operator 
.
For example:

SELECT (DATE '2001-02-16', DATE '2001-12-21') OVERLAPS
   (DATE '2001-10-30', DATE '2002-10-30');


How do I do this in SQLAlchemy? I have no clue how to produce the 
parentheses here.

-- 
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.
For more options, visit https://groups.google.com/groups/opt_out.

[sqlalchemy] sqlacodegen 1.1.3 released.

[Alex Grönholm]

This release fixes compatibility with SQLAlchemy 0.8.3 and onwards. The 
test suite passes on SQLAlchemy 0.9.0b1 as well.

-- 
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.
For more options, visit https://groups.google.com/groups/opt_out.

Re: [sqlalchemy] How do I unregister event listeners?

[Alex Grönholm]

Thanks, I'll try to make it work with the latter method somehow. Clearing 
all session event listeners is not an option because some of the code under 
test relies on a permanent listener being there.

keskiviikko, 11. syyskuuta 2013 21.15.46 UTC+3 Michael Bayer kirjoitti:
>
> you can either remove all the listeners for a certain type, like this:
>
> events.MapperEvents._clear()
>
> the other alternative is wrap your events with a set that you control:
>
> my_listeners = set()
>
> @event.listens_for(target, "whatever")
> def evt(target):
>for listener in my_listeners:
> listener(target)
>
>
>
> On Sep 11, 2013, at 1:44 PM, Alex Grönholm 
> > 
> wrote:
>
> Thanks for the quick reply. I don't want to use prerelease versions of 
> SQLAlchemy though. Is there any recommended way of doing this in 0.8.2?
>
> keskiviikko, 11. syyskuuta 2013 20.40.40 UTC+3 Michael Bayer kirjoitti:
>>
>>
>> On Sep 11, 2013, at 1:16 PM, Alex Grönholm  wrote: 
>>
>> > I'm trying to test code that listens to session events on all sessions. 
>> I can't pin it on any particular session or even sessionmaker due to the 
>> architecture of the software (sessions are explicitly instantiated on the 
>> fly). 
>> > All is well except that the listener sticks after the test is done, 
>> breaking test isolation. The ideal solution would be to unregister the 
>> listener in teardown(), but I don't see anything in the event API that 
>> could be used to do that. 
>>
>> the public API is in 0.9 (of course there are crude hacks in 0.8) :  
>> http://docs.sqlalchemy.org/en/latest/changelog/migration_09.html#event-removal-api
>>  
>>
>>
>>
> -- 
> 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.
> 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.
For more options, visit https://groups.google.com/groups/opt_out.

Re: [sqlalchemy] How do I unregister event listeners?

[Alex Grönholm]

Thanks for the quick reply. I don't want to use prerelease versions of 
SQLAlchemy though. Is there any recommended way of doing this in 0.8.2?

keskiviikko, 11. syyskuuta 2013 20.40.40 UTC+3 Michael Bayer kirjoitti:
>
>
> On Sep 11, 2013, at 1:16 PM, Alex Grönholm 
> > 
> wrote: 
>
> > I'm trying to test code that listens to session events on all sessions. 
> I can't pin it on any particular session or even sessionmaker due to the 
> architecture of the software (sessions are explicitly instantiated on the 
> fly). 
> > All is well except that the listener sticks after the test is done, 
> breaking test isolation. The ideal solution would be to unregister the 
> listener in teardown(), but I don't see anything in the event API that 
> could be used to do that. 
>
> the public API is in 0.9 (of course there are crude hacks in 0.8) :  
> http://docs.sqlalchemy.org/en/latest/changelog/migration_09.html#event-removal-api
>  
>
>
>

-- 
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.
For more options, visit https://groups.google.com/groups/opt_out.

[sqlalchemy] Re: How do I unregister event listeners?

[Alex Grönholm]

There seems to be an undocumented function named remove() in the 
sqlalchemy.event module that looks like what I want, but it doesn't work:

Traceback (most recent call last):
  File 
"/home/alex/virtualenv/triancore/lib/python3.3/site-packages/nose/case.py", 
line 198, in runTest
self.test(*self.arg)
  File "/home/alex/workspace/TrianCore/tests/rpc/test_eventpublisher.py", 
line 36, in test_stop_publisher
self.publisher.stop()
  File "/home/alex/workspace/TrianCore/trian/core/rpc/eventpublisher.py", 
line 62, in stop
event.remove(Session, 'after_flush', self.queue_events)
  File 
"/home/alex/virtualenv/triancore/lib/python3.3/site-packages/sqlalchemy/event.py",
 
line 76, in remove
for tgt in evt_cls._accept_with(target):
nose.proxy.TypeError: 'type' object is not iterable

keskiviikko, 11. syyskuuta 2013 20.16.49 UTC+3 Alex Grönholm kirjoitti:
>
> I'm trying to test code that listens to session events on all sessions. I 
> can't pin it on any particular session or even sessionmaker due to the 
> architecture of the software (sessions are explicitly instantiated on the 
> fly).
> All is well except that the listener sticks after the test is done, 
> breaking test isolation. The ideal solution would be to unregister the 
> listener in teardown(), but I don't see anything in the event API that 
> could be used to do 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.
For more options, visit https://groups.google.com/groups/opt_out.

[sqlalchemy] How do I unregister event listeners?

[Alex Grönholm]

I'm trying to test code that listens to session events on all sessions. I 
can't pin it on any particular session or even sessionmaker due to the 
architecture of the software (sessions are explicitly instantiated on the 
fly).
All is well except that the listener sticks after the test is done, 
breaking test isolation. The ideal solution would be to unregister the 
listener in teardown(), but I don't see anything in the event API that 
could be used to do 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.
For more options, visit https://groups.google.com/groups/opt_out.

Re: [sqlalchemy] Unit testing, mocking and dependency injection with SA Declarative.

[Alex Grönholm]

I wrote a blog post on this very topic recently: 
http://alextechrants.blogspot.fi/2013/08/unit-testing-sqlalchemy-apps.html


tiistai, 10. syyskuuta 2013 19.43.35 UTC+3 Toph Burns kirjoitti:
>
>  Could you use an in-memory, sqlite db for your testing?  For our 
> applications, we have an initialization function that loads the database 
> connection strings from a config (.ini) file, passing those on to 
> create_engine.  In production it's a postgresql connection string, for 
> test, it's a "sqlite:///:memory:'
>
>
>   Toph Burns | Software Engineer
> 5885 Hollis St.  Suite 100
> Emeryville, CA 94608
> 510-597-4797
> bu...@amyris.com 
>   --
> *From:* sqlal...@googlegroups.com  
> [sqlal...@googlegroups.com] 
> on behalf of Michel Albert [exh...@gmail.com ]
> *Sent:* Tuesday, September 10, 2013 1:46 AM
> *To:* sqlal...@googlegroups.com 
> *Subject:* [sqlalchemy] Unit testing, mocking and dependency injection 
> with SA Declarative.
>
>   I am trying to wrap my head around how to do Dependency Injection with 
> SQLAlchemy and I am walking in circles. 
>
>  I want to be able to mock out SA for most of my tests. I trust SA and 
> don't want to test serialisation into the DB. I just want to test my own 
> code. So I was thinking to do dependency injection, and mock out SA during 
> testing.
>
>  But I don't know what to mock out, how and when to set up the session 
> properly, without doing it at the module level (which causes unwanted 
> side-effects only by importing the module).
>
>  The only solution which comes to mind is to have one "singleton" which 
> deals with that. But that feels very unpythonic to me and I am wondering if 
> there's a better solution.
>
>  I also saw that "create_engine" has an optional "module" kwarg, which I 
> could mock out. But then SA begins complaining that the return types are 
> not correct. And I don't want to specify return values for every possible 
> db-module call. That's way out of scope of my tests. I am not calling 
> anything on the db-module. That's SA's job, and, as said, I already trust 
> SA.
>
>  Whenever I work on this I always run into the session_maker 
> initialisation as well. The examples to this on the module level, which I 
> really make me feel uneasy.
>
>  Any tips? Just prodding myself in the right direction might help me out 
> enough.
>  
> -- 
> 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.
> 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.
For more options, visit https://groups.google.com/groups/opt_out.

[sqlalchemy] sqlacodegen 1.1.2 released

[Alex Grönholm]

This is another bugfix release. All reported issues have now been resolved.
Changes in this version:

   - Fixed non-default schema name not being present in __table_args__ 
   (fixes #2)
   - Fixed self referential foreign key causing column type to not be 
   rendered
   - Fixed missing "deferrable" and "initially" keyword arguments in 
   ForeignKey constructs
   - Fixed foreign key and check constraint handling with alternate schemas 
   (fixes #3)
   

-- 
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.
For more options, visit https://groups.google.com/groups/opt_out.

[sqlalchemy] sqlacodegen 1.1.1 released

[Alex Grönholm]

This is a bugfix release. Issues resolved:

   - Fixed TypeError when inflect could not determine the singular name of 
   a table for a many-to-1 relationship
   - Fixed _IntegerType, _StringType etc. being rendered instead of proper 
   types on MySQL
   

-- 
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] image field in mssql

[alex bodnaru]

hello michael,

as usually, you were faster than me to answer.

after reviewing my surrounding code to fix additional bugs there,
i've just found the answer:

a bytearray is needed as sql param. i've built the buffer from an image with
BytesIO.

im = Images.new()
image = BytesIO()
im.save(image)
buffer = image.getvalue()
param = dict(picture=bytearray(buffer)

thanks a lot for your consideration :)

alex

there was no need to use pyodbc.Binary, since it has the very same effect.

On 06/12/2013 06:36 PM, Michael Bayer wrote:
> blobs are problematic, and this is often highly dependent on DBAPI version 
> and platform.
> 
> So can you send along details:
> 
> 1. python version
> 2. operating system
> 3. SQLAlchemy version
> 4. DBAPI in use, version
> 
> I can give it a test on this end.  
> 
> 
> On Jun 12, 2013, at 6:00 AM, alex bodnaru  wrote:
> 
>>
>> hello friends,
>>
>> i'm happily using sa, both with a declarative/elixir orm, and directly 
>> through
>> DBSession.execute(text statement).
>>
>> there is an issue that works great with orm, but i don't khown how to 
>> achieve in
>> direct execution:
>>
>> image fields.
>> i'm inserting the data as:
>>
>> sql = "insert tablename(img) values(:img)"
>> DBSession.execute(sql, dict(img=bytearray(data))
>>
>> data is not empty, but it just retrieves as bytearray(b'')
>> could you advice me?
>>
>> many thanks in advance,
>> alex
>>
>> -- 
>> 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] image field in mssql

[alex bodnaru]

hello friends,

i'm happily using sa, both with a declarative/elixir orm, and directly through
DBSession.execute(text statement).

there is an issue that works great with orm, but i don't khown how to achieve in
direct execution:

image fields.
i'm inserting the data as:

sql = "insert tablename(img) values(:img)"
DBSession.execute(sql, dict(img=bytearray(data))

data is not empty, but it just retrieves as bytearray(b'')
could you advice me?

many thanks in advance,
alex

-- 
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] sqlacodegen 1.1.0 released

[Alex Grönholm]

Although it's only been a week since the initial release, I've already 
added a bunch of new features.
Release highlights:

   - Added automatic detection of joined-table inheritance
   - Fixed missing class name prefix in primary/secondary joins in 
   relationships
   - Instead of wildcard imports, generate explicit imports dynamically
   - Use the inflect library to produce better guesses for table to class 
   name conversion
   - Automatically detect Boolean (and Enum) columns based on 
   CheckConstraints
   - Skip redundant CheckConstraints for Enum and Boolean columns
   

-- 
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] What is polymorphic_on needed for in a joined table inheritance schema?

[Alex Grönholm]

I used joined table inheritance in Hibernate and it worked fine without any 
extra discriminator columns. Why is it necessary in SQLAlchemy?
I can understand the need for such a column in single table inheritance, 
but not joined table.

-- 
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] Announcing new model code generation tool: sqlacodegen

[Alex Grönholm]

18.05.2013 12:35, Chris Withers kirjoitti:

On 18/05/2013 01:28, Alex Grönholm wrote:

This is a tool that reads the structure of an existing database and
generates the appropriate SQLAlchemy model code, using the declarative
style if possible.


Playing devils advocate to get my head around this: why would you want 
to generate code when you could just reflect the models?


Chris


I can think of a number of reasons:

 * You'll have to write the classes manually otherwise (save for the
   column defs if you use reflection)
 * Reflection won't give you relationships -- you'd have to add them
   manually anyway
 * Reflection takes time and will slow down application startup,
   especially with large schemas
 * Automatic generation of schema migration scripts will not work
   unless you have a model that differs from the actual schema
 * You may need static analysis of the code

The more complex your schema is, the more useful automatic code 
generation will be for you. Of course, if you only do metadata 
reflection and don't use declarative, well, that's another matter. But 
most people want mapped classes to use in their apps.


--
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: Announcing new model code generation tool: sqlacodegen

[Alex Grönholm]

Forgot to add the link: https://pypi.python.org/pypi/sqlacodegen

-- 
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] Announcing new model code generation tool: sqlacodegen

[Alex Grönholm]

After a while of trying to fix sqlautocode, I ended up writing a new tool 
instead.

Copypasta from the README:
-

This is a tool that reads the structure of an existing database and 
generates the appropriate SQLAlchemy model code, using the declarative 
style if possible.
Features 
   
   - Supports SQLAlchemy 0.6.x - 0.8.x
   - Produces declarative code that almost looks like it was hand written
   - Produces PEP 8  compliant 
   code
   - Accurately determines relationships, including many-to-many, one-to-one
   - Excellent test coverage

-
It should work at least as well as sqlautocode. There is some room for 
future improvement, of course:

   - Autodetection of joined-table inheritance
   - Code generation for separate tables/classes/mappers instead of 
   declarative
   - Proper code generation for deferred/immediate foreign key constraints
   - Sorting out problems with CheckConstraints (there seems to be a 
   problem outside of sqlacodegen here)
   

-- 
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] Enum recipe on SQLAlchemy 0.8.0 final

[Alex Grönholm]

10.04.2013 18:06, Michael Bayer kirjoitti:


On Apr 10, 2013, at 5:10 AM, Alex Grönholm <mailto:alex.gronh...@nextday.fi>> wrote:



The following class works on 0.8.0b2 but not 0.8.0 final:

class EnumWrapper(SchemaType, TypeDecorator):
def __init__(self, cls):
kwargs = {'name': cls.__name__.lower()}
self.impl = Enum(*(obj.key for obj in cls.values), **kwargs)
self.wrapped = cls

def _set_table(self, table, column):
self.impl._set_table(table, column)

def process_bind_param(self, value, dialect):
if value is None:
return None
if isinstance(value, self.wrapped):
return value.key
elif isinstance(value, str):
if value not in self.wrapped.symbols:
raise TypeError('No such enum value in %s: %s' %
(self.wrapped.__name__, value))
return value
raise TypeError('Expected %s, got %s instead' %
(self.wrapped, type(value)))

def process_result_value(self, value, dialect):
return getattr(self.wrapped, value) if value is not None
else None


The error message ("TypeError: __init__() got an unexpected keyword 
argument 'schema'") originates from types.py, line 1886.
Is inheriting from SchemaType still necessary? That is what seems to 
break things on 0.8.0 final.


Line 1886 of types.py, both in 0.8.0 and in tip, is this:

t.drop(bind=bind, checkfirst=checkfirst)

so that doesn't seem like the line we're referring to.


My bad, it was line 1866: return impltype(name=self.name, ...)
Since your example lacks context, I had to modify the call to "Enum" 
to use a series of string names (don't know what obj.key, cls.values 
refer to).  From there, I managed to get a stack trace that refers to 
line 1863 of types.py.


In the documentation for TypeDecorator at 
http://docs.sqlalchemy.org/en/rel_0_8/core/types.html#sqlalchemy.types.TypeDecorator, 
the example includes a copy() method.   There is also a copy() method 
in the original Enum recipe on my blog at 
http://techspot.zzzeek.org/files/2011/decl_enum.py.


If the constructor of your decorated type is not compatible with the 
type you're wrapping, you need to provide copy() as well:


def copy(self):
return DeclEnumType(self.wrapped)
Yet, the error goes away once I add something like that. Strange though, 
everything worked fine even without a copy() method in 0.8.0b2.


Thanks!



hope this helps !


--
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/LE-EIznAIT4/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.

[sqlalchemy] Enum recipe on SQLAlchemy 0.8.0 final

[Alex Grönholm]

The following class works on 0.8.0b2 but not 0.8.0 final:
 

> class EnumWrapper(SchemaType, TypeDecorator):
> def __init__(self, cls):
> kwargs = {'name': cls.__name__.lower()}
> self.impl = Enum(*(obj.key for obj in cls.values), **kwargs)
> self.wrapped = cls
>
> def _set_table(self, table, column):
> self.impl._set_table(table, column)
>
> def process_bind_param(self, value, dialect):
> if value is None:
> return None
> if isinstance(value, self.wrapped):
> return value.key
> elif isinstance(value, str):
> if value not in self.wrapped.symbols:
> raise TypeError('No such enum value in %s: %s' % 
> (self.wrapped.__name__, value))
> return value
> raise TypeError('Expected %s, got %s instead' % (self.wrapped, 
> type(value)))
>
> def process_result_value(self, value, dialect):
> return getattr(self.wrapped, value) if value is not None else None
>

The error message ("TypeError: __init__() got an unexpected keyword 
argument 'schema'") originates from types.py, line 1886.
Is inheriting from SchemaType still necessary? That is what seems to break 
things on 0.8.0 final.

-- 
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] statement preparation in sa

[alex bodnaru]

hello friends,

i've just made an alternative sqlite driver, to overcome the dbapi short of
running multiple statements separated by ';'.
executescript would do the work, but it won't receive parameters.
hence, i'd like to perform the parameter substitution inside the driver, before
turning to executescript.
is there any proved way to go? any special paramstyle?

thanks in advance,
alex

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] What is the best way to load _all_ related entities into the session (recursively) of a given entity?

[alex]

Hi all,

I have an entity fetched from the database.
Now I want to load its related entities recursively to be sure to have all 
reconstructed and living in the session.
I need this in order to expunge them and add them to another session.

joinedload(*) with appropiate join_depth settings in the relations takes 
too long and needs alot of memory.

Is there an option to do that implicitly on a loaded entity, because I do 
not want to invoke each single relation to load it explicitly?

Cheers,
Alex

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To view this discussion on the web visit 
https://groups.google.com/d/msg/sqlalchemy/-/u0x0m3XEhXIJ.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] How do I create a child class from a parent class in SQLAlchemy's Joined Table Inheritance?

[Alex Chamberlain]

Thanks for your help. I've taken your advice and User inherits from 
db.Model only and I've defined a relationship back to Person.

It would be cool if in the future you could "walk" up and down the object 
tree, but this is my first project with SQLAlchemy.

Thanks again, it's very much appreciated.

Alex

On Monday, October 29, 2012 3:47:51 PM UTC, Michael Bayer wrote:
>
> there's an old ticket proposing to add the feature of "changing the class" 
> of an item but it has many tricky turns and corners and hasn't been worth 
> the large amount of effort it would take to make it work perfectly in all 
> cases.   It is of course much easier to do with single-table inheritance, 
> though.
>
> Ideally your application would be creating User or Person objects as 
> appropriate from the start.  If that's not how your system works, and it's 
> typical that Person objects become User objects later on, I'd use 
> composition for that.
>
>
>
>
> On Oct 29, 2012, at 11:43 AM, Alex Chamberlain wrote:
>
> So, there's no way to upgrade a Person to a User within the normal ORM?
>
> Thanks, 
>
> Alex
> On Oct 29, 2012 3:00 PM, "Michael Bayer" 
> > 
> wrote:
>
>>
>> On Oct 29, 2012, at 5:06 AM, Alex Chamberlain wrote:
>>
>> I posted this on StackOverflow (
>> http://stackoverflow.com/q/13109085/961353), but it received no answers 
>> so I'm hoping I'll have more luck here.
>>
>> I'm developing a small database where there are far more People than 
>> Users, so currently have the following Model.(I'm using Flask-SQLAlchemy 
>> and db is an instance of flask.ext.sqlalchemy.SQLAlchemy.)
>>
>> class Person(db.Model):
>>   __tablename__ = 'people'
>>   id   = db.Column(db.Integer, primary_key = True)
>>   forename = db.Column(db.String(64))
>>   surname  = db.Column(db.String(64))
>> 
>>   memberships = db.relationship('Membership', backref='person')
>> 
>>   @property
>>   def name(self):
>> return self.forename + ' ' + self.surname
>> 
>>   def __repr__(self):
>> return '' % (self.forename, self.surname)
>> 
>> class User(Person):
>>   __tablename__ = 'users'
>>   id   = db.Column(db.Integer, db.ForeignKey('people.id'), 
>> primary_key = True)
>>   email= db.Column(db.String(120), index = True, unique = True)
>>   role = db.Column(db.SmallInteger, default = ROLE_USER)
>> 
>>   salt = db.Column(db.BINARY(8))
>>   password = db.Column(db.BINARY(20))
>>
>>   def __repr__(self):
>> return '' % (self.email)
>>
>> It's working quite well, in that if I create a User then a Person also 
>> get's saved. The problem is creating a User when a Person already exists in 
>> the database.
>>
>> I have tried the following:
>>
>> >>> p = models.Person.query.get(3)
>> >>> u = models.User(id=p.id, email="exa...@example.com 
>> ")
>> >>> u.set_password('password')
>> >>> db.session.add(u)
>> >>> db.session.commit()
>> Traceback
>> ...
>> sqlalchemy.exc.IntegrityError: (IntegrityError) PRIMARY KEY must be 
>> unique u'INSERT INTO people (id, forename, surname) VALUES (?, ?, ?)' (3, 
>> None, None)
>>
>>
>> You're using joined table inheritance here, so the creation of a new User 
>> object means that a row for both "users" and "people" will be generated. 
>>  There's no such thing as a "User that points to a Person" in this model, 
>> there's only Persons and Users (who are also Persons).
>>
>> Your two options are to use composition instead of inheritance here (i.e. 
>> a one-to-one relationship) or to work around the model by manually 
>> inserting rows into "user", that is, 
>> session.execute(User.__table__.insert(), {params}).
>>
>>
>>
>> -- 
>> You received this message because you are subscribed to the Google Groups 
>> "sqlalchemy" group.
>> To post to this group, send email to sqlal...@googlegroups.com
>> .
>> To unsubscribe from this group, send email to 
>> sqlalchemy+...@googlegroups.com .
>> For more options, visit this group at 
>> http://groups.google.com/group/sqlalchemy?hl=en.
>>
>
> -- 
> You received this message because you are subscribed to the Google Groups 
> "sqlalchemy" group.
> To post to this group, send email to sqlal...@googlegroups.com
> .
> To unsubscribe from this group, send email to 
> sqlalchemy+...@googlegroups.com .
> For more options, visit this group at 
> http://groups.google.com/group/sqlalchemy?hl=en.
>
>
>

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To view this discussion on the web visit 
https://groups.google.com/d/msg/sqlalchemy/-/WIVjXOQhWEQJ.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] How do I create a child class from a parent class in SQLAlchemy's Joined Table Inheritance?

[Alex Chamberlain]

So, there's no way to upgrade a Person to a User within the normal ORM?

Thanks,

Alex
On Oct 29, 2012 3:00 PM, "Michael Bayer"  wrote:

>
> On Oct 29, 2012, at 5:06 AM, Alex Chamberlain wrote:
>
> I posted this on StackOverflow (http://stackoverflow.com/q/13109085/961353),
> but it received no answers so I'm hoping I'll have more luck here.
>
> I'm developing a small database where there are far more People than
> Users, so currently have the following Model.(I'm using Flask-SQLAlchemy
> and db is an instance of flask.ext.sqlalchemy.SQLAlchemy.)
>
> class Person(db.Model):
>   __tablename__ = 'people'
>   id   = db.Column(db.Integer, primary_key = True)
>   forename = db.Column(db.String(64))
>   surname  = db.Column(db.String(64))
>
>   memberships = db.relationship('Membership', backref='person')
>
>   @property
>   def name(self):
> return self.forename + ' ' + self.surname
>
>   def __repr__(self):
> return '' % (self.forename, self.surname)
>
> class User(Person):
>   __tablename__ = 'users'
>   id   = db.Column(db.Integer, db.ForeignKey('people.id'),
> primary_key = True)
>   email= db.Column(db.String(120), index = True, unique = True)
>   role = db.Column(db.SmallInteger, default = ROLE_USER)
>
>   salt = db.Column(db.BINARY(8))
>   password = db.Column(db.BINARY(20))
>
>   def __repr__(self):
> return '' % (self.email)
>
> It's working quite well, in that if I create a User then a Person also
> get's saved. The problem is creating a User when a Person already exists in
> the database.
>
> I have tried the following:
>
> >>> p = models.Person.query.get(3)
> >>> u = models.User(id=p.id, email="exam...@example.com")
> >>> u.set_password('password')
> >>> db.session.add(u)
> >>> db.session.commit()
> Traceback
> ...
> sqlalchemy.exc.IntegrityError: (IntegrityError) PRIMARY KEY must be
> unique u'INSERT INTO people (id, forename, surname) VALUES (?, ?, ?)' (3,
> None, None)
>
>
> You're using joined table inheritance here, so the creation of a new User
> object means that a row for both "users" and "people" will be generated.
>  There's no such thing as a "User that points to a Person" in this model,
> there's only Persons and Users (who are also Persons).
>
> Your two options are to use composition instead of inheritance here (i.e.
> a one-to-one relationship) or to work around the model by manually
> inserting rows into "user", that is,
> session.execute(User.__table__.insert(), {params}).
>
>
>  --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> To unsubscribe from this group, send email to
> sqlalchemy+unsubscr...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
>

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] How do I create a child class from a parent class in SQLAlchemy's Joined Table Inheritance?

[Alex Chamberlain]

I posted this on StackOverflow 
(http://stackoverflow.com/q/13109085/961353), but it received no answers so 
I'm hoping I'll have more luck here.

I'm developing a small database where there are far more People than Users, 
so currently have the following Model.(I'm using Flask-SQLAlchemy and db is 
an instance of flask.ext.sqlalchemy.SQLAlchemy.)

class Person(db.Model):
  __tablename__ = 'people'
  id   = db.Column(db.Integer, primary_key = True)
  forename = db.Column(db.String(64))
  surname  = db.Column(db.String(64))

  memberships = db.relationship('Membership', backref='person')

  @property
  def name(self):
return self.forename + ' ' + self.surname

  def __repr__(self):
return '' % (self.forename, self.surname)

class User(Person):
  __tablename__ = 'users'
  id   = db.Column(db.Integer, db.ForeignKey('people.id'), 
primary_key = True)
  email= db.Column(db.String(120), index = True, unique = True)
  role = db.Column(db.SmallInteger, default = ROLE_USER)

  salt = db.Column(db.BINARY(8))
  password = db.Column(db.BINARY(20))

  def __repr__(self):
return '' % (self.email)

It's working quite well, in that if I create a User then a Person also 
get's saved. The problem is creating a User when a Person already exists in 
the database.

I have tried the following:

>>> p = models.Person.query.get(3)
>>> u = models.User(id=p.id, email="exam...@example.com")
>>> u.set_password('password')
>>> db.session.add(u)
>>> db.session.commit()
Traceback
...
sqlalchemy.exc.IntegrityError: (IntegrityError) PRIMARY KEY must be 
unique u'INSERT INTO people (id, forename, surname) VALUES (?, ?, ?)' (3, 
None, None)

What am I doing wrong? Is there a way to create a User from a Person?

Thanks in advance,

Alex

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To view this discussion on the web visit 
https://groups.google.com/d/msg/sqlalchemy/-/yYDW-COkmvkJ.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] Automatically Adding Casting to 'CASE' Expressions in Dialects

[Alex D.]

Roger that, thanks for the prompt response.

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To view this discussion on the web visit 
https://groups.google.com/d/msg/sqlalchemy/-/XU4Zfrz8KJQJ.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] Automatically Adding Casting to 'CASE' Expressions in Dialects

[Alex D.]

Is there a good way to have a dialect automatically add casting to 'THEN' 
expressions in 'CASE' statements?

I'm working on a dialect for the H2 database, via Jython.  H2 requires 
values in a 'THEN' expression to have explicit casts (see: Unknown data 
type thrown with a case statement where all values are parameters in 
preparedStatement
 )

I would like to make it so that my H2 dialect will automatically add 
casting to 'CASE' expressions, so that users of the dialect don't have to 
use literal_columns each time they want to use a 'CASE' expression.

One idea I had was to add a 'before_execute' event that checks elements to 
see if their case statements, but that seems a bit kludgy.  is there a 
better way?


Levity:
Q: What do a database developer and a hollywood producer have in common?
A: They both can have problems with casting.

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To view this discussion on the web visit 
https://groups.google.com/d/msg/sqlalchemy/-/O20TVZRH2wAJ.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] making bindparms required by default

[alex bodnaru]

On 08/27/2012 10:13 PM, Michael Bayer wrote:
> I'm super-hot to get the first betas of 0.8 out the door, and as this is a 
> backwards-compatability-sensitive change, I was in a super rush to get this 
> in, so it's in tip.
> 
seen it' thanks again :).
> 
> 
> On Aug 27, 2012, at 12:49 PM, Michael Bayer wrote:
> 
>> Here's a patch:
>>
>> diff -r 594b0162e8a5 lib/sqlalchemy/sql/expression.py
>> --- a/lib/sqlalchemy/sql/expression.py   Mon Aug 27 12:33:10 2012 -0400
>> +++ b/lib/sqlalchemy/sql/expression.py   Mon Aug 27 12:43:48 2012 -0400
>> @@ -3110,7 +3110,7 @@
>> typemap[key] = sqltypes.to_instance(typemap[key])
>>
>> def repl(m):
>> -self.bindparams[m.group(1)] = bindparam(m.group(1))
>> +self.bindparams[m.group(1)] = bindparam(m.group(1), 
>> required=True)
>> return ':%s' % m.group(1)
>>
>> # scan the string and search for bind parameter names, add them
>>
>>
>> however, this is too easy.  There's lots of other places binds are 
>> generated.   A rule such that if "value" is not passed to bindparam(), then 
>> flip the required flag on, would be the best behavior.  needs a few lines in 
>> the bindparam() function, but also would need a lot of tests, in 
>> test/sql/test_query.py.   ticket #2556 is added.
>>
>> for your immediate needs, you can make a function that returns a Text() 
>> construct, and just supplies a dictionary of bindparam() objects to the 
>> Text() pre-established, with the required=True flag turned on.
>>
>>
>>
>>
>>
>>
>> On Aug 27, 2012, at 12:06 PM, alex bodnaru wrote:
>>
>>>
>>> hello friends,
>>>
>>> for a migrated system, i'm using textual queries in the form:
>>>
>>> dbsession.execute("select name from table where id=:id", dict(id=1))
>>>
>>> to my surprise, "select ... id=:idd" would still "work", asuming idd is 
>>> null,
>>> despite 'idd' is not in bindparms.
>>
>>
>>
>>
>>>
>>> a 'required' argument to bindparam in _TextClause __init__() would indeed 
>>> make
>>> the 'idd' column required, thus raise an exception when not found in bind
>>> dict(id=1).
>>>
>>> is there an official way to acomplish this, or should i just hack in 
>>> hardcoded?
>>>
>>> thanks in advance,
>>> alex
>>>
>>> -- 
>>> You received this message because you are subscribed to the Google Groups 
>>> "sqlalchemy" group.
>>> To post to this group, send email to sqlalchemy@googlegroups.com.
>>> To unsubscribe from this group, send email to 
>>> sqlalchemy+unsubscr...@googlegroups.com.
>>> For more options, visit this group at 
>>> http://groups.google.com/group/sqlalchemy?hl=en.
>>>
>>
>> -- 
>> You received this message because you are subscribed to the Google Groups 
>> "sqlalchemy" group.
>> To post to this group, send email to sqlalchemy@googlegroups.com.
>> To unsubscribe from this group, send email to 
>> sqlalchemy+unsubscr...@googlegroups.com.
>> For more options, visit this group at 
>> http://groups.google.com/group/sqlalchemy?hl=en.
>>
> 

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] making bindparms required by default

[alex bodnaru]

thanks michael,

On 08/27/2012 07:49 PM, Michael Bayer wrote:
> Here's a patch:
> 
> diff -r 594b0162e8a5 lib/sqlalchemy/sql/expression.py
> --- a/lib/sqlalchemy/sql/expression.pyMon Aug 27 12:33:10 2012 -0400
> +++ b/lib/sqlalchemy/sql/expression.pyMon Aug 27 12:43:48 2012 -0400
> @@ -3110,7 +3110,7 @@
>  typemap[key] = sqltypes.to_instance(typemap[key])
>  
>  def repl(m):
> -self.bindparams[m.group(1)] = bindparam(m.group(1))
> +self.bindparams[m.group(1)] = bindparam(m.group(1), 
> required=True)
>  return ':%s' % m.group(1)
>  
>  # scan the string and search for bind parameter names, add them
> 
that's what i did indeed.
but it would break queries that are happy with the null on missing default.
thus, a configurable option, false by default, would do the job.
> 
> however, this is too easy.  There's lots of other places binds are generated. 
>   A rule such that if "value" is not passed to bindparam(), then flip the 
> required flag on, would be the best behavior.  needs a few lines in the 
> bindparam() function, but also would need a lot of tests, in 
> test/sql/test_query.py.   ticket #2556 is added.
thank you very much. the patch is very sensible, but it will certainly break
scripts that rely on the null on missing behaviour.
however, i don't have such scripts ;).
> 
> for your immediate needs, you can make a function that returns a Text() 
> construct, and just supplies a dictionary of bindparam() objects to the 
> Text() pre-established, with the required=True flag turned on.
> 
thought of it too, but it won't do the work: i needed to catch :variables
missing in the given bindparm.
> 
> 
> 
thanks a lot,
alex
> 
> 
> On Aug 27, 2012, at 12:06 PM, alex bodnaru wrote:
> 
>>
>> hello friends,
>>
>> for a migrated system, i'm using textual queries in the form:
>>
>> dbsession.execute("select name from table where id=:id", dict(id=1))
>>
>> to my surprise, "select ... id=:idd" would still "work", asuming idd is null,
>> despite 'idd' is not in bindparms.
> 
> 
> 
> 
>>
>> a 'required' argument to bindparam in _TextClause __init__() would indeed 
>> make
>> the 'idd' column required, thus raise an exception when not found in bind
>> dict(id=1).
>>
>> is there an official way to acomplish this, or should i just hack in 
>> hardcoded?
>>
>> thanks in advance,
>> alex
>>
>> -- 
>> You received this message because you are subscribed to the Google Groups 
>> "sqlalchemy" group.
>> To post to this group, send email to sqlalchemy@googlegroups.com.
>> To unsubscribe from this group, send email to 
>> sqlalchemy+unsubscr...@googlegroups.com.
>> For more options, visit this group at 
>> http://groups.google.com/group/sqlalchemy?hl=en.
>>
> 

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] making bindparms required by default

[alex bodnaru]

hello friends,

for a migrated system, i'm using textual queries in the form:

dbsession.execute("select name from table where id=:id", dict(id=1))

to my surprise, "select ... id=:idd" would still "work", asuming idd is null,
despite 'idd' is not in bindparms.

a 'required' argument to bindparam in _TextClause __init__() would indeed make
the 'idd' column required, thus raise an exception when not found in bind
dict(id=1).

is there an official way to acomplish this, or should i just hack in hardcoded?

thanks in advance,
alex

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] dialect sensible declaration

[alex bodnaru]

thank you very much michael.

both ways worked like a charm.

i have implemented the other way, though this is better and more general: any
constraint with onupdate or ondelete attributes should have these attributes set
to none if the dialect name is mssql.

thanks a lot,
alex

On 07/16/2012 05:10 PM, Michael Bayer wrote:
> thanks - though one thought I had is, why not just modify an existing 
> ForeignKeyConstraint.onupdate directly in the event, instead of trying to add 
> the constraint later and confusing the ORM.  you wouldn't need the 
> relationship arguments then.
> 
> the constraints for the table are in table.constraints, its a set and you'd 
> iterate through to find the ForeignKeyConstraint with the name you're looking 
> for.
> 
> if you can try that out let me know.
> 
> 
> 
> 
> On Jul 16, 2012, at 5:18 AM, alex bodnaru wrote:
> 
>>
>> hello michael, friends,
>>
>> here is my test case.
>> thanks a lot for your consideraion. please take a look.
>>
>>
>> from sqlalchemy import create_engine
>> from sqlalchemy.ext.declarative import declarative_base
>> from sqlalchemy import Column, Integer, String, Unicode, ForeignKeyConstraint
>> from sqlalchemy.orm import relation, backref, sessionmaker
>> from sqlalchemy.event import listen
>>
>>
>> engine = create_engine('sqlite:///:memory:', echo=True)
>> Base = declarative_base()
>>
>> class Lang(Base):
>>__tablename__ = 'lang'
>>lang_code = Column(String(20), primary_key=True)
>>lang_name = Column(Unicode(64))
>>
>> class PageData(Base):
>>__tablename__ = 'pagedata'
>>lang_code = Column(String(20), primary_key=True)
>>lang = relation('Lang', backref='pages',
>>primaryjoin=lang_code==Lang.lang_code,
>>foreign_keys=[Lang.lang_code], uselist=False)
>>data = Column(Unicode(64), nullable=True)
>>
>> def add_lang_fk(table, conn, **kw):
>>if 'added_lang_fk' not in table.info:
>>params = dict(ondelete='cascade')
>>if conn.dialect.name != 'mssql':
>>params['onupdate'] = 'restrict'
>>table.append_constraint(
>>ForeignKeyConstraint(
>>['lang_code'], [Lang.__tablename__ + '.lang_code'], **params))
>>table.info['added_lang_fk'] = True
>>
>> listen(PageData.__table__, "before_create", add_lang_fk)
>>
>> Base.metadata.create_all(engine)
>>
>>
>> Session = sessionmaker(bind=engine)
>> session = Session()
>>
>> de = Lang(lang_code='de', lang_name='Deutsch')
>> session.add(de)
>>
>> data = PageData(lang_code='de', data=u"Vielen Dank im Voraus") #this works
>> data = PageData(lang=de, data=u"Vielen Dank im Voraus") #this fails
>> session.add(data)
>>
>> session.flush()
>>
>>
>> thanks in advance,
>> alex
>>
>> On 07/15/2012 07:08 PM, Michael Bayer wrote:
>>>
>>> On Jul 15, 2012, at 5:53 AM, alex bodnaru wrote:
>>>
>>>>
>>>> hello michael, friends,
>>>>
>>>> On 07/11/2012 10:31 AM, alex bodnaru wrote:
>>>>>
>>>>> hello michael,
>>>>>
>>>>> now it works. i also had to add uselist=False.
>>>>>
>>>>> i tried it the longest way possible, by adding a Pool first_connect 
>>>>> listener,
>>>>> but this was not really needed. just the uselist.
>>>>>
>>>>> thanks a lot,
>>>>> alex
>>>>>
>>>> sorry, not yet:
>>>>
>>>> the relationship should also allow assignment like this:
>>>>
>>>> class Lang(DeclarativeBase):
>>>>  lang_code = Column(String(20), primary_key=True)
>>>>  lang_name = Column(String(20))
>>>>
>>>> class PageData(DeclarativeBase):
>>>>  lang_code = Column(String(20), primary_key=True)
>>>>  lang = relation('Lang', backref='pages',
>>>> primaryjoin=lang_code==Lang.lang_code, foreign_keys=[Lang.lang_code], 
>>>> uselist=False)
>>>>
>>>> the PageData.lang_code foreign key is being added in an event on before 
>>>> create.
>>>>
>>>> before delaying creation of the foreign key, i could do like this:
>>>>
>>>>

Re: [sqlalchemy] dialect sensible declaration

[alex bodnaru]

hello michael, friends,

here is my test case.
thanks a lot for your consideraion. please take a look.


from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, Unicode, ForeignKeyConstraint
from sqlalchemy.orm import relation, backref, sessionmaker
from sqlalchemy.event import listen


engine = create_engine('sqlite:///:memory:', echo=True)
Base = declarative_base()

class Lang(Base):
__tablename__ = 'lang'
lang_code = Column(String(20), primary_key=True)
lang_name = Column(Unicode(64))

class PageData(Base):
__tablename__ = 'pagedata'
lang_code = Column(String(20), primary_key=True)
lang = relation('Lang', backref='pages',
primaryjoin=lang_code==Lang.lang_code,
foreign_keys=[Lang.lang_code], uselist=False)
data = Column(Unicode(64), nullable=True)

def add_lang_fk(table, conn, **kw):
if 'added_lang_fk' not in table.info:
params = dict(ondelete='cascade')
if conn.dialect.name != 'mssql':
params['onupdate'] = 'restrict'
table.append_constraint(
ForeignKeyConstraint(
['lang_code'], [Lang.__tablename__ + '.lang_code'], **params))
table.info['added_lang_fk'] = True

listen(PageData.__table__, "before_create", add_lang_fk)

Base.metadata.create_all(engine)


Session = sessionmaker(bind=engine)
session = Session()

de = Lang(lang_code='de', lang_name='Deutsch')
session.add(de)

data = PageData(lang_code='de', data=u"Vielen Dank im Voraus") #this works
data = PageData(lang=de, data=u"Vielen Dank im Voraus") #this fails
session.add(data)

session.flush()


thanks in advance,
alex

On 07/15/2012 07:08 PM, Michael Bayer wrote:
> 
> On Jul 15, 2012, at 5:53 AM, alex bodnaru wrote:
> 
>>
>> hello michael, friends,
>>
>> On 07/11/2012 10:31 AM, alex bodnaru wrote:
>>>
>>> hello michael,
>>>
>>> now it works. i also had to add uselist=False.
>>>
>>> i tried it the longest way possible, by adding a Pool first_connect 
>>> listener,
>>> but this was not really needed. just the uselist.
>>>
>>> thanks a lot,
>>> alex
>>>
>> sorry, not yet:
>>
>> the relationship should also allow assignment like this:
>>
>> class Lang(DeclarativeBase):
>>   lang_code = Column(String(20), primary_key=True)
>>   lang_name = Column(String(20))
>>
>> class PageData(DeclarativeBase):
>>   lang_code = Column(String(20), primary_key=True)
>>   lang = relation('Lang', backref='pages',
>> primaryjoin=lang_code==Lang.lang_code, foreign_keys=[Lang.lang_code], 
>> uselist=False)
>>
>> the PageData.lang_code foreign key is being added in an event on before 
>> create.
>>
>> before delaying creation of the foreign key, i could do like this:
>>
>> p = PageData()
>> p.lang = Lang.get('en')
>>
>> and p.lang_code got assigned. why isn't lang_code being assigned now anymore?
> 
> it would imply the relationship is not working at all.
> 
> like before: 
> 
>>>>  can you just throw these two classes, the event, and some imports into a 
>>>> file for me ?  I can just run it.
> 
> much quicker than lots of back and forth.
> 
> 
> 
> 
> 
>>
>> thanks in advance,
>> alex
>>> On 07/09/2012 04:25 PM, Michael Bayer wrote:
>>>>
>>>> On Jul 9, 2012, at 4:48 AM, alex bodnaru wrote:
>>>>
>>>>>
>>>>> hello michael, friends,
>>>>>
>>>>> after successfuly fixing the ddl by the append_constraint event, the 
>>>>> relations
>>>>> that needed the said foreign keys remained orphan, asking for a 
>>>>> foreign_keys
>>>>> argument and failing to load the remote table:
>>>>>
>>>>> class Lang(DeclarativeBase):
>>>>>   lang_code = Column(String(20), primary_key=True)
>>>>>   lang_name = Column(String(20))
>>>>>
>>>>> class PageData(DeclarativeBase):
>>>>>   lang_code = Column(String(20), primary_key=True) # this foreign key is 
>>>>> being
>>>>> successfully appended on before_create.
>>>>>   lang = relation('Lang', backref='pages',
>>>>> primaryjoin=lang_code==Lang.lang_code) #this relationship won't work, 
>>>>> since 

Re: [sqlalchemy] dialect sensible declaration

[alex bodnaru]

hello michael, friends,

On 07/11/2012 10:31 AM, alex bodnaru wrote:
> 
> hello michael,
> 
> now it works. i also had to add uselist=False.
> 
> i tried it the longest way possible, by adding a Pool first_connect listener,
> but this was not really needed. just the uselist.
> 
> thanks a lot,
> alex
> 
sorry, not yet:

the relationship should also allow assignment like this:

class Lang(DeclarativeBase):
   lang_code = Column(String(20), primary_key=True)
   lang_name = Column(String(20))

class PageData(DeclarativeBase):
   lang_code = Column(String(20), primary_key=True)
   lang = relation('Lang', backref='pages',
primaryjoin=lang_code==Lang.lang_code, foreign_keys=[Lang.lang_code], 
uselist=False)

the PageData.lang_code foreign key is being added in an event on before create.

before delaying creation of the foreign key, i could do like this:

p = PageData()
p.lang = Lang.get('en')

and p.lang_code got assigned. why isn't lang_code being assigned now anymore?

thanks in advance,
alex
> On 07/09/2012 04:25 PM, Michael Bayer wrote:
>>
>> On Jul 9, 2012, at 4:48 AM, alex bodnaru wrote:
>>
>>>
>>> hello michael, friends,
>>>
>>> after successfuly fixing the ddl by the append_constraint event, the 
>>> relations
>>> that needed the said foreign keys remained orphan, asking for a foreign_keys
>>> argument and failing to load the remote table:
>>>
>>> class Lang(DeclarativeBase):
>>>lang_code = Column(String(20), primary_key=True)
>>>lang_name = Column(String(20))
>>>
>>> class PageData(DeclarativeBase):
>>>lang_code = Column(String(20), primary_key=True) # this foreign key is 
>>> being
>>> successfully appended on before_create.
>>>lang = relation('Lang', backref='pages',
>>> primaryjoin=lang_code==Lang.lang_code) #this relationship won't work, since 
>>> at
>>> the moment the class is being made, the foreign key is not there yet.
>>>
>>> the foreign_keys=Lang.lang_code arg does calm the exception, but doesn't do 
>>> the
>>> work.
>>> could i add the relationship to the mapper on the same event?
>>
>> I would think "foreign_keys" should fix the problem totally, what do you 
>> mean "doesn't do the work"?  I'd have to work up a test case, can you just 
>> throw these two classes, the event, and some imports into a file for me ?  I 
>> can just run it.
>>
> well, almost totally ;)
> it also needed uselist=False.
> 
>>
>>>
>>> thank in advance,
>>> alex
>>>
>>> On 07/07/2012 05:13 PM, Michael Bayer wrote:
>>>> sure engine and connection have .dialect.name.   Foreign key constraints 
>>>> don't matter on SQLite unless you've actually enabled them, which is rare. 
>>>>   I'd still use an event though so at least the behavior is transparent.
>>>>
>>>> @event.listens_for(my_table, "before_create")
>>>> def add_fk(table, conn, **kw):
>>>>if conn.dialect.name != 'mssql':
>>>>table.append_constraint(ForeignKeyConstraint(...))
>>>>
>>>> tricky though to modify the table metadata within a "create" event in the 
>>>> case that the table is created multiple times in an app.  you can put a 
>>>> flag in table.info, like "table.info['added_the_fk'] = True", to keep 
>>>> track of things.
>>>>
>>>>
>>>>
>>>> On Jul 7, 2012, at 12:59 AM, alex bodnaru wrote:
>>>>
>>>>>
>>>>> hello mike and thanks for your answer.
>>>>>
>>>>> no problem with ForeignKeyConstraint, but wouldn't AddConstraint go the 
>>>>> alter
>>>>> way? in this case, it will be ignored by the sqlite dialect.
>>>>>
>>>>> what i was looking for was more like:
>>>>>
>>>>> from sqlalchemy... import get_dialect
>>>>>
>>>>> 
>>>>> fk_parms = dict(.)
>>>>> if get_dialect() != 'mssql':
>>>>>   fk_parms.update(onupdate='restrict')
>>>>> fk = ForeignKey(**fk_parms)
>>>>>
>>>>> would the dialect be accessible from the engine, metadata etc?
>>>>>
>>>>> thanks in advance,
>>>>> alex
>>>>>
>>>>>
>>>>> On 07/06/2012

Re: [sqlalchemy] dialect sensible declaration

[alex bodnaru]

hello michael,

now it works. i also had to add uselist=False.

i tried it the longest way possible, by adding a Pool first_connect listener,
but this was not really needed. just the uselist.

thanks a lot,
alex

On 07/09/2012 04:25 PM, Michael Bayer wrote:
> 
> On Jul 9, 2012, at 4:48 AM, alex bodnaru wrote:
> 
>>
>> hello michael, friends,
>>
>> after successfuly fixing the ddl by the append_constraint event, the 
>> relations
>> that needed the said foreign keys remained orphan, asking for a foreign_keys
>> argument and failing to load the remote table:
>>
>> class Lang(DeclarativeBase):
>>lang_code = Column(String(20), primary_key=True)
>>lang_name = Column(String(20))
>>
>> class PageData(DeclarativeBase):
>>lang_code = Column(String(20), primary_key=True) # this foreign key is 
>> being
>> successfully appended on before_create.
>>lang = relation('Lang', backref='pages',
>> primaryjoin=lang_code==Lang.lang_code) #this relationship won't work, since 
>> at
>> the moment the class is being made, the foreign key is not there yet.
>>
>> the foreign_keys=Lang.lang_code arg does calm the exception, but doesn't do 
>> the
>> work.
>> could i add the relationship to the mapper on the same event?
> 
> I would think "foreign_keys" should fix the problem totally, what do you mean 
> "doesn't do the work"?  I'd have to work up a test case, can you just throw 
> these two classes, the event, and some imports into a file for me ?  I can 
> just run it.
> 
well, almost totally ;)
it also needed uselist=False.

> 
>>
>> thank in advance,
>> alex
>>
>> On 07/07/2012 05:13 PM, Michael Bayer wrote:
>>> sure engine and connection have .dialect.name.   Foreign key constraints 
>>> don't matter on SQLite unless you've actually enabled them, which is rare.  
>>>  I'd still use an event though so at least the behavior is transparent.
>>>
>>> @event.listens_for(my_table, "before_create")
>>> def add_fk(table, conn, **kw):
>>>if conn.dialect.name != 'mssql':
>>>table.append_constraint(ForeignKeyConstraint(...))
>>>
>>> tricky though to modify the table metadata within a "create" event in the 
>>> case that the table is created multiple times in an app.  you can put a 
>>> flag in table.info, like "table.info['added_the_fk'] = True", to keep track 
>>> of things.
>>>
>>>
>>>
>>> On Jul 7, 2012, at 12:59 AM, alex bodnaru wrote:
>>>
>>>>
>>>> hello mike and thanks for your answer.
>>>>
>>>> no problem with ForeignKeyConstraint, but wouldn't AddConstraint go the 
>>>> alter
>>>> way? in this case, it will be ignored by the sqlite dialect.
>>>>
>>>> what i was looking for was more like:
>>>>
>>>> from sqlalchemy... import get_dialect
>>>>
>>>> 
>>>> fk_parms = dict(.)
>>>> if get_dialect() != 'mssql':
>>>>fk_parms.update(onupdate='restrict')
>>>> fk = ForeignKey(**fk_parms)
>>>>
>>>> would the dialect be accessible from the engine, metadata etc?
>>>>
>>>> thanks in advance,
>>>> alex
>>>>
>>>>
>>>> On 07/06/2012 11:39 PM, Michael Bayer wrote:
>>>>> you'd use ForeignKeyConstraint along with the AddConstraint directive, 
>>>>> and limit it per-dialect using create/drop events as documented at 
>>>>> http://docs.sqlalchemy.org/en/rel_0_7/core/schema.html#controlling-ddl-sequences
>>>>>  .
>>>>>
>>>>>
>>>>> On Jul 6, 2012, at 1:30 PM, alex bodnaru wrote:
>>>>>
>>>>>>
>>>>>> hello friends,
>>>>>>
>>>>>> i need to define a foreign key differently for different dialects:
>>>>>> ondelete='restrict' for most engines, but nothing (implied and not 
>>>>>> recognized)
>>>>>> for mssql.
>>>>>>
>>>>>> could you help?
>>>>>>
>>>>>> thanks in advance,
>>>>>> alex
>>>>>>
>>>>>> -- 
>>>>>> You received this message because you are subscribed to the Google 
>>>>>> Groups "sqlalchemy" gro

Re: [sqlalchemy] dialect sensible declaration

[alex bodnaru]

hello michael, friends,

after successfuly fixing the ddl by the append_constraint event, the relations
that needed the said foreign keys remained orphan, asking for a foreign_keys
argument and failing to load the remote table:

class Lang(DeclarativeBase):
lang_code = Column(String(20), primary_key=True)
lang_name = Column(String(20))

class PageData(DeclarativeBase):
lang_code = Column(String(20), primary_key=True) # this foreign key is being
successfully appended on before_create.
lang = relation('Lang', backref='pages',
primaryjoin=lang_code==Lang.lang_code) #this relationship won't work, since at
the moment the class is being made, the foreign key is not there yet.

the foreign_keys=Lang.lang_code arg does calm the exception, but doesn't do the
work.
could i add the relationship to the mapper on the same event?

thank in advance,
alex

On 07/07/2012 05:13 PM, Michael Bayer wrote:
> sure engine and connection have .dialect.name.   Foreign key constraints 
> don't matter on SQLite unless you've actually enabled them, which is rare.   
> I'd still use an event though so at least the behavior is transparent.
> 
> @event.listens_for(my_table, "before_create")
> def add_fk(table, conn, **kw):
> if conn.dialect.name != 'mssql':
> table.append_constraint(ForeignKeyConstraint(...))
> 
> tricky though to modify the table metadata within a "create" event in the 
> case that the table is created multiple times in an app.  you can put a flag 
> in table.info, like "table.info['added_the_fk'] = True", to keep track of 
> things.
> 
> 
> 
> On Jul 7, 2012, at 12:59 AM, alex bodnaru wrote:
> 
>>
>> hello mike and thanks for your answer.
>>
>> no problem with ForeignKeyConstraint, but wouldn't AddConstraint go the alter
>> way? in this case, it will be ignored by the sqlite dialect.
>>
>> what i was looking for was more like:
>>
>> from sqlalchemy... import get_dialect
>>
>> 
>> fk_parms = dict(.)
>> if get_dialect() != 'mssql':
>>  fk_parms.update(onupdate='restrict')
>> fk = ForeignKey(**fk_parms)
>>
>> would the dialect be accessible from the engine, metadata etc?
>>
>> thanks in advance,
>> alex
>>
>>
>> On 07/06/2012 11:39 PM, Michael Bayer wrote:
>>> you'd use ForeignKeyConstraint along with the AddConstraint directive, and 
>>> limit it per-dialect using create/drop events as documented at 
>>> http://docs.sqlalchemy.org/en/rel_0_7/core/schema.html#controlling-ddl-sequences
>>>  .
>>>
>>>
>>> On Jul 6, 2012, at 1:30 PM, alex bodnaru wrote:
>>>
>>>>
>>>> hello friends,
>>>>
>>>> i need to define a foreign key differently for different dialects:
>>>> ondelete='restrict' for most engines, but nothing (implied and not 
>>>> recognized)
>>>> for mssql.
>>>>
>>>> could you help?
>>>>
>>>> thanks in advance,
>>>> alex
>>>>
>>>> -- 
>>>> You received this message because you are subscribed to the Google Groups 
>>>> "sqlalchemy" group.
>>>> To post to this group, send email to sqlalchemy@googlegroups.com.
>>>> To unsubscribe from this group, send email to 
>>>> sqlalchemy+unsubscr...@googlegroups.com.
>>>> For more options, visit this group at 
>>>> http://groups.google.com/group/sqlalchemy?hl=en.
>>>>
>>>
>>
>> -- 
>> You received this message because you are subscribed to the Google Groups 
>> "sqlalchemy" group.
>> To post to this group, send email to sqlalchemy@googlegroups.com.
>> To unsubscribe from this group, send email to 
>> sqlalchemy+unsubscr...@googlegroups.com.
>> For more options, visit this group at 
>> http://groups.google.com/group/sqlalchemy?hl=en.
>>
> 

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] dialect sensible declaration

[alex bodnaru]

it worked very well,

thanks a lot michael :),

alex

On 07/07/2012 05:13 PM, Michael Bayer wrote:
> sure engine and connection have .dialect.name.   Foreign key constraints 
> don't matter on SQLite unless you've actually enabled them, which is rare.   
> I'd still use an event though so at least the behavior is transparent.
> 
> @event.listens_for(my_table, "before_create")
> def add_fk(table, conn, **kw):
> if conn.dialect.name != 'mssql':
> table.append_constraint(ForeignKeyConstraint(...))
> 
> tricky though to modify the table metadata within a "create" event in the 
> case that the table is created multiple times in an app.  you can put a flag 
> in table.info, like "table.info['added_the_fk'] = True", to keep track of 
> things.
> 
> 
> 
> On Jul 7, 2012, at 12:59 AM, alex bodnaru wrote:
> 
>>
>> hello mike and thanks for your answer.
>>
>> no problem with ForeignKeyConstraint, but wouldn't AddConstraint go the alter
>> way? in this case, it will be ignored by the sqlite dialect.
>>
>> what i was looking for was more like:
>>
>> from sqlalchemy... import get_dialect
>>
>> 
>> fk_parms = dict(.)
>> if get_dialect() != 'mssql':
>>  fk_parms.update(onupdate='restrict')
>> fk = ForeignKey(**fk_parms)
>>
>> would the dialect be accessible from the engine, metadata etc?
>>
>> thanks in advance,
>> alex
>>
>>
>> On 07/06/2012 11:39 PM, Michael Bayer wrote:
>>> you'd use ForeignKeyConstraint along with the AddConstraint directive, and 
>>> limit it per-dialect using create/drop events as documented at 
>>> http://docs.sqlalchemy.org/en/rel_0_7/core/schema.html#controlling-ddl-sequences
>>>  .
>>>
>>>
>>> On Jul 6, 2012, at 1:30 PM, alex bodnaru wrote:
>>>
>>>>
>>>> hello friends,
>>>>
>>>> i need to define a foreign key differently for different dialects:
>>>> ondelete='restrict' for most engines, but nothing (implied and not 
>>>> recognized)
>>>> for mssql.
>>>>
>>>> could you help?
>>>>
>>>> thanks in advance,
>>>> alex
>>>>
>>>> -- 
>>>> You received this message because you are subscribed to the Google Groups 
>>>> "sqlalchemy" group.
>>>> To post to this group, send email to sqlalchemy@googlegroups.com.
>>>> To unsubscribe from this group, send email to 
>>>> sqlalchemy+unsubscr...@googlegroups.com.
>>>> For more options, visit this group at 
>>>> http://groups.google.com/group/sqlalchemy?hl=en.
>>>>
>>>
>>
>> -- 
>> You received this message because you are subscribed to the Google Groups 
>> "sqlalchemy" group.
>> To post to this group, send email to sqlalchemy@googlegroups.com.
>> To unsubscribe from this group, send email to 
>> sqlalchemy+unsubscr...@googlegroups.com.
>> For more options, visit this group at 
>> http://groups.google.com/group/sqlalchemy?hl=en.
>>
> 

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] dialect sensible declaration

[alex bodnaru]

thanks a lot michael.

i think i'll go this way :)

best regards,
alex

On 07/07/2012 05:13 PM, Michael Bayer wrote:
> sure engine and connection have .dialect.name.   Foreign key constraints 
> don't matter on SQLite unless you've actually enabled them, which is rare.   
> I'd still use an event though so at least the behavior is transparent.
> 
> @event.listens_for(my_table, "before_create")
> def add_fk(table, conn, **kw):
> if conn.dialect.name != 'mssql':
> table.append_constraint(ForeignKeyConstraint(...))
> 
> tricky though to modify the table metadata within a "create" event in the 
> case that the table is created multiple times in an app.  you can put a flag 
> in table.info, like "table.info['added_the_fk'] = True", to keep track of 
> things.
> 
> 
> 
> On Jul 7, 2012, at 12:59 AM, alex bodnaru wrote:
> 
>>
>> hello mike and thanks for your answer.
>>
>> no problem with ForeignKeyConstraint, but wouldn't AddConstraint go the alter
>> way? in this case, it will be ignored by the sqlite dialect.
>>
>> what i was looking for was more like:
>>
>> from sqlalchemy... import get_dialect
>>
>> 
>> fk_parms = dict(.)
>> if get_dialect() != 'mssql':
>>  fk_parms.update(onupdate='restrict')
>> fk = ForeignKey(**fk_parms)
>>
>> would the dialect be accessible from the engine, metadata etc?
>>
>> thanks in advance,
>> alex
>>
>>
>> On 07/06/2012 11:39 PM, Michael Bayer wrote:
>>> you'd use ForeignKeyConstraint along with the AddConstraint directive, and 
>>> limit it per-dialect using create/drop events as documented at 
>>> http://docs.sqlalchemy.org/en/rel_0_7/core/schema.html#controlling-ddl-sequences
>>>  .
>>>
>>>
>>> On Jul 6, 2012, at 1:30 PM, alex bodnaru wrote:
>>>
>>>>
>>>> hello friends,
>>>>
>>>> i need to define a foreign key differently for different dialects:
>>>> ondelete='restrict' for most engines, but nothing (implied and not 
>>>> recognized)
>>>> for mssql.
>>>>
>>>> could you help?
>>>>
>>>> thanks in advance,
>>>> alex
>>>>
>>>> -- 
>>>> You received this message because you are subscribed to the Google Groups 
>>>> "sqlalchemy" group.
>>>> To post to this group, send email to sqlalchemy@googlegroups.com.
>>>> To unsubscribe from this group, send email to 
>>>> sqlalchemy+unsubscr...@googlegroups.com.
>>>> For more options, visit this group at 
>>>> http://groups.google.com/group/sqlalchemy?hl=en.
>>>>
>>>
>>
>> -- 
>> You received this message because you are subscribed to the Google Groups 
>> "sqlalchemy" group.
>> To post to this group, send email to sqlalchemy@googlegroups.com.
>> To unsubscribe from this group, send email to 
>> sqlalchemy+unsubscr...@googlegroups.com.
>> For more options, visit this group at 
>> http://groups.google.com/group/sqlalchemy?hl=en.
>>
> 

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] dialect sensible declaration

[alex bodnaru]

hello mike and thanks for your answer.

no problem with ForeignKeyConstraint, but wouldn't AddConstraint go the alter
way? in this case, it will be ignored by the sqlite dialect.

what i was looking for was more like:

from sqlalchemy... import get_dialect


fk_parms = dict(.)
if get_dialect() != 'mssql':
fk_parms.update(onupdate='restrict')
fk = ForeignKey(**fk_parms)

would the dialect be accessible from the engine, metadata etc?

thanks in advance,
alex


On 07/06/2012 11:39 PM, Michael Bayer wrote:
> you'd use ForeignKeyConstraint along with the AddConstraint directive, and 
> limit it per-dialect using create/drop events as documented at 
> http://docs.sqlalchemy.org/en/rel_0_7/core/schema.html#controlling-ddl-sequences
>  .
> 
> 
> On Jul 6, 2012, at 1:30 PM, alex bodnaru wrote:
> 
>>
>> hello friends,
>>
>> i need to define a foreign key differently for different dialects:
>> ondelete='restrict' for most engines, but nothing (implied and not 
>> recognized)
>> for mssql.
>>
>> could you help?
>>
>> thanks in advance,
>> alex
>>
>> -- 
>> You received this message because you are subscribed to the Google Groups 
>> "sqlalchemy" group.
>> To post to this group, send email to sqlalchemy@googlegroups.com.
>> To unsubscribe from this group, send email to 
>> sqlalchemy+unsubscr...@googlegroups.com.
>> For more options, visit this group at 
>> http://groups.google.com/group/sqlalchemy?hl=en.
>>
> 

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] dialect sensible declaration

[alex bodnaru]

hello friends,

i need to define a foreign key differently for different dialects:
ondelete='restrict' for most engines, but nothing (implied and not recognized)
for mssql.

could you help?

thanks in advance,
alex

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] CircularDependencyError with relationships

[Alex Grönholm]

06.06.2012 18:06, Michael Bayer kirjoitti:
you need to use the post_update option described at 
http://docs.sqlalchemy.org/en/rel_0_7/orm/relationships.html#rows-that-point-to-themselves-mutually-dependent-rows 
.

Thanks for the pointer. Problem solved :)


On Jun 6, 2012, at 1:15 AM, Alex Grönholm wrote:

I have trouble configuring two relationships from one class to 
another. The following code should be fairly self-explanatory:



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


Base = declarative_base()

class Company(Base):
__tablename__ = 'companies'
id = Column(Integer, primary_key=True)
default_address_id = Column(Integer, ForeignKey('addresses.id', 
use_alter=True, name='defaultaddress_fk'))
addresses = relationship('Address', backref='company', 
primaryjoin='Address.company_id == Company.id')
default_address = relationship('Address', 
primaryjoin='Company.default_address_id == Address.id')



class Address(Base):
__tablename__ = 'addresses'
id = Column(Integer, primary_key=True)
company_id = Column(Integer, ForeignKey(Company.id), nullable=False)


engine = create_engine('sqlite:///', echo=True)
Base.metadata.create_all(engine)
session = Session(engine)
company = Company()
address = Address()
session.add(company)
company.default_address = address
company.addresses.append(address)
session.flush()


What I expect is SQLAlchemy to 1) create the company, 2) create the 
address with the new company's id in company_id, 3) assign the ID of 
the new address to company.default_address_id

Trouble is, I get this error:

sqlalchemy.exc.CircularDependencyError: Circular dependency detected. 
Cycles: set([ProcessState(ManyToOneDP(Company.default_address), 
, delete=False), 
ProcessState(ManyToOneDP(Address.company), , 
delete=False), SaveUpdateState(), 
ProcessState(OneToManyDP(Company.addresses), , 
delete=False), SaveUpdateState()]) all edges: 
set([(ProcessState(OneToManyDP(Company.addresses), 0x16a7210>, delete=False), SaveUpdateState()), 
(SaveUpdateState(), 
ProcessState(ManyToOneDP(Company.default_address), 0x16a7210>, delete=False)), (SaveUpdateState(), 
SaveUpdateState()), (SaveUpdateState(at 0x16a7210>), ProcessState(ManyToOneDP(Address.company), at 0x16ad190>, delete=False)), 
(ProcessState(ManyToOneDP(Company.default_address), 0x16a7210>, delete=False), SaveUpdateState()), 
(ProcessState(ManyToOneDP(Address.company), , 
delete=False), SaveUpdateState()), 
(SaveUpdateState(), 
ProcessState(OneToManyDP(Company.addresses), , 
delete=False))])


What am I doing wrong? I had a similar problem in my production app 
when trying to delete a Company that had a default address assigned.

I'm on SQLAlchemy 0.7.7.

--
You received this message because you are subscribed to the Google 
Groups "sqlalchemy" group.
To view this discussion on the web visit 
https://groups.google.com/d/msg/sqlalchemy/-/fqFKTLBdTYwJ.
To post to this group, send email to sqlalchemy@googlegroups.com 
<mailto:sqlalchemy@googlegroups.com>.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com 
<mailto:sqlalchemy+unsubscr...@googlegroups.com>.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.


--
You received this message because you are subscribed to the Google 
Groups "sqlalchemy" group.

To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.


--
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] CircularDependencyError with relationships

[Alex Grönholm]

I have trouble configuring two relationships from one class to another. The 
following code should be fairly self-explanatory:


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


Base = declarative_base()

class Company(Base):
__tablename__ = 'companies'
id = Column(Integer, primary_key=True)
default_address_id = Column(Integer, ForeignKey('addresses.id', 
use_alter=True, name='defaultaddress_fk'))
addresses = relationship('Address', backref='company', 
primaryjoin='Address.company_id == Company.id')
default_address = relationship('Address', 
primaryjoin='Company.default_address_id == Address.id')


class Address(Base):
__tablename__ = 'addresses'
id = Column(Integer, primary_key=True)
company_id = Column(Integer, ForeignKey(Company.id), nullable=False)


engine = create_engine('sqlite:///', echo=True)
Base.metadata.create_all(engine)
session = Session(engine)
company = Company()
address = Address()
session.add(company)
company.default_address = address
company.addresses.append(address)
session.flush()


What I expect is SQLAlchemy to 1) create the company, 2) create the address 
with the new company's id in company_id, 3) assign the ID of the new 
address to company.default_address_id
Trouble is, I get this error:

sqlalchemy.exc.CircularDependencyError: Circular dependency detected. 
Cycles: set([ProcessState(ManyToOneDP(Company.default_address), , delete=False), ProcessState(ManyToOneDP(Address.company), 
, delete=False), SaveUpdateState(), ProcessState(OneToManyDP(Company.addresses), , delete=False), SaveUpdateState()]) all 
edges: set([(ProcessState(OneToManyDP(Company.addresses), , delete=False), SaveUpdateState()), 
(SaveUpdateState(), 
ProcessState(ManyToOneDP(Company.default_address), , 
delete=False)), (SaveUpdateState(), 
SaveUpdateState()), (SaveUpdateState(), ProcessState(ManyToOneDP(Address.company), , delete=False)), 
(ProcessState(ManyToOneDP(Company.default_address), , 
delete=False), SaveUpdateState()), 
(ProcessState(ManyToOneDP(Address.company), , 
delete=False), SaveUpdateState()), 
(SaveUpdateState(), 
ProcessState(OneToManyDP(Company.addresses), , 
delete=False))])

What am I doing wrong? I had a similar problem in my production app when 
trying to delete a Company that had a default address assigned.
I'm on SQLAlchemy 0.7.7.

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To view this discussion on the web visit 
https://groups.google.com/d/msg/sqlalchemy/-/fqFKTLBdTYwJ.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] Re: Bug in joinedload('*') ?!

[alex]

Hi,

thank you so much for the quick reply. That's exactly what I need!

FYI: I use  '*' with joinedload for the following scenario:

I want to export a certain object and all its related objects to a second 
sqlite database. In addition to the db file I also copy files from disk, 
which are referenced by certain db objects.
The sql and data files are packed into a zip to be transfered to another 
client, that imports it.
To do so, I create a new session and query the root object with 
joinedload('*') to have all related objects in the session, so I can do 
this:

objs = []
# store all session objects
for o in session:
objs.append(o)
# expunge objects from session
for o in objs:
orm.session.make_transient(o)
# add objects to session connected to second db with same metadata
exportSession.add_all(objs)
exportSession.commit()
exportSession.close_all()

best regards and a big praise for your work on sqlalchemy :D
alex

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To view this discussion on the web visit 
https://groups.google.com/d/msg/sqlalchemy/-/_RONPI3aMWwJ.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] Bug in joinedload('*') ?!

[alex]

Hi all,

I want to use session.query(...).options(joinedload('*')) to load all
related objects eagerly.

It seems not to work if inheritance is involved.
This is a complete working example using SQLAlchemy 0.7.7 and Elixir
0.8.0dev:
-
from elixir import *
from sqlalchemy import create_engine
from sqlalchemy.orm import joinedload

engine = create_engine('sqlite:///:memory:', echo = False)
metadata.bind = engine

class PersistentObject(Entity):
pass

class Movie(PersistentObject):
title = Field(Unicode(30))
director = ManyToOne('Director', inverse = "movies")

class Director(PersistentObject):
name = Field(Unicode(60))
movies = OneToMany('Movie', inverse = "director")

setup_all(True)

rscott = Director(name=u"Ridley Scott")
alien = Movie(title=u"Alien")
brunner = Movie(title=u"Blade Runner")
rscott.movies.append(brunner)
rscott.movies.append(alien)
session.commit()

print "without joinedload"
session.close_all()
d = session.query(Director).first()
for i in session: print i
print "with joinedload"
session.close_all()
d = session.query(Director).options(joinedload('*')).first()
for i in session: print i

--

The last line should also print the movies, which does not happen.
When you set Entity as the baseclass of Movie and Director it works.
Is this a bug or is there a reason I don't see?

Thx in advance,
alex

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] Turning a Query instance into SQL (with parameters), need help w/ LIMIT/OFFSET in 0.7.x

[Alex K]

We use this recipe and in 0.7.5 it works ok with limit and offset.

http://www.sqlalchemy.org/trac/wiki/UsageRecipes/old/DebugInlineParams


On Fri, Mar 9, 2012 at 10:32 AM, Randy Syring  wrote:

> I found a recipe on stackoverflow for turning a query instance into a
> string, including parameters.  I only do this for testing purposes and the
> implementation is here:
>
>
> https://bitbucket.org/rsyring/sqlalchemybwc/src/292597b37736/sqlalchemybwc/lib/testing.py
>
> However, I just upgraded to 0.7.5 and it would appear this recipe does not
> handle LIMIT/OFFSET becoming parameterized.  I get the following when using
> the function:
>
> "...persons.last_name AS persons_last_name FROM persons LIMIT :param_1
> OFFSET :param_2"
>
> I'm in over my head on SA internals on this one and would appreciate
> suggestions.
>
> Thanks in advance.
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To view this discussion on the web visit
> https://groups.google.com/d/msg/sqlalchemy/-/ryYu6nVG-RQJ.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> To unsubscribe from this group, send email to
> sqlalchemy+unsubscr...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
>

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] Re: Unpickling of model instances fails when using mapped collections

[Alex Grönholm]

Yup, this is exactly what I did just 2 minutes ago :)

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To view this discussion on the web visit 
https://groups.google.com/d/msg/sqlalchemy/-/Eto2-sirT7wJ.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] Re: Unpickling of model instances fails when using mapped collections

[Alex Grönholm]

So basically this comes down the operator.attrgetter not being 
serializable. I'm wondering why something like this would be serialized at 
all -- aren't collections part of the class definition?

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To view this discussion on the web visit 
https://groups.google.com/d/msg/sqlalchemy/-/sgLu0zjWla0J.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] Unpickling of model instances fails when using mapped collections

[Alex Grönholm]

I'm having trouble unpickling model instances where the class has an 
attribute-mapped collection:

import pickle

from sqlalchemy import create_engine, Column, Integer, Unicode, ForeignKey
from sqlalchemy.orm import Session, relationship
from sqlalchemy.orm.collections import attribute_mapped_collection
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()
engine = create_engine('sqlite:///')
session = Session(engine)

class Parent(Base):
__tablename__ = 'parents'
id = Column(Integer, primary_key=True)

children = relationship('Child', cascade='all, delete-orphan', 
lazy='subquery', collection_class=attribute_mapped_collection('key'))

class Child(Base):
__tablename__ = 'children'
parent_id = Column(Integer, ForeignKey(Parent.id), primary_key=True)
key = Column(Integer, primary_key=True)
value = Column(Unicode)

Base.metadata.create_all(engine)
parent = Parent()
session.add(parent)
session.flush()
session.refresh(parent)
pickled = pickle.dumps(parent, 2)
pickle.loads(pickled)


Executing this results in:

Traceback (most recent call last):
  File "sqlatest.py", line 32, in 
pickle.loads(pickled)
  File "/usr/lib/python2.7/pickle.py", line 1382, in loads
return Unpickler(file).load()
  File "/usr/lib/python2.7/pickle.py", line 858, in load
dispatch[key](self)
  File "/usr/lib/python2.7/pickle.py", line 1083, in load_newobj
obj = cls.__new__(cls, *args)
TypeError: attrgetter expected 1 arguments, got 0


If I remove the relationship, pickling and unpickling work fine. None of 
the other mapped collection variants work either, though they produce 
different tracebacks. Is this a bug or am I doing something wrong?

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To view this discussion on the web visit 
https://groups.google.com/d/msg/sqlalchemy/-/4yfUPzrOZXcJ.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] a list as a named argument for an in clause

[alex bodnaru]

hello friends,

i'm using sa at a quite low level, with session.execute(text, dict)

is it possible to do something in the spirit of:

session.execute("select * from tablename where id in (:ids)", 
dict(ids=[1,2,3,4])) ?

thanks in advance,
alex

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] duplicate key trick

[Alex K]

Thanks!
Great work! :)

On Sat, Jan 21, 2012 at 3:18 AM, Conor  wrote:

>
>
> def get_constraint_name(e):
># Unique constraint violations in PostgreSQL have error code 23505.
>if e.orig.pgcode == "23505":
>return re.search(r'^ERROR:  duplicate key value violates unique
> constraint "(.*?)"',
> e.orig.pgerror).group(1)
>else:
>
>
> -Conor
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> To unsubscribe from this group, send email to
> sqlalchemy+unsubscr...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
>
>

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] override char() handling

[alex bodnaru]

hello friends,

i'm happily using  sqlalchemy in a tg 2.1 project. with a legacy database, thus
my queries are in the form of dbsession.execute(sql).

where should i hook a strip of the data from a char(len) field? this is needed
for this application only.

best regards and merry christmas,
alex

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] empty query

[Alex K]

I need EmptyQuery because query that returns from my function used for
different cases,
and if I return [], it will raise error, because [] not has query methods
like order_by.

e.g.
query1 = getComments(user_id).order_by('comment.time_create desc')
query2 = getComments(user_id).order_by('id')
query3 = getComments(user_id).order_by('comment.user_id')

I can check if instead of query function returns empty list and not apply
order_by,
but if function return EmptyQuery will be very better.


Thanks!


On Sun, Dec 11, 2011 at 10:28 PM, Michael Bayer wrote:

>
> On Dec 11, 2011, at 1:06 PM, Michael Bayer wrote:
>
> >
> > In the above case, you'd just say "comments_replies = []".
>
> correction, as you're attempting to return a Query; you'd factor the
> calling code to expect that an empty list of user_ids as input should
> result in an empty result that can't be further generated.
>
> An EmptyQuery type of object on the SQLA side would be very challenging to
> implement and test and I'm not sure would present a generalized solution to
> the "X IN ()" problem  - cases like:
>
> query(Parent).outerjoin(Child, Parent.children).filter(Child.id.in_([]))
>
> The Query clearly can still return results.  There's lots of ways it might
> be very hard to decide how an EmptyQuery should be evaluated.
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> To unsubscribe from this group, send email to
> sqlalchemy+unsubscr...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
>
>

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] union and literal_column

[Alex Parij]

Hi ,

I'm using SA 0.5.
query1 = session.query(literal_column("'Phrase'").label('type')).filter(...)
query2 = session.query(literal_column("'Exact'").label('type')).filter(...)

and then :
query1.union(query2)
gives me :

SELECT* 'Phrase'  as type*
FROM (SELECT 'Phrase' as type FROM table1 WHERE 
  UNION 
  SELECT 'Exact' as type FROM table1 WHERE 
) AS anon_1

But I need :

SELECT *type*
FROM (
  SELECT  'Phrase' AS type  FROM table1  WHERE ...
 UNION 
   SELECT  'Exact' AS type  FROM table1 WHERE...
)  AS anon_1

Any ideas how I can fix the outer select?

Thanks !

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To view this discussion on the web visit 
https://groups.google.com/d/msg/sqlalchemy/-/K4EEBKA4WvAJ.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] Persisting an object with cascading relationships

[Alex Grönholm]

How silly of me not to have checked that in the docs. Rather embarrassing 
really :) Thanks.

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To view this discussion on the web visit 
https://groups.google.com/d/msg/sqlalchemy/-/rTk1ea1hvrgJ.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] Persisting an object with cascading relationships

[Alex Grönholm]

My use case is the following: each SalesItem requires a calcPriceList and a 
salesPriceList (of type PriceList) attached to it.
For that, SalesItem has two fields:
calcpricelist_id = Column(BigInteger, ForeignKey(PriceList.id), 
nullable=False)
salespricelist_id = Column(BigInteger, ForeignKey(PriceList.id), 
nullable=False)
It also has two relationships:
calcPriceList = relationship(PriceList, primaryjoin=calcpricelist_id == 
PriceList.id, cascade='save-update,delete')
salesPriceList = relationship(PriceList, primaryjoin=salespricelist_id 
== PriceList.id, cascade='save-update')

Now I have a problem -- I want to create a new SalesItem. I want to 
minimize the hassle so I set up a before_insert mapper listener (and 
verified it's being called) that attaches transient PriceList instances to 
said relationships.
What I expected the session to do during flush is to insert these two 
PriceLists into the database, fill in the calcpricelist_id and 
salespricelist_id fields on SalesItem before attempting to insert the 
SalesItem itself.
This is however not happening and it's trying to insert the SalesItem 
first, resulting in an IntegrityError.

Is what I am trying to do wrong? Should I just give up trying to do this in 
an event listener?

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To view this discussion on the web visit 
https://groups.google.com/d/msg/sqlalchemy/-/dUchvgtL1mkJ.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] Single table inheritance + join weirdness

[Alex Grönholm]

Yeah my bad, the original query does indeed query for (Z.id, B.name). I had 
just changed it to A.name to get the printout for the "workaround" query 
and forgot to change it back before pasting here.
If there's something I can do to contribute (not sure I'm qualified to 
write those tests), do tell.

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To view this discussion on the web visit 
https://groups.google.com/d/msg/sqlalchemy/-/lGxM13xQhu8J.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

[sqlalchemy] Single table inheritance + join weirdness

[Alex Grönholm]

I encountered a little strangeness when joining to a class using single 
table inheritance.
I was wondering why I got no results for one particular query.
This was originally encountered with PostgreSQL but was successfully 
reproduced with SQLite.
Is this a bug or a user error?
SNIPSNIP

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.engine import create_engine
from sqlalchemy.schema import Column, ForeignKey
from sqlalchemy.types import Integer, Unicode
from sqlalchemy.orm.session import Session
from sqlalchemy.orm import relationship


Base = declarative_base(create_engine('sqlite:///'))

class A(Base):
__tablename__ = 'a'
id = Column(Integer, primary_key=True)
disc = Column(Unicode, nullable=False)
name = Column(Unicode)

__mapper_args__ = {'polymorphic_on': disc}


class B(A):
__mapper_args__ = {'polymorphic_identity': 'b'}


class Z(Base):
__tablename__ = 'z'
id = Column(Integer, primary_key=True)
b_id = Column(Integer, ForeignKey(B.id))

b = relationship(B)

Base.metadata.create_all()
session = Session()
query = session.query(Z, A.name).outerjoin(Z.b).filter(Z.id == 1)
print query

#SELECT z.id AS z_id, z.b_id AS z_b_id, a.name AS a_name 
#FROM z LEFT OUTER JOIN a ON a.id = z.b_id AND a.disc IN (?) 
#WHERE z.id = ? AND a.disc IN (?)
#   ^- why is this condition here?
#
# WORKAROUND: 
# query = session.query(Z, A.name).outerjoin(Z.b).filter(Z.id == 1)
#  ^- use the superclass instead
#
#SELECT z.id AS z_id, z.b_id AS z_b_id, a.name AS a_name 
#FROM z LEFT OUTER JOIN a ON a.id = z.b_id AND a.disc IN (?) 
#WHERE z.id = ?
# ^- no extra WHERE condition this time around

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To view this discussion on the web visit 
https://groups.google.com/d/msg/sqlalchemy/-/RB_1UbBZRogJ.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] default NULL

[Alex K]

Oh, sorry, my mistake.

Can I create table in sqlalchemy without primary key?

class UserIp(db.Model, UnicodeMixin):
__tablename__ = 'user_ip'

user_id = db.Column(db.Integer, db.ForeignKey('user.id', ondelete='SET
NULL'))
ip = db.Column(postgres.CIDR, nullable=False, server_default='
127.0.0.1/32')

and it raise error:
ArgumentError: Mapper Mapper|UserIp|user_ip could not assemble any primary
key columns for mapped table 'user_ip'


Thanks!


On Fri, Nov 11, 2011 at 2:35 PM, Wichert Akkerman  wrote:

>  On 11/11/2011 11:20 AM, Alex K wrote:
>
> Thanks, but if I need allow nullable primary_keys it not works.
> I tried:
> user_id = db.Column(db.Integer, db.ForeignKey('user.id', ondelete='SET
> NULL'), primary_key=True, nullable=True, server_default=text('NULL'))
>
>
> A primary key can never be null. The PostgreSQL documentation describes
> this as follows: "Technically, a primary key constraint is simply a
> combination of a unique constraint and a not-null constraint.".
>
> I suggest that you replace primary_key=True with unique=True.
>
> Wichert.
>
>

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.

Re: [sqlalchemy] default NULL

[Alex K]

Thanks, but if I need allow nullable primary_keys it not works.
I tried:
user_id = db.Column(db.Integer, db.ForeignKey('user.id', ondelete='SET
NULL'), primary_key=True, nullable=True, server_default=text('NULL'))
it generates
CREATE TABLE user_ip (
user_id INTEGER DEFAULT NULL,
PRIMARY KEY (user_id),
FOREIGN KEY(user_id) REFERENCES "user" (id) ON DELETE SET NULL
)
and I see in pgsql modifiers "not null":
\d user_ip
Table "public.user_ip"
 Column  |  Type   | Modifiers
-+-+---
 user_id | integer | not null
Indexes:
"user_ip_pkey" PRIMARY KEY, btree (user_id)
Foreign-key constraints:
"user_ip_user_id_fkey" FOREIGN KEY (user_id) REFERENCES "user"(id) ON
DELETE SET NULL


Can sqlalchemy allow nullable primary_keys?
Or table without primary key?


Thanks.



On Fri, Nov 11, 2011 at 12:54 AM, Michael Bayer wrote:

>
> 1. time_last = Column(DateTime, nullable=True, server_default=text('NULL'))
>
> 2. all columns in a relational database default to NULL so not sure why
> you'd need to do this.
>
>

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to 
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en.