[sqlalchemy] Storing a two dimensional list structure with different item types in Postgres 9.1
Using Postgres 9.1, SqlAlchemy 0.8. I need to store information like [ [10, 'liters', 'tea'], [20, 'milliliters', 'salt'], ] as one column into Postgres. What is the best way to do this? Of course I use a second table with a 1:N relationship I am thinking about using the Postgres Array type for this...is there a better option? Right now there are numeric and string values to be stored. I can of course convert numeric values to strings if needed. Any options? Andreas -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/9jnn5iZY68IJ. 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] Storing a two dimensional list structure with different item types in Postgres 9.1
If you are only after storage (leaving aside querying, indexing, ...) a simple JSON column [1] could do. The advantage being portability between databases. I've used this to as simple key-value store of data associated with a row. regards robert [1] http://docs.sqlalchemy.org/en/rel_0_8/core/types.html#marshal-json-strings On Thu, Dec 20, 2012 at 11:20 AM, Andreas Jung zopyxfil...@gmail.com wrote: Using Postgres 9.1, SqlAlchemy 0.8. I need to store information like [ [10, 'liters', 'tea'], [20, 'milliliters', 'salt'], ] as one column into Postgres. What is the best way to do this? Of course I use a second table with a 1:N relationship I am thinking about using the Postgres Array type for this...is there a better option? Right now there are numeric and string values to be stored. I can of course convert numeric values to strings if needed. Any options? Andreas -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/9jnn5iZY68IJ. 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: exception message encoded in utf8
Sorry to ressurrect this thread but the problem is still there. Since
SQLAlchemy knows the encoding used to communicate with the database, it can
properly decode the error strings returned by the database to unicode. So,
I think it should be SQLAlchemy's responsibility to convert the error
strings to unicode, not the user's responsibility. Could we open a bug for
that in the tracker?
Sylvain
Le vendredi 7 mars 2008 02:39:56 UTC+1, jean-philippe dutreve a écrit :
On 7 mar, 02:29, Michael Bayer mike...@zzzcomputing.com wrote:
logging module itself throws UnicodeDecodeError ?
yes, in logging.format: ... = %s % msg
with msg the exception message encoded in utf8 and the default
encoding is ascii.
are you sending exception messages using logging.debug() or similar ?
exactly: log.error(... : %s, e.message)
my impression
is that you'd want to decode those manually doing something like
string.decode('utf-8').
yes, it works fine, but it's pain to do this in each try/except.
Another solution is setting utf8 as the default encoding in
sitecustomize.py.
It's better centralized, but has sitepackage effect.
But a better way IMHO is that the DB driver or SA returns unicode
exception message.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To view this discussion on the web visit
https://groups.google.com/d/msg/sqlalchemy/-/OOEvbKoo63cJ.
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: exception message encoded in utf8
OK as I said earlier, I'm not able to reproduce this.So I'd need that
reproduction case in order to do anything. To be honest it sounds more like a
psycopg2 bug, since psycopg2 does the decoding in most cases nowadays and even
works with Python 3, so for it to be raising an exception with the bytes type
for the message is certainly a bug. But would need to see a real world
example to get a feel for it.
On Dec 20, 2012, at 7:03 AM, Sylvain Prat wrote:
Sorry to ressurrect this thread but the problem is still there. Since
SQLAlchemy knows the encoding used to communicate with the database, it can
properly decode the error strings returned by the database to unicode. So, I
think it should be SQLAlchemy's responsibility to convert the error strings
to unicode, not the user's responsibility. Could we open a bug for that in
the tracker?
Sylvain
Le vendredi 7 mars 2008 02:39:56 UTC+1, jean-philippe dutreve a écrit :
On 7 mar, 02:29, Michael Bayer mike...@zzzcomputing.com wrote:
logging module itself throws UnicodeDecodeError ?
yes, in logging.format: ... = %s % msg
with msg the exception message encoded in utf8 and the default
encoding is ascii.
are you sending exception messages using logging.debug() or similar ?
exactly: log.error(... : %s, e.message)
my impression
is that you'd want to decode those manually doing something like
string.decode('utf-8').
yes, it works fine, but it's pain to do this in each try/except.
Another solution is setting utf8 as the default encoding in
sitecustomize.py.
It's better centralized, but has sitepackage effect.
But a better way IMHO is that the DB driver or SA returns unicode
exception message.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To view this discussion on the web visit
https://groups.google.com/d/msg/sqlalchemy/-/OOEvbKoo63cJ.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] How to SELECT FOR SHARE in PostgreSQL dialect?
I am trying SELECT FOR SHARE a set of rows in a table so that they are locked until the end of the transaction. I am using SQLAlchemy 0.7.9 to do this in a PostgreSQL 9.1.6 database. This is the python code in question: NUM_TERMS = 10 conn = engine.connect() get_terms = select([search_terms.c.term_id, search_terms.c.term], and_(search_terms.c.lock==False, search_terms.c.status==False), order_by=search_terms.c.term, limit=NUM_TERMS, for_update=read) trans = conn.begin()try: search_terms = conn.execute(get_terms).fetchall() for term in search_terms: lock_terms = update(search_terms).\ where(search_terms.c.term_id==term.term_id).\ values(lock=True) conn.execute(lock_terms) if trans.commit(): do things with the search termsexcept: trans.rollback() The problem is the SQL query generated by the select code above is not FOR SHARE, it's FOR UPDATE: SELECT search_terms.term_id, search_terms.term FROM search_terms WHERE search_terms.lock = :lock_1 AND search_terms.status = :status_1 ORDER BY search_terms.term LIMIT :param_1 FOR UPDATE According to the SQLAlchemy API docshttp://docs.sqlalchemy.org/en/rel_0_7/core/expression_api.html#sqlalchemy.sql.expression.select, under the for_update parameter description: With the Postgresql dialect, the values “read” and read_nowait translate to FOR SHARE and FOR SHARE NOWAIT, respectively. According to the above, the compiled SQL statement should be FOR SHARE, but it is not. Where is the error in my code? If you have a good answer, please also answer it on stackoverflowhttp://stackoverflow.com/questions/13983048/how-to-select-for-share-using-sqlalchemy-with-postgresql -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/wmFM_7UILRoJ. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] How to SELECT FOR SHARE in PostgreSQL dialect?
can't reproduce:
from sqlalchemy import *
m = MetaData()
t = Table('t', m, Column('x', Integer))
s = select([t], for_update=read)
from sqlalchemy.dialects import postgresql
print s.compile(dialect=postgresql.dialect())
e = create_engine(postgresql://scott:tiger@localhost/test, echo=True)
with e.begin() as conn:
m.create_all(conn)
conn.execute(s)
the output shows that both as a standalone compile as well as within a
Postgresql conversation, we get FOR SHARE. Tested in 0.7.9 and 0.8.0b2. If
you can provide a full runnable test case, that might shed more light.
SELECT t.x
FROM t FOR SHARE
2012-12-20 23:53:33,670 INFO sqlalchemy.engine.base.Engine select version()
2012-12-20 23:53:33,671 INFO sqlalchemy.engine.base.Engine {}
2012-12-20 23:53:33,672 INFO sqlalchemy.engine.base.Engine select
current_schema()
2012-12-20 23:53:33,672 INFO sqlalchemy.engine.base.Engine {}
2012-12-20 23:53:33,674 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2012-12-20 23:53:33,674 INFO sqlalchemy.engine.base.Engine select relname from
pg_class c join pg_namespace n on n.oid=c.relnamespace where
n.nspname=current_schema() and relname=%(name)s
2012-12-20 23:53:33,675 INFO sqlalchemy.engine.base.Engine {'name': u't'}
2012-12-20 23:53:33,676 INFO sqlalchemy.engine.base.Engine SELECT t.x
FROM t FOR SHARE
2012-12-20 23:53:33,676 INFO sqlalchemy.engine.base.Engine {}
2012-12-20 23:53:33,676 INFO sqlalchemy.engine.base.Engine COMMIT
On Dec 20, 2012, at 9:01 PM, utkonos wrote:
I am trying SELECT FOR SHARE a set of rows in a table so that they are locked
until the end of the transaction. I am using SQLAlchemy 0.7.9 to do this in a
PostgreSQL 9.1.6 database. This is the python code in question:
NUM_TERMS = 10
conn = engine.connect()
get_terms = select([search_terms.c.term_id, search_terms.c.term],
and_(search_terms.c.lock==False,
search_terms.c.status==False),
order_by=search_terms.c.term,
limit=NUM_TERMS, for_update=read)
trans = conn.begin()
try:
search_terms = conn.execute(get_terms).fetchall()
for term in search_terms:
lock_terms = update(search_terms).\
where(search_terms.c.term_id==term.term_id).\
values(lock=True)
conn.execute(lock_terms)
if trans.commit():
do things with the search terms
except:
trans.rollback()
The problem is the SQL query generated by the select code above is not FOR
SHARE, it's FOR UPDATE:
SELECT search_terms.term_id, search_terms.term
FROM search_terms
WHERE search_terms.lock = :lock_1 AND search_terms.status = :status_1
ORDER BY search_terms.term
LIMIT :param_1 FOR UPDATE
According to the SQLAlchemy API docs, under the for_update parameter
description:
With the Postgresql dialect, the values “read” and read_nowait translate to
FOR SHARE and FOR SHARE NOWAIT, respectively.
According to the above, the compiled SQL statement should be FOR SHARE, but
it is not. Where is the error in my code?
If you have a good answer, please also answer it on stackoverflow
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To view this discussion on the web visit
https://groups.google.com/d/msg/sqlalchemy/-/wmFM_7UILRoJ.
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] How to SELECT FOR SHARE in PostgreSQL dialect?
The problem is the SQL query generated by the select code above is not FOR SHARE, it's FOR UPDATE: SELECT search_terms.term_id, search_terms.term FROM search_terms WHERE search_terms.lock = :lock_1 AND search_terms.status = :status_1 ORDER BY search_terms.term LIMIT :param_1 FOR UPDATE particularly suspicious is that the paramstyle above, known as named (i.e. :param), is not the paramstyle used by the current Postgresql DBAPIs - psycopg2 uses %(pyformat)s and pg8000 defaults to format. this suggests the above is just the result of saying print statement. What do you see actually being sent to the database ? -- 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.
