[sqlalchemy] Composite primary key
Hi,
I have to load a table from 2 different data-sources witch each having
3 different primary keys. This is because those 2 datasources are
already exported from 3 databases from an archaic application.
From those 2 datasources I created - after normalising - 3 new tables.
The main table keeps the 3 primary keys as one composite primary key.
The two other tables have those 3 fields as foreign keys. Now, if I map
those table definitions onto a table with relations, sqlalchemy
complains with the following error:
sqlalchemy.exceptions.ArgumentError: Error determining primary and/or
secondary join for relationship 'bovines' between mappers
'Mapper|Enterprise|entreprises' and 'Mapper|Bovine|bovines'. You
should specify the 'primaryjoin' (and 'secondaryjoin', if there is an
association table present) keyword arguments to the relation() function
(or for backrefs, by specifying the backref using the backref()
function with keyword arguments) to explicitly specify the join
conditions. Nested error is Cant determine join between 'entreprises'
and 'bovines'; tables have more than one foreign key constraint
relationship between them. Please specify the 'onclause' of this join
explicitly.
Ok, so I have to specify the onclause. But how do I do that? For
reference, here is the (non-working) data definition:
t_bovines = Table( 'bovines', metadata,
Column('id', Integer, primary_key=True),
Column('entrydate', Integer),
Column('key1', Integer, ForeignKey('entreprises.key1')),
Column('key2', Integer, ForeignKey('entreprises.key2')),
Column('key3', String, ForeignKey('entreprises.key3')),
Column('var', Integer),
Column('val', Integer),
)
t_entreprises = Table( 'entreprises', metadata,
Column('key1', Integer, primary_key=True),
Column('key2', Integer, primary_key=True, default=0),
Column('key3', String, primary_key=True),
Column('lname', Unicode(30)),
Column('fname', Unicode(30)),
Column('street', Unicode(30)),
Column('country', String(1)),
Column('zip', String(5)),
Column('locality', Unicode(30)),
)
t_surfaces = Table( 'surfaces', metadata,
Column('id', Integer, primary_key=True),
Column('entrydate', Integer),
Column('key1', Integer, ForeignKey('entreprises.key1')),
Column('key2', Integer, ForeignKey('entreprises.key2')),
Column('key3', String, ForeignKey('entreprises.key3')),
Column('var', Integer),
Column('val', Integer),
)
metadata.create_all()
class Bovine(object):
pass
class Surface(object):
pass
class Enterprise(object):
def __repr__(self):
return [Entreprise %s %s %s] % (self.key1, self.key2,
self.key3)
usermapper = mapper(Bovine, t_bovines)
usermapper = mapper(Surface, t_surfaces)
usermapper = mapper(Enterprise, t_entreprises, properties={
'bovines': relation(Bovine),
'surfaces': relation(Surface)
})
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Accessing attributes in many to many relations
Hi,
I'd like to know how to implicitly access attributes in many to many
relations. Let's assume the following existing tables:
person_table=Table('person',metadata,
Column('personid',Integer,primary_key=True),
Column('name',String(50))
address_table=Table('address',metadata,
Column('addressid',Integer,primary_key=True),
Column('street',String(50))
r_pers_addr_table=Table('r_pers_addr',metadata,
Column('r_pers_addrid',Integer,primary_key=True),
# Relation attribute that denotes if an address is the primary location
of a person
Column('prim_addr',String(1), default='N'),
Column('personid',Integer,ForeignKey('person.personid'),
Column('adresseid',Integer,ForeignKey('adresse.adresseid'))
Let's assume that the above scenario is not very optimal, as this many
to many relation can be replaced by a one to many relation, where
prim_addr and personid is stuffed into the address_table.
However, currently I have to deal with this data model but in the
future, it will be migrated - of course this migration should not
result in an extensive code-review.
So, what I'd like to have is an object person and address
(instances of classes Person and Address, where I can access/store
attributes like this:
person.name='abc'
person.address[0].street='xyz'
person.address[0].prim_addr='N'
But how would I create a mapper in this case? I could do something like
this:
---
mapper(Person, person_table, properties = dict(
address = relation(Address, secondary=r_pers_addrid_table)))
---
This would allow access like person.address[0].street but not
person.address[0].prim_addr.
The following mappers allows full access:
---
mapper(Person, person_table, properties={
'r_pers_addr':relation(R_pers_addr)
}
)
mapper(R_pers_addr, r_pers_addr_table,
properties={
'address' : relation(Address)})
---
But now I can not directly address the street, e.g. I have to do:
person.r_pers_addr[0].address.street
But I can access the relation attribute:
person.r_pers_addr[0].prim_addr
What I could do is create specific properties in my class Person for
street and prim_addr but that is quite complicated, moreover I have
to adapt my class in case of a data structure migration. Maybe I could
also do the following:
---
mapper(Person, person_table, properties = dict(
address = relation(Address, secondary=r_pers_addrid_table))
mapper(Address, address_table, properties = dict(
r_pers_addr=relation(R_pers_addr))
---
So I could do: person.address[0].street and
person.address[0].r_pers_addr.prim_addr - but once again I cannot do a
person.address[0].prim_addr
My current data model which I have to access, is full of such relation
attributes, moreover this scenario is probably quite common, so
perhaps there's a good solution?
Best Regards,
Hermann
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Composite primary key
Well, you could specify the primaryjoin as described here:
http://www.sqlalchemy.org/docs/adv_datamapping.myt#advdatamapping_properties_customjoin
but I suspect that your existing mapper will Just Work if you switch
to a composite FK, rather than 3 keys on individual columns
t_bovines = Table( 'bovines', metadata,
Column('id', Integer, primary_key=True),
Column('entrydate', Integer),
Column('key1', Integer),
Column('key2', Integer),
Column('key3', String),
Column('var', Integer),
Column('val', Integer),
ForeignKeyConstraint(['key1', 'key2', 'key3'],
['enterprise.key1', 'enterprise.key2', 'enterprise.key3'])
)
t_entreprises = Table( 'entreprises', metadata,
Column('key1', Integer),
Column('key2', Integer),
Column('key3', String),
Column('lname', Unicode(30)),
Column('fname', Unicode(30)),
Column('street', Unicode(30)),
Column('country', String(1)),
Column('zip', String(5)),
Column('locality', Unicode(30)),
PrimaryKeyConstraint('key1', 'key2', 'key3')
)
# similarly adjust surfaces
On 1/9/07, exhuma.twn [EMAIL PROTECTED] wrote:
Hi,
I have to load a table from 2 different data-sources witch each having
3 different primary keys. This is because those 2 datasources are
already exported from 3 databases from an archaic application.
From those 2 datasources I created - after normalising - 3 new tables.
The main table keeps the 3 primary keys as one composite primary key.
The two other tables have those 3 fields as foreign keys. Now, if I map
those table definitions onto a table with relations, sqlalchemy
complains with the following error:
sqlalchemy.exceptions.ArgumentError: Error determining primary and/or
secondary join for relationship 'bovines' between mappers
'Mapper|Enterprise|entreprises' and 'Mapper|Bovine|bovines'. You
should specify the 'primaryjoin' (and 'secondaryjoin', if there is an
association table present) keyword arguments to the relation() function
(or for backrefs, by specifying the backref using the backref()
function with keyword arguments) to explicitly specify the join
conditions. Nested error is Cant determine join between 'entreprises'
and 'bovines'; tables have more than one foreign key constraint
relationship between them. Please specify the 'onclause' of this join
explicitly.
Ok, so I have to specify the onclause. But how do I do that? For
reference, here is the (non-working) data definition:
t_bovines = Table( 'bovines', metadata,
Column('id', Integer, primary_key=True),
Column('entrydate', Integer),
Column('key1', Integer, ForeignKey('entreprises.key1')),
Column('key2', Integer, ForeignKey('entreprises.key2')),
Column('key3', String, ForeignKey('entreprises.key3')),
Column('var', Integer),
Column('val', Integer),
)
t_entreprises = Table( 'entreprises', metadata,
Column('key1', Integer, primary_key=True),
Column('key2', Integer, primary_key=True, default=0),
Column('key3', String, primary_key=True),
Column('lname', Unicode(30)),
Column('fname', Unicode(30)),
Column('street', Unicode(30)),
Column('country', String(1)),
Column('zip', String(5)),
Column('locality', Unicode(30)),
)
t_surfaces = Table( 'surfaces', metadata,
Column('id', Integer, primary_key=True),
Column('entrydate', Integer),
Column('key1', Integer, ForeignKey('entreprises.key1')),
Column('key2', Integer, ForeignKey('entreprises.key2')),
Column('key3', String, ForeignKey('entreprises.key3')),
Column('var', Integer),
Column('val', Integer),
)
metadata.create_all()
class Bovine(object):
pass
class Surface(object):
pass
class Enterprise(object):
def __repr__(self):
return [Entreprise %s %s %s] % (self.key1, self.key2,
self.key3)
usermapper = mapper(Bovine, t_bovines)
usermapper = mapper(Surface, t_surfaces)
usermapper = mapper(Enterprise, t_entreprises, properties={
'bovines': relation(Bovine),
'surfaces': relation(Surface)
})
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Composite primary key
Jonathan Ellis wrote:
Well, you could specify the primaryjoin as described here:
http://www.sqlalchemy.org/docs/adv_datamapping.myt#advdatamapping_properties_customjoin
but I suspect that your existing mapper will Just Work if you switch
to a composite FK, rather than 3 keys on individual columns
t_bovines = Table( 'bovines', metadata,
Column('id', Integer, primary_key=True),
Column('entrydate', Integer),
Column('key1', Integer),
Column('key2', Integer),
Column('key3', String),
Column('var', Integer),
Column('val', Integer),
ForeignKeyConstraint(['key1', 'key2', 'key3'],
['enterprise.key1', 'enterprise.key2', 'enterprise.key3'])
)
t_entreprises = Table( 'entreprises', metadata,
Column('key1', Integer),
Column('key2', Integer),
Column('key3', String),
Column('lname', Unicode(30)),
Column('fname', Unicode(30)),
Column('street', Unicode(30)),
Column('country', String(1)),
Column('zip', String(5)),
Column('locality', Unicode(30)),
PrimaryKeyConstraint('key1', 'key2', 'key3')
)
# similarly adjust surfaces
On 1/9/07, exhuma.twn [EMAIL PROTECTED] wrote:
Hi,
I have to load a table from 2 different data-sources witch each having
3 different primary keys. This is because those 2 datasources are
already exported from 3 databases from an archaic application.
From those 2 datasources I created - after normalising - 3 new tables.
The main table keeps the 3 primary keys as one composite primary key.
The two other tables have those 3 fields as foreign keys. Now, if I map
those table definitions onto a table with relations, sqlalchemy
complains with the following error:
sqlalchemy.exceptions.ArgumentError: Error determining primary and/or
secondary join for relationship 'bovines' between mappers
'Mapper|Enterprise|entreprises' and 'Mapper|Bovine|bovines'. You
should specify the 'primaryjoin' (and 'secondaryjoin', if there is an
association table present) keyword arguments to the relation() function
(or for backrefs, by specifying the backref using the backref()
function with keyword arguments) to explicitly specify the join
conditions. Nested error is Cant determine join between 'entreprises'
and 'bovines'; tables have more than one foreign key constraint
relationship between them. Please specify the 'onclause' of this join
explicitly.
Ok, so I have to specify the onclause. But how do I do that? For
reference, here is the (non-working) data definition:
t_bovines = Table( 'bovines', metadata,
Column('id', Integer, primary_key=True),
Column('entrydate', Integer),
Column('key1', Integer, ForeignKey('entreprises.key1')),
Column('key2', Integer, ForeignKey('entreprises.key2')),
Column('key3', String, ForeignKey('entreprises.key3')),
Column('var', Integer),
Column('val', Integer),
)
t_entreprises = Table( 'entreprises', metadata,
Column('key1', Integer, primary_key=True),
Column('key2', Integer, primary_key=True, default=0),
Column('key3', String, primary_key=True),
Column('lname', Unicode(30)),
Column('fname', Unicode(30)),
Column('street', Unicode(30)),
Column('country', String(1)),
Column('zip', String(5)),
Column('locality', Unicode(30)),
)
t_surfaces = Table( 'surfaces', metadata,
Column('id', Integer, primary_key=True),
Column('entrydate', Integer),
Column('key1', Integer, ForeignKey('entreprises.key1')),
Column('key2', Integer, ForeignKey('entreprises.key2')),
Column('key3', String, ForeignKey('entreprises.key3')),
Column('var', Integer),
Column('val', Integer),
)
metadata.create_all()
class Bovine(object):
pass
class Surface(object):
pass
class Enterprise(object):
def __repr__(self):
return [Entreprise %s %s %s] % (self.key1, self.key2,
self.key3)
usermapper = mapper(Bovine, t_bovines)
usermapper = mapper(Surface, t_surfaces)
usermapper = mapper(Enterprise, t_entreprises, properties={
'bovines': relation(Bovine),
'surfaces': relation(Surface)
})
Magical! This worked :)
Thanks a lot
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: [Sqlalchemy-users] named cursor
On Jan 9, 2007, at 5:24 AM, Michele Petrazzo wrote: Because, like what I wrote on the psycopg2 ml (lists.initd.org/ pipermail/psycopg/2007-January/005250.html) without the server side cursor, the memory usage on the client side is proportionally at the number of records returned by the query (on my example 400k records, 60MB ram). I think because the driver fetch all the data from the server and leave it available with the fetchXXX methods. really ? im not sure about psycopg2 specifically, but its been my experience with virtually all database client libraries that as you fetch rows and discard them, you only pull as much as you fetch into memory. i.e. if you had a resultset of 4000 rows and just fetched 5 rows, only 5 rows get pulled over the wire. thats definitely how the lower level APIs work and it would be pretty bad if psycopg2 didnt maintain that behavior. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Oracle autoload problem?
I am experimenting with autoloading tables from Oracle (9.2) running on a
remote machine. Normal selects using cx_Oracle work just fine. Here is
what I get:
from sqlalchemy import *
eng = create_engine('oracle:// ...')
md = BoundMetaData(eng)
g2r = Table('gene2refseq',md,autoload=True)
Traceback (most recent call last):
File stdin, line 1, in module
File build/bdist.linux-x86_64/egg/sqlalchemy/schema.py, line 143, in
__call__
File build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py, line 505,
in reflecttable
File build/bdist.linux-x86_64/egg/sqlalchemy/databases/oracle.py, line
214, in reflecttable
File build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py, line 255,
in execute
File build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py, line 259,
in execute_text
File build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py, line 329,
in _execute_raw
File build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py, line 348,
in _execute
sqlalchemy.exceptions.SQLError: (DatabaseError) ORA-01008: not all variables
bound
'select distinct OWNER from ALL_TAB_COLUMNS where TABLE_NAME = :table_name'
{'table_name': 'GENE2REFSEQ'}
Any thoughts?
Thanks,
Sean
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: [Sqlalchemy-users] named cursor
Michael Bayer wrote:
On Jan 9, 2007, at 5:24 AM, Michele Petrazzo wrote:
Because, like what I wrote on the psycopg2 ml (lists.initd.org/
pipermail/psycopg/2007-January/005250.html)
without the server side cursor, the memory usage on the client
side is proportionally at the number of records returned by the
query (on my example 400k records, 60MB ram). I think because the
driver fetch all the data from the server and leave it available
with the fetchXXX methods.
really ? im not sure about psycopg2 specifically,
Here, sure! Assuming the I'm on linux and I have ps utility locally.
The db are on the same machine where I'm making tests. PG are 8.1.5 and
psycopg2 2.0.4.
This is my test:
-code-
import time
import subprocess as SP
import psycopg2
NAMED_CURSOR = 1
def get_mem():
#Wait for the memory stabilization
time.sleep(0.5)
#get the memory usage with ps.
output = SP.Popen([ps,-eo,rss,args],
stdout=SP.PIPE).communicate()[0]
for line in output.split('\n'):
if not line: continue
mem, pr_name = line.split(None,1)
#show only the my python exe
if not python + __file__ in pr_name: continue
return mem
def create_cur():
if NAMED_CURSOR:
return con.cursor(test)
else:
return con.cursor()
def exec_q(q):
cur = create_cur()
print empty cursor , get_mem()
cur.execute(q)
print cursor after execute , get_mem()
print result --, cur.fetchone()
print after fetchone , get_mem()
cur.close()
print cursor closed , get_mem()
print python and modules , get_mem()
con = psycopg2.connect('user=test password=test dbname=book')
print connection , get_mem()
exec_q(SELECT COUNT(id) FROM book)
exec_q(SELECT * FROM book)
/-code-
## with NAMED_CURSOR = 1
michele:~/tmp$ time python test_sql_mem.py
python and modules 3928
connection 4048
empty cursor 4068
cursor after execute 4080
result -- (406500L,)
after fetchone 4088
cursor closed 4092
empty cursor 4092
cursor after execute 4092
result -- (6087, 'title', 15, '18', 'Sonzogno', 508126)
after fetchone 4092
cursor closed 4092
real0m6.107s
user0m0.080s
sys 0m0.076s
michele:~/tmp$
## with NAMED_CURSOR = 0
michele:~/tmp$ time python test_sql_mem.py
python and modules 3932
connection 4048
empty cursor 4068
cursor after execute 4080
result -- (406500L,)
after fetchone 4092
cursor closed 4092
empty cursor 4092
cursor after execute 58440
result -- (6087, 'title', 15, '18', 'Sonzogno', 508126)
after fetchone 58452
cursor closed 58452
real0m11.499s
user0m0.544s
sys 0m0.176s
michele:~/tmp$
but its been my
experience with virtually all database client libraries that as you
fetch rows and discard them, you only pull as much as you fetch into
memory. i.e. if you had a resultset of 4000 rows and just fetched 5
rows, only 5 rows get pulled over the wire. thats definitely how the
lower level APIs work
I'm with you, but my code show other results
and it would be pretty bad if psycopg2 didnt maintain that behavior.
I'll send another email to the psycopg2 ml, with this results, and I'll
reply you in the near future.
Michele
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Oracle autoload problem?
thats really weird, since you can see the SQL + bind params right
there and they are all bound. We have made some changes to Oracle
reflection recently but the unit tests were passing on my linux box,
as well as on the machine of the person who contributed the changes.
so i would ask can you reflect any tables from that database ? can
you make a small test table with a very simple layout and reflect
that ? does general SQL operation through SQLAlchemy work with this
database ? (i.e. dont use reflection, but try out some generated
queries?) do you have any other oracle databases which you can try
reflecting from ?
On Jan 9, 2007, at 2:08 PM, Sean Davis wrote:
I am experimenting with autoloading tables from Oracle (9.2)
running on a remote machine. Normal selects using cx_Oracle work
just fine. Here is what I get:
from sqlalchemy import *
eng = create_engine('oracle:// ...')
md = BoundMetaData(eng)
g2r = Table('gene2refseq',md,autoload=True)
Traceback (most recent call last):
File stdin, line 1, in module
File build/bdist.linux-x86_64/egg/sqlalchemy/schema.py, line
143, in __call__
File build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py,
line 505, in reflecttable
File build/bdist.linux-x86_64/egg/sqlalchemy/databases/
oracle.py, line 214, in reflecttable
File build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py,
line 255, in execute
File build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py,
line 259, in execute_text
File build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py,
line 329, in _execute_raw
File build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py,
line 348, in _execute
sqlalchemy.exceptions.SQLError: (DatabaseError) ORA-01008: not all
variables bound
'select distinct OWNER from ALL_TAB_COLUMNS where TABLE_NAME
= :table_name' {'table_name': 'GENE2REFSEQ'}
Any thoughts?
Thanks,
Sean
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: [Sqlalchemy-users] named cursor
OK well this is all quite disturbing !
I would want to examine this more closely. but, lets assume this is
really how it is, since thats how it looks (eesh).
So, you dont really want to use named cursors, you just want psycopg2
to use cursors in its underlying operations so that it has more
efficient operation...and the magical incantation you send to
psycopg2 in order for it to use cursors is conn.cursor(x) as
opposed to conn.cursor() (which to me is like, totally arbitrary..and
at this point i am getting pretty annoyed with how utterly
undocumented psycopg2 is...but then again so is MySQLDB). so this is
total artifact-land, and it has no implication for SA to grow some
new fine-grained cursor-based API, we are here just looking for a
hack workaround internally.
My first instinct is to say, ok, then we just use the call
conn.cursor('x') to get cursors when using the postgres dialect.
however, im told that this probably wont work for CRUD (i.e. insert,
update etc) operations, so we would have to keep that in mind (SA
does scan incoming SQL for INSERT/UPDATE etc so thats not an issue).
also, is conn.cursor(x) more efficient in all cases ? or is the
overhead of creating a cursor significant, and only worthwhile when
you know you are going to fetch only a subset of rows from an
enormous result set ?
so my second instinct is to perhaps go with the approach of using
conn.cursor(x) for all SELECT statements when using the postgres
dialect, *if* you tell the dialect you want to use that. im thinking
a flag use_named_cursors=True when you create_engine(). Id really
rather not pollute the connect()/Connection/execute() API with this
concept if at all possible...i was initially thinking of doing
something like, conn.with_cursor(mycursor).execute(), but thats
pretty explicit for something that should really be an internal
optimization.
thats my thoughts so far. just FYI if you want to hack your SA to
use named cursors in all cases, its line 282 and 322 of lib/
sqlalchemy/engine/base.py .
On Jan 9, 2007, at 11:40 AM, Michele Petrazzo wrote:
Michael Bayer wrote:
On Jan 9, 2007, at 5:24 AM, Michele Petrazzo wrote:
Because, like what I wrote on the psycopg2 ml (lists.initd.org/
pipermail/psycopg/2007-January/005250.html)
without the server side cursor, the memory usage on the client
side is proportionally at the number of records returned by the
query (on my example 400k records, 60MB ram). I think because the
driver fetch all the data from the server and leave it available
with the fetchXXX methods.
really ? im not sure about psycopg2 specifically,
Here, sure! Assuming the I'm on linux and I have ps utility locally.
The db are on the same machine where I'm making tests. PG are 8.1.5
and
psycopg2 2.0.4.
This is my test:
-code-
import time
import subprocess as SP
import psycopg2
NAMED_CURSOR = 1
def get_mem():
#Wait for the memory stabilization
time.sleep(0.5)
#get the memory usage with ps.
output = SP.Popen([ps,-eo,rss,args],
stdout=SP.PIPE).communicate()[0]
for line in output.split('\n'):
if not line: continue
mem, pr_name = line.split(None,1)
#show only the my python exe
if not python + __file__ in pr_name: continue
return mem
def create_cur():
if NAMED_CURSOR:
return con.cursor(test)
else:
return con.cursor()
def exec_q(q):
cur = create_cur()
print empty cursor , get_mem()
cur.execute(q)
print cursor after execute , get_mem()
print result --, cur.fetchone()
print after fetchone , get_mem()
cur.close()
print cursor closed , get_mem()
print python and modules , get_mem()
con = psycopg2.connect('user=test password=test dbname=book')
print connection , get_mem()
exec_q(SELECT COUNT(id) FROM book)
exec_q(SELECT * FROM book)
/-code-
## with NAMED_CURSOR = 1
michele:~/tmp$ time python test_sql_mem.py
python and modules 3928
connection 4048
empty cursor 4068
cursor after execute 4080
result -- (406500L,)
after fetchone 4088
cursor closed 4092
empty cursor 4092
cursor after execute 4092
result -- (6087, 'title', 15, '18', 'Sonzogno', 508126)
after fetchone 4092
cursor closed 4092
real0m6.107s
user0m0.080s
sys 0m0.076s
michele:~/tmp$
## with NAMED_CURSOR = 0
michele:~/tmp$ time python test_sql_mem.py
python and modules 3932
connection 4048
empty cursor 4068
cursor after execute 4080
result -- (406500L,)
after fetchone 4092
cursor closed 4092
empty cursor 4092
cursor after execute 58440
result -- (6087, 'title', 15, '18', 'Sonzogno', 508126)
after fetchone 58452
cursor closed 58452
real0m11.499s
user0m0.544s
sys 0m0.176s
michele:~/tmp$
but its been my
experience with virtually all database client libraries that as you
fetch rows and discard them, you only pull as much as you fetch into
memory. i.e. if you had a
[sqlalchemy] customize table creation
Hi alchemists! Is there any way to have SA do some custom work before it creates a table? I tried to subclass Table and override create, which obviously doesn't work that easily. Any suggestions or ideas? The background is, I have some objects that dynamically add columns to the table on demand, which seems to work fine. Now, I need to ensure that these objects can do their work on table creation. And doing it manually would be painful... Thanks in advance Daniel --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: customize table creation
On 1/9/07, Daniel Haus [EMAIL PROTECTED] wrote: Hi alchemists! Is there any way to have SA do some custom work before it creates a table? I tried to subclass Table and override create, which obviously doesn't work that easily. Any suggestions or ideas? The background is, I have some objects that dynamically add columns to the table on demand, which seems to work fine. Now, I need to ensure that these objects can do their work on table creation. And doing it manually would be painful... You're trying to dynamically add columns to the table in the database, or just to the Table instance? --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] MSSQL session.flush() doesn't always fetch primary keys
Hi, Using MSSQL, session.flush() doesn't always fetch primary keys back from the database. It works if you explicity specify a sequence, but if the sequence is implicitly created it doesn't fetch. I have raised ticket #415 for this issue. In MSSQLExecutionContext, it looks to see if any column has a sequence, and if so issues select @@identity afterwards. A couple of simple solutions to this: 1) Always issue select @@identity (quick tests show this should be safe) 2) Copy the code from MSSQLSchemaGenerator that determines whether to create an implicit sequence, and run it for each query. Thing is, I'm a bit worried about the performance of both those options. I'm wondering why the decision to give a column an implicit sequence is in the database engine code. The fix that seems logical to me is to put the code to create an implicit sequence in Column.__init__. It's down to the individual engines how they implement this sequence, but its existence or not is engine independent. Let me know what you think, Paul --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Oracle autoload problem?
On 1/9/07, Andrija Zarić [EMAIL PROTECTED] wrote:
This very strange, because SQLAlchemy is issuing the query like it is
supposed to do. There is bind variable (table_name) but for some
reason cx_Oracle/Oracle is raising ORA-1008.
Can you assert that following is working:
import cx_Oracle as ora
e = ora.connect('...')
c = e.cursor()
c.execute(select distinct OWNER from ALL_TAB_COLUMNS where
TABLE_NAME = :table_name,{'table_name':'GENE2REFSEQ'})
c.fetchall()
I'm using autoload feature with Oracle 8.1.7 and 10XE, both tables
with unique name and same name with multiple owners without any
problems. SQLAlchemy is from svn trunk and cx_Oracle is 4.1
cx_Oracle indeed looks to be the issue. I'll sort it out in more detail,
but bound parameters are not working for me in any queries I have tried. It
works otherwise, but not with bind params.
Sean
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Accessing attributes in many to many relations
Am Dienstag, 9. Januar 2007 17:01 schrieb Michael Bayer: we do have an extension called associationproxy that is used for something similar with association objects...it may give you some ideas on how to proxy attributes across. i dont think you should approach the idea of adding individual properties as quite complicated...for example, you could write a function that adds all the Address properties to the Person class en-masse: for key in class_mapper(Address).props: setattr(Person, key, property(...some getter function...)) if you come up with something generally useful in that idiom, you can post to the SA wiki or we can even make another extension if its really of general use. At first, thanks for your quick answer. My basic idea was to maintain the abstraction between the data model and the actual object. So the ideal place to specify such things would be in the mapper. To my mind, some magic with the properties could do it, e.g. like this: mapper(Person, person_table, properties = dict( address=relation(Address, secondary=r_pers_addrid_table, proxy_relation_attributes=True)) In case proxy_relation_attributes is set to True, all columns (except the foreign keys which are obsolete) are made accessible through the relation - very simple syntax. Correct me if I'm wrong, but many to many relations are normally done this way: table1 --- association_table --- table2 table1 has a one to many relation to association_table and table2 has also a one to many relation to association_table. From the perpective of table1, it should therefore in any case be possible to locate association/relation attributes along with the attributes of table2 (and the same in the other direction). The only problem could be name clashes, which could probably be handled in some way (e.g. raise an exception or prefix them). This may also obsolete the associationproxy - but maybe I don't fully understand its concepts. This solution would make it possible to alter the data model, e.g. make a one to many relation from a many to many relation, e.g.: table1 - table2 without having to touch the object representing the function - the only thing I'd have to change is the mapper. I have no clue how I would implement such a relation property as I'm very new to SA, perhaps it's impossible for some reason. However, I think putting attributes in association tables is a common pattern in data models, so this could be of use for many SA users. What do you think of this idea? Best Regards, Hermann -- [EMAIL PROTECTED] GPG key ID: 299893C7 (on keyservers) FP: 0124 2584 8809 EF2A DBF9 4902 64B4 D16B 2998 93C7 --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: MSSQL session.flush() doesn't always fetch primary keys
Hi Rick, I think we're actually agreeing about almost everything, although I have responded to your points individually. Lets see what Michael says about this. Actually reading over my post again, my preferred fix was a little hidden at the bottom. I will highlight it here: The fix that seems logical to me is to put the code to create an implicit sequence in Column.__init__. It's down to the individual engines how they implement this sequence, but its existence or not is engine independent. If all you're looking for is an implicit generation of an IDENTITY column, you can use the autoincrement=True keyword to the Column constructor. Yep, that's true, but if I do that then session.flush() doesn't fetch the pk, hence my bug report. But be careful with the assumption of autoincrementing PKs. It's perfectly valid to have a PK that is not autoincrementing. That's why the pseudo-Sequence() mechanism is there in the first place, to distinguish between the two (and to allow the specification of the IDENTITY seed value). Yep, hence the fairly complicated code in MSSQLSchemaGenerator # install a IDENTITY Sequence if we have an implicit IDENTITY column if column.primary_key and column.autoincrement and isinstance(column.type, sqltypes.Integer) and not column.foreign_key: if column.default is None or (isinstance(column.default, schema.Sequence) and column.default.optional): column.sequence = schema.Sequence(column.name + '_seq') Issuing a spurious SELECT @@IDENTITY could be construed as a bit risky as well -- that will simply return the last inserted IDENTITY value on that DB connection -- which could be from a completely different query. Given that SA pools connection, it seems to me that this is bad-tasting recipe. I agree in principle. In fact, it returns null if your last insert was a table without an identity column, at least with SQL Server 2005. But still, doing this seems risky. I'm not sure I understand the motivation to check each query anew for identity keys. What's wrong with checking for them only at table definition time? Exactly, hence my last suggestion to move the sequence generation to Column.__init__. The thing is, that's just not how SA is setup at the moment, so this will need a little jiggery pokery. Regards, Paul --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: MSSQL session.flush() doesn't always fetch primary keys
the current behavior of SA is to utilize the databases implicit PK generation feature *if one is available*. the three databases that I know have this are sqlite (send None in to a PK INSERT, it gets generated), MySQL (AUTOINCREMENT) and Postgres (SERIAL/BIGSERIAL, which results in the creation of a sequence with a naming scheme). the other database im familiar with, Oracle, does not have such a feature. So with Oracle, you have to create an explicit Sequence object if you want PKs to get generated automatically upon INSERT, and SA will fire off that sequence. you can put a Sequence on any Column, and it will still work on mysql/sqlite, since in those cases its just ignored, and the normal autoincrementing behavior of those databases is used instead. so we dont have anything that is creating named sequences automatically. im not sure what MS-SQL's needs are in this department. but the autoincrement=True on Column is always like that, it basically means use the database's auto-incrementing feature, *if available*...if MS-SQL doesnt have that then you wont get autoincrementing. Now, if we want to say, SQLAlchemy should automatically create named sequences for databases that use them, thats a change we can look into making, and of course the question becomes what do we name the sequences, and stuff like that. but at the moment thats not how its designed, so thats probably why its not working. On Jan 9, 2007, at 6:33 PM, Paul Johnston wrote: Hi Rick, I think we're actually agreeing about almost everything, although I have responded to your points individually. Lets see what Michael says about this. Actually reading over my post again, my preferred fix was a little hidden at the bottom. I will highlight it here: The fix that seems logical to me is to put the code to create an implicit sequence in Column.__init__. It's down to the individual engines how they implement this sequence, but its existence or not is engine independent. If all you're looking for is an implicit generation of an IDENTITY column, you can use the autoincrement=True keyword to the Column constructor. Yep, that's true, but if I do that then session.flush() doesn't fetch the pk, hence my bug report. But be careful with the assumption of autoincrementing PKs. It's perfectly valid to have a PK that is not autoincrementing. That's why the pseudo-Sequence() mechanism is there in the first place, to distinguish between the two (and to allow the specification of the IDENTITY seed value). Yep, hence the fairly complicated code in MSSQLSchemaGenerator # install a IDENTITY Sequence if we have an implicit IDENTITY column if column.primary_key and column.autoincrement and isinstance(column.type, sqltypes.Integer) and not column.foreign_key: if column.default is None or (isinstance(column.default, schema.Sequence) and column.default.optional): column.sequence = schema.Sequence(column.name + '_seq') Issuing a spurious SELECT @@IDENTITY could be construed as a bit risky as well -- that will simply return the last inserted IDENTITY value on that DB connection -- which could be from a completely different query. Given that SA pools connection, it seems to me that this is bad-tasting recipe. I agree in principle. In fact, it returns null if your last insert was a table without an identity column, at least with SQL Server 2005. But still, doing this seems risky. I'm not sure I understand the motivation to check each query anew for identity keys. What's wrong with checking for them only at table definition time? Exactly, hence my last suggestion to move the sequence generation to Column.__init__. The thing is, that's just not how SA is setup at the moment, so this will need a little jiggery pokery. Regards, Paul --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Saving all transactions against a database (logging queries?)
Jose,
Try it without the level=, format=, and datefmt= options in
logging.basicConfig() (leaving in only filename= and filemode=) and I
think it'll work. Then you can try adding in the other options one at
a time and see if you can get them to work. When I only had filename
and filemode specified, things worked. Once I started adding more
parameters, I had similar problems.
Brian
On 1/6/07, jose [EMAIL PROTECTED] wrote:
Hello Barry,
I'm trying to get logging work...I put in my model.py the following code
and I expected to see my queries into /tmp/sa.log file, but...
what's wrong with it?
from turbogears import database
from sqlalchemy import Table, relation
from sqlalchemy.engine import create_engine
from sqlalchemy.ext.assignmapper import assign_mapper
import logging
database.bind_meta_data()
session = database.session
engine = database.metadata.engine
context = session.context
engine.echo = False
logging.basicConfig(level=logging.DEBUG,
format='%(asctime)s %(levelname)-8s %(message)s',
datefmt='%a, %d %b %Y %H:%M:%S',
filename='/tmp/sa.log',
filemode='w')
logging.getLogger('sqlalchemy.engine').setLevel(logging.INFO)·
Barry Warsaw wrote:
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On Jan 5, 2007, at 12:01 PM, Michael Bayer wrote:
i havent gotten around to adding docs for logging to the main
docs...but its using Python's logging module now. turn off all the
echo=True flags and go straight to logging:
import logging
logging.basicConfig() # see python's logging docs for options
logging.getLogger('sqlalchemy.engine').setLevel(logging.INFO)
Sweet.
- -Barry
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.5 (Darwin)
iQCVAwUBRZ6IsnEjvBPtnXfVAQLINgP+JUdkjNn+CtAA5Uao3Gs+n+4IuuMeiCoz
8J9yY9XlU74AW4N1ysf0wao0r2p871t2+sgIuFnBpRxO3DficR+iji3LJ24ZeWdH
KcG/uxUGk4uF7jfMeAdzVVRe/XqVRwI1uwMCN5p2rkXjdEokJp7G8uT872nC7EM5
3OzCHKORs2M=
=PBfM
-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 sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
