[sqlalchemy] Using update()
Hi there, Can someone give me an example of using update()? Thanks. -- 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] Re: Unexpected SessionTransaction behaviour
On Jun 15, 2010, at 12:59 PM, Vinay Sajip wrote: > On Jun 15, 4:30 pm, Michael Bayer wrote: >> err, no, your test is incorrect. You are maintaining a reference to the >> SessionTransaction in "tolist". > > Whoops, you're right. However, should I really have to do a > gc.collect() after the session.remove() calls? Without it, I still get > the AssertionError. With it, I don't - but doesn't a requirement to > call gc.collect() mean that there could be non-deterministic, > potentially long pauses during request handling, because of other > stuff needing to be garbage-collected which has no connection to the > SQLA stuff? your original error is that you're seeing a SQLite connection shared between threads. If there are connection or session resources still present in the interpreter, but they are unreachable (i.e. would be collected on the next gc run), it is impossible for that unreachable connection object to find its way into another one of your requests. So no, calling gc.collect() is not necessary under normal circumstances, it is only needed here to for the purposes of the method of testing. > alchemy+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 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] Re: Unexpected SessionTransaction behaviour
On Jun 15, 4:30 pm, Michael Bayer wrote: > err, no, your test is incorrect. You are maintaining a reference to the > SessionTransaction in "tolist". Whoops, you're right. However, should I really have to do a gc.collect() after the session.remove() calls? Without it, I still get the AssertionError. With it, I don't - but doesn't a requirement to call gc.collect() mean that there could be non-deterministic, potentially long pauses during request handling, because of other stuff needing to be garbage-collected which has no connection to the SQLA stuff? Regards, Vinay Sajip -- 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] how do i take advantage of sqlite manifest typing / type affinity using sqlalchemy?
Thanks Michael - i've seen others complain about the arbitrary-precision numbers issue... I'm not so bothered by that, but i do like the ability to natively store integers and strings in the same column. On Tue, Jun 15, 2010 at 4:40 PM, Harry Percival wrote: > Answered my own question: > > Define a custom column type, as per > http://www.sqlalchemy.org/docs/reference/sqlalchemy/types.html#custom-types > > a combination of the documentation and some trial & error have given me > this: > > class MyDuckType(sqlalchemy.types.TypeDecorator): > > """ > SQLALchemy custom column type, designed to let sqlite handle the typing > using 'numeric affinity' which intelligently handles both numbers and > strings > > """ > impl = sqlite.NUMERIC > > def bind_processor(self, dialect): > > > #function for type coercion during db write > return None #ie pass value as-is, let sqlite do the typing > > > def result_processor(self, dialect, coltype): > > #function for type coercion during db read > return None #ie pass value as sqlite has stored it, should be > ducktyped already > > > def process_bind_param(self, value, dialect): > > #any changes to an individual value before store in DN > return value > > def process_result_value(self, value, dialect): > > > #any changes to an individual value after retrieve from DB > return value > > def copy(self): > > > #not quite sure what this is for > return MyDuckType() > > The current sqlalchemy dialect type returns to_float in bind_processor, > which is why I was getting the errors before. i.m.v.v.h.o., this is a bug. > > for my bonus points: manually setting column type to MyDuckType in my > metadata.reflect() code: > > def get_database_tables(engine): > meta = MetaData() > > meta.reflect(bind=engine) > > tables = meta.raw_tables > for tbl in tables.values(): > > > for col in tbl.c: > > col.type = MyDuckType() > return tables > > seems to work for me. Any suggestions / improvements? I've tentatively > filled out a ticket on the sqlalchemy bug tracker, not sure if that was a > little arrogant? > > > On Tue, Jun 15, 2010 at 4:22 PM, Michael Bayer > wrote: > >> >> On Jun 15, 2010, at 7:14 AM, Harry Percival wrote: >> >> Not sure what the etiquette is re cross-posting to this list from >> stackoverflow? here's my question: >> >> >> http://stackoverflow.com/questions/3044518/how-do-i-take-advantage-of-sqlite-manifest-typing-type-affinity-using-sqlalchem >> >> >> AFAIK this would be a function of Pysqlite.You can make your own >> SQLAlchemy types sure but thats SQLalchemy doing it, not SQLite's feature. >> I've read that sqlite documentation page many times and I've never seen >> what it says actually occur, particularly regarding numerics.Its either >> inaccurate or Pysqlite squashes the behavior, or I just don't really >> understand it. There's no way to get numerics of arbitrary precision in >> and out of SQLite, for example - if you google around for "decimal" support >> you'll see that its not possible - floating point conversions always kick >> in. >> >> >> >> >> >> >> >> I like the idea of sqlite's manifest typing / type affinity: >> >> http://www.sqlite.org/datatype3.html >> >> Essentially, if I set a column's affinity as 'numeric', it will duck type >> integers or floats to store them as such, but still allow me to store >> strings if I want to. Seems to me this is the best 'default' type for a >> column when i'm not sure ahead of time of what data i want to store in it. >> >> so off i go: >> >> metadata = MetaData() >> new_table = Table(table_name, metadata ) >> >> >> >> for col_name in column_headings: >> new_table.append_column(Column(col_name, >> >> >> >> >>sqlite.NUMERIC, #this should duck-type >> numbers but can handle strings as well >> >> >>primary_key=col_name in >> primary_key_columns)) >> >> >> new_table.create(self.engine, checkfirst=False) >> >> but when i try and store some string values, eg "abc" in the table, >> sqlalchemy falls over: >> >> File "[...]\sqlalchemy\processors.py", line 79, in to_float >> >> >> >> >> return float(value) >> ValueError: invalid literal for float(): abc >> >> >> >> Boo, hiss. So, is there any way I can convince sqlalchemy to let sqlite do >> the typing? perhaps i can use a type from sqlalchemy.types instead of >> sqlachemy.dialects.sqlite? >> >> since the so post, i've done a little more digging. Am I going to have to >> write my own custom type as per >> http://www.sqlalchemy.org/docs/reference/sqlalchemy/types.html#custom-types? >> >> hp >> >> >> -- >> -- >> Harry J.W. Percival >> -- >> Italy Mobile: +39 389 095 8959 >> UK Mobile: +44 (0) 78877 02511 (may be turned off) >> Skype: harry.percival >> Email: har
Re: [sqlalchemy] how do i take advantage of sqlite manifest typing / type affinity using sqlalchemy?
Answered my own question: Define a custom column type, as per http://www.sqlalchemy.org/docs/reference/sqlalchemy/types.html#custom-types a combination of the documentation and some trial & error have given me this: class MyDuckType(sqlalchemy.types.TypeDecorator): """ SQLALchemy custom column type, designed to let sqlite handle the typing using 'numeric affinity' which intelligently handles both numbers and strings """ impl = sqlite.NUMERIC def bind_processor(self, dialect): #function for type coercion during db write return None #ie pass value as-is, let sqlite do the typing def result_processor(self, dialect, coltype): #function for type coercion during db read return None #ie pass value as sqlite has stored it, should be ducktyped already def process_bind_param(self, value, dialect): #any changes to an individual value before store in DN return value def process_result_value(self, value, dialect): #any changes to an individual value after retrieve from DB return value def copy(self): #not quite sure what this is for return MyDuckType() The current sqlalchemy dialect type returns to_float in bind_processor, which is why I was getting the errors before. i.m.v.v.h.o., this is a bug. for my bonus points: manually setting column type to MyDuckType in my metadata.reflect() code: def get_database_tables(engine): meta = MetaData() meta.reflect(bind=engine) tables = meta.raw_tables for tbl in tables.values(): for col in tbl.c: col.type = MyDuckType() return tables seems to work for me. Any suggestions / improvements? I've tentatively filled out a ticket on the sqlalchemy bug tracker, not sure if that was a little arrogant? On Tue, Jun 15, 2010 at 4:22 PM, Michael Bayer wrote: > > On Jun 15, 2010, at 7:14 AM, Harry Percival wrote: > > Not sure what the etiquette is re cross-posting to this list from > stackoverflow? here's my question: > > > http://stackoverflow.com/questions/3044518/how-do-i-take-advantage-of-sqlite-manifest-typing-type-affinity-using-sqlalchem > > > AFAIK this would be a function of Pysqlite.You can make your own > SQLAlchemy types sure but thats SQLalchemy doing it, not SQLite's feature. > I've read that sqlite documentation page many times and I've never seen > what it says actually occur, particularly regarding numerics.Its either > inaccurate or Pysqlite squashes the behavior, or I just don't really > understand it. There's no way to get numerics of arbitrary precision in > and out of SQLite, for example - if you google around for "decimal" support > you'll see that its not possible - floating point conversions always kick > in. > > > > > > > > I like the idea of sqlite's manifest typing / type affinity: > > http://www.sqlite.org/datatype3.html > > Essentially, if I set a column's affinity as 'numeric', it will duck type > integers or floats to store them as such, but still allow me to store > strings if I want to. Seems to me this is the best 'default' type for a > column when i'm not sure ahead of time of what data i want to store in it. > > so off i go: > > metadata = MetaData() > new_table = Table(table_name, metadata ) > > > for col_name in column_headings: > new_table.append_column(Column(col_name, > > > >sqlite.NUMERIC, #this should duck-type > numbers but can handle strings as well > > >primary_key=col_name in > primary_key_columns)) > > > new_table.create(self.engine, checkfirst=False) > > but when i try and store some string values, eg "abc" in the table, > sqlalchemy falls over: > > File "[...]\sqlalchemy\processors.py", line 79, in to_float > > > > return float(value) > ValueError: invalid literal for float(): abc > > > Boo, hiss. So, is there any way I can convince sqlalchemy to let sqlite do > the typing? perhaps i can use a type from sqlalchemy.types instead of > sqlachemy.dialects.sqlite? > > since the so post, i've done a little more digging. Am I going to have to > write my own custom type as per > http://www.sqlalchemy.org/docs/reference/sqlalchemy/types.html#custom-types? > > hp > > > -- > -- > Harry J.W. Percival > -- > Italy Mobile: +39 389 095 8959 > UK Mobile: +44 (0) 78877 02511 (may be turned off) > Skype: harry.percival > Email: harry.perci...@gmail.com > > -- > 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. > > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To
Re: [sqlalchemy] Re: Unexpected SessionTransaction behaviour
err, no, your test is incorrect. You are maintaining a reference to the
SessionTransaction in "tolist".
Change the middle of the loop to read:
assert len(tolist) == 1
del tolist
so that you are not artificially holding onto the SessionTransaction, and
additionally:
for s in sessions:
#s.commit() # Not needed, and makes no difference
s.remove()
gc.collect()
before your last fetch of "tolist", otherwise uncollected cycles remain. The
fact that they go away, however, means that no references remain.
On Jun 15, 2010, at 4:51 AM, Vinay Sajip wrote:
> import gc
> from sqlalchemy import create_engine
> from sqlalchemy.orm import sessionmaker, scoped_session
> from sqlalchemy.orm.session import SessionTransaction
> from sqlalchemy.pool import NullPool
>
> engine = create_engine('sqlite://', poolclass=NullPool)
> sessions = []
>
> # To simulate multi-db setup, create two sessions
> for i in range(2):
>sessions.append(scoped_session(sessionmaker(bind=engine)))
>
> for i in range(1, 3):
>print 'Looping: iteration (request) #%d' % i
>tolist = [c for c in gc.get_objects()
> if isinstance(c, SessionTransaction)]
>assert len(tolist) == 0
># Apparently scoped_sessions can be used without
># instantiating, so do that
># Just one of the databases will be used in the
># processing
>s = sessions[0]
>s.execute('select 1')
>tolist = [c for c in gc.get_objects()
> if isinstance(c, SessionTransaction)]
>assert len(tolist) == 1
>for s in sessions:
>#s.commit() # Not needed, and makes no difference
>s.remove()
># There should be no SessionTransactions remaining
>tolist = [c for c in gc.get_objects()
> if isinstance(c, SessionTransaction)]
># On my system, the next line raises AssertionError
>assert len(tolist) == 0, 'No SessionTransactions hanging around'
--
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] Kerberos authentication with sybase
On Jun 15, 2010, at 9:13 AM, Cserna, Zsolt wrote: > >>> I think it you want to make it flexible there should be a >> dictionary or a two-dimensional list specifying which options >> should be set, so in case of kerberos it would have two >> elements. Unfortunatelly these options cannot be specified >> for the connect() function of python-sybase. >> >> the goal here is so that the options can all be embedded in >> the URL at least as key/value pairs. How would the >> Sybase.XXX symbols be embedded ? >> > > The biggest problem here is the serialization of those values to string and > de-serializing them when sqlalchemy sets them to python-sybase. > It could be ok if we would know the type of the property but as far as I see > it cannot be introspected from the sybase library. > > We could have an algorithm serializing/deserializing the value: > - if it's starting with "CS_", we use it as the name of the variable in the > Sybase module > - if we can convert it to an integer we use it as an integer > - otherwise we use it as string specified > > Based on the above, my kerberos connection url would be the following > (missing username+pw in this case): > > sybase+pysybase://hostname/?CS_SEC_NETWORKAUTH=CS_TRUE&CS_SEC_SERVERPRINCIPAL=sybase/some_host > > It's just an idea, I don't know how it could fit into the design of > sqlalchemy. we can do whatever we want, this would all be local to the python-sybase dialect. The above is sort of like what I was thinking. > > Zsolt > > > -- > NOTICE: If received in error, please destroy, and notify sender. Sender does > not intend to waive confidentiality or privilege. Use of this email is > prohibited when received in error. We may monitor and store emails to the > extent permitted by applicable law. > > -- > 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. > -- 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] how do i take advantage of sqlite manifest typing / type affinity using sqlalchemy?
On Jun 15, 2010, at 7:14 AM, Harry Percival wrote: > Not sure what the etiquette is re cross-posting to this list from > stackoverflow? here's my question: > > http://stackoverflow.com/questions/3044518/how-do-i-take-advantage-of-sqlite-manifest-typing-type-affinity-using-sqlalchem AFAIK this would be a function of Pysqlite.You can make your own SQLAlchemy types sure but thats SQLalchemy doing it, not SQLite's feature. I've read that sqlite documentation page many times and I've never seen what it says actually occur, particularly regarding numerics.Its either inaccurate or Pysqlite squashes the behavior, or I just don't really understand it. There's no way to get numerics of arbitrary precision in and out of SQLite, for example - if you google around for "decimal" support you'll see that its not possible - floating point conversions always kick in. > > I like the idea of sqlite's manifest typing / type affinity: > > http://www.sqlite.org/datatype3.html > > Essentially, if I set a column's affinity as 'numeric', it will duck type > integers or floats to store them as such, but still allow me to store strings > if I want to. Seems to me this is the best 'default' type for a column when > i'm not sure ahead of time of what data i want to store in it. > > so off i go: > > metadata = MetaData() > new_table = Table(table_name, metadata ) > > > for col_name in column_headings: > new_table.append_column(Column(col_name, > > >sqlite.NUMERIC, #this should duck-type > numbers but can handle strings as well > > >primary_key=col_name in > primary_key_columns)) > > > new_table.create(self.engine, checkfirst=False) > > > but when i try and store some string values, eg "abc" in the table, > sqlalchemy falls over: > > File "[...]\sqlalchemy\processors.py", line 79, in to_float > > > return float(value) > ValueError: invalid literal for float(): abc > > > Boo, hiss. So, is there any way I can convince sqlalchemy to let sqlite do > the typing? perhaps i can use a type from sqlalchemy.types instead of > sqlachemy.dialects.sqlite? > > > since the so post, i've done a little more digging. Am I going to have to > write my own custom type as per > http://www.sqlalchemy.org/docs/reference/sqlalchemy/types.html#custom-types ? > > > hp > > > -- > -- > Harry J.W. Percival > -- > Italy Mobile: +39 389 095 8959 > UK Mobile: +44 (0) 78877 02511 (may be turned off) > Skype: harry.percival > Email: harry.perci...@gmail.com > > -- > 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. -- 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 with func.count
Hello, OK I figured this out: rsvp = session.query(Project.project, Project.id, func.count(Reservation.project_id)).join(Reservation.project).group_by(Project.project, Project.id).order_by(desc(func.count(Reservation.project_id))).all() I'm not normally a vaseline man, but this is amazing: how did SQLA guess *correctly* what I wanted here? I love this toolkit! -- Regards, mk -- Premature optimization is the root of all fun. -- 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] Kerberos authentication with sybase
> > I think it you want to make it flexible there should be a > dictionary or a two-dimensional list specifying which options > should be set, so in case of kerberos it would have two > elements. Unfortunatelly these options cannot be specified > for the connect() function of python-sybase. > > the goal here is so that the options can all be embedded in > the URL at least as key/value pairs. How would the > Sybase.XXX symbols be embedded ? > The biggest problem here is the serialization of those values to string and de-serializing them when sqlalchemy sets them to python-sybase. It could be ok if we would know the type of the property but as far as I see it cannot be introspected from the sybase library. We could have an algorithm serializing/deserializing the value: - if it's starting with "CS_", we use it as the name of the variable in the Sybase module - if we can convert it to an integer we use it as an integer - otherwise we use it as string specified Based on the above, my kerberos connection url would be the following (missing username+pw in this case): sybase+pysybase://hostname/?CS_SEC_NETWORKAUTH=CS_TRUE&CS_SEC_SERVERPRINCIPAL=sybase/some_host It's just an idea, I don't know how it could fit into the design of sqlalchemy. Zsolt -- NOTICE: If received in error, please destroy, and notify sender. Sender does not intend to waive confidentiality or privilege. Use of this email is prohibited when received in error. We may monitor and store emails to the extent permitted by applicable law. -- 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] Harder problem
Ok, two queries: rsvp = session.query(Project.project, func.count(Reservation.project_id)).join(Reservation.project).group_by(Project.project, Project.id).order_by(Project.project).all() h = session.query(Project.project, func.count(Host.id)).join(Project.hosts).group_by(Project.project).all() The first one counts reservations per project, the second one hosts per project. Is it possible to do this in one query? Joins are no help as they produce outlandish numbers: >>> h = session.query(Project.project, func.count(Host.id), func.count(Reservation.project_id)).join(Project.hosts).join(Project.reservations).group_by(Project.project) >>> print h SELECT project.project AS project_project, count(hosts.id) AS count_1, count(reservation.project_id) AS count_2 FROM project JOIN hosts ON project.id = hosts.project_id JOIN reservation ON project.id = reservation.project_id GROUP BY project.project >>> h.all() [(u'DMS_OTIS', 54L, 54L), (u'CLOUDBURST', 8L, 8L), (u'CIS', 12L, 12L), (u'TESTPROJ', 4L, 4L), (u'ICAD', 118L, 118L), (u'DI', 8250L, 8250L), (u'ITPA', 2544L, 2544L), (u'LMT', 886030L, 886030L), (u'RXA', 160L, 160L)] This is probably bc hosts.id is counted multiple times (per reservation)? The obvious answer would be to use subquery for Project, but how to do it? -- Regards, mk -- Premature optimization is the root of all fun. -- 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] good IDE for SQLAlchemy and wxPython?
werner wrote: You should probably also look at some of the tools out there, e.g. - wxPythonPit lists a lot - http://wiki.wxpython.org/wxPythonPit%20Apps - WindIde - http://www.wingware.com/ - Ulipad - http://code.google.com/p/ulipad/ - Editra - http://editra.org/ Don't count on getting too much from them, though: I'm using Wing IDE Pro which is arguably the best of them and it still cannot figure out the type and subsequently internals of sqla objects. Regards, mk -- Premature optimization is the root of all fun. -- 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 with func.count
Hello, I have this query: rsvp = session.query(Project.project, func.count(Reservation.project_id)).join(Reservation.project).group_by(Project.project, Project.id).order_by(Project.project) >>> print rsvp SELECT project.project AS project_project, count(reservation.project_id) AS count_1 FROM reservation JOIN project ON project.id = reservation.project_id GROUP BY project.project, project.id ORDER BY project.project So far so good - but what if I want to order by column "func.count(Reservation.project_id)"? I can do this in SQL all right: SELECT project.project AS project_project, count(reservation.project_id) AS count_1 FROM reservation JOIN project ON project.id = reservation.project_id GROUP BY project.project, project.id ORDER BY count_1 DESC But how to do this in above sqla query? -- Regards, mk -- Premature optimization is the root of all fun. -- 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] how do i take advantage of sqlite manifest typing / type affinity using sqlalchemy?
Not sure what the etiquette is re cross-posting to this list from stackoverflow? here's my question: http://stackoverflow.com/questions/3044518/how-do-i-take-advantage-of-sqlite-manifest-typing-type-affinity-using-sqlalchem I like the idea of sqlite's manifest typing / type affinity: http://www.sqlite.org/datatype3.html Essentially, if I set a column's affinity as 'numeric', it will duck type integers or floats to store them as such, but still allow me to store strings if I want to. Seems to me this is the best 'default' type for a column when i'm not sure ahead of time of what data i want to store in it. so off i go: metadata = MetaData() new_table = Table(table_name, metadata ) for col_name in column_headings: new_table.append_column(Column(col_name, sqlite.NUMERIC, #this should duck-type numbers but can handle strings as well primary_key=col_name in primary_key_columns)) new_table.create(self.engine, checkfirst=False) but when i try and store some string values, eg "abc" in the table, sqlalchemy falls over: File "[...]\sqlalchemy\processors.py", line 79, in to_float return float(value) ValueError: invalid literal for float(): abc Boo, hiss. So, is there any way I can convince sqlalchemy to let sqlite do the typing? perhaps i can use a type from sqlalchemy.types instead of sqlachemy.dialects.sqlite? since the so post, i've done a little more digging. Am I going to have to write my own custom type as per http://www.sqlalchemy.org/docs/reference/sqlalchemy/types.html#custom-types? hp -- -- Harry J.W. Percival -- Italy Mobile: +39 389 095 8959 UK Mobile: +44 (0) 78877 02511 (may be turned off) Skype: harry.percival Email: harry.perci...@gmail.com -- 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] Re: Unexpected SessionTransaction behaviour
On Jun 14, 11:19 pm, Michael Bayer wrote:
> the new SessionTransaction that occurs in close() does not request any
> connection resources, and is discarded immediately along with the session
> that is the subject of remove(). I don't see how it could be affected by
> any previous requests. Each request should have a totally new Session
> object, with a totally new SessionTransaction object. The old one is gone as
> soon as remove() completes.
Here's my example short test case:
import gc
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker, scoped_session
from sqlalchemy.orm.session import SessionTransaction
from sqlalchemy.pool import NullPool
engine = create_engine('sqlite://', poolclass=NullPool)
sessions = []
# To simulate multi-db setup, create two sessions
for i in range(2):
sessions.append(scoped_session(sessionmaker(bind=engine)))
for i in range(1, 3):
print 'Looping: iteration (request) #%d' % i
tolist = [c for c in gc.get_objects()
if isinstance(c, SessionTransaction)]
assert len(tolist) == 0
# Apparently scoped_sessions can be used without
# instantiating, so do that
# Just one of the databases will be used in the
# processing
s = sessions[0]
s.execute('select 1')
tolist = [c for c in gc.get_objects()
if isinstance(c, SessionTransaction)]
assert len(tolist) == 1
for s in sessions:
#s.commit() # Not needed, and makes no difference
s.remove()
# There should be no SessionTransactions remaining
tolist = [c for c in gc.get_objects()
if isinstance(c, SessionTransaction)]
# On my system, the next line raises AssertionError
assert len(tolist) == 0, 'No SessionTransactions hanging around'
output:
Looping: iteration (request) #1
Traceback (most recent call last):
File "sqlatest.py", line 35, in
assert len(tolist) == 0, 'No SessionTransactions hanging around'
AssertionError: No SessionTransactions hanging around
BTW I'm using SQLA trunk on Ubuntu Karmic.
Regards,
Vinay Sajip
--
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.
