Re: [sqlalchemy] how to use version_id_col
On 01/18/2011 02:08 AM, Michael Bayer wrote: On Jan 17, 2011, at 5:42 PM, Jan Mueller wrote: try: obj = Session.query(MyObject).filter(MyObject.updated_at == my_hidden_updated_at).filter(MyObject.id == id).one() except orm.exc.NoResultFound: print stale version ! form.populate_object(obj) yes but then you don't have the assurance of exclusion... i mean it would be possible, that someone else saved it between your query above and the update operation which should follow this query... checking and saving has to be atomic... like it is... with the update ... where id=id and updated_at=edited_version_updated_at... er no since MyObject is still versioned. The query above returns MyObject with the current version, if any. You update it, the UPDATE statement uses the same version id in the WHERE clause so is atomic. ah yeah... right... sorry... i didn't think that far ;-) ehm... just another question... does the versioning exactly work like in hibernate? that would mean it only works during one transaction... no detatching allowed... etc etc... that would mean... the above code is the only one you can use with a stateless http application server? thanks again... i'm learning more and more :) -- Greetings Jan Müller -- 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: rollback not working
I have put a self contained script that can probably reproduce this
for you. I think that the argument 'connect_args': {'autocommit' :
True } is causing the transactions to not work properly. Is this
expected and if so can you explain the reason. Thanks in advance.
from sqlalchemy import *
from sqlalchemy.sql import *
def main():
Main body of the script.
meta= MetaData()
kwargs = {
'echo' : True,
'module_name' : 'pyodbc',
'connect_args': {'autocommit' : True },
}
engine = create_engine(mssql://login:password@/database?
driver=FreeTDSdsn=DBDEV, **kwargs)
connection = engine.connect()
meta.bind = engine
table = Table(bbb, meta, Column('id', Integer,
primary_key=True), Column('name', String),
Column('tt_start', Date, primary_key=True),
Column('tt_end', Date, primary_key=True),
Column('vt_start', Date, primary_key=True),
Column('vt_end', Date, primary_key=True))
table.create()
for row in connection.execute(select([table])):
print row
trans = connection.begin()
try:
ins = table.insert().values(id=122, name='k',
tt_start='20100101', tt_end='20100101', vt_start='20100101',
vt_end='20100101')
connection.execute(ins)
ins = table.insert().values(id=121, name='k')
connection.execute(ins)
trans.commit()
except:
trans.rollback()
for row in connection.execute(select([table])):
print row
raise
if __name__ == __main__:
main()
--
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] class_variable as polymorphic_identity
Hi everybody, (fast start :) ) I have a class hierarchy and I'm using joined table inheritance, but my classes are not always going to be used with SQLAlchemy (or I plan to give the freedom to people to use these classes anywhere they want). Anyway I was using the entity_type column in my base table to map the polymorphic_identity. It was nice to have the entity_type variable *after* mapping with SQLAlchemy. Now, I decided to add this variable to all my classes as a new class variable. So my problem is when I map with SQLAlchemy the variable becomes an sqlalchemy.orm.attributes.InstrumentedAttribute and prints out None before committing the objects, so I created another column for the discriminator with the name db_entity_type now I'm not able to use the default value for entity_type from the class. Is there a solution for that? Thanks... E.Ozgur Yilmaz Lead Technical Director eoyilmaz.blogspot.com www.ozgurfx.com -- 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] Joining three tables - Selecting column from two different tables
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()
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 at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Re: rollback not working
that is absolutely the reason transactions would not be working for you.
autocommit on the DBAPI essentially makes the commit() and rollback() methods
of the DBAPI connection a no-op.
On Jan 18, 2011, at 5:12 AM, bool wrote:
I have put a self contained script that can probably reproduce this
for you. I think that the argument 'connect_args': {'autocommit' :
True } is causing the transactions to not work properly. Is this
expected and if so can you explain the reason. Thanks in advance.
from sqlalchemy import *
from sqlalchemy.sql import *
def main():
Main body of the script.
meta= MetaData()
kwargs = {
'echo' : True,
'module_name' : 'pyodbc',
'connect_args': {'autocommit' : True },
}
engine = create_engine(mssql://login:password@/database?
driver=FreeTDSdsn=DBDEV, **kwargs)
connection = engine.connect()
meta.bind = engine
table = Table(bbb, meta, Column('id', Integer,
primary_key=True), Column('name', String),
Column('tt_start', Date, primary_key=True),
Column('tt_end', Date, primary_key=True),
Column('vt_start', Date, primary_key=True),
Column('vt_end', Date, primary_key=True))
table.create()
for row in connection.execute(select([table])):
print row
trans = connection.begin()
try:
ins = table.insert().values(id=122, name='k',
tt_start='20100101', tt_end='20100101', vt_start='20100101',
vt_end='20100101')
connection.execute(ins)
ins = table.insert().values(id=121, name='k')
connection.execute(ins)
trans.commit()
except:
trans.rollback()
for row in connection.execute(select([table])):
print row
raise
if __name__ == __main__:
main()
--
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] Joining three tables - Selecting column from two different tables
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 at
http://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 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] class_variable as polymorphic_identity
On Jan 18, 2011, at 9:42 AM, Erkan Özgür Yılmaz wrote: Hi everybody, (fast start :) ) I have a class hierarchy and I'm using joined table inheritance, but my classes are not always going to be used with SQLAlchemy (or I plan to give the freedom to people to use these classes anywhere they want). Anyway I was using the entity_type column in my base table to map the polymorphic_identity. It was nice to have the entity_type variable *after* mapping with SQLAlchemy. Now, I decided to add this variable to all my classes as a new class variable. So my problem is when I map with SQLAlchemy the variable becomes an sqlalchemy.orm.attributes.InstrumentedAttribute and prints out None before committing the objects, so I created another column for the discriminator with the name db_entity_type now I'm not able to use the default value for entity_type from the class. Is there a solution for that? 0.7 has the polymorphic discriminator column auto-populated when you first create an object, so you wouldn't have this issue then. For now keep your db_entity_type as the mapped column, keep entity_type as class based, and use it in the mapping: class MyClass(MyParent): entity_type = 'myclass' mapper(MyClass, mytable, inherits=MyParent, polymorphic_identity=MyClass.entity_type) Thanks... E.Ozgur Yilmaz Lead Technical Director eoyilmaz.blogspot.com www.ozgurfx.com -- 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] Problem with a query
Hi all, It doesn't do the second filter with those queries: session.query(User).options(joinedload(channels)).filter(User.id == int(userId)).filter(Channel.title != zeptextstuff.txt).order_by(Channel.titleView).first() or session.query(User).join(User.channels).filter(User.id == int(userId)).filter(Channel.title != zeptextstuff.txt).order_by(Channel.titleView).first() or session.query(User).options(joinedload(channels)).filter(and_(User.id == int(userId), Channel.title != zeptextstuff.txt)).order_by(Channel.titleView).first() I get a user with a list of sorted channels, but I also get zeptextstuff.txt channel. Any clue?? Thanks in advance! -- 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] Problem with a query
you want to use query.join() here, not joinedload. http://www.sqlalchemy.org/trac/wiki/FAQ#ImusinglazyFalsetocreateaJOINOUTERJOINandSQLAlchemyisnotconstructingthequerywhenItrytoaddaWHEREORDERBYLIMITetc.whichreliesupontheOUTERJOIN I need to update the links in that FAQ entry. On Jan 18, 2011, at 12:12 PM, Alvaro Reinoso wrote: Hi all, It doesn't do the second filter with those queries: session.query(User).options(joinedload(channels)).filter(User.id == int(userId)).filter(Channel.title != zeptextstuff.txt).order_by(Channel.titleView).first() or session.query(User).join(User.channels).filter(User.id == int(userId)).filter(Channel.title != zeptextstuff.txt).order_by(Channel.titleView).first() or session.query(User).options(joinedload(channels)).filter(and_(User.id == int(userId), Channel.title != zeptextstuff.txt)).order_by(Channel.titleView).first() I get a user with a list of sorted channels, but I also get zeptextstuff.txt channel. Any clue?? Thanks in advance! -- 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] DB API for MSSQL on Cygwin
Has anyone had success with installing a DB API for Microsoft SQL Server on Cygwin? I could not install pyodbc or pymssql on Cygwin, ostensibly due to a lack of support for Cygwin and there isn't a specific Cygwin compatibility column on http://www.sqlalchemy.org/docs/core/engines.html#supported-dbapis -- 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] Writing values to Postgresql type Float
Hello, I am having problems using sqlalchemy to write values to Postgresq columns of type Float. I am getting sqlalchemy.exc.ProgrammingError: (ProgrammingError) can't adapt errors when I try to insert records. My Postgresql table is defined as: Column | Type |Modifiers + +-- max_pm25 | double precision | mean_pm25 | double precision | After importing the Postgresql dialect: from sqlalchemy.dialects.postgresql import * I define my sqlalchemy table as: class dream4_eta_15km_pm10(Base): __tablename__='pm10_dream_rasters' max_pm10=Column(DOUBLE_PRECISION) mean_pm10=Column(DOUBLE_PRECISION) Any help appreciated, Bill -- 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] I need a final push
Hi All,
I need a last pushand I hope someone here in SQLAlcheny cab give me
some.
My application uses elixir on a sqlite database.
I Have a table Person with a field birthdate, now I want to sort on the
month of this field.
From examples and a lot of peeking I have worked out how to add
Userdefined functions.
session.bind = metadata.bind
def monthfrom(date):
return datetime.strptime(date, '%Y-%m-%d %H:%M:%S').month
con = session.bind.connect().connection
con.create_function(monthfrom, 1, monthfrom)
but now come the part I have made very little progress on.
personslist=Person.query.filter(Person.birthdate!=None).order_by(func.monthfrom(:date),
{date: datetime(2000, 1, 2, 0, 0, 0), })
I get an SQLAlchemy.exc.OperationalError
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.
[sqlalchemy] reflected DATETIME
I'm trying to build a MySQL-PostgreSQL migration tool that reflects tables from MySQL, then creates them in pg. So far so good, except that when SQLAlchemy reflects a MySQL table with a DATETIME column, it reflects it as a sqlalchemy.types.DATETIME, then tries to create a DATETIME in PostgreSQL. I get LINE 7: SFDC_LAST_UPDATED DATETIME DEFAULT '-00-00 00:00:00' ... sqlalchemy.exc.ProgrammingError: (ProgrammingError) type datetime does not exist I haven't been able to come up with a way around this... either change the column's type after reflection to DateTime (how?), or command sqlalchemy to reflect them as sqlalchemy.DateTime in the first place (how?), or... I don't know. I can't hard-code the column definitions b/c I want the tool to adapt when the original (MySQL) database is changed. Can anybody help? Thanks very much! -- - Catherine http://catherinedevlin.blogspot.com -- 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] INSERT RETURNING question
Hi Probably a very simple question. I use the ORM for inserts, with postgres 8.3. How can I get the ids resulting from my inserts' RETURNING clauses? I haven't been able to find the information in the doc. Thanks a lot, -- Eric Lemoine Camptocamp France SAS Savoie Technolac, BP 352 73377 Le Bourget du Lac, Cedex Tel : 00 33 4 79 44 44 96 Mail : eric.lemo...@camptocamp.com http://www.camptocamp.com -- 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: INSERT RETURNING question
On Tuesday, January 18, 2011, Eric Lemoine eric.lemo...@camptocamp.com wrote: Hi Probably a very simple question. I use the ORM for inserts, with postgres 8.3. How can I get the ids resulting from my inserts' RETURNING clauses? I haven't been able to find the information in the doc. Doing just_inserted_obj.id causes a SELECT ... WHERE id= query, which I'd like to avoid. -- Eric Lemoine Camptocamp France SAS Savoie Technolac, BP 352 73377 Le Bourget du Lac, Cedex Tel : 00 33 4 79 44 44 96 Mail : eric.lemo...@camptocamp.com http://www.camptocamp.com -- 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: INSERT RETURNING question
On Jan 18, 2011, at 4:47 PM, Eric Lemoine wrote: On Tuesday, January 18, 2011, Eric Lemoine eric.lemo...@camptocamp.com wrote: Hi Probably a very simple question. I use the ORM for inserts, with postgres 8.3. How can I get the ids resulting from my inserts' RETURNING clauses? I haven't been able to find the information in the doc. Doing just_inserted_obj.id causes a SELECT ... WHERE id= query, which I'd like to avoid. That sounds like you're calling commit() which is expiring all data, resulting in a re-fetch when you hit .id. Just call session.flush(), then get the .id from your objects, before a commit() occurs. The insert() construct used by the ORM abstracts away whether RETURNING, cursor.lastrowid, or some other method is used to get the server-generated primary key. This is probably why searching specifically for RETURNING/ ORM is not turning anything up. -- Eric Lemoine Camptocamp France SAS Savoie Technolac, BP 352 73377 Le Bourget du Lac, Cedex Tel : 00 33 4 79 44 44 96 Mail : eric.lemo...@camptocamp.com http://www.camptocamp.com -- 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: INSERT RETURNING question
On 01/18/2011 10:47 PM, Eric Lemoine wrote: On Tuesday, January 18, 2011, Eric Lemoineeric.lemo...@camptocamp.com wrote: Hi Probably a very simple question. I use the ORM for inserts, with postgres 8.3. How can I get the ids resulting from my inserts' RETURNING clauses? I haven't been able to find the information in the doc. Doing just_inserted_obj.id causes a SELECT ... WHERE id= query, which I'd like to avoid. Hey there, i am just guessing a little bit... maybe you need to set the following? sessionmaker(expire_on_commit=False) -- Greetings Jan Müller -- 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] reflected DATETIME
Ultimately you have to modify the reflected tables or rewrite them using data types that are appropriate to the target database. Reflection is in fact going to produce the most specific type possible, so in this case you're actually getting sqlalchemy.dialects.mysql.VARCHAR objects and such back which contain additional arguments.For most types, when the PG dialect receives them it will just interpret a mysql.VARCHAR as a postgresql.VARCHAR, ignoring the MySQL-specific arguments. For a DATETIME though there is no actual DATETIME on PG. So this would be a matter of changing the type on all the Column() objects to be types.DateTime(), the generic datetime type, on those columns that are types.DATETIME. You should be able to reassign it, i.e. somecolumn.type = DateTime(), and it will work (just instantiate the type object). There's a vague plan at some point to allow this behavior to be configurable with reflection, i.e. either reflect the most specific types possible, or reflect generic types when possible. Currently though it just works the one way (and in 0.5 and earlier, worked the other way,which was changed since it loses information). On Jan 18, 2011, at 4:07 PM, Catherine Devlin wrote: I'm trying to build a MySQL-PostgreSQL migration tool that reflects tables from MySQL, then creates them in pg. So far so good, except that when SQLAlchemy reflects a MySQL table with a DATETIME column, it reflects it as a sqlalchemy.types.DATETIME, then tries to create a DATETIME in PostgreSQL. I get LINE 7: SFDC_LAST_UPDATED DATETIME DEFAULT '-00-00 00:00:00' ... sqlalchemy.exc.ProgrammingError: (ProgrammingError) type datetime does not exist I haven't been able to come up with a way around this... either change the column's type after reflection to DateTime (how?), or command sqlalchemy to reflect them as sqlalchemy.DateTime in the first place (how?), or... I don't know. I can't hard-code the column definitions b/c I want the tool to adapt when the original (MySQL) database is changed. Can anybody help? Thanks very much! -- - Catherine http://catherinedevlin.blogspot.com -- 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] I need a final push
On Jan 18, 2011, at 3:48 PM, F.A.Pinkse wrote:
Hi All,
I need a last pushand I hope someone here in SQLAlcheny cab give me some.
My application uses elixir on a sqlite database.
I Have a table Person with a field birthdate, now I want to sort on the month
of this field.
From examples and a lot of peeking I have worked out how to add Userdefined
functions.
session.bind = metadata.bind
def monthfrom(date):
return datetime.strptime(date, '%Y-%m-%d %H:%M:%S').month
con = session.bind.connect().connection
con.create_function(monthfrom, 1, monthfrom)
but now come the part I have made very little progress on.
personslist=Person.query.filter(Person.birthdate!=None).order_by(func.monthfrom(:date),
{date: datetime(2000, 1, 2, 0, 0, 0), })
you're looking for order_by(func.monthfrom(datetime(2000, 1, 2, 0, 0, 0))).
Alternatively, if you want to pass in the date parameter separately, you
could say
order_by(func.monthfrom(bindparam(date))).params(date=datetime(2000, 1, 2, 0,
0, 0))
I get an SQLAlchemy.exc.OperationalError
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.
Re: [sqlalchemy] Writing values to Postgresql type Float
Here's a tested example of DOUBLE_PRECISION using both float and Decimal
versions. Make sure you're on a recent release of psycopg2:
from sqlalchemy import Column, create_engine, Integer
from sqlalchemy.orm import Session
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.dialects.postgresql import DOUBLE_PRECISION
from decimal import Decimal
Base = declarative_base()
class dream4_eta_15km_pm10(Base):
__tablename__='pm10_dream_rasters'
id = Column(Integer, primary_key=True)
# use float values
max_pm10=Column(DOUBLE_PRECISION)
# use Decimal values
mean_pm10=Column(DOUBLE_PRECISION(asdecimal=True))
def __repr__(self):
return dream4_eta_15km_pm10(%r, %r) % (self.max_pm10, self.mean_pm10)
engine = create_engine('postgresql://scott:tiger@localhost/test', echo=True)
Base.metadata.create_all(engine)
sess = Session(engine)
sess.add(dream4_eta_15km_pm10(max_pm10=76945.283959,
mean_pm10=Decimal(7683.27835)))
sess.commit()
print sess.query(dream4_eta_15km_pm10).all()
On Jan 18, 2011, at 3:24 PM, wilbur wrote:
Hello,
I am having problems using sqlalchemy to write values to Postgresq
columns of type Float. I am getting sqlalchemy.exc.ProgrammingError:
(ProgrammingError) can't adapt errors when I try to insert records.
My Postgresql table is defined as:
Column | Type
|Modifiers
+
+--
max_pm25 | double precision |
mean_pm25 | double precision |
After importing the Postgresql dialect:
from sqlalchemy.dialects.postgresql import *
I define my sqlalchemy table as:
class dream4_eta_15km_pm10(Base):
__tablename__='pm10_dream_rasters'
max_pm10=Column(DOUBLE_PRECISION)
mean_pm10=Column(DOUBLE_PRECISION)
Any help appreciated,
Bill
--
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] class_variable as polymorphic_identity
thanks Michael, because I edited my question a couple of times, and also trying to solve it while editing the text, it went to a slightly different way then I want to point to, Let me explain it in a different way, may be I am asking the wrong question: Right now I'm developing an Open Source Production Asset Management System (ProdAM). Lets say I have Project, Sequence, Shot, Asset, Task etc. classes, each class should have a status attribute: A Project can have these statuses: - Waiting To Start - On Hold - In Progress - Complete A Shot can have these statuses: - Waiting To Start - Waiting Offline - In Progress - On Hold - Pending Review - Approved - Complete A Sequence can have these statuses: - Waiting To Start - On Hold - In Progress - Pending Review - Approved - Complete etc. As you see most of the statuses are going to be common, so I decided to have a StatusList object holding Status objects. And let the user create all the statuses he needs in his studio and then group them in StatusList objects. But this introduces another problem, when the user creates a new Project object he needs to specify which StatusList he wants to use, and serving all the StatusLists and letting him to choose is not a good idea (then he can choose a wrong StatusList which is not designed for that kind of objects). So instead of doing that, and to automate this part, I wanted to have another object holding the available StatusList objects for specific object types, lets say an EntityTypeStatusListOption table/object which has a column for entity_type and another for statusList_id. (all right we are there finally). Then, I wanted to use the entity_type attribute of the polymorphic_identity (just because it was showing the type of the object) and the id of the StatusList to hold the StatusList for specific kind of objects. And because I'm using plain Python objects in my class hierarchy, I didn't want to use something (the entity_type attribute) which is only introduced when the user uses SQLAlchemy (after mapping). Then, I decided to add the entity_type as a class attribute and try to persist it and also use it as the polymorphic_identity, but it didn't work like that, I did what you suggest (another column with the name db_entity_type for the polymorphic_identity) but then I couldn't able to store the value of the class attribute entity_type in the base table (where as the db_entity_type was holding the correct value (db_entity_type=MyClass.entity_type) ) I believe I need to store it to be able to use it in secondary join with the EntityTypeStatusListOption table (or should I use the db_entity_type for the join, may be, anyway, I'm not sure). So this is my first question, do you have any suggestion? My second question is about the validity of my idea, just because I have never designed a system which uses a database, I'm not sure about all this setup, and may be I'm making it complex than it needs to be, or there are other simple ways of doing the same thing (I think I need to read about the design patterns in relational databases). So anyway is there anything you can suggest me with this setup. and sorry about asking something which is not directly related with SQLAlchemy itself but the design of the database. Thank you again... E.Ozgur Yilmaz Lead Technical Director eoyilmaz.blogspot.com www.ozgurfx.com On Tue, Jan 18, 2011 at 5:56 PM, Michael Bayer mike...@zzzcomputing.comwrote: On Jan 18, 2011, at 9:42 AM, Erkan Özgür Yılmaz wrote: Hi everybody, (fast start :) ) I have a class hierarchy and I'm using joined table inheritance, but my classes are not always going to be used with SQLAlchemy (or I plan to give the freedom to people to use these classes anywhere they want). Anyway I was using the entity_type column in my base table to map the polymorphic_identity. It was nice to have the entity_type variable *after* mapping with SQLAlchemy. Now, I decided to add this variable to all my classes as a new class variable. So my problem is when I map with SQLAlchemy the variable becomes an sqlalchemy.orm.attributes.InstrumentedAttribute and prints out None before committing the objects, so I created another column for the discriminator with the name db_entity_type now I'm not able to use the default value for entity_type from the class. Is there a solution for that? 0.7 has the polymorphic discriminator column auto-populated when you first create an object, so you wouldn't have this issue then. For now keep your db_entity_type as the mapped column, keep entity_type as class based, and use it in the mapping: class MyClass(MyParent): entity_type = 'myclass' mapper(MyClass, mytable, inherits=MyParent, polymorphic_identity=MyClass.entity_type) Thanks... E.Ozgur Yilmaz Lead Technical Director eoyilmaz.blogspot.com www.ozgurfx.com -- You received this message because you are subscribed to the Google
[sqlalchemy] ODBC general question
Hi All We need to connect to a Progress database, and we are in the very early days of this. I understand it supports an ODBC interface and therefore should be able to be connected to using SA - correct? Are there any limitations on the ODBC connector or gotcha's that we should look out for? Any advise / direction would be most appreciated when you have a moment. Cheers from very wet Brisbane Australia. Warwick -- 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] class_variable as polymorphic_identity
On Jan 18, 2011, at 6:51 PM, Erkan Özgür Yılmaz wrote:
thanks Michael,
because I edited my question a couple of times, and also trying to solve it
while editing the text, it went to a slightly different way then I want to
point to,
Let me explain it in a different way, may be I am asking the wrong question:
Right now I'm developing an Open Source Production Asset Management System
(ProdAM). Lets say I have Project, Sequence, Shot, Asset, Task etc. classes,
each class should have a status attribute:
A Project can have these statuses:
- Waiting To Start
- On Hold
- In Progress
- Complete
A Shot can have these statuses:
- Waiting To Start
- Waiting Offline
- In Progress
- On Hold
- Pending Review
- Approved
- Complete
A Sequence can have these statuses:
- Waiting To Start
- On Hold
- In Progress
- Pending Review
- Approved
- Complete
etc.
As you see most of the statuses are going to be common, so I decided to have
a StatusList object holding Status objects. And let the user create all the
statuses he needs in his studio and then group them in StatusList objects.
But this introduces another problem, when the user creates a new Project
object he needs to specify which StatusList he wants to use, and serving all
the StatusLists and letting him to choose is not a good idea (then he can
choose a wrong StatusList which is not designed for that kind of objects).
So instead of doing that, and to automate this part, I wanted to have another
object holding the available StatusList objects for specific object types,
lets say an EntityTypeStatusListOption table/object which has a column for
entity_type and another for statusList_id. (all right we are there
finally). Then, I wanted to use the entity_type attribute of the
polymorphic_identity (just because it was showing the type of the object) and
the id of the StatusList to hold the StatusList for specific kind of objects.
And because I'm using plain Python objects in my class hierarchy, I didn't
want to use something (the entity_type attribute) which is only introduced
when the user uses SQLAlchemy (after mapping). Then, I decided to add the
entity_type as a class attribute and try to persist it and also use it as
the polymorphic_identity, but it didn't work like that, I did what you
suggest (another column with the name db_entity_type for the
polymorphic_identity) but then I couldn't able to store the value of the
class attribute entity_type in the base table (where as the db_entity_type
was holding the correct value (db_entity_type=MyClass.entity_type) ) I
believe I need to store it to be able to use it in secondary join with the
EntityTypeStatusListOption table (or should I use the db_entity_type for the
join, may be, anyway, I'm not sure). So this is my first question, do you
have any suggestion?
The values you have in StatusList aren't polymorphic identities, those are
states. For a Sequence object, its polymorphic identity would be sequence,
it has another column state that would have a StatusList value.
I would encode StatusList using an enum (see the recipe at
http://techspot.zzzeek.org/2011/01/14/the-enum-recipe/ ), and as far as
constraining them, there are two choices. Either each of Project, Sequence,
Shot has its own table with a status field constrained to a different class
of StatusList, or if they are all using a common base table, I would constrain
StatusList using validation:
from sqlalchemy.orm import validates
class StatusList(DeclEnum):
waiting = waiting, Waiting
on_hold = on_hold, On Hold
in_progress = in_progress, In Progress
class BaseAsset(Base):
__tablename__ = 'base'
id = Column(Integer, primary_key=True)
status = Column(StatusList.db_type(), nullable=False)
db_entity_type = Column(String(50), nullable=False)
@validates('status')
def _set_status(self, key, value):
assert value in self.available_statuses
@declared_attr
def __mapper_args__(cls):
if hasattr(cls, 'entity_type'):
return {'polymorphic_identity':cls.entity_type}
else:
return {'polymorphic_on':cls.db_entity_type}
class Project(BaseAsset, Base):
__tablename__ = 'project'
entity_type = 'project'
available_statuses = \
StatusList.waiting,\
StatusList.on_hold,\
StatusList.in_progress
My second question is about the validity of my idea, just because I have
never designed a system which uses a database, I'm not sure about all this
setup, and may be I'm making it complex than it needs to be, or there are
other simple ways of doing the same thing (I think I need to read about the
design patterns in relational databases). So anyway is there anything you can
suggest me with this setup.
I would just make sure that Project, Sequence,
Re: [sqlalchemy] ODBC general question
pyodbc works very well, as does mxodbc which is commercial. Most issues have to do with using ODBC from unix, where if we're working for free we use FreeTDS, that has a lot of quirks. There are commercial ODBC drivers for unix which I haven't used but we will be using them soon for a commercial project. When writing an ODBC dialect for SQLAlchemy you extend sqlalchemy.connectors.pyodbc.PyODBCConnector (or MxODBCConnector) for your dialect, you can look at the several pyodbc clients we have already (mssql, mysql, sybase) for examples. The other advantage/disadvantage of ODBC is the usage of datasources. This is basically an extra layer of indirection between connect strings and an actual TCP hostname. Some setups allow the bypassing of the DSN and a lot of confusion comes from whether or not that is in use, since a lot of homegrowers impatiently try to skip that layer and get all confused. Its best to use externally configured DSNs when working with ODBC since that's how it was designed to be used. On Jan 18, 2011, at 6:51 PM, Warwick Prince wrote: Hi All We need to connect to a Progress database, and we are in the very early days of this. I understand it supports an ODBC interface and therefore should be able to be connected to using SA - correct? Are there any limitations on the ODBC connector or gotcha's that we should look out for? Any advise / direction would be most appreciated when you have a moment. Cheers from very wet Brisbane Australia. Warwick -- 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] ODBC general question
Hi Michael Thanks yet again for excellent advice provided in a timely manner! :-) Cheers Warwick On 19/01/2011, at 10:25 AM, Michael Bayer wrote: pyodbc works very well, as does mxodbc which is commercial. Most issues have to do with using ODBC from unix, where if we're working for free we use FreeTDS, that has a lot of quirks. There are commercial ODBC drivers for unix which I haven't used but we will be using them soon for a commercial project. When writing an ODBC dialect for SQLAlchemy you extend sqlalchemy.connectors.pyodbc.PyODBCConnector (or MxODBCConnector) for your dialect, you can look at the several pyodbc clients we have already (mssql, mysql, sybase) for examples. The other advantage/disadvantage of ODBC is the usage of datasources. This is basically an extra layer of indirection between connect strings and an actual TCP hostname. Some setups allow the bypassing of the DSN and a lot of confusion comes from whether or not that is in use, since a lot of homegrowers impatiently try to skip that layer and get all confused. Its best to use externally configured DSNs when working with ODBC since that's how it was designed to be used. On Jan 18, 2011, at 6:51 PM, Warwick Prince wrote: Hi All We need to connect to a Progress database, and we are in the very early days of this. I understand it supports an ODBC interface and therefore should be able to be connected to using SA - correct? Are there any limitations on the ODBC connector or gotcha's that we should look out for? Any advise / direction would be most appreciated when you have a moment. Cheers from very wet Brisbane Australia. Warwick -- 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.
[sqlalchemy] Adjacency List Relationship in a Child Class
Whenever I try to establish an adjacency list relationship within a
child class (Department -- Parent Department in this case) the orm
complains about foreign key columns present in both the parent and
child class, and won’t construct the mapping. Below is an example
illustrating the problem. I'd appreciate any insight.
Thanks,
Michael Naber
from sqlalchemy.orm import scoped_session, sessionmaker, relationship,
backref
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine, Column, Integer, String,
ForeignKey, Text, Date
Session = scoped_session(sessionmaker())
Base = declarative_base()
engine = create_engine('sqlite:///data.db')
Session.configure(bind=engine)
class Node(Base):
__tablename__ = 'node'
id = Column(Integer, primary_key=True)
name = Column(String(100))
discriminator = Column('discriminator', String(50))
class Department(Node):
__tablename__ = 'department'
__mapper_args__ = {'polymorphic_identity': 'department'}
id = Column(Integer, ForeignKey('node.id'), primary_key=True)
description = Column(Text)
parent_department_id = Column(Integer,
ForeignKey('department.id'))
parent_department = relationship(Department,
primaryjoin=Department.parent_department_id==Department.id,
foreign_keys=Department.parent_department_id,
backref=backref(subdepartments), remote_side=Department.id)
Base.metadata.drop_all(checkfirst=True, bind=Session.bind)
Base.metadata.create_all(bind=Session.bind)
s = Session()
d = Department(name='Great Department', description='some text')
s.add(d)
s.commit()
for dept in s.query(Department).all():
print dept.id
print dept.name
--
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: INSERT RETURNING question
On Tue, Jan 18, 2011 at 11:33 PM, Michael Bayer mike...@zzzcomputing.com wrote: On Jan 18, 2011, at 4:47 PM, Eric Lemoine wrote: On Tuesday, January 18, 2011, Eric Lemoine eric.lemo...@camptocamp.com wrote: Hi Probably a very simple question. I use the ORM for inserts, with postgres 8.3. How can I get the ids resulting from my inserts' RETURNING clauses? I haven't been able to find the information in the doc. Doing just_inserted_obj.id causes a SELECT ... WHERE id= query, which I'd like to avoid. That sounds like you're calling commit() which is expiring all data, resulting in a re-fetch when you hit .id. Just call session.flush(), then get the .id from your objects, before a commit() occurs. I did not expect that obj.id (the primary key) would be expired, as doing SELECT id WHERE id= didn't make sense to me. Thanks. -- Eric Lemoine Camptocamp France SAS Savoie Technolac, BP 352 73377 Le Bourget du Lac, Cedex Tel : 00 33 4 79 44 44 96 Mail : eric.lemo...@camptocamp.com http://www.camptocamp.com -- 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.
