[sqlalchemy] Timezone handling with postgres
Postgres can handle timezones fairly well. Using a direct select you can
see how it handles daylight saving correctly:
test=# select
'2010-01-15 12:30 Europe/Amsterdam'::timestamp with time zone,
'2010-01-15 12:30 Europe/Amsterdam'::timestamp with time zone +
interval '7 months',
'2010-08-15 12:30 Europe/Berlin'::timestamp with time zone;
timestamptz |?column?| timestamptz
++
2010-01-15 12:30:00+01 | 2010-08-15 12:30:00+02 | 2010-08-15 12:30:00+02
(1 row)
When using a table to store a timestamp this still works properly:
tribaspace=# create table test (moment timestamp with time zone);
CREATE TABLE
tribaspace=# insert into test values ('2010-01-15 12:30
Europe/Berlin'::timestamp with time zone);
INSERT 0 1
tribaspace=# select moment + interval '7 months' from test;
?column?
2010-08-15 12:30:00+02
(1 row)
However that extra timezone information is lost when I use SQLAlchemy.
After adding a primary key column I use this bit of python to test the
timezone handling:
import datetime
from sqlalchemy import orm
from sqlalchemy import schema
from sqlalchemy import types
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class Test(Base):
__tablename__ = test
id = schema.Column(types.Integer(), primary_key=True,
autoincrement=True)
moment = schema.Column(types.Time(timezone=True))
engine = create_engine('postgres:///test')
Base.metadata.create_all(engine)
Session = orm.sessionmaker(bind=engine)
session = Session()
row = session.query(Test).first()
print row.moment
print row.moment + datetime.timedelta(days=212)
Which outputs:
2010-01-15 12:30:00+01:00
2010-08-15 12:30:00+01:00
The second timestamp should have +02:00 as timezone due do daylight
saving differences. Unfortuantely the timezone information reported on
the column has a fixed offset instead of the more informative
Europe/Amsterdam time.
I am guessing that this is mostly due to psycopg2 not handling timezones
properly. I am wondering if SQLAlchemy itself will handle this correctly
if psycopg2 would do the right thing, and if other dialects implement
this better?
Wichert.
--
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] problem when executing multiple insert statements and boolean type
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Hi.
I'm here again with a problem I don't know if it is a bug in SA or in my
code.
Here is the offending code:
from sqlalchemy import schema, types, sql, create_engine
metadata = schema.MetaData()
test = schema.Table(
'test', metadata,
schema.Column('x', types.Integer, primary_key=True),
schema.Column('y', types.Boolean, default=True, nullable=False)
)
engine = create_engine('sqlite://')
engine.create(metadata)
try:
params = [ {'x': 1}, {'x': 2, 'y': False} ]
engine.execute(test.insert(), params)
print engine.execute(test.select()).fetchall()
finally:
engine.drop(metadata)
This should print:
[(1, True), (2, False)]
and instead it prints
[(1, True), (2, True)]
Thanks Manlio
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iEYEARECAAYFAkti31QACgkQscQJ24LbaUQSdgCfctrxG3mAH22uWIoVj65EXCKH
bKIAnjPmGw5CvQID6JvW7bHpn5aAgD2j
=m+dS
-END PGP SIGNATURE-
--
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 to diagnose a transaction hang problem?
Today I met a strange problem with SqlAlchemy and Postgresql. The code is like this: def update_user(user_id, sess): user = sess.query(User).get(user_id).one() user.last_activity_time = datetime.now() session.commit() It hangs here forever. In the code above, sess is a scoped session. I don't have any clue of what happened. In most case the code above worked. But suddenly it hangs and any other thread that want to talk to database after that line is hit are also hanged. I have to kill the process. It does not look like that this a problem of database since after I restart my application, it works again. What might cause this kind of problem? -- 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] problem when executing multiple insert statements and boolean type
-Original Message-
From: sqlalchemy@googlegroups.com
[mailto:sqlalch...@googlegroups.com] On Behalf Of Manlio Perillo
Sent: 29 January 2010 13:15
To: sqlalchemy@googlegroups.com
Subject: [sqlalchemy] problem when executing multiple insert
statements and boolean type
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Hi.
I'm here again with a problem I don't know if it is a bug in
SA or in my
code.
Here is the offending code:
from sqlalchemy import schema, types, sql, create_engine
metadata = schema.MetaData()
test = schema.Table(
'test', metadata,
schema.Column('x', types.Integer, primary_key=True),
schema.Column('y', types.Boolean, default=True, nullable=False)
)
engine = create_engine('sqlite://')
engine.create(metadata)
try:
params = [ {'x': 1}, {'x': 2, 'y': False} ]
engine.execute(test.insert(), params)
print engine.execute(test.select()).fetchall()
finally:
engine.drop(metadata)
This should print:
[(1, True), (2, False)]
and instead it prints
[(1, True), (2, True)]
Thanks Manlio
This is explained in the last paragraph of
http://www.sqlalchemy.org/docs/sqlexpression.html#executing-multiple-sta
tements:
When executing multiple sets of parameters, each dictionary must have
the same set of keys; i.e. you cant have fewer keys in some dictionaries
than others. This is because the Insert statement is compiled against
the first dictionary in the list, and it's assumed that all subsequent
argument dictionaries are compatible with that statement.
I think a check has been added in 0.6 so that an exception is raised if
you don't follow this advice.
Hope that helps,
Simon
--
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 to diagnose a transaction hang problem?
On Jan 29, 2010, at 15:01 , 一首诗 wrote: What might cause this kind of problem? Possibly waiting on locks. Do you have any concurrent transactions modifying the same data? When the problem appears, run `select * from pg_stat_activity` to see whether there are locking issues. To see the locks involved, run `select * from pg_locks`. -- Alex Brasetvik -- 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] problem when executing multiple insert statements and boolean type
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
King Simon-NFHD78 ha scritto:
[...]
params = [ {'x': 1}, {'x': 2, 'y': False} ]
engine.execute(test.insert(), params)
print engine.execute(test.select()).fetchall()
[...]
This should print:
[(1, True), (2, False)]
and instead it prints
[(1, True), (2, True)]
[...]
This is explained in the last paragraph of
http://www.sqlalchemy.org/docs/sqlexpression.html#executing-multiple-sta
tements:
When executing multiple sets of parameters, each dictionary must have
the same set of keys; i.e. you cant have fewer keys in some dictionaries
than others. This is because the Insert statement is compiled against
the first dictionary in the list, and it's assumed that all subsequent
argument dictionaries are compatible with that statement.
Ah, thanks.
I think a check has been added in 0.6 so that an exception is raised if
you don't follow this advice.
No, I'm using the version from trunk, and there is no exception or warnings.
Regards Manlio
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iEYEARECAAYFAkti8dUACgkQscQJ24LbaURziQCdH+Co40QqeYv+9YVWXyXay0/+
9zYAn1bpZ7RxjkSjeNULeT4alxokFqYG
=aoIT
-END PGP SIGNATURE-
--
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] MPTT
Can someone please help me to figure out the equivalent of this sql query to sqlalchemy This my nested_category table: +-+--+-+-+ | category_id | name | lft | rgt | +-+--+-+-+ | 1 | ELECTRONICS | 1 | 20 | | 2 | TELEVISIONS | 2 | 9 | | 3 | TUBE | 3 | 4 | | 4 | LCD | 5 | 6 | | 5 | PLASMA | 7 | 8 | | 6 | PORTABLE ELECTRONICS | 10 | 19 | | 7 | MP3 PLAYERS | 11 | 14 | | 8 | FLASH| 12 | 13 | | 9 | CD PLAYERS | 15 | 16 | | 10 | 2 WAY RADIOS | 17 | 18 | +-+--+-+-+ SELECT node.name, (COUNT(node.name)-1) AS level FROM nested_category AS node, nested_category AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt GROUP BY node.name; The result will be: +--+---+ | name | depth | +--+---+ | ELECTRONICS | 0 | | TELEVISIONS | 1 | | TUBE | 2 | | LCD | 2 | | PLASMA | 2 | | PORTABLE ELECTRONICS | 1 | | MP3 PLAYERS | 2 | | FLASH| 3 | | CD PLAYERS | 2 | | 2 WAY RADIOS | 2 | +--+---+ -- 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] Timezone handling with postgres
Wichert Akkerman wrote: Which outputs: 2010-01-15 12:30:00+01:00 2010-08-15 12:30:00+01:00 The second timestamp should have +02:00 as timezone due do daylight saving differences. Unfortuantely the timezone information reported on the column has a fixed offset instead of the more informative Europe/Amsterdam time. I am guessing that this is mostly due to psycopg2 not handling timezones properly. I am wondering if SQLAlchemy itself will handle this correctly if psycopg2 would do the right thing, and if other dialects implement this better? Its true, we don't do anything with the date objects passed to/from psycopg2, so you'd have to ask them about best practices for handling timezone-aware dates. Personally I don't use them, I try to store everything as UTC across the board and deal with timezone conversions only at the point of data collection and display. Wichert. -- 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] problem when executing multiple insert statements and boolean type
Manlio Perillo wrote: -BEGIN PGP SIGNED MESSAGE- Ah, thanks. I think a check has been added in 0.6 so that an exception is raised if you don't follow this advice. No, I'm using the version from trunk, and there is no exception or warnings. the error is raised if a subsequent dictionary is missing keys that are present in the first. we aren't at the moment validating the actual size of each subsequent dictionary which would detect extra keys. Regards Manlio -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAkti8dUACgkQscQJ24LbaURziQCdH+Co40QqeYv+9YVWXyXay0/+ 9zYAn1bpZ7RxjkSjeNULeT4alxokFqYG =aoIT -END PGP SIGNATURE- -- 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] Example project
Hi Daniel, Good question as I like to learn this way myself. I'm also just getting started, but I found the overview in the Pylons book to be helpful and a good intro (by my approximation at least) into to the pattern you're talking about. It might be a bit more basic than you're looking for, but I'll throw it out there if you haven't already taken a look: http://www.pylonsbook.com You can jump straight into Chapter 7: http://pylonsbook.com/en/1.1/introducing-the-model-and-sqlalchemy.html Good luck! Mike Daniel Strasser wrote: Hello list I'm using sqlalchemy quite a while now, but only for informal projects and internal tools. So far, I've stored all models in a file called db.py, from database initialization to methods for changing data. However, I'm planning a bigger project and I'm stuck with the question how to use sqlalchemy in a better way. Actually, I want to split models; meaning that i have i.e. a person model and a car model, which are stored in seperate files. However, I'm missing an example or information how to do this. Does anyone have an overview about a complete project? I'm basically missing the big picture, or best practices: - where to store database initialization and configuration - how to store models in separate files, and how to interact with them Thanks Daniel -- Michael Chambliss em...@mchambliss.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] Querying with polymorphism
Given the following relationships:
employees_table = Table('employees', metadata,
Column('id', Integer, primary_key=True),
Column('row_type', Integer, nullable=False)
Column('name', String(50)),
Column('is_certified', Boolean)
)
employee_mapper = mapper(Employee, employees_table,
polymorphic_on=employees_table.c.row_type,
polymorphic_identity=1,
exclude_properties=['is_certified'])
manager_mapper = mapper(Manager, inherits=employee_mapper,
polymorphic_identity=2,
properties={
'is_certified': employees_table.c.is_certified
})
How can I query for employees who aren't managers or managers who are
certified without referring to the polymorphic identity? Basically,
without doing this:
session.query(Employee).filter(or_(Employee.row_type!=2,
Manager.is_certified==True))
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] Querying with polymorphism
Yoann Roman wrote:
Given the following relationships:
employees_table = Table('employees', metadata,
Column('id', Integer, primary_key=True),
Column('row_type', Integer, nullable=False)
Column('name', String(50)),
Column('is_certified', Boolean)
)
employee_mapper = mapper(Employee, employees_table,
polymorphic_on=employees_table.c.row_type,
polymorphic_identity=1,
exclude_properties=['is_certified'])
manager_mapper = mapper(Manager, inherits=employee_mapper,
polymorphic_identity=2,
properties={
'is_certified': employees_table.c.is_certified
})
How can I query for employees who aren't managers or managers who are
certified without referring to the polymorphic identity? Basically,
without doing this:
session.query(Employee).filter(or_(Employee.row_type!=2,
Manager.is_certified==True))
the ultimate SQL must include the row_type column, or perhaps you could
detect if is_certified is NULL to detect a non-manager row. There's no
way to get around having to tell your query check the type of the object
somehow.
However, if you're just disturbed about the actual row_type column and
the hardcoding of 2, you can make yourself an operator pretty easily
here. A simple one is like:
def isinstance_(cls_):
mapper = class_mapper(cls_)
return mapper.polymorphic_on == mapper.polymorphic_identity
a more comprehensive one that takes into account further subclasses:
def isinstance_(cls_):
mapper = class_mapper(cls_)
return mapper.polymorphic_on.in_(
[m.polymorphic_identity
for m in mapper.polymorphic_iterator()]
)
a query like:
sess.query(Employee).filter(~isinstance_(Manager))
would render:
SELECT *
FROM employees
WHERE employees.row_type NOT IN (...)
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.
--
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] OperationalError: (OperationalError) no such table:
I’ve been stuck trying to get the pylons application to connect to my database. I was able to connect to the database through a python shell in the “virtualenv” as you can see below. The app acts like it can connect to the database, but not to the table. I admit this is my first pylons project and I'm a little confused as to where to start looking for a problem. There seems to be a lot of outdated doc's on the web and I don't know what to believe is the current way of doing things. import sqlalchemy as sa engine = sa.create_engine(login-info) from pwi import model model.init_model(engine) engine.has_table(pwi_wildcard) True OperationalError: (OperationalError) no such table: pwi_wildcard u'SELECT pwi_wildcard.wildcard_id AS pwi_wildcard_wildcard_id, pwi_wildcard.priority AS pwi_wildcard_priority, pwi_wildcard.name AS pwi_wildcard_name, pwi_wildcard.shot AS pwi_wildcard_shot, pwi_wildcard.scene AS pwi_wildcard_scene, pwi_wildcard.created_by AS pwi_wildcard_created_by, pwi_wildcard.expires AS pwi_wildcard_expires \nFROM pwi_wildcard' [] thanks in advance, James -- 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: OperationalError: (OperationalError) no such table:
Hi James, it would be helpful if you posted the call stack where that error occurs, and the code leading up to the failed query execution, which database and driver (the first word in the engine URL). Without further details, the first place I would look is where the session (or connection) gets created. Are you sure it is getting the same engine URL that you used in the python shell code (which you replaced with login-info before posting)? - Gulli On Jan 29, 9:00 pm, James Sathre jamessat...@gmail.com wrote: I’ve been stuck trying to get the pylons application to connect to my database. I was able to connect to the database through a python shell in the “virtualenv” as you can see below. The app acts like it can connect to the database, but not to the table. I admit this is my first pylons project and I'm a little confused as to where to start looking for a problem. There seems to be a lot of outdated doc's on the web and I don't know what to believe is the current way of doing things. import sqlalchemy as sa engine = sa.create_engine(login-info) from pwi import model model.init_model(engine) engine.has_table(pwi_wildcard) True OperationalError: (OperationalError) no such table: pwi_wildcard u'SELECT pwi_wildcard.wildcard_id AS pwi_wildcard_wildcard_id, pwi_wildcard.priority AS pwi_wildcard_priority, pwi_wildcard.name AS pwi_wildcard_name, pwi_wildcard.shot AS pwi_wildcard_shot, pwi_wildcard.scene AS pwi_wildcard_scene, pwi_wildcard.created_by AS pwi_wildcard_created_by, pwi_wildcard.expires AS pwi_wildcard_expires \nFROM pwi_wildcard' [] thanks in advance, James -- 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] Working with temp tables on Postgres
Greetings, Alchemists,
what's the best way to work with temp tables on Postgres?
It's fairly easy to have one created:
tmp_foo = Table('tmp_foo',
metadata,
Column('id', Integer, unique=True),
Column('bar', Integer),
prefixes=['TEMPORARY'])
tmp_foo.create()
The problem is that if I am not sure that the table was created, I
can't use it. The following:
tmp_foo.create(checkfirst=True)
does not work. It issues the following SQL that won't find a match
for temp tables:
select relname from pg_class c
join pg_namespace n on n.oid=c.relnamespace
where n.nspname=current_schema() and lower(relname)=%(name)s
One work around would be to use ON COMMIT DROP but I don't now how
to do that since Table() has no `suffixes` parameter.
Any help on that one?
While I'm at it, I might as well state the high level problem that
pushed me to use temp tables. I'm using Xapian to do full text
indexing. Xapian is good to give me a list of document ids that I can
then retrieve from the database but if I want to apply additional
criteria, I have to do the filtering on the database side. On way to
do that is with a huge IN clause, the other is with a temp table. I
like the temp table because I can also use it to order by Xapian
ranking and do the paging on the alchemy side. I could also duplicate
all the criteria on the Xapian side but I want to avoid that if
possible.
Any suggestions for either problems?
--
Yannick Gingras
http://ygingras.net
http://confoo.ca -- track coordinator
http://montrealpython.org -- lead organizer
signature.asc
Description: This is a digitally signed message part.
[sqlalchemy] Re: MPTT
Hi Juan,
this will do it in version 10.5.8 (and probably earlier:
nested_category = Table(
'nested_category',
MetaData(),
Column('category_id', Integer, primary_key=True),
Column('name', Text, nullable=False),
Column('lft', Integer, nullable=False),
Column('rgt', Integer, nullable=False)
)
node = nested_category.alias('node')
parent = nested_category.alias('parent')
query = select([node.c.name, (func.count(node.c.name) - text
('1')).label('level')],
from_obj=join(node, parent,
node.c.lft.between(parent.c.lft, parent.c.rgt)
)
).group_by(node.c.name)
str(query) will show that it is correct (it uses a JOIN expression
instead of the WHERE condition, but that's equivalent and more
explicit)
The text('1') instead of just 1 is so that the literal constant 1 is
not needlessly replaced by a bind param. It works either way though.
Regards,
- Gulli
On Jan 29, 8:53 am, Juan Dela Cruz juandelacru...@gmail.com wrote:
Can someone please help me to figure out the equivalent of this sql query to
sqlalchemy
This my nested_category table:
+-+--+-+-+
| category_id | name | lft | rgt |
+-+--+-+-+
| 1 | ELECTRONICS | 1 | 20 |
| 2 | TELEVISIONS | 2 | 9 |
| 3 | TUBE | 3 | 4 |
| 4 | LCD | 5 | 6 |
| 5 | PLASMA | 7 | 8 |
| 6 | PORTABLE ELECTRONICS | 10 | 19 |
| 7 | MP3 PLAYERS | 11 | 14 |
| 8 | FLASH | 12 | 13 |
| 9 | CD PLAYERS | 15 | 16 |
| 10 | 2 WAY RADIOS | 17 | 18 |
+-+--+-+-+
SELECT node.name, (COUNT(node.name)-1) AS level
FROM nested_category AS node, nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name;
The result will be:
+--+---+
| name | depth |
+--+---+
| ELECTRONICS | 0 |
| TELEVISIONS | 1 |
| TUBE | 2 |
| LCD | 2 |
| PLASMA | 2 |
| PORTABLE ELECTRONICS | 1 |
| MP3 PLAYERS | 2 |
| FLASH | 3 |
| CD PLAYERS | 2 |
| 2 WAY RADIOS | 2 |
+--+---+
--
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] Working with temp tables on Postgres
On Jan 29, 2010, at 7:27 PM, Yannick Gingras wrote:
Greetings, Alchemists,
what's the best way to work with temp tables on Postgres?
It's fairly easy to have one created:
tmp_foo = Table('tmp_foo',
metadata,
Column('id', Integer, unique=True),
Column('bar', Integer),
prefixes=['TEMPORARY'])
tmp_foo.create()
The problem is that if I am not sure that the table was created, I
can't use it. The following:
tmp_foo.create(checkfirst=True)
does not work. It issues the following SQL that won't find a match
for temp tables:
select relname from pg_class c
join pg_namespace n on n.oid=c.relnamespace
where n.nspname=current_schema() and lower(relname)=%(name)s
One work around would be to use ON COMMIT DROP but I don't now how
to do that since Table() has no `suffixes` parameter.
from sqlalchemy.schema import CreateTable
from sqlalchemy.ext.compiler import compiles
@compiles(CreateTable)
def check_temporary(create, compiler, **kw):
table = create.element
ret = compiler.visit_create_table(create)
if 'TEMPORARY' in table._prefixes:
ret += ON COMMIT DROP
return ret
While I'm at it, I might as well state the high level problem that
pushed me to use temp tables. I'm using Xapian to do full text
indexing. Xapian is good to give me a list of document ids that I can
then retrieve from the database but if I want to apply additional
criteria, I have to do the filtering on the database side. On way to
do that is with a huge IN clause, the other is with a temp table. I
like the temp table because I can also use it to order by Xapian
ranking and do the paging on the alchemy side. I could also duplicate
all the criteria on the Xapian side but I want to avoid that if
possible.
I usually go with the IN clause but I wonder if its possible to write PG stored
procedures that can get to xapian as well (since you can write them in python
or any other language).
--
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: How to diagnose a transaction hang problem?
Another quick way of troubleshooting hangs is the tool pg_top, in which you might see a process in the state “Idle in transaction”. This state means that some database operations have been performed in a transaction on that connection but the transaction has not yet been committed. Those database operations will have been granted locks, for which your stalled session is waiting. Behind this idle-in-transaction connection might be another SQLAlchemy session that you neglected to commit or close. That's a common way for this situation to come up. You can see the locks held by the connection using pg_top (hit L), or you can find them with pg_locks as Alex mentioned. These locks may give you a clue as to where in your code that other session was created, helping you track down the bug to correct. To avoid creating cases like this, I try to be careful about session objects: I never store them (keep them on the stack, i.e. as local variables and function arguments), and I always create and close them using a construct like this: from contextlib import closing with closing(Session()) as session: do_stuff() session.commit() if I want to Note that sessions are not the same as DB connections (which are pooled further down in the layers of stuff going on), you gain nothing by storing and reusing them, and you risk creating cases like this. Per the docs, “Sessions are very inexpensive to make, and don’t use any resources whatsoever until they are first used...so create some!” (and close and discard them happily). - G. On Jan 29, 2:13 pm, Alex Brasetvik a...@brasetvik.com wrote: On Jan 29, 2010, at 15:01 , 一首诗 wrote: What might cause this kind of problem? Possibly waiting on locks. Do you have any concurrent transactions modifying the same data? When the problem appears, run `select * from pg_stat_activity` to see whether there are locking issues. To see the locks involved, run `select * from pg_locks`. -- Alex Brasetvik -- 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] Working with temp tables on Postgres
On January 29, 2010, Michael Bayer wrote: One work around would be to use ON COMMIT DROP but I don't now how to do that since Table() has no `suffixes` parameter. from sqlalchemy.schema import CreateTable from sqlalchemy.ext.compiler import compiles @compiles(CreateTable) def check_temporary(create, compiler, **kw): table = create.element ret = compiler.visit_create_table(create) if 'TEMPORARY' in table._prefixes: ret += ON COMMIT DROP return ret Very nice, thanks! On way to do that is with a huge IN clause, the other is with a temp table. I like the temp table because I can also use it to order by Xapian ranking and do the paging on the alchemy side. I usually go with the IN clause but I wonder if its possible to write PG stored procedures that can get to xapian as well (since you can write them in python or any other language). I would not be too hard to make it run on in PL/Python but PL/Python is a non-safe language so it's a bit of a pain to have new versions of the proc deployed since you need to admin in PG to update it. This is why I'd rather go with a solution on the client side. When you go with the big IN, you sort by full text ranking on the Python side? This forces you to fetch the full result set to have the desired page. My experience is that the IN solution get unbearably slow quite fast. With 4+ results from Xapian, it can take several seconds to get my results with IN. I doubt that any sane human will go through all those 40k results so it's probably safe to only send the first fer thousands full text ids to the database but our requirements call for an accurate page count. The more I think about it, the more it looks like the stored proc in PL/Python is the only same way to do it. Thank again for all the infos. -- Yannick Gingras http://ygingras.net http://confoo.ca -- track coordinator http://montrealpython.org -- lead organizer signature.asc Description: This is a digitally signed message part.
Re: [sqlalchemy] Working with temp tables on Postgres
On Jan 29, 2010, at 10:52 PM, Yannick Gingras wrote: I usually go with the IN clause but I wonder if its possible to write PG stored procedures that can get to xapian as well (since you can write them in python or any other language). I would not be too hard to make it run on in PL/Python but PL/Python is a non-safe language so it's a bit of a pain to have new versions of the proc deployed since you need to admin in PG to update it. This is why I'd rather go with a solution on the client side. When you go with the big IN, you sort by full text ranking on the Python side? This forces you to fetch the full result set to have the desired page. My experience is that the IN solution get unbearably slow quite fast. With 4+ results from Xapian, it can take several seconds to get my results with IN. I doubt that any sane human will go through all those 40k results so it's probably safe to only send the first fer thousands full text ids to the database but our requirements call for an accurate page count. the IN is only used to add extra information for display purposes, so is typically on a pageful at at time. anything you're filtering or sorting on needs to be indexed on the search engine side. The more I think about it, the more it looks like the stored proc in PL/Python is the only same way to do it. Thank again for all the infos. -- Yannick Gingras http://ygingras.net http://confoo.ca -- track coordinator http://montrealpython.org -- lead organizer -- 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: How to diagnose a transaction hang problem?
Yeah， there might be another transaction modifying the same data (actually the same line of data in database). But I didn't expect that might cause problem before! Oh, if that's true, then I have to add some lock in my code to avoid that. That's a big problem. On Jan 29, 10:13 pm, Alex Brasetvik a...@brasetvik.com wrote: On Jan 29, 2010, at 15:01 , 一首诗 wrote: What might cause this kind of problem? Possibly waiting on locks. Do you have any concurrent transactions modifying the same data? When the problem appears, run `select * from pg_stat_activity` to see whether there are locking issues. To see the locks involved, run `select * from pg_locks`. -- Alex Brasetvik -- 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.
