[sqlalchemy] How to cache autoloaded tables?
Hi all, i'm trying to avoid to do the expensive autoload every time i access a table. I found this question http://stackoverflow.com/questions/11785457/sqlalchemy-autoloaded-orm-persistence . If i try to do the same i get errors. When i query (select) the Table created using unpickled metadata i get table name \MY_TABLE\ specified more than once, and in the PostgreSQL logs i find 'SELECT [cut] FROM TABLE, TABLE' statements... I'm using SQLAlchemy 0.8.0 and Postgresql 9.1. Any ideas? Thanks -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] How to map a oracle table with long table name
On Wed, Apr 17, 2013 at 2:38 AM, Evan Jon evanjon@gmail.com wrote:
Hello all,
I want to map a table whose name is BAND_ORDER_OF_LOCAL_TESTING.
class BandOrderOfLocalTesting(Base):
__TABLENAME__ = 'BAND_ORDER_OF_LOCAL_TESTING'
order_id = Column(order_id, Number(18), primary_key=True)
...
I assume the above is a typo - you need to be setting __tablename__,
not __TABLENAME__.
Each time I got the following message:
013-04-17 09:26:55,857 INFO sqlalchemy.engine.base.Engine {'ROWNUM_1': 1}
Traceback (most recent call last):
File tables.py, line 108, in module
instance = session.query(BandOrderOfMonth).first()
File
/usr/lib/python2.7/site-packages/SQLAlchemy-0.8.1dev-py2.7.egg/sqlalchemy/orm/query.py,
line 2181, in first
ret = list(self[0:1])
File
/usr/lib/python2.7/site-packages/SQLAlchemy-0.8.1dev-py2.7.egg/sqlalchemy/orm/query.py,
line 2048, in __getitem__
return list(res)
File
/usr/lib/python2.7/site-packages/SQLAlchemy-0.8.1dev-py2.7.egg/sqlalchemy/orm/loading.py,
line 72, in instances
rows = [process[0](row, None) for row in fetch]
File
/usr/lib/python2.7/site-packages/SQLAlchemy-0.8.1dev-py2.7.egg/sqlalchemy/orm/loading.py,
line 356, in _instance
tuple([row[column] for column in pk_cols])
File
/usr/lib/python2.7/site-packages/SQLAlchemy-0.8.1dev-py2.7.egg/sqlalchemy/engine/result.py,
line 71, in __getitem__
processor, obj, index = self._parent._key_fallback(key)
File
/usr/lib/python2.7/site-packages/SQLAlchemy-0.8.1dev-py2.7.egg/sqlalchemy/engine/result.py,
line 314, in _key_fallback
expression._string_or_unprintable(key))
sqlalchemy.exc.NoSuchColumnError: Could not locate column in row for column
'BAND_ORDER_OF_LOCAL_TESTING.order_id'
Is there a limit of table name in sqlalchemy?
How to solve this problem?
Best regards,
Evan
The class that you are querying here (BandOrderOfMonth) doesn't match
the class that you mentioned above, so it's difficult to know what is
going on. A full running example would make it easier to understand.
What database system are you working with? Different databases have
different rules about case sensitivity. From an SQL prompt, does
SELECT * from band_order_of_local_testing work, or give an error?
In general, SQLAlchemy assumes that if you specify a table name with
any upper-case characters, then the table name is case-sensitive and
so it will be quoted in any queries. If you specify the table name in
all lower-case, it is assumed to be case-insensitive:
http://docs.sqlalchemy.org/en/rel_0_8/core/schema.html#sqlalchemy.schema.Table
I suggest you try specifying the table name in lower-case and see if it works.
Hope that helps,
Simon
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] SQL insert error
Hello,
I have try to insert some data from a dict into my table and I have used
the description
on http://docs.sqlalchemy.org/en/rel_0_8/core/tutorial.html#insert-expressions
I run this command:
connect.execute( sqlalchemy.Table( tablename, metadata ).insert().values(
i.keys() ).compile(), **i )
where i is my dict. A changing to .valies( i ) create an error, so it is
different to the tutorial, because the tutorial shows IMHO that I can push
the dict full into the value
method. On my command I get an error:
'SQLCompiler' object has no attribute 'positiontup' (original cause:
AttributeError: 'SQLCompiler' object has no attribute 'positiontup')
'INSERT INTO mytable2 (fieldB, pk) VALUES (:fieldB, :pk)' [{'fieldB':
'blub', 'fieldA': '1234'}]
My i is defined like i = {fieldB : blub, fieldA : 1234}, the error
is also shown, if I used the dict values in the execute call.
I would like to add different fields to my value clause, the field names
are the keys of my dict and the dict values are the content which should be
inserted.
I don't understand, why the values() call add the pk field, it is not in
my dict and why generates the execute a list of my dict.
Thanks for help
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] views declarative?
Starting with the Wiki article on implementing views: http://www.sqlalchemy.org/trac/wiki/UsageRecipes/Views Is it possible to map Table instances back to classes defined through declarative_base()? I'm using SQLAlchemy 0.7.1. Thanks. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Querying using objects
Thank you Mike!
In fact, that's not my actual model - it is a little more complex than
that (specially the @setter), that's why my curiosity about using
hybrids and the possibility to query against an object.
Best regards,
Richard.
On 04/16/2013 09:34 PM, Michael Bayer wrote:
On Apr 16, 2013, at 9:30 AM, Richard Gerd Kuesters
rich...@humantech.com.br mailto:rich...@humantech.com.br wrote:
Hello all!
Ok, maybe people asks this a lot, but I wonder if it's possible to
perform a query using an object as a filter - and I searched for it,
didn't found anything close to my idea.
Simple dumb example code:
*class User(Base):**
**
** user_id = Column(Integer, Sequence(...), primary_key=True)**
** username = Column(Unicode)**
**
**
**class Subscription(Base):**
**
**subscription_id = Column(Integer, Sequence(...),
primary_key=True)**
**name = Column(unicode)**
**owner_id = Column(Integer, ForeignKey('user.user_id'),
nullable=False)**
**
**@hybrid_property**
**def owner(self):**
if not object_session(self):
return None
**return
object_session(self).query(User).filter(self.owner_id ==
User.user_id)**.first()
**
**@owner.setter**
**def owner(self, owner):**
**self.owner_id = owner.user_id if isinstance(owner, User)
else owner**
**if object_session(self):**
**object_session(self).commit()**
**
**# @owner.expression** # ???**
**
**
**# ok, so far *almost* good**
*
OK, all of that complexity with hybrid_property is not needed at all
here. Just say, owner = relationship(User), and you're done.
SQLAlchemy manages one-to-many, many-to-one, and many-to-many
automatically with relationship().
*
**
**# but, i would like to do _this_ instead**
**
**print session.query(Subscription).filter(Subscription.owner ==
new_user).all()*
I've tried it in so many ways that I feel dizzy. The only way I think
would be using @owner.expression to return User, but that didn't
the trick, it only appends WHERE false to the query, hehehe.
yeah just use relationship(), and you'd be using the first operator as
described right here in the ORM tutorial (which is a must-read):
http://docs.sqlalchemy.org/en/rel_0_8/orm/tutorial.html#common-relationship-operators
--
You received this message because you are subscribed to the Google
Groups sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send
an email to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] Re: views declarative?
James Hartley jjhart...@gmail.com writes: Starting with the Wiki article on implementing views: http://www.sqlalchemy.org/trac/wiki/UsageRecipes/Views Is it possible to map Table instances back to classes defined through declarative_base()? I'm not sure I understand the question, but given the reference to the recipe, I assume you are asking whether you can map a view onto a Python class using the declarative layer. If so, yes, SQLAlchemy does not care about how the underlying table is implemented, it will issue the same SQL in either cases. ciao, lele. -- nickname: Lele Gaifax | Quando vivrò di quello che ho pensato ieri real: Emanuele Gaifas | comincerò ad aver paura di chi mi copia. l...@metapensiero.it | -- Fortunato Depero, 1929. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] Logger
Hi all, I wonder if I can log all [and only] SQL statements made to the database (using echo=True), instead of sending the output of my script | program to a file. Cheers, Richard. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Re: views declarative?
On Wed, Apr 17, 2013 at 6:20 AM, Lele Gaifax l...@metapensiero.it wrote: James Hartley jjhart...@gmail.com writes: Is it possible to map Table instances back to classes defined through declarative_base()? ...I assume you are asking whether you can map a view onto a Python class using the declarative layer. If so, yes, SQLAlchemy does not care about how the underlying table is implemented, it will issue the same SQL in either cases. Thanks for your reply. Any hints on how to tie a class inheriting from Base to a Table? I have been unsuccessful in bridging the two. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Re: views declarative?
On Wed, Apr 17, 2013 at 2:59 PM, James Hartley jjhart...@gmail.com wrote: On Wed, Apr 17, 2013 at 6:20 AM, Lele Gaifax l...@metapensiero.it wrote: James Hartley jjhart...@gmail.com writes: Is it possible to map Table instances back to classes defined through declarative_base()? ...I assume you are asking whether you can map a view onto a Python class using the declarative layer. If so, yes, SQLAlchemy does not care about how the underlying table is implemented, it will issue the same SQL in either cases. Thanks for your reply. Any hints on how to tie a class inheriting from Base to a Table? I have been unsuccessful in bridging the two. Set __table__ to your Table instance: http://docs.sqlalchemy.org/en/rel_0_8/orm/extensions/declarative.html#using-a-hybrid-approach-with-table Simon -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] Re: Session.add doesn't work on Python 3.3.0?
I did some more testing and it appears to be Python 3.3.0 only. Indeed the exact same code with exactly the same SQLAlchemy version works on Python 3.3.1 but not in Python 3.3.0. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Re: Session.add doesn't work on Python 3.3.0?
I tested on python 3.3.0 exactly so you might want to check that something is strange with your python 3.3.0 environment. Sent from my iPhone On Apr 17, 2013, at 10:14 AM, Tim Cooijmans timcooijm...@gmail.com wrote: I did some more testing and it appears to be Python 3.3.0 only. Indeed the exact same code with exactly the same SQLAlchemy version works on Python 3.3.1 but not in Python 3.3.0. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] mysql dialect and contains
Can't seem to find any reference to anyone else having this issue, so it
must be something I'm doing incorrectly. In Mysql, the contains operator
doesnt seem to be translated properly by the dialect.
Docs state the following:
contains(other, **kwargs)¶
Implement the ‘contains’ operator.
In a column context, produces the clause LIKE '%other%'
But I'm getting the following:
print table.c.attribute.contains(test)
ciq_user_event.attribute LIKE '%%' || :attribute_1 || '%%'
It should be something more like this ('||' is not a concat operator in
mysql):
ciq_user_event.attribute LIKE CONCAT('%', :attribute_1, '%')
rather than .contains('test'), I can obviously do
.like(func.concat(%,test,%)).
But I'd really appreciate anyone reaching out and explaining what I'm
missing with the .contains operator in mysql.
sqlalchemy 0.8.0
For completeness:
ubuntu 12.04
python 2.7.3
mysqlconnector 1.0.9
AWS RDS, MySQL 5.5.27
Thanks!
Randy
(Actual code snippet below):
__
engine = sqlalchemy.create_engine('mysql+mysqlconnector://%s:%s@%s/%s' %
(config[user], config[pass], config[host], config[db]),connect_args
= {sql_mode: config[mode],'client_flags': [ClientFlag.SSL],'ssl_ca':
config[ssl_loc],'ssl_cert': None,'ssl_key': None})
metadata = sqlalchemy.MetaData()
metadata.reflect(bind=engine)
conn = engine.connect()
table = sqlalchemy.Table('ciq_user_event',metadata)
select =
sqlalchemy.select([table.c.partner_id]).where(table.c.attribute.contains(test))
print select
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] mysql dialect and contains
concat is a function that's specific to MySQL. So if you create an
expression against contains() without any specification that MySQL is in use,
you get a LIKE.
When the construct is compiled against the MySQL dialect, same as if it is
invoked by an engine that's associated with MySQL, you get concat().
from sqlalchemy.sql import table, column
from sqlalchemy.dialects import mysql
t = table('t', column('x'))
print t.c.x.contains(test)
print t.c.x.contains(test).compile(dialect=mysql.dialect())
t.x LIKE '%%' || :x_1 || '%%'
t.x LIKE concat(concat('%%', %s), '%%')
On Apr 17, 2013, at 2:28 PM, Randy Shults randy.c.shu...@gmail.com wrote:
Can't seem to find any reference to anyone else having this issue, so it must
be something I'm doing incorrectly. In Mysql, the contains operator doesnt
seem to be translated properly by the dialect.
Docs state the following:
contains(other, **kwargs)¶
Implement the ‘contains’ operator.
In a column context, produces the clause LIKE '%other%'
But I'm getting the following:
print table.c.attribute.contains(test)
ciq_user_event.attribute LIKE '%%' || :attribute_1 || '%%'
It should be something more like this ('||' is not a concat operator in
mysql):
ciq_user_event.attribute LIKE CONCAT('%', :attribute_1, '%')
rather than .contains('test'), I can obviously do
.like(func.concat(%,test,%)).
But I'd really appreciate anyone reaching out and explaining what I'm missing
with the .contains operator in mysql.
sqlalchemy 0.8.0
For completeness:
ubuntu 12.04
python 2.7.3
mysqlconnector 1.0.9
AWS RDS, MySQL 5.5.27
Thanks!
Randy
(Actual code snippet below):
__
engine = sqlalchemy.create_engine('mysql+mysqlconnector://%s:%s@%s/%s' %
(config[user], config[pass], config[host], config[db]),connect_args =
{sql_mode: config[mode],'client_flags': [ClientFlag.SSL],'ssl_ca':
config[ssl_loc],'ssl_cert': None,'ssl_key': None})
metadata = sqlalchemy.MetaData()
metadata.reflect(bind=engine)
conn = engine.connect()
table = sqlalchemy.Table('ciq_user_event',metadata)
select =
sqlalchemy.select([table.c.partner_id]).where(table.c.attribute.contains(test))
print select
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an
email to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] SQL insert error
On Apr 17, 2013, at 6:53 AM, Philipp Kraus philipp.kr...@flashpixx.de wrote: Hello, I have try to insert some data from a dict into my table and I have used the description on http://docs.sqlalchemy.org/en/rel_0_8/core/tutorial.html#insert-expressions I run this command: connect.execute( sqlalchemy.Table( tablename, metadata ).insert().values( i.keys() ).compile(), **i ) A few things. One, your Table has no columns declared. Maybe you've already declared this Table in that MetaData with some columns, in which case it's OK, you're just calling it back up, OK. Otherwise, the Table needs to have at least the columns in which you want to use for your insert() statement. The Table object then gets cached in that MetaData() so if this is really a throwaway table object I might want to use lowercase table(), column() (from sqlalchemy.sql import table, column). The direct reason for the error here is calling compile() on the Insert() construct - this is compiling it against the default dialect, which is not positional, and as you can see later on the database you're using expects positional bind parameters so that isn't working either. There's no need to call compile() explicitly, the engine/connection does that for you, using the correct dialect and other arguments that are specific to the execute() call. The way values() is being called here is also incorrect - values() accepts a dictionary, or list of dictionaries, not a list of names.It's not necessary to send the i dictionary twice - just the arguments passed to execute() will cause the compilation to be specific to those names: ins = table.insert() conn.execute(ins, **i) -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Querying using objects
OK, if you want to stick with hybrids, you can define your own
SomeClass.myattribute == someobject behavior by creating your own comparator,
see the docs at
http://docs.sqlalchemy.org/en/rel_0_8/orm/extensions/hybrid.html#building-custom-comparators
.
On Apr 17, 2013, at 7:15 AM, Richard Gerd Kuesters rich...@humantech.com.br
wrote:
Thank you Mike!
In fact, that's not my actual model - it is a little more complex than that
(specially the @setter), that's why my curiosity about using hybrids and the
possibility to query against an object.
Best regards,
Richard.
On 04/16/2013 09:34 PM, Michael Bayer wrote:
On Apr 16, 2013, at 9:30 AM, Richard Gerd Kuesters
rich...@humantech.com.br wrote:
Hello all!
Ok, maybe people asks this a lot, but I wonder if it's possible to perform
a query using an object as a filter - and I searched for it, didn't found
anything close to my idea.
Simple dumb example code:
class User(Base):
user_id = Column(Integer, Sequence(...), primary_key=True)
username = Column(Unicode)
class Subscription(Base):
subscription_id = Column(Integer, Sequence(...), primary_key=True)
name = Column(unicode)
owner_id = Column(Integer, ForeignKey('user.user_id'), nullable=False)
@hybrid_property
def owner(self):
if not object_session(self):
return None
return object_session(self).query(User).filter(self.owner_id ==
User.user_id).first()
@owner.setter
def owner(self, owner):
self.owner_id = owner.user_id if isinstance(owner, User) else owner
if object_session(self):
object_session(self).commit()
# @owner.expression # ???
# ok, so far *almost* good
OK, all of that complexity with hybrid_property is not needed at all here.
Just say, owner = relationship(User), and you're done. SQLAlchemy
manages one-to-many, many-to-one, and many-to-many automatically with
relationship().
# but, i would like to do _this_ instead
print session.query(Subscription).filter(Subscription.owner ==
new_user).all()
I've tried it in so many ways that I feel dizzy. The only way I think would
be using @owner.expression to return User, but that didn't the trick, it
only appends WHERE false to the query, hehehe.
yeah just use relationship(), and you'd be using the first operator as
described right here in the ORM tutorial (which is a must-read):
http://docs.sqlalchemy.org/en/rel_0_8/orm/tutorial.html#common-relationship-operators
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an
email to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an
email to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] How to cache autoloaded tables?
would need to see a working example, however I'd be careful about naming tables ALL_UPPERCASE like that, that means you're dealing with a case-sensitive name. Typically, names should be all lower case, indicating case insensitive, which is easier to deal with. Seems like there might be more than one version of the name my_table floating around. On Apr 17, 2013, at 4:53 AM, Davide Setti davide.se...@gmail.com wrote: Hi all, i'm trying to avoid to do the expensive autoload every time i access a table. I found this question http://stackoverflow.com/questions/11785457/sqlalchemy-autoloaded-orm-persistence . If i try to do the same i get errors. When i query (select) the Table created using unpickled metadata i get table name \MY_TABLE\ specified more than once, and in the PostgreSQL logs i find 'SELECT [cut] FROM TABLE, TABLE' statements... I'm using SQLAlchemy 0.8.0 and Postgresql 9.1. Any ideas? Thanks -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] views declarative?
On Apr 17, 2013, at 9:59 AM, James Hartley jjhart...@gmail.com wrote:
On Wed, Apr 17, 2013 at 6:20 AM, Lele Gaifax l...@metapensiero.it wrote:
James Hartley jjhart...@gmail.com writes:
Is it possible to map Table instances back to classes defined through
declarative_base()?
...I assume you are asking whether you can map a view onto a
Python class using the declarative layer. If so, yes, SQLAlchemy does
not care about how the underlying table is implemented, it will issue
the same SQL in either cases.
Thanks for your reply. Any hints on how to tie a class inheriting from Base
to a Table? I have been unsuccessful in bridging the two.
the typical form is:
Base = declarative_base()
some_table = Table('some_table', Base.metadata, Column('id', Integer,
primary_key=True))
class SomeClass(Base):
__table__ = some_table
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Logger
the easiest way is to use Python logging directly, where you can specify
configurations specific to individual loggers:
import logging
logger = logging.getLogger(sqlalchemy.engine)
handler = logging.handlers.FileHandler(somefile.log)
handler.setFormatter(logging.Formatter('%(asctime)s - %(name)s - %(levelname)s
- %(message)s'))
logger.addHandler(handler)
On Apr 17, 2013, at 9:45 AM, Richard Gerd Kuesters rich...@humantech.com.br
wrote:
Hi all,
I wonder if I can log all [and only] SQL statements made to the database
(using echo=True), instead of sending the output of my script | program to a
file.
Cheers,
Richard.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an
email to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] mysql dialect and contains
Awesome thank you.
So even though the Table object is attached to the metadata that was bound
to the engine that uses a mysql connection, I have to explicitly set the
dialect and compile when printing out the literal sql.
When I execute, is this handled seamlessly by the engine? Or do I need to
explicitly compile to the mysql dialect before executing?
Randy
On Wednesday, April 17, 2013 3:28:08 PM UTC-4, Michael Bayer wrote:
concat is a function that's specific to MySQL. So if you create an
expression against contains() without any specification that MySQL is in
use, you get a LIKE.
When the construct is compiled against the MySQL dialect, same as if it is
invoked by an engine that's associated with MySQL, you get concat().
from sqlalchemy.sql import table, column
from sqlalchemy.dialects import mysql
t = table('t', column('x'))
print t.c.x.contains(test)
print t.c.x.contains(test).compile(dialect=mysql.dialect())
t.x LIKE '%%' || :x_1 || '%%'
t.x LIKE concat(concat('%%', %s), '%%')
On Apr 17, 2013, at 2:28 PM, Randy Shults randy.c...@gmail.comjavascript:
wrote:
Can't seem to find any reference to anyone else having this issue, so it
must be something I'm doing incorrectly. In Mysql, the contains operator
doesnt seem to be translated properly by the dialect.
Docs state the following:
contains(other, **kwargs)¶
Implement the ‘contains’ operator.
In a column context, produces the clause LIKE '%other%'
But I'm getting the following:
print table.c.attribute.contains(test)
ciq_user_event.attribute LIKE '%%' || :attribute_1 || '%%'
It should be something more like this ('||' is not a concat operator in
mysql):
ciq_user_event.attribute LIKE CONCAT('%', :attribute_1, '%')
rather than .contains('test'), I can obviously do
.like(func.concat(%,test,%)).
But I'd really appreciate anyone reaching out and explaining what I'm
missing with the .contains operator in mysql.
sqlalchemy 0.8.0
For completeness:
ubuntu 12.04
python 2.7.3
mysqlconnector 1.0.9
AWS RDS, MySQL 5.5.27
Thanks!
Randy
(Actual code snippet below):
__
engine = sqlalchemy.create_engine('mysql+mysqlconnector://%s:%s@%s/%s' %
(config[user], config[pass], config[host], config[db]),connect_args
= {sql_mode: config[mode],'client_flags': [ClientFlag.SSL],'ssl_ca':
config[ssl_loc],'ssl_cert': None,'ssl_key': None})
metadata = sqlalchemy.MetaData()
metadata.reflect(bind=engine)
conn = engine.connect()
table = sqlalchemy.Table('ciq_user_event',metadata)
select =
sqlalchemy.select([table.c.partner_id]).where(table.c.attribute.contains(test))
print select
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an
email to sqlalchemy+...@googlegroups.com javascript:.
To post to this group, send email to sqlal...@googlegroups.comjavascript:
.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] mysql dialect and contains
On Apr 17, 2013, at 3:37 PM, Randy Shults randy.c.shu...@gmail.com wrote:
Awesome thank you.
So even though the Table object is attached to the metadata that was bound to
the engine that uses a mysql connection, I have to explicitly set the dialect
and compile when printing out the literal sql.
OK well here the answer is sort of. *Some* SQL constructs, essentially those
that are executable, will compile themselves according to the dialect they're
associated with; basically select(), insert(), update(), delete():
from sqlalchemy import Table, Column, MetaData, create_engine, String, select
m = MetaData()
t = Table('t', m, Column('x', String))
m.bind = create_engine(mysql://)
print select([t.c.x.contains(test)])
but just the column expression, since the expression isn't by itself
executable it doesn't spend the time searching for an Engine:
print t.c.x.contains(test)
So if you're only dealing with execute(), you should be OK. But this is
why I've really de-emphasized bound metadata overall, just makes things more
confusing.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] mysql dialect and contains
Thank you. That makes sense.
On Wednesday, April 17, 2013 at 4:27 PM, Michael Bayer wrote:
On Apr 17, 2013, at 3:37 PM, Randy Shults randy.c.shu...@gmail.com
(mailto:randy.c.shu...@gmail.com) wrote:
Awesome thank you.
So even though the Table object is attached to the metadata that was bound
to the engine that uses a mysql connection, I have to explicitly set the
dialect and compile when printing out the literal sql.
OK well here the answer is sort of. *Some* SQL constructs, essentially
those that are executable, will compile themselves according to the dialect
they're associated with; basically select(), insert(), update(), delete():
from sqlalchemy import Table, Column, MetaData, create_engine, String, select
m = MetaData()
t = Table('t', m, Column('x', String))
m.bind = create_engine(mysql://)
print select([t.c.x.contains(test)])
but just the column expression, since the expression isn't by itself
executable it doesn't spend the time searching for an Engine:
print t.c.x.contains(test)
So if you're only dealing with execute(), you should be OK. But this is why
I've really de-emphasized bound metadata overall, just makes things more
confusing.
--
You received this message because you are subscribed to a topic in the Google
Groups sqlalchemy group.
To unsubscribe from this topic, visit
https://groups.google.com/d/topic/sqlalchemy/ZhksMS_1DCU/unsubscribe?hl=en.
To unsubscribe from this group and all its topics, send an email to
sqlalchemy+unsubscr...@googlegroups.com
(mailto:sqlalchemy+unsubscr...@googlegroups.com).
To post to this group, send email to sqlalchemy@googlegroups.com
(mailto:sqlalchemy@googlegroups.com).
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] How to map a oracle table with long table name
Hi Simon,
Sorry, my bad.
I follow your tips to modify my program. It works.
#!/usr/bin/env python
# -*- coding:utf-8 -*-
from sqlalchemy import create_engine
from sqlalchemy import Table, Column, Integer, String, MetaData,
ForeignKey, \
DateTime
from sqlalchemy.dialects.oracle import NUMBER, VARCHAR2, DATE
from sqlalchemy.orm import mapper
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class BandOrderOfLocalTesting(Base):
__tablename__ = band_order_of_local_testing
order_id = Column(order_id, NUMBER(18), primary_key=True)
order_no = Column(order_no, VARCHAR2(255))
def __init__(self, order_id, order_no):
self.order_id = order_id
self.order_no = order_no
def repr(self):
return BandOrderOfLocalTesting'%s', '%s' % (self.order_id,
self.order_no)
engine = create_engine('oracle+cx_oracle://user:user@oracle_test',
echo=True, convert_unicode=True, encoding='utf-8')
Session = sessionmaker(bind=engine)
session = Session()
instance = session.query(BandOrderOfLocalTesting).first()
print(instance)
Thanks a lot.
On 04/17/2013 06:00 PM, Simon King wrote:
On Wed, Apr 17, 2013 at 2:38 AM, Evan Jon evanjon@gmail.com wrote:
Hello all,
I want to map a table whose name is BAND_ORDER_OF_LOCAL_TESTING.
class BandOrderOfLocalTesting(Base):
__TABLENAME__ = 'BAND_ORDER_OF_LOCAL_TESTING'
order_id = Column(order_id, Number(18), primary_key=True)
...
I assume the above is a typo - you need to be setting __tablename__,
not __TABLENAME__.
Each time I got the following message:
013-04-17 09:26:55,857 INFO sqlalchemy.engine.base.Engine {'ROWNUM_1': 1}
Traceback (most recent call last):
File tables.py, line 108, in module
instance = session.query(BandOrderOfMonth).first()
File
/usr/lib/python2.7/site-packages/SQLAlchemy-0.8.1dev-py2.7.egg/sqlalchemy/orm/query.py,
line 2181, in first
ret = list(self[0:1])
File
/usr/lib/python2.7/site-packages/SQLAlchemy-0.8.1dev-py2.7.egg/sqlalchemy/orm/query.py,
line 2048, in __getitem__
return list(res)
File
/usr/lib/python2.7/site-packages/SQLAlchemy-0.8.1dev-py2.7.egg/sqlalchemy/orm/loading.py,
line 72, in instances
rows = [process[0](row, None) for row in fetch]
File
/usr/lib/python2.7/site-packages/SQLAlchemy-0.8.1dev-py2.7.egg/sqlalchemy/orm/loading.py,
line 356, in _instance
tuple([row[column] for column in pk_cols])
File
/usr/lib/python2.7/site-packages/SQLAlchemy-0.8.1dev-py2.7.egg/sqlalchemy/engine/result.py,
line 71, in __getitem__
processor, obj, index = self._parent._key_fallback(key)
File
/usr/lib/python2.7/site-packages/SQLAlchemy-0.8.1dev-py2.7.egg/sqlalchemy/engine/result.py,
line 314, in _key_fallback
expression._string_or_unprintable(key))
sqlalchemy.exc.NoSuchColumnError: Could not locate column in row for column
'BAND_ORDER_OF_LOCAL_TESTING.order_id'
Is there a limit of table name in sqlalchemy?
How to solve this problem?
Best regards,
Evan
The class that you are querying here (BandOrderOfMonth) doesn't match
the class that you mentioned above, so it's difficult to know what is
going on. A full running example would make it easier to understand.
What database system are you working with? Different databases have
different rules about case sensitivity. From an SQL prompt, does
SELECT * from band_order_of_local_testing work, or give an error?
In general, SQLAlchemy assumes that if you specify a table name with
any upper-case characters, then the table name is case-sensitive and
so it will be quoted in any queries. If you specify the table name in
all lower-case, it is assumed to be case-insensitive:
http://docs.sqlalchemy.org/en/rel_0_8/core/schema.html#sqlalchemy.schema.Table
I suggest you try specifying the table name in lower-case and see if it works.
Hope that helps,
Simon
--
Best Regards,
Qiu Dong
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
