Re: [sqlalchemy] Query for date between a range

2013-10-09 Thread Enrico Morelli 
On Fri, 4 Oct 2013 23:58:38 +0300
Ofir Herzas herz...@gmail.com wrote:

 Enrico, It should be available on 0.7.10
 
 Simon, you are right. The expression is indeed a must.
 
 class Plan(Base):
 @hybrid_property
 def calculated_date(self):
 return date(self.year, self.month, self.day)
 
 @calculated_date.expression
 def calculated_date(self):
 return sa.cast(self.year + '-' + self.month + '-' + self.day,
 sa.Date)
 
 

Thanks to all. At the end I solve adding a date column in the db and
write the code to fill the new column.

Thanks again.

 -Original Message-
 From: sqlalchemy@googlegroups.com
 [mailto:sqlalchemy@googlegroups.com] On Behalf Of Simon King
 Sent: Friday, October 04, 2013 7:01 PM
 To: sqlalchemy@googlegroups.com
 Subject: Re: [sqlalchemy] Query for date between a range
 
 I'm not sure that will work on it's own, will it? When used in a class
 context (Plan.calculated_date), you will end up calling the date
 function with 3 SQLAlchemy column objects, which won't work.
 
 At a minimum, you'd need this:
 
 class Plan(Base):
 @hybrid_property
 def calculated_date(self):
 return date(self.year, self.month, self.day)
 
 @calculated_date.expression
 def calculated_date(cls):
 # I suspect that what you put in here depends on the database
 return sa.cast(cls.year + '-' + cls.month + '-' + cls.day,
 sa.Date)
 
 On Fri, Oct 4, 2013 at 2:25 PM, Ofir Herzas herz...@gmail.com wrote:
  I'm sorry, you should use hybrid_property:
 
  from sqlalchemy.ext.hybrid import hybrid_property
 
  class Plan(Base):
  @hybrid_property
  def calculated_date(self):
  return date(self.year, self.month, self.day)
 
 
  Also, in your query, don't use between:
  session.query(Plan).\
  filter(Plan.calculated_date = from_date).\
  filter(Plan.calculated_date = to_date))
 
  Cheers,
  Ofir
 
  -Original Message-
  From: sqlalchemy@googlegroups.com
  [mailto:sqlalchemy@googlegroups.com] On Behalf Of Enrico Morelli
  Sent: Friday, October 04, 2013 4:05 PM
  To: sqlalchemy@googlegroups.com
  Subject: Re: [sqlalchemy] Query for date between a range
 
  On Fri, 4 Oct 2013 15:55:07 +0300
  Ofir Herzas herz...@gmail.com wrote:
 
  You can create a custom field in your model and check against it:
 
  class Plan(Base):
  .
  .
  .
  @property
  def calculated_date(self):
  return date(self.year, self.month, self.day)
 
 
  Then, in your query, use that field:
  session.query(Plan).filter(Plan.calculated_date.between(from_date,
  to_date))
 
  Haven't checked it myself, but I guess it should work ...
 
  Thanks, but now I receive the error:
 
  AttributeError: 'property' object has no attribute 'between'
 
 
  -Original Message-
  From: sqlalchemy@googlegroups.com
  [mailto:sqlalchemy@googlegroups.com] On Behalf Of Enrico Morelli
  Sent: Friday, October 04, 2013 12:07 PM
  To: sqlalchemy@googlegroups.com
  Subject: [sqlalchemy] Query for date between a range
 
  Dear all,
 
  I've a table where the date is separated in single fields, one for 
  year, one for day and one for month. So I need to query for a date 
  range. I search in Internet and I found the following query that 
  seems to be works: SELECT * FROM plan WHERE year * 1 + month * 
  100 + day BETWEEN +'20130101' AND '20130131';
 
  Now I'm trying to translate to sqlalchemy, but I receive the 
  following
  error:
 
  DataError: (DataError) invalid input syntax for integer:
  2013-01-01T00:00:00 LINE 3: ...year * 1000 + plan.month * 100 + 
  plan.day BETWEEN '2013-01-0... ^ 'SELECT plan.data AS plan_data, 
  plan.month AS plan_month, plan.instrument_id AS plan_instrument_id,
  count(plan.instrument_id) AS count_1 \nFROM plan \nWHERE plan.year
  * %(year_1)s + plan.month * %(month_1)s + plan.day BETWEEN
  %(param_1)s AND %(param_2)s AND plan.data ILIKE %(data_1)s GROUP
  BY plan.data, plan.month, plan.instrument_id ORDER BY month,
  instrument_id' {'data_1': u'%#L%', 'param_1':
  datetime.datetime(2013, 1, 1, 0, 0), 'month_1': 100, 'year_1':
  1000, 'param_2': datetime.datetime(2013, 10, 3, 0, 0)}
 
 
  The latest attempt to write the correct code is the following (the 
  range come from a web form using a javascript plugin):
 
  from_date = request.POST.get('from_date', '') to_date = 
  request.POST.get('to_date', '') from_date = 
  datetime.strptime(from_date, '%Y-%m-%d') to_date = 
  datetime.strptime(to_date, '%Y-%m-%d') if from_date.day  10:
 day = 0%s % from_date.day
  else:
 day = %s % from_date.day
  if from_date.month  10:
 month = 0%s % from_date.month
  else:
 month = %s % from_date.month
  if to_date.day  10:
 tday = 0%s % to_date.day
  else:
 tday = %s % to_date.day
  if to_date.month  10:
 tmonth = 0%s % to_date.month
  else:
 tmonth = %s % to_date.month
  fd = '%s%s%s' % (from_date.year, month, day) td = '%s%s%s' % 
  (to_date.year, tmonth, tday) print fd, td results

[sqlalchemy] Query for date between a range

2013-10-04 Thread Enrico Morelli 
Dear all,

I've a table where the date is separated in single fields, one for
year, one for day and one for month. So I need to query for a date
range. I search in Internet and I found the following query that seems
to be works:
SELECT *
FROM plan
WHERE year * 1 + month * 100 + day BETWEEN +'20130101' AND
'20130131';

Now I'm trying to translate to sqlalchemy, but I receive the following
error:

DataError: (DataError) invalid input syntax for integer:
2013-01-01T00:00:00 LINE 3: ...year * 1000 + plan.month * 100 +
plan.day BETWEEN '2013-01-0... ^ 'SELECT plan.data AS plan_data,
plan.month AS plan_month, plan.instrument_id AS plan_instrument_id,
count(plan.instrument_id) AS count_1 \nFROM plan \nWHERE plan.year *
%(year_1)s + plan.month * %(month_1)s + plan.day BETWEEN %(param_1)s
AND %(param_2)s AND plan.data ILIKE %(data_1)s GROUP BY plan.data,
plan.month, plan.instrument_id ORDER BY month,
instrument_id' {'data_1': u'%#L%', 'param_1': datetime.datetime(2013,
1, 1, 0, 0), 'month_1': 100, 'year_1': 1000, 'param_2':
datetime.datetime(2013, 10, 3, 0, 0)}


The latest attempt to write the correct code is the following (the
range come from a web form using a javascript plugin):

from_date = request.POST.get('from_date', '')
to_date = request.POST.get('to_date', '')
from_date = datetime.strptime(from_date, '%Y-%m-%d')
to_date = datetime.strptime(to_date, '%Y-%m-%d')
if from_date.day  10:
   day = 0%s % from_date.day
else:
   day = %s % from_date.day
if from_date.month  10:
   month = 0%s % from_date.month
else:
   month = %s % from_date.month
if to_date.day  10:
   tday = 0%s % to_date.day
else:
   tday = %s % to_date.day
if to_date.month  10:
   tmonth = 0%s % to_date.month
else:
   tmonth = %s % to_date.month
fd = '%s%s%s' % (from_date.year, month, day)
td = '%s%s%s' % (to_date.year, tmonth, tday)
print fd, td
results = Session.query(Plan.data, 
Plan.month,Plan.instrument_id,func.count(Plan.instrument_id)).filter( 
and_((Plan.year*1000+Plan.month*100+Plan.day).between(from_date,
to_date),  Plan.data.ilike('%%%s%%' % item),)).group_by(
Plan.data, Plan.month,
Plan.instrument_id ).order_by('month', 'instrument_id').all()

Where I'm wrong?

Thanks to all
-- 
-
  Enrico Morelli
  System Administrator | Programmer | Web Developer

  CERM - Polo Scientifico
  Via Sacconi, 6 - 50019 Sesto Fiorentino (FI) - ITALY
  phone: +39 055 457 4269
  fax:   +39 055 457 4253
-

-- 
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] Query for date between a range

2013-10-04 Thread Enrico Morelli 
On Fri, 4 Oct 2013 16:25:00 +0300
Ofir Herzas herz...@gmail.com wrote:

 I'm sorry, you should use hybrid_property:
 
 from sqlalchemy.ext.hybrid import hybrid_property
 
 class Plan(Base):
 @hybrid_property
 def calculated_date(self):
 return date(self.year, self.month, self.day)
 
 
 Also, in your query, don't use between:
 session.query(Plan).\
   filter(Plan.calculated_date = from_date).\
   filter(Plan.calculated_date = to_date))
 
 Cheers,
 Ofir

Argh!! I've to use and old version of sqlalchemy (0.6.8) that hasn't
the hybrid module. It's a dog that bites its own tail.
 
 -Original Message-
 From: sqlalchemy@googlegroups.com
 [mailto:sqlalchemy@googlegroups.com] On Behalf Of Enrico Morelli
 Sent: Friday, October 04, 2013 4:05 PM
 To: sqlalchemy@googlegroups.com
 Subject: Re: [sqlalchemy] Query for date between a range
 
 On Fri, 4 Oct 2013 15:55:07 +0300
 Ofir Herzas herz...@gmail.com wrote:
 
  You can create a custom field in your model and check against it:
  
  class Plan(Base):
  .
  .
  .
  @property
  def calculated_date(self):
  return date(self.year, self.month, self.day)
  
  
  Then, in your query, use that field:
  session.query(Plan).filter(Plan.calculated_date.between(from_date,
  to_date))
  
  Haven't checked it myself, but I guess it should work ...
 
 Thanks, but now I receive the error:
 
 AttributeError: 'property' object has no attribute 'between'
 
  
  -Original Message-
  From: sqlalchemy@googlegroups.com
  [mailto:sqlalchemy@googlegroups.com] On Behalf Of Enrico Morelli
  Sent: Friday, October 04, 2013 12:07 PM
  To: sqlalchemy@googlegroups.com
  Subject: [sqlalchemy] Query for date between a range
  
  Dear all,
  
  I've a table where the date is separated in single fields, one for 
  year, one for day and one for month. So I need to query for a date 
  range. I search in Internet and I found the following query that
  seems to be works: SELECT * FROM plan WHERE year * 1 + month *
  100 + day BETWEEN +'20130101' AND '20130131';
  
  Now I'm trying to translate to sqlalchemy, but I receive the
  following error:
  
  DataError: (DataError) invalid input syntax for integer:
  2013-01-01T00:00:00 LINE 3: ...year * 1000 + plan.month * 100 + 
  plan.day BETWEEN '2013-01-0... ^ 'SELECT plan.data AS plan_data, 
  plan.month AS plan_month, plan.instrument_id AS plan_instrument_id,
  count(plan.instrument_id) AS count_1 \nFROM plan \nWHERE plan.year
  * %(year_1)s + plan.month * %(month_1)s + plan.day BETWEEN
  %(param_1)s AND %(param_2)s AND plan.data ILIKE %(data_1)s GROUP BY
  plan.data, plan.month, plan.instrument_id ORDER BY month,
  instrument_id' {'data_1': u'%#L%', 'param_1':
  datetime.datetime(2013, 1, 1, 0, 0), 'month_1': 100, 'year_1':
  1000, 'param_2': datetime.datetime(2013, 10, 3, 0, 0)}
  
  
  The latest attempt to write the correct code is the following (the 
  range come from a web form using a javascript plugin):
  
  from_date = request.POST.get('from_date', '') to_date = 
  request.POST.get('to_date', '') from_date = 
  datetime.strptime(from_date, '%Y-%m-%d') to_date = 
  datetime.strptime(to_date, '%Y-%m-%d') if from_date.day  10:
 day = 0%s % from_date.day
  else:
 day = %s % from_date.day
  if from_date.month  10:
 month = 0%s % from_date.month
  else:
 month = %s % from_date.month
  if to_date.day  10:
 tday = 0%s % to_date.day
  else:
 tday = %s % to_date.day
  if to_date.month  10:
 tmonth = 0%s % to_date.month
  else:
 tmonth = %s % to_date.month
  fd = '%s%s%s' % (from_date.year, month, day) td = '%s%s%s' % 
  (to_date.year, tmonth, tday) print fd, td results = 
  Session.query(Plan.data, 
  Plan.month,Plan.instrument_id,func.count(Plan.instrument_id)).filter( 
  and_((Plan.year*1000+Plan.month*100+Plan.day).between(from_date,
  to_date),  Plan.data.ilike('%%%s%%' % item),)).group_by(
  Plan.data, Plan.month, 
  Plan.instrument_id ).order_by('month', 'instrument_id').all()
  
  Where I'm wrong?
  
  Thanks to all
  --
  -
Enrico Morelli
System Administrator | Programmer | Web Developer
  
CERM - Polo Scientifico
Via Sacconi, 6 - 50019 Sesto Fiorentino (FI) - ITALY
phone: +39 055 457 4269
fax:   +39 055 457 4253
  -
  
  --
  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.
  
 
 
 --
 -
   Enrico Morelli
   System Administrator

Re: [sqlalchemy] Query for date between a range

2013-10-04 Thread Enrico Morelli 
On Fri, 4 Oct 2013 16:03:18 +0200
Enrico Morelli more...@cerm.unifi.it wrote:

 On Fri, 4 Oct 2013 16:25:00 +0300
 Ofir Herzas herz...@gmail.com wrote:
 
  I'm sorry, you should use hybrid_property:
  
  from sqlalchemy.ext.hybrid import hybrid_property
  
  class Plan(Base):
  @hybrid_property
  def calculated_date(self):
  return date(self.year, self.month, self.day)
  
  
  Also, in your query, don't use between:
  session.query(Plan).\
  filter(Plan.calculated_date = from_date).\
  filter(Plan.calculated_date = to_date))
  
  Cheers,
  Ofir
 
 Argh!! I've to use and old version of sqlalchemy (0.6.8) that hasn't
 the hybrid module. It's a dog that bites its own tail.

Sigh! In which version I can found the hybrid module? My application is
in a production environment and I'm afraid to update sqlalchemy.
Usually I create a virtual environment where the python modules are
frozen to avoid problems. 

  
  -Original Message-
  From: sqlalchemy@googlegroups.com
  [mailto:sqlalchemy@googlegroups.com] On Behalf Of Enrico Morelli
  Sent: Friday, October 04, 2013 4:05 PM
  To: sqlalchemy@googlegroups.com
  Subject: Re: [sqlalchemy] Query for date between a range
  
  On Fri, 4 Oct 2013 15:55:07 +0300
  Ofir Herzas herz...@gmail.com wrote:
  
   You can create a custom field in your model and check against it:
   
   class Plan(Base):
   .
   .
   .
   @property
   def calculated_date(self):
   return date(self.year, self.month, self.day)
   
   
   Then, in your query, use that field:
   session.query(Plan).filter(Plan.calculated_date.between(from_date,
   to_date))
   
   Haven't checked it myself, but I guess it should work ...
  
  Thanks, but now I receive the error:
  
  AttributeError: 'property' object has no attribute 'between'
  
   
   -Original Message-
   From: sqlalchemy@googlegroups.com
   [mailto:sqlalchemy@googlegroups.com] On Behalf Of Enrico Morelli
   Sent: Friday, October 04, 2013 12:07 PM
   To: sqlalchemy@googlegroups.com
   Subject: [sqlalchemy] Query for date between a range
   
   Dear all,
   
   I've a table where the date is separated in single fields, one
   for year, one for day and one for month. So I need to query for a
   date range. I search in Internet and I found the following query
   that seems to be works: SELECT * FROM plan WHERE year * 1 +
   month * 100 + day BETWEEN +'20130101' AND '20130131';
   
   Now I'm trying to translate to sqlalchemy, but I receive the
   following error:
   
   DataError: (DataError) invalid input syntax for integer:
   2013-01-01T00:00:00 LINE 3: ...year * 1000 + plan.month * 100 + 
   plan.day BETWEEN '2013-01-0... ^ 'SELECT plan.data AS plan_data, 
   plan.month AS plan_month, plan.instrument_id AS
   plan_instrument_id, count(plan.instrument_id) AS count_1 \nFROM
   plan \nWHERE plan.year
   * %(year_1)s + plan.month * %(month_1)s + plan.day BETWEEN
   %(param_1)s AND %(param_2)s AND plan.data ILIKE %(data_1)s GROUP
   BY plan.data, plan.month, plan.instrument_id ORDER BY month,
   instrument_id' {'data_1': u'%#L%', 'param_1':
   datetime.datetime(2013, 1, 1, 0, 0), 'month_1': 100, 'year_1':
   1000, 'param_2': datetime.datetime(2013, 10, 3, 0, 0)}
   
   
   The latest attempt to write the correct code is the following
   (the range come from a web form using a javascript plugin):
   
   from_date = request.POST.get('from_date', '') to_date = 
   request.POST.get('to_date', '') from_date = 
   datetime.strptime(from_date, '%Y-%m-%d') to_date = 
   datetime.strptime(to_date, '%Y-%m-%d') if from_date.day  10:
  day = 0%s % from_date.day
   else:
  day = %s % from_date.day
   if from_date.month  10:
  month = 0%s % from_date.month
   else:
  month = %s % from_date.month
   if to_date.day  10:
  tday = 0%s % to_date.day
   else:
  tday = %s % to_date.day
   if to_date.month  10:
  tmonth = 0%s % to_date.month
   else:
  tmonth = %s % to_date.month
   fd = '%s%s%s' % (from_date.year, month, day) td = '%s%s%s' % 
   (to_date.year, tmonth, tday) print fd, td results = 
   Session.query(Plan.data, 
   Plan.month,Plan.instrument_id,func.count(Plan.instrument_id)).filter( 
   and_((Plan.year*1000+Plan.month*100+Plan.day).between(from_date,
   to_date),  Plan.data.ilike('%%%s%%' % item),)).group_by(
   Plan.data,
   Plan.month, Plan.instrument_id ).order_by('month',
   'instrument_id').all()
   
   Where I'm wrong?
   
   Thanks to all
   --
   -
 Enrico Morelli
 System Administrator | Programmer | Web Developer
   
 CERM - Polo Scientifico
 Via Sacconi, 6 - 50019 Sesto Fiorentino (FI) - ITALY
 phone: +39 055 457 4269
 fax:   +39 055 457 4253
   -
   
   --
   You received this message because you are subscribed to the
   Google Groups sqlalchemy group

[sqlalchemy] ManyToMany query

2012-07-24 Thread Enrico Morelli 
Dear all,

I've the following tables:

site_table = Table('site', metadata,
Column('id', types.Integer, primary_key=True),
Column('name', types.Unicode(15), nullable=False))

chain_table = Table('chain', metadata,
Column('id', types.Integer, primary_key=True),
Column('letter', types.Unicode(1), nullable=False),
Column('ec_number', types.Unicode(50), nullable=True))

sites_chains_table = Table('sites_chains', metadata,
Column('site_id', types.Integer,
ForeignKey('site.id'),primary_key=True),
Column('chain_id', types.Integer,ForeignKey('chain.id'),
primary_key=True))

and the following mappers:

mapper(Site, site_table,
   properties={'chain': relationship(Chain, backref='site',
secondary=sites_chains_table), })

mapper(Chain, chain_table)

Which query I've to write to count all site where
chain.ec_number==something using its relation.

I tried to do something like:
count =
Session.query(func.count(Site.id)).filter(Chain.ec_number==ec_number).filter(Site.chain.any(id=Chain.id)).all()

but the result isn't real.
-- 
---
   (o_
(o_//\  Coltivate Linux che tanto Windows si pianta da solo.
(/)_   V_/_
+--+
| ENRICO MORELLI |  email: more...@cerm.unifi.it   |
| * *   *   *|  phone: +39 055 4574269 |
|  University of Florence|  fax  : +39 055 4574253 |
|  CERM - via Sacconi, 6 -  50019 Sesto Fiorentino (FI) - ITALY|
+--+

-- 
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] Association Object creation

2011-11-23 Thread Enrico Morelli 

Dear all,

I have to create an association object that have to relate elements of
the same table.

These are the table definition:
ligand_table = Table('ligand', metadata,
Column('id', types.Integer, primary_key=True),
Column('number', types.Integer, nullable=False),
Column('name', types.Unicode(4), nullable=False),
)


ligand_ligand_table = Table('ligand_ligand', metadata,
   Column('ligand_id1', types.Integer,
ForeignKey('ligand.id'),primary_key=True), 
   Column('ligand_id2', types.Integer,
ForeignKey('ligand.id'),primary_key=True),
   Column('interaction_id',types.Integer, ForeignKey('interaction.id')),
   Column('interaction_type', types.Unicode(10), nullable=False),
   Column('distance', types.Float, nullable=False), )

These are the mappers:
mapper(Ligand, ligand_table,
   properties={
 'ligand':relationship(LigandLigand,
 primaryjoin=and_(ligand_table.c.id==ligand_ligand_table.c.ligand_id1,
 ligand_table.c.id==ligand_ligand_table.c.ligand_id2),
backref=backref('ligandligand') ), })

mapper(LigandLigand, ligand_ligand_table,
   properties={
  'first_sphere': relationship(Ligand, 
   primaryjoin=and_(ligand_ligand_table.c.ligand_id2==ligand_table.c.id,

ligand_ligand_table.c.ligand_id1==ligand_table.c.id),
   backref=backref('root')),
   })

When I try to access to the ligand.ligand properties, this is empty
even if in the db there are relations. Where is the problem?

Thanks


-- 
---
   (o_
(o_//\  Coltivate Linux che tanto Windows si pianta da solo.
(/)_   V_/_
+--+
| ENRICO MORELLI |  email: more...@cerm.unifi.it   |
| * *   *   *|  phone: +39 055 4574269 |
|  University of Florence|  fax  : +39 055 4574253 |
|  CERM - via Sacconi, 6 -  50019 Sesto Fiorentino (FI) - ITALY|
+--+

-- 
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] Association Object creation

2011-11-23 Thread Enrico Morelli 
On Wed, 23 Nov 2011 10:59:03 +0100
Enrico Morelli more...@cerm.unifi.it wrote:


 These are the mappers:
 mapper(Ligand, ligand_table,
properties={
  'ligand':relationship(LigandLigand,
  primaryjoin=and_(ligand_table.c.id==ligand_ligand_table.c.ligand_id1,
ligand_table.c.id==ligand_ligand_table.c.ligand_id2),
   backref=backref('ligandligand') ), })
 
 mapper(LigandLigand, ligand_ligand_table,
properties={
   'first_sphere': relationship(Ligand, 

 primaryjoin=and_(ligand_ligand_table.c.ligand_id2==ligand_table.c.id,
 
 ligand_ligand_table.c.ligand_id1==ligand_table.c.id),
backref=backref('root')),
})
 
 When I try to access to the ligand.ligand properties, this is empty
 even if in the db there are relations. Where is the problem?
 
 Thanks
 
 

I think to have solved the problem changing the logical operator in the
primaryjoin from and_ to or_. 

Now when I remove the relations I have the following warning:
SAWarning: Multiple rows returned with uselist=False for lazily-loaded attribute
'LigandLigand.ligandligand' value = callable_(passive=passive)

What means?
-- 
---
   (o_
(o_//\  Coltivate Linux che tanto Windows si pianta da solo.
(/)_   V_/_
+--+
| ENRICO MORELLI |  email: more...@cerm.unifi.it   |
| * *   *   *|  phone: +39 055 4574269 |
|  University of Florence|  fax  : +39 055 4574253 |
|  CERM - via Sacconi, 6 -  50019 Sesto Fiorentino (FI) - ITALY|
+--+

-- 
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] SA remove related fields

2011-09-20 Thread Enrico Morelli 
Dear all,

I'm using SA 0.6.7 on a RHEL 6 with Python 2.6 and PostgreSQL 8.4.7.
These are some table of my DB:
pdb_table = Table('pdb', metadata,
Column('id', types.Integer, primary_key=True),
Column('code', types.Unicode(4), nullable=False),
)
chain_table = Table('chain', metadata,
Column('id', types.Integer, primary_key=True),
Column('letter', types.Unicode(1), nullable=False),
Column('pdb_id', types.Integer, ForeignKey('pdb.id')),
)

metal_table = Table('metal', metadata,
Column('id', types.Integer, primary_key=True),
Column('number', types.Integer, nullable=False),
Column('name', types.Unicode(4), nullable=False),
Column('pdb_id', types.Integer, ForeignKey('pdb.id')),
)

mapper(Pdb, pdb_table)
mapper(Chain, chain_table,
   properties={
  'pdb': relationship(Pdb, backref='chain')
})
mapper(Metal, metal_table,
   properties={
  'pdb': relationship(Pdb, backref='metal'),
  'chain': relationship(Chain, backref='metal'),
})

If I try to remove a pdb table row related with others from
pgAdminIII, I correctly receive the constraint error and the row isn't
removed.

Using the following code, SA remove all rows related with others
without error:
for code in open(pdblist): 
pdb = Session.query(Pdb).filter(Pdb.code==code.strip()).all()
for p in pdb:
   # Remove PDB
   Session.delete(p)
   Session.commit()

Where I wrong?


Thanks
-- 
---
   (o_
(o_//\  Coltivate Linux che tanto Windows si pianta da solo.
(/)_   V_/_
+--+
| ENRICO MORELLI |  email: more...@cerm.unifi.it   |
| * *   *   *|  phone: +39 055 4574269 |
|  University of Florence|  fax  : +39 055 4574253 |
|  CERM - via Sacconi, 6 -  50019 Sesto Fiorentino (FI) - ITALY|
+--+

-- 
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] SA remove related fields

2011-09-20 Thread Enrico Morelli 
On Tue, 20 Sep 2011 09:14:20 -0400
Michael Bayer mike...@zzzcomputing.com wrote:

 
 On Sep 20, 2011, at 3:24 AM, Enrico Morelli wrote:
 
  Dear all,
  
  I'm using SA 0.6.7 on a RHEL 6 with Python 2.6 and PostgreSQL 8.4.7.
  These are some table of my DB:
  pdb_table = Table('pdb', metadata,
 Column('id', types.Integer, primary_key=True),
 Column('code', types.Unicode(4), nullable=False),
  )
  chain_table = Table('chain', metadata,
 Column('id', types.Integer, primary_key=True),
 Column('letter', types.Unicode(1), nullable=False),
 Column('pdb_id', types.Integer, ForeignKey('pdb.id')),
 )
  
  metal_table = Table('metal', metadata,
 Column('id', types.Integer, primary_key=True),
 Column('number', types.Integer, nullable=False),
 Column('name', types.Unicode(4), nullable=False),
 Column('pdb_id', types.Integer, ForeignKey('pdb.id')),
  )
  
  mapper(Pdb, pdb_table)
  mapper(Chain, chain_table,
properties={
   'pdb': relationship(Pdb, backref='chain')
 })
  mapper(Metal, metal_table,
properties={
   'pdb': relationship(Pdb, backref='metal'),
   'chain': relationship(Chain, backref='metal'),
 })
  
  If I try to remove a pdb table row related with others from
  pgAdminIII, I correctly receive the constraint error and the row
  isn't removed.
  
  Using the following code, SA remove all rows related with others
  without error:
  for code in open(pdblist): 
 pdb = Session.query(Pdb).filter(Pdb.code==code.strip()).all()
 for p in pdb:
# Remove PDB
Session.delete(p)
Session.commit()
  
  Where I wrong?
 
 Turn on echo=True in your create_engine() and you'll likely see
 SQLAlchemy setting those foreign key columns to NULL.  Set them
 to NOT NULL to see a nullable constraint error, or add
 passive_deletes='all' to each relationship() to turn off the null
 set.
 
 http://www.sqlalchemy.org/docs/orm/relationships.html?highlight=relationship#sqlalchemy.orm.relationship
 
 

Thanks, I tried to put passive_deletes='all' in the relationships:

mapper(Site, site_table,
   properties={
 'pdb': relationship(Pdb, backref='site', passive_deletes='all'), })

mapper(Chain, chain_table,
  properties={
 'pdb': relationship(Pdb, backref='chain', passive_deletes='all')
})

But I obtain:

/py_virt/metalweb/lib/python2.6/site-packages/SQLAlchemy-0.6.8-py2.6.egg/sqlalchemy/orm/properties.py:901:
SAWarning: On Chain.pdb, 'passive_deletes' is normally configured on 
one-to-many, one-to-one, many-to-many relationships only.
  self._determine_direction()

/py_virt/metalweb/lib/python2.6/site-packages/SQLAlchemy-0.6.8-py2.6.egg/sqlalchemy/orm/properties.py:901:
SAWarning: On Site.pdb, 'passive_deletes' is normally configured on 
one-to-many, one-to-one, many-to-many relationships only.
  self._determine_direction()


-- 
---
   (o_
(o_//\  Coltivate Linux che tanto Windows si pianta da solo.
(/)_   V_/_
+--+
| ENRICO MORELLI |  email: more...@cerm.unifi.it   |
| * *   *   *|  phone: +39 055 4574269 |
|  University of Florence|  fax  : +39 055 4574253 |
|  CERM - via Sacconi, 6 -  50019 Sesto Fiorentino (FI) - ITALY|
+--+

-- 
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] SA remove related fields

2011-09-20 Thread Enrico Morelli 
On Tue, 20 Sep 2011 09:32:09 -0400
Michael Bayer mike...@zzzcomputing.com wrote:

 
 On Sep 20, 2011, at 9:28 AM, Enrico Morelli wrote:
 
  
  Thanks, I tried to put passive_deletes='all' in the relationships:
  
 
 sorry, 'pdb':relationship(Pdb, backref=backref('metal',
 passive_deletes='all'))
 
 more docs that I'd recommend:
 
 http://www.sqlalchemy.org/docs/orm/relationships.html#linking-relationships-with-backref
 
 
 
 
Thanks, all works fine.

-- 
---
   (o_
(o_//\  Coltivate Linux che tanto Windows si pianta da solo.
(/)_   V_/_
+--+
| ENRICO MORELLI |  email: more...@cerm.unifi.it   |
| * *   *   *|  phone: +39 055 4574269 |
|  University of Florence|  fax  : +39 055 4574253 |
|  CERM - via Sacconi, 6 -  50019 Sesto Fiorentino (FI) - ITALY|
+--+

-- 
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] Dynamic query

2011-05-09 Thread Enrico Morelli 
On Fri, 6 May 2011 17:11:39 +0100
King Simon-NFHD78 simon.k...@motorolasolutions.com wrote:

  -Original Message-
  From: sqlalchemy@googlegroups.com
  [mailto:sqlalchemy@googlegroups.com] On Behalf Of Enrico Morelli
  Sent: 06 May 2011 16:20
  To: sqlalchemy
  Subject: [sqlalchemy] Dynamic query
  
  Dear all,
  
  I've a form where people fill one or more fields to search in a db.
  For the moment I solve it using a lot of if statement and a lot of
  different query based on the filled fields. Something like that:
  
  if start_date and end_date and instrument and details and
  technician: c.results =
  
  Session.query(Repairs).filter(and_(Repairs.start_date=start_date,
  Repairs.end_date=end_date,
  Repairs.instrument_id==instrument,
  Repairs.details.like('%%%s%%' % details),
  Repairs.technician.like('%%%s%%' % technician)
  )).order_by('start_date').all()
  
  elif start_date and end_date and instrument and details:
  c.results =
  
  Session.query(Repairs).filter(and_(Repairs.start_date=start_date,
  Repairs.end_date=end_date,
  Repairs.instrument_id==instrument,
  Repairs.details.like('%%%s%%' %
  details), )).order_by('start_date').all()
  
  and so on for each combination (for 5 fields I have 20 query!).
  There is
  a way to do that in a more dynamic way?
  
 
 You can call Query.filter multiple times. Here's an example:
 
 query = Session.query(Repairs)
 
 if start_date:
 query = query.filter(Repairs.start_date = start_date)
 
 if end_date:
 query = query.filter(Repairs.end_date = end_date)
 
 if instrument:
 query = query.filter(Repairs.instrument_id == instrument)
 
 # etc.
 
 results = query.order_by('start_date').all()
 
 
 Each filter condition will be combined using AND.
 
 Hope that helps,
 
 Simon
 
THANKS!!! Works very fine :-))

-- 
---
   (o_
(o_//\  Coltivate Linux che tanto Windows si pianta da solo.
(/)_   V_/_
+--+
| ENRICO MORELLI |  email: more...@cerm.unifi.it   |
| * *   *   *|  phone: +39 055 4574269 |
|  University of Florence|  fax  : +39 055 4574253 |
|  CERM - via Sacconi, 6 -  50019 Sesto Fiorentino (FI) - ITALY|
+--+

-- 
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] Dynamic query

2011-05-06 Thread Enrico Morelli 
Dear all,

I've a form where people fill one or more fields to search in a db.
For the moment I solve it using a lot of if statement and a lot of
different query based on the filled fields. Something like that:

if start_date and end_date and instrument and details and technician:
c.results =
Session.query(Repairs).filter(and_(Repairs.start_date=start_date,
Repairs.end_date=end_date, 
Repairs.instrument_id==instrument,
Repairs.details.like('%%%s%%' % details), 
Repairs.technician.like('%%%s%%' % technician)
)).order_by('start_date').all()

elif start_date and end_date and instrument and details:
c.results =
Session.query(Repairs).filter(and_(Repairs.start_date=start_date,
Repairs.end_date=end_date,
Repairs.instrument_id==instrument,
Repairs.details.like('%%%s%%' % details), 
)).order_by('start_date').all()

and so on for each combination (for 5 fields I have 20 query!). There is
a way to do that in a more dynamic way?

Thanks

-- 
---
   (o_
(o_//\  Coltivate Linux che tanto Windows si pianta da solo.
(/)_   V_/_
+--+
| ENRICO MORELLI |  email: more...@cerm.unifi.it   |
| * *   *   *|  phone: +39 055 4574269 |
|  University of Florence|  fax  : +39 055 4574253 |
|  CERM - via Sacconi, 6 -  50019 Sesto Fiorentino (FI) - ITALY|
+--+

-- 
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] Association object

2011-02-09 Thread Enrico Morelli 
Dear all,

I've to create an association object where the many-to-many relation has
to be created with only one table:

atominfo_table = Table('atom_info', metadata,
Column('id', types.Integer, primary_key=True),
Column('number', types.Integer, nullable=False),
Column('coord_x', types.Unicode(15), nullable=False),
Column('coord_y', types.Unicode(15), nullable=False),
Column('coord_z', types.Unicode(15), nullable=False),
Column('residue_id', types.Integer,
ForeignKey('residue_info.id'),primary_key=True), 
Column('periodic_id', types.Integer,ForeignKey('periodic_glos.id'),
primary_key=True), )

atom_atom_table = Table('atom_atom', metadata,
Column('atom1_id', types.Integer,
ForeignKey('atom_info.id'),primary_key=True), 
Column('atom2_id', types.Integer, ForeignKey('atom_info.id'),
primary_key=True), 
Column('interaction_type', types.Unicode(50),nullable=False), 
Column('distance', types.Unicode(10),nullable=False), )

Is it possible? If yes how can create the mapper?
The following attempt give me the error:
Could not determine join condition between parent/child tables on
 relationship AtomInfo.atom1.  Specify a 'primaryjoin' expression.  If
'secondary' is present, 'secondaryjoin' is needed as well.

mapper(AtomInfo, atominfo_table,
   properties={
  'residue': relationship(ResidueInfo, backref='atominfo'),
  'periodic': relationship(Periodic, backref='atominfo'),
  'atom1': relationship(AtomAtom)
})

mapper(AtomAtom, atom_atom_table,
   properties={
   'atom2': relationship(AtomInfo)
})


Thanks
-- 
---
   (o_
(o_//\  Coltivate Linux che tanto Windows si pianta da solo.
(/)_   V_/_
+--+
| ENRICO MORELLI |  email: more...@cerm.unifi.it   |
| * *   *   *|  phone: +39 055 4574269 |
|  University of Florence|  fax  : +39 055 4574253 |
|  CERM - via Sacconi, 6 -  50019 Sesto Fiorentino (FI) - ITALY|
+--+

-- 
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] Association object

2011-02-09 Thread Enrico Morelli 
On Wed, 9 Feb 2011 10:34:22 +0100
Enrico Morelli more...@cerm.unifi.it wrote:


 mapper(AtomInfo, atominfo_table,
properties={
   'residue': relationship(ResidueInfo, backref='atominfo'),
   'periodic': relationship(Periodic, backref='atominfo'),
   'atom1': relationship(AtomAtom)
 })
 
 mapper(AtomAtom, atom_atom_table,
properties={
'atom2': relationship(AtomInfo)
 })
 

Using the following mapper, I haven't the error:
mapper(AtomInfo, atominfo_table,
   properties={
  'residue': relationship(ResidueInfo, backref='atominfo'),
  'periodic': relationship(Periodic, backref='atominfo'),
  'atom1': relationship(AtomAtom,
primaryjoin=atominfo_table.c.id==atom_atom_table.c.atom1_id)
})
mapper(AtomAtom, atom_atom_table,
   properties={
   'atom2': relationship(AtomInfo,
primaryjoin=atominfo_table.c.id==atom_atom_table.c.atom2_id)
  
})

Is that the correct solution?

Thanks again.


-- 
---
   (o_
(o_//\  Coltivate Linux che tanto Windows si pianta da solo.
(/)_   V_/_
+--+
| ENRICO MORELLI |  email: more...@cerm.unifi.it   |
| * *   *   *|  phone: +39 055 4574269 |
|  University of Florence|  fax  : +39 055 4574253 |
|  CERM - via Sacconi, 6 -  50019 Sesto Fiorentino (FI) - ITALY|
+--+

-- 
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] Complex query (for me)

2011-01-26 Thread Enrico Morelli 
Dear all,

I've a situation where some tutors has some doctorates. Each doctorate
has to upload some reports. Each tutor has to approve reports of his
doctorates.

These are the tables and mappers:

members_table = Table('members', metadata,
Column('id', types.Integer, primary_key=True),
Column('lastname', types.Unicode(30), nullable=False),
Column('tutor_id', types.Integer, ForeignKey('members.id'),
nullable=True),
Column('removed', types.Boolean, default=False))

reports_table = Table('reports', metadata,
Column('id', types.Integer, primary_key=True),
Column('approved', types.Boolean, default=False),
Column('writer', types.Integer, ForeignKey('members.id'),
nullable=False))

mapper(Members, members_table,
   properties={
   'tutor': relation(Members, backref='doctorate',
 remote_side=members_table.c.id) 
   })

mapper(Reports, reports_table,
properties={
'owner': relation(Members, backref='report')
})

I have to create a query to select all reports of all doctorates of a
tutor. Using the following query I'm able to have all doctorate of a
tutor. 
result = Session.query(Members).filter(and_(Members.removed==False,
Members.tutor.has(Members.id==tutor_id))).all()

But I'm not able to select also the doctorate reports.

Thanks
-- 
---
   (o_
(o_//\  Coltivate Linux che tanto Windows si pianta da solo.
(/)_   V_/_
+--+
| ENRICO MORELLI |  email: more...@cerm.unifi.it   |
| * *   *   *|  phone: +39 055 4574269 |
|  University of Florence|  fax  : +39 055 4574253 |
|  CERM - via Sacconi, 6 -  50019 Sesto Fiorentino (FI) - ITALY|
+--+

-- 
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] Complex query (for me)

2011-01-26 Thread Enrico Morelli 
For the moment I solved using these query:

doctorate = Session.query(Members).filter(and_(Members.removed==False,
Members.tutor.has(id=tutor_id))).subquery()
reports = Session.query(Reports, doctorate.c.id).outerjoin((doctorate,
Reports.writer==doctorate.c.id)).order_by(Reports.id).all()
# Only to obtain the objects related to the tutor
reports = [reports[i][0] for i in range(len(reports)) if reports[i][1]]

Are there other possibilities?

Thanks
-- 
---
   (o_
(o_//\  Coltivate Linux che tanto Windows si pianta da solo.
(/)_   V_/_
+--+
| ENRICO MORELLI |  email: more...@cerm.unifi.it   |
| * *   *   *|  phone: +39 055 4574269 |
|  University of Florence|  fax  : +39 055 4574253 |
|  CERM - via Sacconi, 6 -  50019 Sesto Fiorentino (FI) - ITALY|
+--+

-- 
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] order_by: ArgumentError

2010-11-17 Thread Enrico Morelli 
On Tue, 16 Nov 2010 11:37:12 -0500
Michael Bayer mike...@zzzcomputing.com wrote:

 
 On Nov 16, 2010, at 6:16 AM, Enrico Morelli wrote:
 
  On Mon, 15 Nov 2010 15:56:06 -0500
  Michael Bayer mike...@zzzcomputing.com wrote:
  
  its looking for a Column object.menus_table.c.weight instead of
  'weight'.
  
  
  Thanks, I modified the query:
  main_menu = Session.query(Menu).filter(and_(Menu.parent_id==None,
  Menu.lang==session['lang'])).order_by(menus_table.c.weight.asc()).all()
  
  but the error is the same:
  
  ArgumentError: Column-based expression object expected for argument
  'order_by'; got: 'weight', type type 'str'
 
 no , the mapping:
 
 mapper(Menu, menus_table,
  properties={
  'children': relation(Menu, order_by=menus_table.c.weight),
  'permissions': relation(Permissions, backref='menus',
  secondary=menus_permissions_table)
  })
 
 

Thank you very much.

-- 
---
   (o_
(o_//\  Coltivate Linux che tanto Windows si pianta da solo.
(/)_   V_/_
+--+
| ENRICO MORELLI |  email: more...@cerm.unifi.it   |
| * *   *   *|  phone: +39 055 4574269 |
|  University of Florence|  fax  : +39 055 4574253 |
|  CERM - via Sacconi, 6 -  50019 Sesto Fiorentino (FI) - ITALY|
+--+

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalch...@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] order_by: ArgumentError

2010-11-16 Thread Enrico Morelli 
On Mon, 15 Nov 2010 15:56:06 -0500
Michael Bayer mike...@zzzcomputing.com wrote:

 its looking for a Column object.menus_table.c.weight instead of
 'weight'.
 

Thanks, I modified the query:
main_menu = Session.query(Menu).filter(and_(Menu.parent_id==None,
Menu.lang==session['lang'])).order_by(menus_table.c.weight.asc()).all()

 but the error is the same:

ArgumentError: Column-based expression object expected for argument
'order_by'; got: 'weight', type type 'str'

 
 On Nov 15, 2010, at 10:03 AM, Enrico Morelli wrote:
 
  Dear all,
  
  I've a lot of applications using SA 0.5.6. Now I upgraded my
  personal computer and now I can use SA 0.6.5 but my applications
  stops to work.
  
  I receive the error:
  ArgumentError: Column-based expression object expected for argument
  'order_by'; got: 'weight', type type 'str'
  
  I try to search in google but I don't understand why I receive this
  error. Someone can explain to me?
  
  Thanks in advance
  
  This is the table declaration:
  
  menus_table = Table('menus', metadata,
 Column('id', types.Integer, primary_key=True),
 Column('parent_id', types.Integer, ForeignKey('menus.id')),
 Column('name', types.Unicode(80), nullable=False),
 Column('title', types.Unicode(80)),
 Column('url', types.Unicode(80)),
 Column('weight', types.Integer, index=True),
 Column('lang', types.Unicode(2))
  )
  
  This is the mapper declaration:
  mapper(Menu, menus_table,
properties={
'children': relation(Menu, order_by='weight'),
'permissions': relation(Permissions, backref='menus',
secondary=menus_permissions_table)
})
  
  At the end the query:
  main_menu = Session.query(Menu).filter(and_(Menu.parent_id==None,
  Menu.lang==session['lang'])).order_by(Menu.weight.asc()).all()
  
  -- 
  ---
(o_
  (o_//\  Coltivate Linux che tanto Windows si pianta da solo.
  (/)_   V_/_
  +--+
  | ENRICO MORELLI |  email: more...@cerm.unifi.it   |
  | * *   *   *|  phone: +39 055 4574269 |
  |  University of Florence|  fax  : +39 055 4574253 |
  |  CERM - via Sacconi, 6 -  50019 Sesto Fiorentino (FI) - ITALY|
  +--+
  
  -- 
  You received this message because you are subscribed to the Google
  Groups sqlalchemy group. To post to this group, send email to
  sqlalch...@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.
  
 


-- 
---
   (o_
(o_//\  Coltivate Linux che tanto Windows si pianta da solo.
(/)_   V_/_
+--+
| ENRICO MORELLI |  email: more...@cerm.unifi.it   |
| * *   *   *|  phone: +39 055 4574269 |
|  University of Florence|  fax  : +39 055 4574253 |
|  CERM - via Sacconi, 6 -  50019 Sesto Fiorentino (FI) - ITALY|
+--+

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalch...@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] order_by: ArgumentError

2010-11-15 Thread Enrico Morelli 
Dear all,

I've a lot of applications using SA 0.5.6. Now I upgraded my personal
computer and now I can use SA 0.6.5 but my applications stops to work.

I receive the error:
ArgumentError: Column-based expression object expected for argument
'order_by'; got: 'weight', type type 'str'

I try to search in google but I don't understand why I receive this
error. Someone can explain to me?

Thanks in advance

This is the table declaration:

menus_table = Table('menus', metadata,
Column('id', types.Integer, primary_key=True),
Column('parent_id', types.Integer, ForeignKey('menus.id')),
Column('name', types.Unicode(80), nullable=False),
Column('title', types.Unicode(80)),
Column('url', types.Unicode(80)),
Column('weight', types.Integer, index=True),
Column('lang', types.Unicode(2))
)

This is the mapper declaration:
mapper(Menu, menus_table,
   properties={
   'children': relation(Menu, order_by='weight'),
   'permissions': relation(Permissions, backref='menus',
   secondary=menus_permissions_table)
   })

At the end the query:
main_menu = Session.query(Menu).filter(and_(Menu.parent_id==None,
Menu.lang==session['lang'])).order_by(Menu.weight.asc()).all()

-- 
---
   (o_
(o_//\  Coltivate Linux che tanto Windows si pianta da solo.
(/)_   V_/_
+--+
| ENRICO MORELLI |  email: more...@cerm.unifi.it   |
| * *   *   *|  phone: +39 055 4574269 |
|  University of Florence|  fax  : +39 055 4574253 |
|  CERM - via Sacconi, 6 -  50019 Sesto Fiorentino (FI) - ITALY|
+--+

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalch...@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] Confusion about using Session() globally

2007-11-07 Thread Enrico Morelli 

Dear all,

I'm quite confused to use Session like a global statement through
different parts of a web program. The directory structure is the
following:

WebML/
 +- webml.py (main program)
 +- globals.py 
 +- managers/
  +- __init__.py
  +- users_schema.py
  +- emails_schema.py
  +- ml_schema.py

So, in the main program webml.py I have:

from sqlalchemy import create_engine, MetaData, Table
from sqlalchemy.orm import mapper, scoped_session, sessionmaker
from  managers import users_schema, ml_schema, emails_schema
class HttpProtected:
def __init__(self):

engine=create_engine('postgres://[EMAIL 
PROTECTED]/webml',strategy='threadlocal') 
Session = scoped_session(sessionmaker(autoflush=True,
transactional=True)) 
session=Session(bind=engine) 
self.m=ml_schema.MLManager()
self.u=users_schema.UsersManager()
self.e=emails_schema.EmailsManager()

In managers/users_schema.py I have:

from sqlalchemy import Table, MetaData
from sqlalchemy.orm import  relation, class_mapper, mapper
import ml_schema

class Users(object):
pass 

class UsersManager(object):
def __init__(self):
metadata=MetaData('postgres://[EMAIL PROTECTED]/webml')   
users = Table('users', metadata, autoload=True)
ml_users=Table('ml_users', metadata, autoload=True)
usersmapper = mapper(Users, users, properties = {
'ml' : relation(ml_schema.ML, secondary = ml_users,
backref='users') })

def saveNewUser(self,  kw):
session=Session()


It's clear that saveNewUser doesn't works, because doesn't find
Session(). I tried to put the Session initialization everywhere (except
where it has to be :-)) without success. Where I have to put Session
initialization to use it globally?


Thanks in advance
-- 
---
   (o_
(o_//\  Coltivate Linux che tanto Windows si pianta da solo.
(/)_   V_/_
+--+
| ENRICO MORELLI |  email: [EMAIL PROTECTED]   |
| * *   *   *|  phone: +39 055 4574269 |
|  University of Florence|  fax  : +39 055 4574253 |
|  CERM - via Sacconi, 6 -  50019 Sesto Fiorentino (FI) - ITALY|
+--+

--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Postgresql arrays

2007-01-04 Thread Enrico Morelli 


Dear all,

sqlalchemy is able to manage the postgres arrays?

I have a table name tarray like:
refcode char(5)
wokdays int[]


When I try to use it from SA I have the following error:

tarray=Table('tarray', metadata, autoload=True)

KeyError: 'integer[]'

--
---
  (o_
(o_//\  Coltivate Linux che tanto Windows si pianta da solo.
(/)_   V_/_
+--+
| ENRICO MORELLI |  email: [EMAIL PROTECTED]   |
| * *   *   *|  phone: +39 055 4574269 |
|  University of Florence|  fax  : +39 055 4574253 |
|  CERM - via Sacconi, 6 -  50019 Sesto Fiorentino (FI) - ITALY|
+--+

--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---