[sqlalchemy] Re: Joining three tables - Selecting column from two different tables

2011-01-20 Thread Steve
Hi,

Thanks. Worked like a charm.

Also thanks for SqlAlchemy. A refreshing change for someone from java
background.

I am using this with Jython. Thanks for the Jython support also.

Steve

On Jan 18, 8:54 pm, Michael Bayer mike...@zzzcomputing.com wrote:
 On Jan 18, 2011, at 9:11 AM, Steve wrote:



  Hi all,

  Newbie here.

  I just want to execute the following sql using SqlAlchemy . But
  getting various errors.

  select ssf.factor,ssf.displayname,pmw.weight
  from probability_models_weights pmw
  inner join probability_models pm on pm.id = pmw.model_id
  inner join success_factors ssf on ssf.id = pmw.factor_id
  where pm.id = 6

  I want to execute this using session.

  I am using declarative base with the following auto loaded classes.

  class SucessFactors(WBase):
     __tablename__ = success_factors
     __table_args__ = {'autoload':True}

  class ProbabilityModels(WBase):
     __tablename__ = probability_models
     __table_args__ = {'autoload':True}

  class ProbabilityModelsWeights(WBase):
     __tablename__ = probability_models_weights
     __table_args__ = {'autoload':True}

  I tried the following but it didn't work.

  session.query(SucessFactors.factor,SucessFactors.displayname,ProbabilityModelsWeights.weight).
  \
         join(ProbabilityModelsWeights,ProbabilityModels,
  ProbabilityModelsWeights.model_id == ProbabilityModels.id).\
         join(ProbabilityModelsWeights,SucessFactors,
  ProbabilityModelsWeights.factor_id == SucessFactors.id).\
         filter(ProbabilityModels.id == model_id).\
         all()

 query.join() is a one-argument form (it will accept two arguments in 0.7, but 
 thats not released yet), so here you want to be saying

 query(...).select_from(ProbabiliyModelsWeights).join((ProbabiltityModels, 
 ProbabiltiyModelsWeights.model_id==ProbabilityModels.id)).

 the select_from() accepting a mapped class is a helper that was introudced in 
 0.6.5.   Also note the tuple form inside of join(), i.e. join((target, 
 onclause)) (you won't need that in 0.7).   Documented 
 athttp://www.sqlalchemy.org/docs/orm/tutorial.html#querying-with-joins.



  Thanks in advance.

  Steve.

  --
  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 
  athttp://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] Re: scalar association_proxy

2011-01-20 Thread AgentOrange

 Maybe you can squelch the exception by using a synonym with the descriptor 
 argument to map to a fake property.

Oooh, I had not come across this construct before - looks as if it
might do exactly what I want. I might even be able to make a decorator
that wraps this all up nicely. Thank you very much; I shall let you
know how I get on!

-- 
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] select distinct on a relation to an entity with composite primary key

2011-01-20 Thread NiL
hi list,

I have the following (elixir) definitions

class Invoice(Entity):
user_name = Field(Unicode(255))
item = ManyToOne(Item)

class Item(Entity):
item_id = Field(Integer,  primary_key=True)
service_id = Field(Unicode(255),  primary_key=True)
item_class = Field(Unicode(255),  primary_key=True)

I wish to select invoices that have distinct items

(Pdb) Session.query(Invoice.item.distinct())
*** RuntimeError: maximum recursion depth exceeded while calling a
Python object

this works
(Pdb) Session.query(Invoice.item_item_id.distinct())
sqlalchemy.orm.query.Query object at 0x988e7ec
but doesn't lead to the expected result

here is the invoice's table description

CREATE TABLE invoice (
id INTEGER NOT NULL,
user_name VARCHAR(255),
item_item_id INTEGER,
item_service_id VARCHAR(255),
item_item_class VARCHAR(255),
PRIMARY KEY (id),
CONSTRAINT invoice_item_item_id_item_service_id_item_item_class_fk
FOREIGN KEY(item_item_id, item_service_id, item_item_class) REFERENCES
item (item_id, service_id, item_class)
)


How can I achieve my query ?

regards
NIL

-- 
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: rollback not working

2011-01-20 Thread Michael Bayer

On Jan 20, 2011, at 2:04 AM, bool wrote:

 
 If I dont use autocommit:True option, it seems the driver will be in a
 chained transaction mode and results in every single statement
 (including selects) being run in a new transaction. This is not
 desirable either.
 
 Is there a way out ?

The DBAPI doesn't do that, since DBAPI calls for a single connection to be open 
in a transaction immediately, the state of which is only ended by calling 
rollback or commit on that connection.   if you're using SQLalchemy using 
connectionless execution, i.e. table.insert().execute(), then yes each 
execute() is in its own transaction, as it should be since no transactional 
boundary has been declared.   This style of usage is not appropriate for a 
large volume of operations.

Usually you create a transaction for a series of operations, which is automatic 
when using the ORM with a Session object, so that there's a reasonable boundary 
of transactions.


-- 
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] select distinct on a relation to an entity with composite primary key

2011-01-20 Thread Michael Bayer
its a little unfortunate that Invoice.item.distinct() is recursion overflowing 
like that, but in general if you want to select distinct Item objects you'd be 
saying query(Item).distinct().

not sure I understand what invoices that have distinct items means.   I 
understand invoices that have items.   If Invoice inv1 and inv2 both 
reference item im1, are you saying you don't want either of them ?  Only 
invoice inv3, which references im2, and nobody else references im2 ?

thats a fairly tricky query, probably using EXISTS:

from sqlalchemy.orm import aliased
from sqlalchemy import and_

inv = aliased(Invoice)
query(Invoice).filter(~Invoice.any(and_(Invoice.item_id==inv.item_id, 
Invoice.id!=inv.id)))



On Jan 20, 2011, at 6:33 AM, NiL wrote:

 hi list,
 
 I have the following (elixir) definitions
 
 class Invoice(Entity):
user_name = Field(Unicode(255))
item = ManyToOne(Item)
 
 class Item(Entity):
item_id = Field(Integer,  primary_key=True)
service_id = Field(Unicode(255),  primary_key=True)
item_class = Field(Unicode(255),  primary_key=True)
 
 I wish to select invoices that have distinct items
 
 (Pdb) Session.query(Invoice.item.distinct())
 *** RuntimeError: maximum recursion depth exceeded while calling a
 Python object
 
 this works
 (Pdb) Session.query(Invoice.item_item_id.distinct())
 sqlalchemy.orm.query.Query object at 0x988e7ec
 but doesn't lead to the expected result
 
 here is the invoice's table description
 
 CREATE TABLE invoice (
   id INTEGER NOT NULL,
   user_name VARCHAR(255),
   item_item_id INTEGER,
   item_service_id VARCHAR(255),
   item_item_class VARCHAR(255),
   PRIMARY KEY (id),
   CONSTRAINT invoice_item_item_id_item_service_id_item_item_class_fk
 FOREIGN KEY(item_item_id, item_service_id, item_item_class) REFERENCES
 item (item_id, service_id, item_class)
 )
 
 
 How can I achieve my query ?
 
 regards
 NIL
 
 -- 
 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] Re: select distinct on a relation to an entity with composite primary key

2011-01-20 Thread NiL
Hi Michael,

thank you very much for your prompt answer.

What I want to achieve is, counting the number of distinct items,
grouped by user_name

Given

Item1:
item_id = 1
service_id = 'test'
item_class = 'dummy'
Item2:
item_id = 2
service_id = 'other'
item_class = 'dummy'

Invoice1:
id = 1
user_name = 'lorem'
item = Item1
Invoice2:
id = 2
user_name = 'lorem'
item = Item1
Invoice3:
id = 3
user_name = 'ipsum'
item = Item1
Invoice4:
id = 4
user_name = 'ipsum'
item = Item2

the expected result is:

user: lorem, distinct item count : 1
user: ipsum, distinct item count : 2



-- 
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: select distinct on a relation to an entity with composite primary key

2011-01-20 Thread Michael Bayer

from sqlalchemy import func, distinct

query(Invoice.user_name, 
func.count(distinct(Item.id))).join(Invoice.items).group_by(Invoice.user_name)


On Jan 20, 2011, at 9:51 AM, NiL wrote:

 Hi Michael,
 
 thank you very much for your prompt answer.
 
 What I want to achieve is, counting the number of distinct items,
 grouped by user_name
 
 Given
 
 Item1:
item_id = 1
service_id = 'test'
item_class = 'dummy'
 Item2:
item_id = 2
service_id = 'other'
item_class = 'dummy'
 
 Invoice1:
id = 1
user_name = 'lorem'
item = Item1
 Invoice2:
id = 2
user_name = 'lorem'
item = Item1
 Invoice3:
id = 3
user_name = 'ipsum'
item = Item1
 Invoice4:
id = 4
user_name = 'ipsum'
item = Item2
 
 the expected result is:
 
 user: lorem, distinct item count : 1
 user: ipsum, distinct item count : 2
 
 
 
 -- 
 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] Re: select distinct on a relation to an entity with composite primary key

2011-01-20 Thread NiL
thanks again

but the unicity of Item is guaranteed by the triplet of PK

I can't just discriminate the distinct() based on the item_id only (it
is not unique by itself in my set of data)

-- 
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: select distinct on a relation to an entity with composite primary key

2011-01-20 Thread Michael Bayer

session.query(Invoice.user_name, 
Item).join(Invoice.item).distinct().from_self(Invoice.user_name, 
func.count(1)).group_by(Invoice.user_name)



On Jan 20, 2011, at 11:45 AM, NiL wrote:

 thanks again
 
 but the unicity of Item is guaranteed by the triplet of PK
 
 I can't just discriminate the distinct() based on the item_id only (it
 is not unique by itself in my set of data)
 
 -- 
 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] SQLAlchemy case insensitive like for unicode word

2011-01-20 Thread proft
Hello!

I have gtk application with sqlite db, contain russian words.

My model

code
class Patient(Base):
lastname = Column(Unicode)
/code

Search operation

code
patients = self.session.query(Patient)

lastname = unicode(self.lastname_entry.get_text())
if lastname:
 patients = patients.filter(Patient.lastname.like(u%%%s%% %
lastname))
/code

It perfectly work if i search as 'Ivanov', but didn't return results
if i search 'ivanov'. Note, i search russian words, not english.

In SQLite manager (firefox plugin) search query

code
SELECT * FROM patients WHERE lastname LIKE %ivanov%
/code

If i query db and look at value:

code
In [28]: p.lastname
Out[28]: u'\u0413\u0430\u043f\u0447\u0443\u043a'
/code

and than check it in query, generated by SQLAlchemy

code
In [29]: patients.filter(Patient.lastname.ilike(u%%%s%% %
l.decode('utf-8'))).count()

2011-01-20 21:20:30,950 INFO sqlalchemy.engine.base.Engine.0x...1250
SELECT count(1) AS count_1
FROM patients
WHERE lower(patients.lastname) LIKE lower(?)
2011-01-20 21:20:30,950 INFO sqlalchemy.engine.base.Engine.0x...1250
(u'%\u0433\u0430\u043f\u0447\u0443\u043a%',)
Out[29]: 0
/code

It is looked like lower function in SQLAlchemy didn't understood
cyrillic ...

Thanks!

PS: Sorry for my english)

-- 
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] Re: I need a final push

2011-01-20 Thread F.A.Pinkse

Hi All,

After some additional peeking around I decided to do a test with 
SQLAlchemy alone.


I took the tutorial fr0m the book Essential SQLAlchemy as my guide.

This is what I got working.

# testing the func following the tutorial in the book Essential SQLALchemy

#pg.25
from sqlalchemy import *
from datetime import datetime

metadata=MetaData('sqlite:///tutorial.sqlite')
#metadata.bind.echo=True

person_table=Table(
 'person', metadata,
 Column('id', Integer, primary_key=True),
 Column('birthdate', DateTime, default=datetime.now))

metadata.create_all()
#
stmt=person_table.insert()

#stmt.execute(birthdate=datetime(2000, 4, 4, 0, 0))
#stmt.execute(birthdate=datetime(2000, 3, 3, 0, 0))
#stmt.execute(birthdate=datetime(2000, 2, 2, 0, 0))
#stmt.execute(birthdate=datetime(2000, 1, 1, 0, 0))

#pg 28 Mapping
from sqlalchemy.orm import *

class Person(object):pass

mapper(Person, person_table)

Session= sessionmaker()
session=Session()

query =session.query(Person)

def monthfrom(date):
print(date)
#i do a split because I get seconds as 00.0
if date != None:
datesplit=date.split(' ')[0]
a=datetime.strptime(datesplit, '%Y-%m-%d').month
else:
a=1
return a

metadata.bind.connect().connection.connection.create_function(monthfrom, 
1, monthfrom)


print('monthfrom in:')
pp=query.order_by(func.monthfrom(Person.birthdate)).all()
print('result:')
for p in pp:
print (p.birthdate)




For the first run you have to uncomment the 4 lines with the insert 
execution to fill your empty db.

Put the comments back on or your db will fill up


Next task will be to get it transplanted to my app.


There is still one issue though.

the function def monthfrom get the date with the seconds as 00.00
But the print(p.birthdate) shows the seconds as 00
See:

monthfrom in:
2000-04-04 00:00:00.00
2000-03-03 00:00:00.00
2000-02-02 00:00:00.00
2000-01-01 00:00:00.00
result:
2000-01-01 00:00:00
2000-02-02 00:00:00
2000-03-03 00:00:00
2000-04-04 00:00:00

That is why the def monthfrom does a .split

Question is this a bug?


Thanks

Frans.


--
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: sqlalchemy rocks my socks off!

2011-01-20 Thread Hector Blanco
+1

2011/1/16 Jan Müller m...@dfi-net.de:
 +1

 On Jan 15, 9:58 am, Eric Ongerth ericonge...@gmail.com wrote:
 +1

 On Jan 13, 5:08 pm, rdlowrey rdlow...@gmail.com wrote:







  To Michael Bayer: sqlalchemy simplifies my life every day and makes me
  vastly more productive! Many thanks.

 --
 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] SQLAlchemy case insensitive like for unicode word

2011-01-20 Thread Michael Bayer
That's SQLite's lower() function.   If you'd like to use Python's lower() 
function, you should call lower() on the string and use 
column.like(mystring.lower()).  But that won't do case-insensitive comparison 
since you need to call lower() on the database column in the statement.

So you really need to first get this statement to work with pysqlite, to figure 
out what collation issues may exist with sqlite3 or encoding issues with 
pysqlite:

import sqlite3

connection = sqlite3.connect(/path/to/your/db)

cursor = connection.cursor()
cursor.execute(SELECT * FROM patients WHERE lower(lastname) LIKE lower(?), 
['Ivanov'])
print cursor.fetchall()

SQLAlchemy doesn't look at the contents of your string at all with pysqlite, 
its a pass through.   



On Jan 20, 2011, at 4:15 PM, proft wrote:

 Hello!
 
 I have gtk application with sqlite db, contain russian words.
 
 My model
 
 code
 class Patient(Base):
lastname = Column(Unicode)
 /code
 
 Search operation
 
 code
 patients = self.session.query(Patient)
 
 lastname = unicode(self.lastname_entry.get_text())
 if lastname:
 patients = patients.filter(Patient.lastname.like(u%%%s%% %
 lastname))
 /code
 
 It perfectly work if i search as 'Ivanov', but didn't return results
 if i search 'ivanov'. Note, i search russian words, not english.
 
 In SQLite manager (firefox plugin) search query
 
 code
 SELECT * FROM patients WHERE lastname LIKE %ivanov%
 /code
 
 If i query db and look at value:
 
 code
 In [28]: p.lastname
 Out[28]: u'\u0413\u0430\u043f\u0447\u0443\u043a'
 /code
 
 and than check it in query, generated by SQLAlchemy
 
 code
 In [29]: patients.filter(Patient.lastname.ilike(u%%%s%% %
 l.decode('utf-8'))).count()
 
 2011-01-20 21:20:30,950 INFO sqlalchemy.engine.base.Engine.0x...1250
 SELECT count(1) AS count_1
 FROM patients
 WHERE lower(patients.lastname) LIKE lower(?)
 2011-01-20 21:20:30,950 INFO sqlalchemy.engine.base.Engine.0x...1250
 (u'%\u0433\u0430\u043f\u0447\u0443\u043a%',)
 Out[29]: 0
 /code
 
 It is looked like lower function in SQLAlchemy didn't understood
 cyrillic ...
 
 Thanks!
 
 PS: Sorry for my english)
 
 -- 
 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] Re: I need a final push

2011-01-20 Thread Michael Bayer

On Jan 20, 2011, at 4:53 PM, F.A.Pinkse wrote:

 Hi All,
 
 After some additional peeking around I decided to do a test with SQLAlchemy 
 alone.
 
 I took the tutorial fr0m the book Essential SQLAlchemy as my guide.

very very old outdated book. If you want a current book, go to 
http://www.sqlalchemy.org/docs/ , download the PDF and print it out.  I find 
reading HTML online much easier though (and use the search, it works).

 
 This is what I got working.
 def monthfrom(date):
print(date)
#i do a split because I get seconds as 00.0
if date != None:
datesplit=date.split(' ')[0]
a=datetime.strptime(datesplit, '%Y-%m-%d').month
else:
a=1
return a
 
 metadata.bind.connect().connection.connection.create_function(monthfrom, 1, 
 monthfrom)

wow, thats in the book ?   that is not at all how that should be done.
Though I will grant this is a recipe that isn't in our main docs (I've never 
seen SQLite python plugin functions used before), probably worth adding to the 
SQLite docs.  Anyway, this is how you should install your function:

from sqlalchemy.interfaces import PoolListener
class MyListener(PoolListener):
def connect(self, dbapi_con, con_record):
dbapi_con.create_function(monthfrom, 1, monthfrom)

engine = create_engine('sqlite:///path_to_my_db', listeners=[MyListener()])

reference : 
http://www.sqlalchemy.org/docs/core/interfaces.html#connection-pool-events

SQLite doesn't have a date type.  The SQLAlchemy DateTime type stores the value 
as a string.  This isn't the only way to go, you can also store dates as 
epochs, that is integers, which on SQLite may be more amenable to date 
arithmetic on the server, but in any case you're just dealing with extracting 
here, no big deal.   The SQLite DateTime type in SQLA stores the value using 
the format:

 %04d-%02d-%02d %02d:%02d:%02d.%06d % (value.year, value.month, value.day,
 value.hour, value.minute, value.second,
 value.microsecond)

so you're seeing the microsecond value there.  you need to deal with that 
too.   Surprisingly the docs seem a little light on this too so ticket 2029 is 
added to fill this in.

 
 # testing the func following the tutorial in the book Essential SQLALchemy
 
 #pg.25
 from sqlalchemy import *
 from datetime import datetime
 
 metadata=MetaData('sqlite:///tutorial.sqlite')
 #metadata.bind.echo=True
 
 person_table=Table(
 'person', metadata,
 Column('id', Integer, primary_key=True),
 Column('birthdate', DateTime, default=datetime.now))
 
 metadata.create_all()
 #
 stmt=person_table.insert()
 
 #stmt.execute(birthdate=datetime(2000, 4, 4, 0, 0))
 #stmt.execute(birthdate=datetime(2000, 3, 3, 0, 0))
 #stmt.execute(birthdate=datetime(2000, 2, 2, 0, 0))
 #stmt.execute(birthdate=datetime(2000, 1, 1, 0, 0))
 
 #pg 28 Mapping
 from sqlalchemy.orm import *
 
 class Person(object):pass
 
 mapper(Person, person_table)
 
 Session= sessionmaker()
 session=Session()
 
 query =session.query(Person)
 
 def monthfrom(date):
print(date)
#i do a split because I get seconds as 00.0
if date != None:
datesplit=date.split(' ')[0]
a=datetime.strptime(datesplit, '%Y-%m-%d').month
else:
a=1
return a
 
 metadata.bind.connect().connection.connection.create_function(monthfrom, 1, 
 monthfrom)
 
 print('monthfrom in:')
 pp=query.order_by(func.monthfrom(Person.birthdate)).all()
 print('result:')
 for p in pp:
print (p.birthdate)
 
 
 
 
 For the first run you have to uncomment the 4 lines with the insert execution 
 to fill your empty db.
 Put the comments back on or your db will fill up
 
 
 Next task will be to get it transplanted to my app.
 
 
 There is still one issue though.
 
 the function def monthfrom get the date with the seconds as 00.00
 But the print(p.birthdate) shows the seconds as 00
 See:
 
 monthfrom in:
 2000-04-04 00:00:00.00
 2000-03-03 00:00:00.00
 2000-02-02 00:00:00.00
 2000-01-01 00:00:00.00
 result:
 2000-01-01 00:00:00
 2000-02-02 00:00:00
 2000-03-03 00:00:00
 2000-04-04 00:00:00
 
 That is why the def monthfrom does a .split
 
 Question is this a bug?
 
 
 Thanks
 
 Frans.
 
 
 -- 
 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.