[sqlalchemy] Table has no column named xxxx ??
Hello list, I'm new to sqlalchemy and database programming. I have defined a declarative class import sqlalchemy as SQL import sqlalchemy.ext.declarative as Declare import sqlalchemy.orm as ORM import datetime Base = Declare.declarative_base() class CUniversity(Base): __tablename__ = universities id = SQL.Column(SQL.Integer, primary_key=True) Name = SQL.Column(SQL.String) State = SQL.Column(SQL.String) Rankprof = SQL.Column(SQL.Integer) RankTotal = SQL.Column(SQL.Integer) Deadline = SQL.Column(SQL.Date) Status = SQL.Column(SQL.String) Comment = SQL.Column(SQL.Text) def __init__(self, name, state, rank_prof, rank_total, deadline, status, comment): self.Name = name self.State = state self.Rankprof = rank_prof self.RankTotal = rank_total self.Deadline = deadline self.Status = status self.Comment = comment def __repr__(self): return '\n'.join([Name: + self.Name, State: + self.State, Professional rank: + str(self.Rankprof), Total rank: + str(self.RankTotal), Deadline: + str(self.Deadline), Status: + self.Status, Comment: + self.Comment]) And I add an entry to a database using the following procedue: DBPATH = data.db Engine = SQL.create_engine(sqlite:/// + DBPATH) MData = Base.metadata UniTable = CUniversity.__table__ MData.create_all(Engine) CSession = ORM.sessionmaker(bind=Engine) Session = CSession() TempDate = datetime.date(2009, 1, 15) TestUni = CUniversity(CMU, PA, 29, 0, TempDate, , Rec form, No app. fee) Session.add(TestUni) Session.commit() Python issued an error on the commit() operation: OperationalError: (OperationalError) table universities has no column named Rankprof u'INSERT INTO universities (Name, State, Rankprof, RankTotal, Deadline, Status, Comment) VALUES (?, ?, ?, ?, ?, ?, ?)' ['CMU', 'PA', '29', 0, '2009-01-15', '', 'Rec form, No app. fee'] What did I do wrong? --~--~-~--~~~---~--~~ 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] Column('last_updated', onupdate=func.current_timestamp())
Hi all,
I created a table with the following column:
Column('last_updated', DateTime, PassiveDefault(func.current_timestamp
()), onupdate=func.current_timestamp())
Maybe I don't understand how onupdate works.
I would like to have this column to be changed every time the row is
updated,
but it doesn't work.
thank you for any help
j
--~--~-~--~~~---~--~~
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: MySQL, unions and ordering
you'd say, s.alias().select()
it makes subqueries which MySQL probably doesn't require.
On Dec 5, 2008, at 10:35 PM, Bo Shi wrote:
Thanks; the monkeypatch approach works nicely. Using the alias()
method will raise
AttributeError: 'Alias' object has no attribute '_order_by_clause'
On Fri, Dec 5, 2008 at 7:25 PM, Michael Bayer [EMAIL PROTECTED]
wrote:
there's logic which is removing the order_by's from the selects, and
in that case this is what's blowing away the parenthesis as well.
Some databases don't even allow ORDER BY inside of the queries used
in
a UNION since in the absense of LIMIT/OFFSET, which also is not
standard SQL, they have no effect.
However I dont think its good form for SQLA to be whacking the ORDER
BY from the unions if that is in fact what was requested.So this
behavior is changed in the 0.5 series in r5425. As far as the 0.4
series, we're only supporting critical bugfixes there and I'd like to
avoid any behavioral changes (0.4 is also on a more conservative
release schedule). If you're truly stuck with 0.4, you can use
select.order_by(...).alias().select() to get an equivalent query
which
is insulated from changes (and is probably more compatible across
databases), or to get exactly the same SQL here's a safe monkeypatch
approach:
from sqlalchemy import *
s = select([x, y]).select_from(table)
def frozen_order_by(s):
s = s.self_group()
s.order_by = lambda *args: s
return s
qlist = [s.limit(10).order_by('x'), s.limit(10).order_by('x')]
print union_all(*[frozen_order_by(q) for q in
qlist]).order_by(foo).limit(10)
frozen_order_by() calls self_group() thereby generating a new
select()
so that the original is unchanged.
On Dec 5, 2008, at 5:08 PM, Bo Shi wrote:
Oh, check this out:
(SA 0.4.7)
from sqlalchemy import *
s = select([x, y]).select_from(table)
qlist = [s.limit(10).order_by('x').self_group(),
s.limit(10).order_by('x').self_group()]
print union_all(*qlist).order_by(foo).limit(10)
SELECT x, y
FROM table
LIMIT 10 UNION ALL SELECT x, y
FROM table
LIMIT 10 ORDER BY foo
LIMIT 10
for q in qlist:
... print q
...
(SELECT x, y
FROM table ORDER BY x
LIMIT 10)
(SELECT x, y
FROM table ORDER BY x
LIMIT 10)
On Fri, Dec 5, 2008 at 4:55 PM, Bo Shi [EMAIL PROTECTED] wrote:
I had to upgrade to 0.4.7 from 0.4.2, but your sample query works,
however, my application of it does not.
Sorry I'm being so light on details, I'll try to reproduce with a
complete sample versus using snippets of production code.
Each select statement is generated like so:
sel = select(pre_select + selectlist,
from_obj=join_datatables(tables))
I apply self_group() using a list comprehension and if I print each
select statement in the list individually, the parentheses show up.
If I then union_all(*querylist) and print that, the parentheses
disappear.
Weird.
I should note that the individual selects have filters, an order by
clause and a limit, but the following works fine so I would not
expect
that to be a problem.
from sqlalchemy import *
s = select([x, y]).select_from(table)
print s.self_group()
(SELECT x, y
FROM table)
print union_all(s.self_group(), s.self_group()).order_by(foo)
(SELECT x, y
FROM table) UNION ALL (SELECT x, y
FROM table) ORDER BY foo
print union_all(s.limit(10).self_group(),
s.limit(10).self_group()).order_by(foo).limit(10)
(SELECT x, y
FROM table
LIMIT 10) UNION ALL (SELECT x, y
FROM table
LIMIT 10) ORDER BY foo
LIMIT 10
import sqlalchemy as sa
sa.__version__
'0.4.7'
On Fri, Dec 5, 2008 at 4:23 PM, Michael Bayer [EMAIL PROTECTED]
wrote:
from sqlalchemy import *
s = select([x, y]).select_from(table)
print union_all(s.self_group(), s.self_group()).order_by(foo)
(SELECT x, y
FROM table) UNION ALL (SELECT x, y
FROM table) ORDER BY foo
On Dec 5, 2008, at 4:17 PM, Bo Shi wrote:
Thanks for the quick response!
The following does *not* work. Am I making the call incorrectly?
sel = union_all(*[q.self_group() for q in querylist])
On Fri, Dec 5, 2008 at 4:08 PM, Michael Bayer [EMAIL PROTECTED]
wrote:
try calling self_group() on each select object.
On Dec 5, 2008, at 3:55 PM, Bo Shi wrote:
Hi all,
There appear to be some nuances to using order by statements
with
set
operations like unions in MySQL but the following is allowed*:
(SELECT a,b from DBA.tbl ORDER BY b LIMIT 5)
UNION ALL
(SELECT a,b from DBB.tbl ORDER BY b LIMIT 5)
ORDER BY b
When I attempt to generate such a statement with:
union_all(*list_of_select_objs),
The SQL generated lacks parentheses around the SELECT
statements (in
addition to dropping the order by clauses, but that appears
to be
expected behavior). Is there a way to put the parentheses in?
*just an example, the query i've written is meaningless/
useless :-)
--
Bo Shi
207-469-8264
--
Bo Shi
207-469-8264
--
Bo Shi
207-469-8264
--
Bo Shi
[sqlalchemy] Re: Column('last_updated', onupdate=func.current_timestamp())
that is the correct syntax. It will take effect any time an update()
construct is used or when the ORM updates a row. Because onupdate is
not a DDL-side construct, it will not take effect if you use a plain
text UPDATE statement or if the update is otherwise not emitted by the
SQLAlchemy application. The PassiveDefault, since it does represent
DDL, will work in this manner, but only if the table was created using
this table construct.
On Dec 6, 2008, at 4:50 AM, jo wrote:
Hi all,
I created a table with the following column:
Column('last_updated', DateTime, PassiveDefault(func.current_timestamp
()), onupdate=func.current_timestamp())
Maybe I don't understand how onupdate works.
I would like to have this column to be changed every time the row is
updated,
but it doesn't work.
thank you for any help
j
--~--~-~--~~~---~--~~
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: Column('last_updated', onupdate=func.current_timestamp())
Column('last_updated', DateTime, PassiveDefault(func.current_timestamp
()), onupdate=func.current_timestamp())
Maybe I don't understand how onupdate works.
I would like to have this column to be changed every time the row is
updated,
but it doesn't work.
I just use something like:
Column('last_updated', DateTime, onupdate=datetime.now)
I haven't tried it with the func but it should work I would think.
Michael
--~--~-~--~~~---~--~~
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] circular reference
Hi all,
I have two tables in my schema with circular references and I don't know
hot to create them.
tbl['anagrafica']=Table('anagrafica',database.metadata,
Column('id', Integer, Sequence('anagrafica_id_seq'),
primary_key=True, nullable=False),
Column('nome', Unicode(200), nullable=False, index=True,
case_sensitive=True),
Column('id_operatore', Integer, ForeignKey('operatore.id'))
)
tbl['operatore']=Table('operatore',database.metadata,
Column('id', Integer, Sequence('operatore_id_seq'),
nullable=False, primary_key=True),
Column('id_anagrafica', Integer,
ForeignKeyConstraint('anagrafica.id'))
)
thank you for any help.
j
--~--~-~--~~~---~--~~
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] create index with a condition
Hi all, I would like to create an index with a condition, like this: CREATE UNIQUE INDEX univocita_codice_aziendale on azienda (lower(codice_aziendale), stato_record) WHERE stato_record = 'A' Is there a way to do that, using the Index() command? j --~--~-~--~~~---~--~~ 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: utf hex instead of utf-8 return
thanks for the quick reply. i kept trying with it and no have reached
the utter state of confusion.
the specification of Unicode versus String in the table def's coupled
with actual str representation
has my totally confused. here's a quick script, have a look at the
mysql table itself to see character
display:
#!/usr/bin/env python
# -*- coding: utf-8 -*-
import os, sys
import unicodedata
from sqlalchemy import *
from sqlalchemy.orm import *
#set db
import MySQLdb
db = MySQLdb.connect(host='localhost', user='root', passwd='',
db='xxx', use_unicode=True, charset='utf8')
cur = db.cursor()
cur.execute('SET NAMES utf8')
cur.execute('SET CHARACTER SET utf8')
cur.execute('SET character_set_connection=utf8')
cur.execute('SET character_set_server=utf8')
cur.execute('''SHOW VARIABLES LIKE 'char%'; ''')
print cur.fetchall()
utf_repr = '\xc3\xab'
hex_repr = '\xeb'
mysql_url = 'mysql://root:@localhost/xxx'
connect_args = {'charset':'utf8', 'use_unicode':'0'}
engine = create_engine(mysql_url, connect_args=connect_args)
metadata = MetaData()
test_table = Table('encoding_test', metadata,
Column(u'id', Integer, primary_key=True),
Column(u'unicode', Integer),
Column(u'u_hex', Unicode(10)),
Column(u'u_utf', Unicode(10)),
Column(u'u_str', Unicode(10)),
Column(u's_hex', String(10)),
Column(u's_utf', String(10)),
Column(u's_str', String(10))
)
class EncodingTest(object): pass
mapper(EncodingTest, test_table)
metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()
et = EncodingTest()
et.unicode = 1
et.u_str = u'ë'
et.u_hex = u'\xeb'
et.u_utf = u'\xc3\xab'
et.s_str = u'ë'
et.s_hex = u'\xeb'
et.s_utf = u'\xc3\xab'
session.add(et)
session.commit()
et = EncodingTest()
et.unicode = 0
et.u_str = 'ë'
et.u_hex = '\xeb'
et.u_utf = '\xc3\xab'
et.s_str = 'ë'
et.s_hex = '\xeb'
et.s_utf = '\xc3\xab'
session.add(et)
session.commit()
session.close()
session = Session()
results = session.query(EncodingTest).all()
for result in results:
print result.unicode
print repr(result.u_hex), repr(result.u_utf), repr(result.u_str)
print repr(result.s_hex), repr(result.s_utf), repr(result.s_str)
print
in addition, i don't seem to be able to run the mysql settings (# set
db) from SA.
any insights are greatly appreciated. btw, the use_unciode, either in
MySQLdb or SA,
doesn't seem to have any effect on results.
thx
On Dec 5, 3:25 pm, Michael Bayer [EMAIL PROTECTED] wrote:
I'm not sure of the mechanics of what you're experiencing, but make
sure you use charset=utf8use_unicode=0 with MySQL.
On Dec 5, 2008, at 4:17 PM, n00b wrote:
greetings,
SA (0.5.0rc1) keeps returning utf hex in stead of utf-8 and in the
process driving me batty. all the mysql setup is fine, the chars look
good and are umlauting to goethe's delight. moreover, insert and
select are working perfectly with the MySQLdb api on three different
*nix systems, two servers, ... it works.
where things fall apart is on the retrieval side of SA; inserts are
fine (using the config_args = {'charset':'utf8'} dict in the
create_engine call).
for example, ë, the latin small letter e with diaeresis, is stored in
mysql hex as C3 AB; using the MySQldb client, this is exactly what i
get back: '\xc3\xab' (in the # -*- coding: UTF-8 -*- environment) no
further codecs work required. SA, on the other hand, hands me back the
utf-hex representation, '\xeb'.
there must be some setting that i'm missing that'll give the
appropriate utf-8 representation at the SA (api) level. any ideas,
suggestions?
thx
yes, i could do '\xeb'.encode('utf8) but it's not an option. we got
too much data to deal with and MySQLdb is working perfectly well
without the extra step. thx.
--~--~-~--~~~---~--~~
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 Sphinx Documentation Preview
You're right about CSS selectors; the simple fix for this without re- generating any source, is just to instruct the browser to not double up on the indentation when it sees a ul nested in a blockquote. Hey wait, the problem is already fixed. Looks great today. The lists too; thanks for the changes. On Dec 6, 6:44 am, Michael Bayer [EMAIL PROTECTED] wrote: On Dec 5, 2008, at 11:00 PM, Eric Ongerth wrote: Oh yeah, and in Main Documentation (at least) you have some ul class=simple lists nested inside of blockquote elements, which is resulting in some of your lists being much farther indented than others, without a good visual reason why. Seems like the difference could be eliminated. sphinx (actually docutils) creates that structure; unless we've done something wrong in the rest markup, we can't change it without parsing it and reconstructing it (which seems like overkill to me, since CSS selectors can usually find things). not sure what is prompting it to create a blockquote though. --~--~-~--~~~---~--~~ 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: create index with a condition
Postgres supports this using the postgres_where keyword argument sent to Index(). Otherwise use DDL(). http://www.sqlalchemy.org/docs/05/reference/dialects/postgres.html?highlight=postgres_where#indexes http://www.sqlalchemy.org/docs/05/reference/sqlalchemy/schema.html?highlight=ddl#sqlalchemy.schema.DDL On Dec 6, 2008, at 1:15 PM, jose wrote: Hi all, I would like to create an index with a condition, like this: CREATE UNIQUE INDEX univocita_codice_aziendale on azienda (lower(codice_aziendale), stato_record) WHERE stato_record = 'A' Is there a way to do that, using the Index() command? j --~--~-~--~~~---~--~~ 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: circular reference
specify use_alter=True to one or both ForeignKey constructs.
On Dec 6, 2008, at 12:37 PM, jose wrote:
Hi all,
I have two tables in my schema with circular references and I don't
know
hot to create them.
tbl['anagrafica']=Table('anagrafica',database.metadata,
Column('id', Integer, Sequence('anagrafica_id_seq'),
primary_key=True, nullable=False),
Column('nome', Unicode(200), nullable=False, index=True,
case_sensitive=True),
Column('id_operatore', Integer, ForeignKey('operatore.id'))
)
tbl['operatore']=Table('operatore',database.metadata,
Column('id', Integer, Sequence('operatore_id_seq'),
nullable=False, primary_key=True),
Column('id_anagrafica', Integer,
ForeignKeyConstraint('anagrafica.id'))
)
thank you for any help.
j
--~--~-~--~~~---~--~~
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: Column('last_updated', onupdate=func.current_timestamp())
Yes, Michael, I see how it works now. Thank you
j
Michael Bayer wrote:
that is the correct syntax. It will take effect any time an update()
construct is used or when the ORM updates a row. Because onupdate is
not a DDL-side construct, it will not take effect if you use a plain
text UPDATE statement or if the update is otherwise not emitted by the
SQLAlchemy application. The PassiveDefault, since it does represent
DDL, will work in this manner, but only if the table was created using
this table construct.
On Dec 6, 2008, at 4:50 AM, jo wrote:
Hi all,
I created a table with the following column:
Column('last_updated', DateTime, PassiveDefault(func.current_timestamp
()), onupdate=func.current_timestamp())
Maybe I don't understand how onupdate works.
I would like to have this column to be changed every time the row is
updated,
but it doesn't work.
thank you for any help
j
--~--~-~--~~~---~--~~
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: circular reference
the use_alter=True raises this error:
ForeignKeyConstraint(['id_operatore'],['operatore.id'],use_alter=True),
File /usr/lib/python2.4/site-packages/sqlalchemy/schema.py, line
701, in __init__
raise exceptions.ArgumentError(Alterable
ForeignKey/ForeignKeyConstraint requires a name)
Michael Bayer wrote:
specify use_alter=True to one or both ForeignKey constructs.
On Dec 6, 2008, at 12:37 PM, jose wrote:
Hi all,
I have two tables in my schema with circular references and I don't
know
hot to create them.
tbl['anagrafica']=Table('anagrafica',database.metadata,
Column('id', Integer, Sequence('anagrafica_id_seq'),
primary_key=True, nullable=False),
Column('nome', Unicode(200), nullable=False, index=True,
case_sensitive=True),
Column('id_operatore', Integer, ForeignKey('operatore.id'))
)
tbl['operatore']=Table('operatore',database.metadata,
Column('id', Integer, Sequence('operatore_id_seq'),
nullable=False, primary_key=True),
Column('id_anagrafica', Integer,
ForeignKeyConstraint('anagrafica.id'))
)
thank you for any help.
j
--~--~-~--~~~---~--~~
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: create index with a condition
Are these features available on ver. 0.3? j Michael Bayer wrote: Postgres supports this using the postgres_where keyword argument sent to Index(). Otherwise use DDL(). http://www.sqlalchemy.org/docs/05/reference/dialects/postgres.html?highlight=postgres_where#indexes http://www.sqlalchemy.org/docs/05/reference/sqlalchemy/schema.html?highlight=ddl#sqlalchemy.schema.DDL On Dec 6, 2008, at 1:15 PM, jose wrote: Hi all, I would like to create an index with a condition, like this: CREATE UNIQUE INDEX univocita_codice_aziendale on azienda (lower(codice_aziendale), stato_record) WHERE stato_record = 'A' Is there a way to do that, using the Index() command? j --~--~-~--~~~---~--~~ 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: circular reference
I gave it a name but now...
raise FlushError(Circular dependency detected + repr(edges) +
repr(queue))
sqlalchemy.exceptions.FlushError: Circular dependency detected
sqlalchemy.topological._EdgeCollection object at 0xb73f356c[]
jose wrote:
the use_alter=True raises this error:
ForeignKeyConstraint(['id_operatore'],['operatore.id'],use_alter=True),
File /usr/lib/python2.4/site-packages/sqlalchemy/schema.py, line
701, in __init__
raise exceptions.ArgumentError(Alterable
ForeignKey/ForeignKeyConstraint requires a name)
Michael Bayer wrote:
specify use_alter=True to one or both ForeignKey constructs.
On Dec 6, 2008, at 12:37 PM, jose wrote:
Hi all,
I have two tables in my schema with circular references and I don't
know
hot to create them.
tbl['anagrafica']=Table('anagrafica',database.metadata,
Column('id', Integer, Sequence('anagrafica_id_seq'),
primary_key=True, nullable=False),
Column('nome', Unicode(200), nullable=False, index=True,
case_sensitive=True),
Column('id_operatore', Integer, ForeignKey('operatore.id'))
)
tbl['operatore']=Table('operatore',database.metadata,
Column('id', Integer, Sequence('operatore_id_seq'),
nullable=False, primary_key=True),
Column('id_anagrafica', Integer,
ForeignKeyConstraint('anagrafica.id'))
)
thank you for any help.
j
--~--~-~--~~~---~--~~
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] How can I reuse the string syntax in my own code?
Hi, I have been working on a little project to transform excel docs
(actually them saved as csv files) into SQLAlchemy objects.
Of course this is tailored for my own database which I need to import
but I have been splitting it into api more and more and eventually
plan to release it as some configurable importer for SA. But first I
need to clean up the code!
currently I have this type of configuration.
_excel_to_field = {
'First Name' : 'first_name',
'Last Name' : 'last_name',
'Address' : 'address',
'City' : 'city',
'State' : 'state',
'Zip' : 'zip_code',
}
and the bulk of the system is run as
for k,v in row:
if not k in [NoModelField,NoCSVField]:
if v:
log.debug('Adding Field %s:%s' % (k,v))
else:
log.warning('Adding Empty Field for %s at line %i'
% (k,index))
setattr(contact,k,v)
else:
log.warning(Field not accounted for (%s,%s) % (k,v))
so currently that for is very big for (see below)
So in order to make code a lot more readable I was thinking I could
use the syntax SA uses for filter and such so I can write the
_excel_to_field as
_excel_to_field = {
'First Name' : 'Contact.first_name',
'Last Name' : 'Contact.last_name',
'Address' : 'Location.address',
'City' : 'Location.city',
'State' : 'Location.state',
'Zip' : 'Location.zip_code',
}
please note the Location obj is a fabrication of this email
but how can I replace the setatrr call? I guess I could parse things
around but I assume there is some kind of api in SA to accomplish
this.
My plan is to add two more parameter to this dict, #1 a typeConverter
#2 a headerReader, I'll explain those in due time. I'm working on
transforming the forloop into a more elegant forloop with a callback.
So the final plan is to have something like this
_excel_to_field = {
'First Name' : 'Contact.first_name',string,
'Last Name' : 'Contact.last_name','string',
'Address' : 'Location.address',address,
'City' : 'Location.city',string
'State' : 'Location.state',state
'Zip' : 'Location.zip_code',int
}
this third callback will be either a build-in, I have several already
for date,float,us_money,etc also more complex ones like always create
(adds a new record all the time) and query_create (looks for one if
that fails create it) fields but you can code your own as they are
simply python functions with a common interface. Ideally this will
grow into a number big number of defaults that will live inside the
package. So you will never have to code it yourself just look for
them, but I want to do this string-python formatting First.
--~--~-~--~~~---~--~~
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: circular reference
that's an ORM problem.you might want to look into the
post_update flag if you need to flush two mutually dependent rows.
See the relation() docs for details.
On Dec 6, 2008, at 3:41 PM, jose wrote:
I gave it a name but now...
raise FlushError(Circular dependency detected + repr(edges) +
repr(queue))
sqlalchemy.exceptions.FlushError: Circular dependency detected
sqlalchemy.topological._EdgeCollection object at 0xb73f356c[]
jose wrote:
the use_alter=True raises this error:
ForeignKeyConstraint(['id_operatore'],
['operatore.id'],use_alter=True),
File /usr/lib/python2.4/site-packages/sqlalchemy/schema.py, line
701, in __init__
raise exceptions.ArgumentError(Alterable
ForeignKey/ForeignKeyConstraint requires a name)
Michael Bayer wrote:
specify use_alter=True to one or both ForeignKey constructs.
On Dec 6, 2008, at 12:37 PM, jose wrote:
Hi all,
I have two tables in my schema with circular references and I don't
know
hot to create them.
tbl['anagrafica']=Table('anagrafica',database.metadata,
Column('id', Integer, Sequence('anagrafica_id_seq'),
primary_key=True, nullable=False),
Column('nome', Unicode(200), nullable=False, index=True,
case_sensitive=True),
Column('id_operatore', Integer, ForeignKey('operatore.id'))
)
tbl['operatore']=Table('operatore',database.metadata,
Column('id', Integer, Sequence('operatore_id_seq'),
nullable=False, primary_key=True),
Column('id_anagrafica', Integer,
ForeignKeyConstraint('anagrafica.id'))
)
thank you for any help.
j
--~--~-~--~~~---~--~~
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: create index with a condition
they are not. you can of course issue any DDL you'd like using engine.execute(somestring). On Dec 6, 2008, at 3:39 PM, jose wrote: Are these features available on ver. 0.3? j Michael Bayer wrote: Postgres supports this using the postgres_where keyword argument sent to Index(). Otherwise use DDL(). http://www.sqlalchemy.org/docs/05/reference/dialects/postgres.html?highlight=postgres_where#indexes http://www.sqlalchemy.org/docs/05/reference/sqlalchemy/schema.html?highlight=ddl#sqlalchemy.schema.DDL On Dec 6, 2008, at 1:15 PM, jose wrote: Hi all, I would like to create an index with a condition, like this: CREATE UNIQUE INDEX univocita_codice_aziendale on azienda (lower(codice_aziendale), stato_record) WHERE stato_record = 'A' Is there a way to do that, using the Index() command? j --~--~-~--~~~---~--~~ 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: How can I reuse the string syntax in my own code?
On Dec 6, 2008, at 4:16 PM, Jorge Vargas wrote:
So in order to make code a lot more readable I was thinking I could
use the syntax SA uses for filter and such so I can write the
_excel_to_field as
_excel_to_field = {
'First Name' : 'Contact.first_name',
'Last Name' : 'Contact.last_name',
'Address' : 'Location.address',
'City' : 'Location.city',
'State' : 'Location.state',
'Zip' : 'Location.zip_code',
}
please note the Location obj is a fabrication of this email
but how can I replace the setatrr call? I guess I could parse things
around but I assume there is some kind of api in SA to accomplish
this.
if I understand the problem correctly it's likely easier to place the
descriptors directly in the dict, ie. {'First
Name':Contact.first_name} and then use
dict[fieldname].__set__(instance, value).
--~--~-~--~~~---~--~~
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] Extending sqlalchemy.schema.Column and metaprogramming traps
Hello.
I had a problem described in subject. Here is the testcase:
import sqlalchemy
engine = sqlalchemy.create_engine('sqlite:///:memory:')
metadata = sqlalchemy.MetaData(engine)
class ForeignKey(sqlalchemy.Column):
def __init__(self, name, foreign_column, *args, **kwargs):
fk = sqlalchemy.ForeignKey(foreign_column)
super(ForeignKey, self).__init__(name, fk, *args, **kwargs)
table1 = sqlalchemy.Table('table1', metadata,
sqlalchemy.Column('id', sqlalchemy.Integer)
)
table2 = sqlalchemy.Table('table2', metadata,
ForeignKey('fk', 'table1.id')
)
metadata.create_all()
It fails with really strange exception: AttributeError: 'ForeignKey'
object has no attribute 'use_alter'. It was really hard to me to
track down the error's nature and I found that it came from here:
http://www.sqlalchemy.org/trac/browser/sqlalchemy/trunk/lib/sqlalchemy/sql/visitors.py#L29
I solved my problem by adding class property __visit_name__ = 'column'
to ForeignKey class. But I am a bit confused now and I have two
questions about the code in
sqlalchemy.sql.visitors.VisitableType.__init__()
1. What about another side-effects depending on clsname? Is it
actually safe to extend sqlalchemy.schema.Column, or it may have
unpredictable behavior similar to that i've encountered?
2. (almost offtopic) Is 'exec' really need there? What's wrong with
closures?
3. Maybe I should send it to developers mailing list?
Thanks.
--
Angri
--~--~-~--~~~---~--~~
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: Extending sqlalchemy.schema.Column and metaprogramming traps
On Dec 6, 2008, at 4:27 PM, Angri wrote: 1. What about another side-effects depending on clsname? Is it actually safe to extend sqlalchemy.schema.Column, or it may have unpredictable behavior similar to that i've encountered? The Column object is one of the most key classes in all of SQLAlchemy and we do put it through some fairly intricate copy/proxy patterns particularly when using the ORM. Extending it should be safe, although this is usually not needed. For custom creation patterns as you're seeking here its more straightforward to build a creation function, so that the resulting object is returned unchanged in its type. 2. (almost offtopic) Is 'exec' really need there? What's wrong with closures? the visit step is called very intensively during statement compilation so exec'ing the direct code instead of relying upon getattr() with a composed name at runtime is an optimization to reduce function-call and attribute-retrieval overhead. Just as a point of reference I tried rewriting our visit dispatcher in C, and my experiments showed that using the exec approach you see there performs just as well - though the time savings compared to a basic getattr() approach are very small. Since you raised the issue, I went to try a different approach which is probably the best possible approach without using exec, which is this: visit_name = cls.__dict__[__visit_name__] if isinstance(visit_name, str): getter = operator.attrgetter(visit_%s % visit_name) def _compiler_dispatch(self, visitor, **kw): return getter(visitor)(self, **kw) else: def _compiler_dispatch(self, visitor, **kw): return getattr(visitor, 'visit_%s' % self.__visit_name__)(self, **kw) Above, we use operator.attrgetter so that the string composition is already handled, and the attrgetter itself is a native object which performs as fast as direct access. This change still adds a few function calls per compile. Our bench of a single select() compile goes from 183 to 187, and two of the zoomark_orm tests fail past the 5% threshhold, with tests three and four moving from 6623 to 6723 and 23345 to 23861 function calls, respectively. Which is an extremely small amount, so its not a terribly big deal either way. So the exec approach is saving a tiny amount of call counts, but not necessarily any actual time. I'd be willing to scrap it if it continues to scare other developers. The reason I'm at all comfortable with exec is that we're already using 'exec' for decorators - its a technique used by the decorators module (which I'd like to transition to at some point) to faithfully represent the calling signature of a decorated function. 3. Maybe I should send it to developers mailing list? eitherdevel is not very active. though this is a pretty develop-y subject... --~--~-~--~~~---~--~~ 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: MySQL, unions and ordering
Right; my bad - I misread the instructions.
On Sat, Dec 6, 2008 at 9:41 AM, Michael Bayer [EMAIL PROTECTED] wrote:
you'd say, s.alias().select()
it makes subqueries which MySQL probably doesn't require.
On Dec 5, 2008, at 10:35 PM, Bo Shi wrote:
Thanks; the monkeypatch approach works nicely. Using the alias()
method will raise
AttributeError: 'Alias' object has no attribute '_order_by_clause'
On Fri, Dec 5, 2008 at 7:25 PM, Michael Bayer [EMAIL PROTECTED]
wrote:
there's logic which is removing the order_by's from the selects, and
in that case this is what's blowing away the parenthesis as well.
Some databases don't even allow ORDER BY inside of the queries used
in
a UNION since in the absense of LIMIT/OFFSET, which also is not
standard SQL, they have no effect.
However I dont think its good form for SQLA to be whacking the ORDER
BY from the unions if that is in fact what was requested.So this
behavior is changed in the 0.5 series in r5425. As far as the 0.4
series, we're only supporting critical bugfixes there and I'd like to
avoid any behavioral changes (0.4 is also on a more conservative
release schedule). If you're truly stuck with 0.4, you can use
select.order_by(...).alias().select() to get an equivalent query
which
is insulated from changes (and is probably more compatible across
databases), or to get exactly the same SQL here's a safe monkeypatch
approach:
from sqlalchemy import *
s = select([x, y]).select_from(table)
def frozen_order_by(s):
s = s.self_group()
s.order_by = lambda *args: s
return s
qlist = [s.limit(10).order_by('x'), s.limit(10).order_by('x')]
print union_all(*[frozen_order_by(q) for q in
qlist]).order_by(foo).limit(10)
frozen_order_by() calls self_group() thereby generating a new
select()
so that the original is unchanged.
On Dec 5, 2008, at 5:08 PM, Bo Shi wrote:
Oh, check this out:
(SA 0.4.7)
from sqlalchemy import *
s = select([x, y]).select_from(table)
qlist = [s.limit(10).order_by('x').self_group(),
s.limit(10).order_by('x').self_group()]
print union_all(*qlist).order_by(foo).limit(10)
SELECT x, y
FROM table
LIMIT 10 UNION ALL SELECT x, y
FROM table
LIMIT 10 ORDER BY foo
LIMIT 10
for q in qlist:
... print q
...
(SELECT x, y
FROM table ORDER BY x
LIMIT 10)
(SELECT x, y
FROM table ORDER BY x
LIMIT 10)
On Fri, Dec 5, 2008 at 4:55 PM, Bo Shi [EMAIL PROTECTED] wrote:
I had to upgrade to 0.4.7 from 0.4.2, but your sample query works,
however, my application of it does not.
Sorry I'm being so light on details, I'll try to reproduce with a
complete sample versus using snippets of production code.
Each select statement is generated like so:
sel = select(pre_select + selectlist,
from_obj=join_datatables(tables))
I apply self_group() using a list comprehension and if I print each
select statement in the list individually, the parentheses show up.
If I then union_all(*querylist) and print that, the parentheses
disappear.
Weird.
I should note that the individual selects have filters, an order by
clause and a limit, but the following works fine so I would not
expect
that to be a problem.
from sqlalchemy import *
s = select([x, y]).select_from(table)
print s.self_group()
(SELECT x, y
FROM table)
print union_all(s.self_group(), s.self_group()).order_by(foo)
(SELECT x, y
FROM table) UNION ALL (SELECT x, y
FROM table) ORDER BY foo
print union_all(s.limit(10).self_group(),
s.limit(10).self_group()).order_by(foo).limit(10)
(SELECT x, y
FROM table
LIMIT 10) UNION ALL (SELECT x, y
FROM table
LIMIT 10) ORDER BY foo
LIMIT 10
import sqlalchemy as sa
sa.__version__
'0.4.7'
On Fri, Dec 5, 2008 at 4:23 PM, Michael Bayer [EMAIL PROTECTED]
wrote:
from sqlalchemy import *
s = select([x, y]).select_from(table)
print union_all(s.self_group(), s.self_group()).order_by(foo)
(SELECT x, y
FROM table) UNION ALL (SELECT x, y
FROM table) ORDER BY foo
On Dec 5, 2008, at 4:17 PM, Bo Shi wrote:
Thanks for the quick response!
The following does *not* work. Am I making the call incorrectly?
sel = union_all(*[q.self_group() for q in querylist])
On Fri, Dec 5, 2008 at 4:08 PM, Michael Bayer [EMAIL PROTECTED]
wrote:
try calling self_group() on each select object.
On Dec 5, 2008, at 3:55 PM, Bo Shi wrote:
Hi all,
There appear to be some nuances to using order by statements
with
set
operations like unions in MySQL but the following is allowed*:
(SELECT a,b from DBA.tbl ORDER BY b LIMIT 5)
UNION ALL
(SELECT a,b from DBB.tbl ORDER BY b LIMIT 5)
ORDER BY b
When I attempt to generate such a statement with:
union_all(*list_of_select_objs),
The SQL generated lacks parentheses around the SELECT
statements (in
addition to dropping the order by clauses, but that appears
to be
expected behavior). Is there a way to put the parentheses in?
*just an example, the query i've written is meaningless/
useless :-)
--
Bo Shi
[sqlalchemy] Re: objects created using sqlalchemy
On Fri, 5 Dec 2008, Faheem Mitha wrote:
Hi,
I'm using sqla with the following schema (see below). I'm creating a cell
object implicitly, using the function make_cell and the association proxy
pattern.
def make_cell(patient_obj, snp_obj, snpval):
patient_obj.snps[snp_obj] = snpval
return patient_obj
My question is, is there some way to get my hands on the Cell object that was
just created? If possible, I'd like make_cell to return the cell object. My
immediate reason is that this would make it easy to save the object using
session.save() (there might be some indirect way to do this, of course), but
it would be nice anyway.
A followup to my original post. I must be doing something wrong, because
the Cell object is not being saved. and the proxy in the other direction
is not being updated either. I'm reluctant to ask for debugging help, but
I'm having difficulty tracking down the problem.
The files included in order below are
Schema file: dbschema.py
Utility functions: dbutils.py
Session file: dbsession.py
The last file runs the actual code to populate the dbs, and is one big
function, make_tables.
The most relevant lines here are:
print p1.snps is %s%p1.snps
print s.patients is %s%s.patients
print cell table is %s%list(cell_table.select().execute())
[...]
get_obj(session, Cell)
The output I'm getting is
p1.snps is {SNP rs10458597: Snpval 0}
s.patients is {}
cell table is []
[...]
*** list of Cell objects in class. ***
*** end list of Cell objects. ***
I wouldn't expect the last three, namely s.patients, cell table and
list of Cell objects to all be empty. Can someone tell me what I'm
doing wrong? For an experienced person, it may be obvious.
Note: My use of cascade in the Mappers may be redundant. I just put it
in there for good measure, and I'm not sure what it does.
Regards, Faheem.
dbschema.py
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.associationproxy import association_proxy
from sqlalchemy.orm.collections import attribute_mapped_collection
from datetime import datetime
metadata = MetaData('sqlite:///btsnp.sqlite')
patient_table = Table(
'patient', metadata,
Column('id', String(20), primary_key=True, index=True),
Column('celfilename', String(30), nullable=False, index=True, unique=True),
Column('sex', String(1)),
)
cell_table = Table(
'cell', metadata,
Column('patient_id', None, ForeignKey('patient.id', onupdate='CASCADE',
ondelete='CASCADE'), index=True, nullable=False, primary_key=True),
Column('snp_id', None, ForeignKey('snp.rsid', onupdate='CASCADE',
ondelete='CASCADE'), index=True, nullable=False, primary_key=True),
Column('snpval_id', None, ForeignKey('snpval.val', onupdate='CASCADE',
ondelete='CASCADE'), index=True, nullable=False)
)
snp_table = Table(
'snp', metadata,
Column('rsid', String(20), nullable=False, primary_key=True),
Column('chromosome', Integer, nullable=False),
Column('location', Integer, nullable=False),
Column('probe_set_id', String(20), nullable=False, unique=True),
Column('allele', String(3), nullable=False),
)
snpval_table = Table(
'snpval', metadata,
Column('val', Integer, primary_key=True),
)
metadata.create_all()
def create_cell(snp, snpval):
return Cell(snp=snp, snpval=snpval)
class Patient(object):
def __init__(self, id, celfilename, sex):
self.id = id
self.celfilename = celfilename
self.sex = sex
def __repr__(self):
return 'Patient %s'%self.id
snps = association_proxy('by_rsid', 'snpval', creator=create_cell)
class Cell(object):
def __init__(self, patient=None, snp=None, snpval=None):
self.patient = patient
self.snp = snp
self.snpval = snpval
def __repr__(self):
return 'Cell %s'%self.snpval
class Snp(object):
def __init__(self, rsid, chromosome, location, probe_set_id, allele):
self.rsid = rsid
self.chromosome = chromosome
self.location = location
self.probe_set_id = probe_set_id
self.allele = allele
def __repr__(self):
return 'SNP %s'%self.rsid
patients = association_proxy('by_patient', 'snpval', creator=create_cell)
class Snpval(object):
def __init__(self, val):
self.val = val
def __repr__(self):
return 'Snpval %s'%self.val
# 'cells' corresponds to a 1 to many relation.
mapper(Patient, patient_table, properties={'cells':relation(Cell,
backref='patient'),
'by_rsid': relation(Cell, cascade =
all, delete-orphan, collection_class=attribute_mapped_collection('snp'))}
)
# 'patient_snpval'
[sqlalchemy] Re: I can replace an object with another in python, by changing the object dict to the other object dict. How does it settle with sqlalchemy? Does it works when another mapped object
I have talked to my customers, and they agreed that your solution is great. To match their demands, I've changed two things: 1. If the object exists in the db during construction than it's returned, else the object returned is a new object (that isn't returned). 2. All the attributes that identifies the object, are read-only, so I don't have to worry about them changing the identity of the object (Which may cause an unpredicted behaviour) SO THANK! On 4 דצמבר, 03:59, Michael Bayer [EMAIL PROTECTED] wrote: On Dec 3, 2008, at 6:39 PM, [EMAIL PROTECTED] wrote: B. So I can settle with a merge() method that returns anobject. I think it's the only solution (beside forcing them to work with saving to the db). Actually, it's not too bad. But it's also not easy, I tell you, to write such a function. Because what happens if one of the things that defines me, a part of my uniqueness, is not really a unique field, but a list of objects that are connected to me in a many- to-many relationship from another table. And what if I need to call merge() on each one of them. And what if two of them are evaluated to be the sameobject, so I need to fix the list. oof! merge() doesn't do much that you couldn't do manually on your own. if you need to write a jacked up merge()-like function that compares collections and things, i dont think Python will let you down in that regard. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
