[sqlalchemy] Re: sql executemany and postgresql - probably bug
Thanks Michael, it is not urgent to me. if i found time i'll look down what is causing this and eventually try to patch. regards, stefan On 25 Авг, 03:09, Michael Bayer [EMAIL PROTECTED] wrote: On Aug 23, 2007, at 11:18 AM, che wrote: i cant reply to this issue for another week since im out of town. as a workaround, placing autoincrement=False on your primary key column for now may resolve the issue. --~--~-~--~~~---~--~~ 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: One To Many Polymorphic Association.
Currently, unable to find any solution for this problem. I think my
little knowledge with respect to
sqlalchemy is restricting me to get a solution for this issue. Anyone
with somewhat more in-depth
knowledge should be able to find a solution.
Waiting...
Regards,
Pradeep Jindal
On Aug 25, 1:15 am, Pradeep Jindal [EMAIL PROTECTED] wrote:
On Friday 24 August 2007 19:20:00 Gaetan de Menten wrote:
You might be interested by:http://techspot.zzzeek.org/?p=13
(also in the examples directory of SQLAlchemy)
On 8/24/07, praddy [EMAIL PROTECTED] wrote:
##
from sqlalchemy import *
meta = BoundMetaData('sqlite://', echo=False)
# Parents table.
parents = Table('parents', meta,
Column(id, Integer, primary_key=True),
Column(data, String(50), nullable=False)
)
# Children_1 Table.
children_1 = Table('children_1', meta,
Column(id, Integer, primary_key=True),
Column(data, String(50), nullable=False)
)
# Children_2 Table.
children_2 = Table('children_2', meta,
Column(id, Integer, primary_key=True),
Column(data, String(50))
)
# Association Table.
# This is a generic table which can relate anything to parent.
assoc = Table('assoc', meta,
# parents.c.id
Column(parent_id, Integer, ForeignKey(parents.c.id)),
# associate's id either children_1.c.id or children_2.c.id or any
other child.
Column(assoc_id, Integer),
# Which can be either 'child_1' or 'child_2' for now (can be used
for extending children
# type, decides which table to look in.
Column(assoc_type, String(20))
)
###
I am a novice with respect to sqlalchemy may be RDBMS as well.
How would you like to work on this scenario to achieve backwards
cascading (may not be the right word) which means when one deletes
one specific child from children_1 table (for example), there should
not be any
association entry, which associates that child to the parent, in the
association table as well?
Thanks for the reply. I have already gone through that article, but was unable
to figure out the solution. Anyways, I will give it one more try. Please note
that this scenario is already there and I can't make any changes to the
tables at all.
Thanks
- Pradeep Jindal
--~--~-~--~~~---~--~~
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: interface error with Decimal(0) in where clause
the convert to Decimal thing is a new feature for Numeric types in
version 0.4. If you wish to use Python floats, you can either use
the Float type, or specify asdecimal=False to the constructor of
any Numeric type.
On Aug 27, 2007, at 12:44 AM, cfriedalek wrote:
Thanks. I didn't realize sqlite didn't support Decimal. As shown
above, I haven't setup my table to require decimal support, only
numeric. But SQLAlchemy metadata reports that the numeric type is
decimal after I autoload the table. This seems to be a default of
SQLAlchemy. However I can't find out how to change this default, to
say, Float. Can anyone help?
btw I am interested in Decimal support for another case where I use an
arbitrary precision library (GMP) so this thread has helped me out
with that. Thanks.
On Aug 24, 5:37 pm, Florent Aide [EMAIL PROTECTED] wrote:
Hi,
As far as I know, sqlite does not allow you to store decimal objects,
only floats. Which really is not the same. If you really need
decimals
(ie: accounting books anyone ?) then you should consider using
firebird which is the only other database engine supported by SA that
is embeddable in a python application without the need of and
external
server.
If someone has a way to accurately manipulate floats with the same
precision as decimals I would gladly hear from it because for the
moment I just banned sqlite from my dbengine choices for this
particular reason :(
Regards,
Florent.
On 8/22/07, cfriedalek [EMAIL PROTECTED] wrote:
I have two sqlite databases with the same table structure. I want to
combine them. I want to insert entries from the second into the
first
if they don't already exist in the first. So I wrote this small
script
(probably not the optimal way but I'm on the steep side of the
learning curve ... going up I hope.) Problem is I get an interface
error for a where clause when it includes a Decimal(0) entry.
Seems
like a bug but I hesitate to submit a ticket when I'm so green.
Here's the code and database as sql. Run as combine_databases.py db1
db2 . If db1 and db2 are the same there should be a bunch of skipped
entry messages. Note I hacked the code at line 37 to get it to
run for
the case of a Decimal(0) comparison in the where clase.
line 37 if trloc == 0: trloc = float(trloc) # hack to make
this work
So is this s bug or user error?
cf
import sys
import sqlalchemy as sa
import sqlalchemy.orm as orm
def update_db(table, temp, shape, sidx, nc, br, bi, axloc, trloc):
try:
keys = ['temp', 'shape', 'sidx','nc', 'br', 'bi',
'axloc','trloc']
vals = [temp, shape, sidx, nc, br, bi, axloc, trloc]
dic = dict(zip(keys, vals))
i = table.insert().execute(dic)
except:
print ERROR: failed to store data , dic, \n, temp,
shape,
sidx, nc,\
if len(sys.argv) = 2:
sys.exit(Usage: supply two or more databases: 2nd ...
inserted to
first)
dbs = sys.argv[1:]
engines = [sa.create_engine(''.join(('sqlite:///', db))) for db in
dbs]
metadatas = [sa.MetaData(engine) for engine in engines]
tables = [sa.Table('temperatures', md, autoload=True) for md in
metadatas]
import pdb;pdb.set_trace()
TABLE = tables[0]
ENGINE = engines[0]
for table, engine in zip(tables[1:], engines[1:]):
query = table.select()
for row in engine.execute(query):
temp = row[table.c.temp]
shape = row[table.c.shape]
sidx = row[table.c.sidx]
nc = row[table.c.nc]
br = row[table.c.br]
bi = row[table.c.bi]
axloc = row[table.c.axloc]
trloc = row[table.c.trloc]
#if trloc == 0: trloc = float(trloc) # hack to make this
work
QUERY = TABLE.select().where(sa.and_(
TABLE.c.shape == shape,
TABLE.c.sidx ==
sidx,
TABLE.c.nc == nc, TABLE.c.br == br,
TABLE.c.bi == bi, TABLE.c.axloc ==
axloc,
TABLE.c.trloc == trloc))
for ROW in ENGINE.execute(QUERY):
if ROW:
print .Skipped , row
else:
update_db(TABLE, temp, shape, sidx, nc, br, bi,
axloc,
trloc)
print Added .. , row
BEGIN TRANSACTION;
CREATE TABLE temperatures (id INTEGER PRIMARY KEY, temp NUMERIC,
shape
VARCHAR(10), sidx INTEGER, nc INTEGER, br NUMERIC, bi NUMERIC, axloc
NUMERIC, trloc NUMERIC);
INSERT INTO temperatures VALUES(1,-1.73316368036707e-05,'circle',
10,2000,NULL,NULL,1.0e-05,0);
INSERT INTO temperatures VALUES(2,4.60309299836984e-06,'circle',
10,2000,NULL,NULL,1.0e-05,0.01);
INSERT INTO temperatures VALUES(3,-2.76801870080499e-06,'circle',
10,2000,NULL,NULL,1.0e-05,0.02);
INSERT INTO temperatures VALUES(4,1.77485003202532e-06,'circle',
10,2000,NULL,NULL,1.0e-05,0.03);
INSERT INTO temperatures VALUES(5,-1.08068601149679e-06,'circle',
[sqlalchemy] Re: sql executemany and postgresql - probably bug
Ive added ticket #759 for this and I think before 0.4 is finalized, I'll be working out the full solution for Postgres inserts/updates, which will allow a flag inline=True on all Insert/Update constructs indicating that all SQL expressions should execute inline (i.e. no pre-execution), and I'll also make it more intelligent about PG in this case (i.e. where SERIAL can autoincrement the sequence for you, dont render any literal sequence expression at all). inline=True will also take effect automatically for all executemany() scenarios since the pre-execute thing there is totally wasteful. (the pre- execution logic is primarily so we can get the row's ID in the case of a sequence-oriented database, which includes postgres, oracle and firebird). On Aug 27, 2007, at 2:44 AM, che wrote: Thanks Michael, it is not urgent to me. if i found time i'll look down what is causing this and eventually try to patch. regards, stefan On 25 Авг, 03:09, Michael Bayer [EMAIL PROTECTED] wrote: On Aug 23, 2007, at 11:18 AM, che wrote: i cant reply to this issue for another week since im out of town. as a workaround, placing autoincrement=False on your primary key column for now may resolve the issue. --~--~-~--~~~---~--~~ 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: One To Many Polymorphic Association.
On Aug 24, 2007, at 8:39 AM, praddy wrote:
##
from sqlalchemy import *
meta = BoundMetaData('sqlite://', echo=False)
# Parents table.
parents = Table('parents', meta,
Column(id, Integer, primary_key=True),
Column(data, String(50), nullable=False)
)
# Children_1 Table.
children_1 = Table('children_1', meta,
Column(id, Integer, primary_key=True),
Column(data, String(50), nullable=False)
)
# Children_2 Table.
children_2 = Table('children_2', meta,
Column(id, Integer, primary_key=True),
Column(data, String(50))
)
# Association Table.
# This is a generic table which can relate anything to parent.
assoc = Table('assoc', meta,
# parents.c.id
Column(parent_id, Integer, ForeignKey(parents.c.id)),
# associate's id either children_1.c.id or children_2.c.id or any
other child.
Column(assoc_id, Integer),
# Which can be either 'child_1' or 'child_2' for now (can be used for
extending children
# type, decides which table to look in.
Column(assoc_type, String(20))
)
###
I am a novice with respect to sqlalchemy may be RDBMS as well.
How would you like to work on this scenario to achieve backwards
cascading (may not be the right word) which means when one deletes
one specific child from children_1 table (for example), there should
not be any
association entry, which associates that child to the parent, in the
association table as well?
To allow proper foreign key support, you probably want to place the
foreign key to the association table on each of children_1 and
children_2, and remove the assoc_id column from the assoc table.
this is the key to the blog article about polymorphic associations.
its probably easiest for you to work with the example mentioned in
the blog and modify. (http://techspot.zzzeek.org/files/
poly_assoc_2.py ). at least, if you study the example it will lead
to more insight on how to approach this. If you really want to keep
your foreign keys as they are, the ActiveRecord example (http://
techspot.zzzeek.org/files/poly_assoc_1.py ) illustrates that pattern
with SA.
next, you're going to want to specify cascade='all, delete-orphan'
on both relations which reference the association table:
mapper(Parent, parents, properties={
'associations':relation(Association, cascade=all, delete-orphan)
})
class Association(object):
def _child(self):
if self.assoc_type='child1':
return self.child_1
else:
return self.child_2
child=property(_child)
mapper(Association, assoc, properties={
'child_1':relation(Child1, backref=backref(assoc_1,
cascade=all, delete-orphan)),
'child_2':relation(Child2, backref=backref(assoc_2,
cascade=all, delete-orphan)),
})
mapper(Child1, children_1)
mapper(Child2, children_2)
im out of town this week so I dont have the resources to work out the
full solution but this is the general idea.
--~--~-~--~~~---~--~~
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: SA 0.4, pylons 0.9.6rc3
On 22 ago, 19:29, jason kirtland [EMAIL PROTECTED] wrote: Anil wrote: Actually, there was some information in the logs that I just noticed that might help detect the problem (some kind of unicode issue?): 12:58:21,710 INFO [sqlalchemy.engine.base.Engine.0x..50] SHOW CREATE TABLE `User` 12:58:21,711 INFO [sqlalchemy.engine.base.Engine.0x..50] None 12:58:21,802 WARNING sqlalchemy /home/aj99243/lib/python2.5/site-packages/SQLAlchemy-0.4.0beta3-py2.5.egg/sqlalchemy/databases/mysql.py:1891: RuntimeWarning: Unknown schema content: u' PRIMARY KEY USING BTREE (`id`),' repr(line))) 12:58:21,805 INFO sqlalchemy.engine.base.Engine.0x..50 SHOW VARIABLES LIKE 'character_set%%' Ok, this is now fixed in r3412. Sorry it didn't make it into beta4! It's a *very* simple change if you want to update your copy of SQLAlchemy directly: http://www.sqlalchemy.org/trac/changeset/3412 Sorry, but I'm still having the same issue. I updated only the file in the changeset and the error keeps appearing. I'm missing something? Thanks --~--~-~--~~~---~--~~ 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] outerjoin constructor throws exception from 0.3.9. Bug or user error?
Hi,
I have the following two tables (in MySql):
CREATE TABLE `A` (
`xkey` varchar(200) NOT NULL,
`yval` int(11) default NULL,
PRIMARY KEY (`xkey`)
) ENGINE=InnoDB;
and
CREATE TABLE `B` (
`xkey` varchar(200) NOT NULL default '',
`s` enum('yes','no') NOT NULL default 'yes',
PRIMARY KEY (`xkey`,`s`)
) ENGINE=InnoDB;
The following statement worked fine till 0.3.8 , and from 0.3.9 throws
the exception:
oj=outerjoin(A, B, and_(A.c.xkey==B.c.xkey, B.c.s=='yes'))
Traceback (most recent call last):
File join.py, line 13, in ?
oj=outerjoin(A, B, and_(A.c.xkey==B.c.xkey, B.c.s=='yes'))
File /usr/local/lib/python2.4/site-packages/SQLAlchemy-0.3.9-
py2.4.egg/sqlalchemy/sql.py, line 117, in outerjoin
return Join(left, right, onclause, isouter = True, **kwargs)
File /usr/local/lib/python2.4/site-packages/SQLAlchemy-0.3.9-
py2.4.egg/sqlalchemy/sql.py, line 2256, in __init__
self._init_primary_key()
File /usr/local/lib/python2.4/site-packages/SQLAlchemy-0.3.9-
py2.4.egg/sqlalchemy/sql.py, line 2287, in _init_primary_key
if p.references(c) or (c.primary_key and not p.primary_key):
AttributeError: '_BindParamClause' object has no attribute
'primary_key'
Note, if B.s were not an enum but a varchar, it would have worked.
1) Is this a usage error or a bug?
2) If the latter. How can i work around it?
TIA
--~--~-~--~~~---~--~~
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: One To Many Polymorphic Association.
On Monday 27 August 2007 19:57:21 Michael Bayer wrote:
On Aug 24, 2007, at 8:39 AM, praddy wrote:
##
from sqlalchemy import *
meta = BoundMetaData('sqlite://', echo=False)
# Parents table.
parents = Table('parents', meta,
Column(id, Integer, primary_key=True),
Column(data, String(50), nullable=False)
)
# Children_1 Table.
children_1 = Table('children_1', meta,
Column(id, Integer, primary_key=True),
Column(data, String(50), nullable=False)
)
# Children_2 Table.
children_2 = Table('children_2', meta,
Column(id, Integer, primary_key=True),
Column(data, String(50))
)
# Association Table.
# This is a generic table which can relate anything to parent.
assoc = Table('assoc', meta,
# parents.c.id
Column(parent_id, Integer, ForeignKey(parents.c.id)),
# associate's id either children_1.c.id or children_2.c.id or any
other child.
Column(assoc_id, Integer),
# Which can be either 'child_1' or 'child_2' for now (can be used for
extending children
# type, decides which table to look in.
Column(assoc_type, String(20))
)
###
I am a novice with respect to sqlalchemy may be RDBMS as well.
How would you like to work on this scenario to achieve backwards
cascading (may not be the right word) which means when one deletes
one specific child from children_1 table (for example), there should
not be any
association entry, which associates that child to the parent, in the
association table as well?
To allow proper foreign key support, you probably want to place the
foreign key to the association table on each of children_1 and
children_2, and remove the assoc_id column from the assoc table.
this is the key to the blog article about polymorphic associations.
its probably easiest for you to work with the example mentioned in
the blog and modify. (http://techspot.zzzeek.org/files/
poly_assoc_2.py ). at least, if you study the example it will lead
to more insight on how to approach this. If you really want to keep
your foreign keys as they are, the ActiveRecord example (http://
techspot.zzzeek.org/files/poly_assoc_1.py ) illustrates that pattern
with SA.
next, you're going to want to specify cascade='all, delete-orphan'
on both relations which reference the association table:
mapper(Parent, parents, properties={
'associations':relation(Association, cascade=all, delete-orphan)
})
class Association(object):
def _child(self):
if self.assoc_type='child1':
return self.child_1
else:
return self.child_2
child=property(_child)
mapper(Association, assoc, properties={
'child_1':relation(Child1, backref=backref(assoc_1,
cascade=all, delete-orphan)),
'child_2':relation(Child2, backref=backref(assoc_2,
cascade=all, delete-orphan)),
})
mapper(Child1, children_1)
mapper(Child2, children_2)
im out of town this week so I dont have the resources to work out the
full solution but this is the general idea.
Michael,
Thanks for the reply. I think I already got the solution, I will post
it here
after the testing part of it. Further, I could not change any table in the
real scenario, I had to go with the existing tables.
Thanks again.
Pradeep Jindal
--~--~-~--~~~---~--~~
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: SA 0.4, pylons 0.9.6rc3
pablo wrote: Sorry, but I'm still having the same issue. I updated only the file in the changeset and the error keeps appearing. I'm missing something? That table is reflecting without issues for me in the trunk. Are you seeing the same warning in the log? --~--~-~--~~~---~--~~ 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 with turbogears and mapper-part3 relation and flow control
Hello,
ok so I got the connection, select statements working using
turbogears, assign_mapper and sqlalchemy.
Now i need to work out the relation.
User has a one to many relation with address, email, accounts.
In reverse: address, email and accounts have many to one...
#here is my table
user_table = sqlalchemy.Table('user', metadata, autoload=True)
uaddress_table = sqlalchemy.Table('uaddress', metadata, autoload=True)
uemail_table = sqlalchemy.Table('uemail', metadata, autoload=True)
uaccounts_table = sqlalchemy.Table('uaccounts', metadata, autoload=True)
#my python class
class User(object):
pass
class Address(object):
pass
class Email(object):
pass
class Accounts(object):
pass
#mapper for table to class
#1 to many
The Primary key on:
User is 'User_Sid'
on Address is 'User_Sid' and 'Address_Sid'
on Email is 'User_Sid' and 'email_Sid'
on Accounts is 'User_Sid' and 'accounts_Sid'
Is this done correctly?
usermapper=assign_mapper(session.context,User,user_table, properties={
'address':sqlalchemy.relation(Address),
'email':sqlalchemy.relation(Email),
'accounts':sqlalchemy.relation(Accounts),
})
Do I have to create a relation on these mappers?
uaddressmapper=assign_mapper(session.context,Address,uaddress_table)
uemailmapper=assign_mapper(session.context,Email,uemail_table)
uaccountsmapper=assign_mapper(session.context,Account,uaccount_table)
What if the relation was many to many? How would the properties={??} look like?
In controller.py how do I use my mapper with relations?
u=model.User()
Do I do:
a=u.address()
or
a=model.address()
u.address=a
How do I save them? Do I save one by one? u.flush() then a.flush() or
u.flash() saves it all???
Thanks,
Lucas
--~--~-~--~~~---~--~~
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] Testing Conditions for insertion, and grouping queries together
Hi, I'm new to SQLAlchemy, and I was wondering if there's a way to test a condition while inserting. I'm trying to make my application use as few separate queries at the same time as possible. Here's a couple cases I have had issues with: Inserting something with a unique column if it doesn't exist. Right now, I am doing a select with a count() call. If I just insert it if it already exists I get an exception, but the exception doesn't propogate until the session is flushed. Also, I am having a tough time catching the SQLAlchemy exceptions. What's the best way to go about this? I have a join table where you have tags and things, and the join table tags the things. I want to test if it already exists and whatnot, and insert it in the same query if it doesn't. Are there any good articles on optimizing your SQL queries and such? Thanks, Mike Lewis --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
