[sqlalchemy] Re: API that allows me to do additional database operations just before insert execution for SQL Expression
Also based on certain conditions I would not like to execute the insert at all... So is there a way to just return from execute method of MyProxy class with out actually executing the insert statement? -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Oracle reserved words
I have a problem with oracle column names that are oracle reserved
words (http://download.oracle.com/docs/cd/B19306_01/em.102/b40103/
app_oracle_reserved_words.htm). I read through the archive and found:
https://groups.google.com/group/sqlalchemy/browse_thread/thread/5e2699594c73fe1c/41f35436c9149818?hl=en&lnk=gst&q=oracle+reserved+words#41f35436c9149818
sadly I have the same problem with the current quoting of oracle
reserved words as bindvars, i.e. I get something
like this:
---8<---
DatabaseError: (DatabaseError) ORA-01036: illegal variable name/number
'UPDATE SET "Group"=:"Group"' {u'"Group"': u'Undefined'}
---8<---
The workaround with a different local key works, but is IMHO not
ideal.
Is there any other way to get around this issue? I noticed that an
unquoted bindvar does work on this oracle db.
Kind Regards,
Sirko
--
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
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] datetime is causing error when accesing MSSQL via sqlalchemy - pyodbc/TDS
that error is generated by TDS/pyodbc and is not related to SQLAlchemy.
Here's your test case with pyodbc alone, the pyodbc or FreeTDS lists might have
further info:
import pyodbc
conn =
pyodbc.connect("DRIVER={FreeTDS};SERVER=myserver;DATABASE=mydatabase;PORT=1435;UID=MYUSER;PWD=MYPASS1")
cursor = conn.cursor()
cursor.execute("select * from test_product")
print cursor.fetchone()
On Apr 27, 2011, at 8:44 PM, Thang Nguyen wrote:
> Hi,
> I am getting an error accessing MSSQL database using
> sqlalchemy/pyodbc/TDS driver. After narrowing the case, looks like it
> has something to do with datetime datatype. If I deldete the
> created_at column (datetime) then the same code will work.
> Any suggestion for further debugging? Thanks in advance. -- TN
>
> pyodbc: pyodbc-2.1.8-py2.5-linux-x86_64.egg
> TDS: freetds-0.82
>
> here is the python code:
> ==
>
> import ePackages
> import pyodbc
> import sqlalchemy
> from sqlalchemy import *
> import sys, os
>
> def pycon():
>return
> pyodbc.connect("DRIVER={FreeTDS};SERVER=myserver;DATABASE=mydatabase;PORT=1435;UID=MYUSER;PWD=MYPASS1")
>
> engine = create_engine('mssql://', creator=pycon)
> metadata = MetaData(bind=engine)
> result = engine.execute("select * from test_product")
> for e in result:
>print e.device
>
> Here is the error:
>
> Traceback (most recent call last):
> File "testodbc2.py", line 14, in
>for e in result:
> File "/nfs/test/ePackages/sqlalchemy/engine/base.py", line 1548, in __iter__
> File "/nfs/test/ePackages/sqlalchemy/engine/base.py", line 1668, in fetchone
> File "/nfs/test/ePackages/sqlalchemy/engine/base.py", line 931, in
> _handle_dbapi_exception
> sqlalchemy.exc.DBAPIError: (Error) ('HY000', 'The driver did not
> supply an error!') None None
>
> Here is the database:
> ===
> Column name Data Type Value
> device varchar(8) xyz
> revision varchar(8) ttt
> process varchar(10)abc
> created_atdatetime4/26/2011 12:20:00 PM
>
> --
> 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
> sqlalchemy+unsubscr...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] datetime is causing error when accesing MSSQL via sqlalchemy - pyodbc/TDS
Hi,
I am getting an error accessing MSSQL database using
sqlalchemy/pyodbc/TDS driver. After narrowing the case, looks like it
has something to do with datetime datatype. If I deldete the
created_at column (datetime) then the same code will work.
Any suggestion for further debugging? Thanks in advance. -- TN
pyodbc: pyodbc-2.1.8-py2.5-linux-x86_64.egg
TDS: freetds-0.82
here is the python code:
==
import ePackages
import pyodbc
import sqlalchemy
from sqlalchemy import *
import sys, os
def pycon():
return
pyodbc.connect("DRIVER={FreeTDS};SERVER=myserver;DATABASE=mydatabase;PORT=1435;UID=MYUSER;PWD=MYPASS1")
engine = create_engine('mssql://', creator=pycon)
metadata = MetaData(bind=engine)
result = engine.execute("select * from test_product")
for e in result:
print e.device
Here is the error:
Traceback (most recent call last):
File "testodbc2.py", line 14, in
for e in result:
File "/nfs/test/ePackages/sqlalchemy/engine/base.py", line 1548, in __iter__
File "/nfs/test/ePackages/sqlalchemy/engine/base.py", line 1668, in fetchone
File "/nfs/test/ePackages/sqlalchemy/engine/base.py", line 931, in
_handle_dbapi_exception
sqlalchemy.exc.DBAPIError: (Error) ('HY000', 'The driver did not
supply an error!') None None
Here is the database:
===
Column name Data Type Value
device varchar(8) xyz
revision varchar(8) ttt
process varchar(10)abc
created_atdatetime4/26/2011 12:20:00 PM
--
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
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Multi-table (polymorphic?) row-specific relations
Ah I was really close. This worked: class TagMixin(object): @declared_attr def tags(cls): class Tag(BaseModel): __tablename__ = "tag_%s" % cls.__tablename__ id = Column(types.Integer, primary_key=True) time_created = Column(types.DateTime, default=datetime.now, nullable=False) row_id = Column(types.Integer, ForeignKey(cls.id), index=True) name = Column(types.String, nullable=False, index=True) cls.TagClass = Tag return orm.relationship(Tag, backref='tagged') class User(BaseModel, TagMixin): __tablename__ = 'user' id = Column(types.Integer, primary_key=True) ... -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: sqlite transaction isolation, select for update, race condition
On Apr 27, 10:41 am, Michael Bayer wrote:
>
> then yes, for your case this is exactly the pysqlite bug Daniel mentions:
> http://code.google.com/p/pysqlite/issues/detail?id=21. Pysqlite doesn't open
> the transaction until DML is encountered specifically to reduce file locks.
> This should be configurable, as well as the type of BEGIN emitted.
> SQLAlchemy itself never emits BEGIN.
oic, so if pysqlite was doing the right thing with the explicit BEGIN
I would expect to be able to do something like this:
engine = create_engine('sqlite:///foo.db',
connect_args={'isolation_level': 'IMMEDIATE'})
... and all connections would automatically issue the correct begin
statement and acquire a reserved lock at the beginning of the
transaction. But as it is, they don't do anything until they get down
to the update, and it's kind of a disaster.
Thanks for the info,
-clayg
--
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
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Multi-table (polymorphic?) row-specific relations
this works (been working on getting this kind of thing into recipes/docs/books)
from sqlalchemy import Column, Integer, ForeignKey, String
from sqlalchemy.ext.declarative import declarative_base, declared_attr
from sqlalchemy.orm import relationship
Base = declarative_base()
class Mixin(object):
@declared_attr
def foob(cls):
cls.Blah = Blah = type("%sBlah" % cls.__name__,
(Base, ),
dict(
__tablename__ = 'blah_%s' % cls.__tablename__,
id = Column(Integer, primary_key=True),
name_id = Column(Integer, ForeignKey(cls.id))
)
)
return relationship(Blah)
class Name(Mixin, Base):
__tablename__ = "name"
id = Column(Integer, primary_key=True)
name = Column(String)
class Name2(Mixin, Base):
__tablename__ = "name2"
id = Column(Integer, primary_key=True)
name = Column(String)
print Name2.foob.contains(Name2.Blah())
On Apr 25, 2011, at 9:28 PM, Andrey Petrov wrote:
> Looks like this almost-sorta works:
>
> class TagMixin(object):
> @declared_attr
> def TagClass(cls):
> class Tag(BaseModel):
> __tablename__ = "tag_%s" % cls.__tablename__
>
> id = Column(types.Integer, primary_key=True)
> time_created = Column(types.DateTime, default=datetime.now,
> nullable=False)
>
> row_id = Column(types.Integer, ForeignKey(cls.id), index=True)
> name = Column(types.String, nullable=False, index=True)
>
> # This part breaks with... sqlalchemy.exc.InvalidRequestError: Table
> 'tag_user' is already defined for this MetaData instance.
> #@declared_attr
> #def tags(cls):
> #return orm.relationship(cls.TagClass, backref='tagged')
>
> class User(BaseModel, TagMixin):
> __tablename__ = 'user'
>
> id = Column(types.Integer, primary_key=True)
> ...
>
> The appropriate tables do get generated on create_all(). But as soon as I
> touch the User.TagClass attribute, it barfs with the same error again:
>
> InvalidRequestError: Table 'tag_user' is already defined for this MetaData
> instance. Specify 'extend_existing=True' to redefine options and columns on
> an existing Table object.
>
>
>
>
> --
> 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
> sqlalchemy+unsubscr...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Multi-table (polymorphic?) row-specific relations
On Apr 25, 2011, at 9:10 PM, Andrey Petrov wrote: > One more thought: > > Is there a sane way to hide a schema object within another schema object? > > Specifically, I want to make a factory method (or maybe a class decorator) > which generates these Tag schemas onto specific tables. Something along the > lines of: > > @Taggable > class User(BaseModel) > __tablename__ = "user" > > The Taggable class decorator would generate another table called "tag_user" > and attach the appropriate relationship attributes to the User class. I'm > thinking it'll also attach the anonymous Tag declarative class to the User > class as User.TagClass or somesuch. > > Is this reasonable? Is there a better way to do this than monkey-patching the > User class in the Taggable decorator? > > One fantasy I had was if you could do... > > class TaggableMixin(object): > @declared_attr > def TaggableClass(cls): > class Tag(BaseModel): > __tablename__ = "tag_%s" % cls.__tablename__ > # ... schema here > > class User(BaseModel, TaggableMixin): > # ... > > Perhaps I should try it but I don't imagine this will work. you should create tables inside of @declared_attr. Usually I'm using straight Table in there but declared class should work too (though you might get a warning about the same class created twice, unless you do some kind of uniqifying, it probably would be nice to fix declarative to not call any @declared_attr twice). For the "already have this table in this metadata" issue use the table argument "keep_existing=True". Otherwise, I do this exact pattern you have above except explicitly: class UserTag(TagBase, Base): __tablename__ = 'user_tag' class User(Taggable, Base): tag_cls = UserTag TagBase and Taggable do the rest of the work. *maybe* this works too: class User(Taggable, Base): class tag_cls(TagBase, Base): __tablename__ = 'user_tag' You could also use an event like "instrument_class" or "mapper_configured" , associated with mapper() and would look for Taggable subclasses. -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: sqlite transaction isolation, select for update, race condition
On Apr 27, 9:37 am, Daniel Holth wrote: > Is this pysqlite issue about SELECT not starting a transaction > related?http://code.google.com/p/pysqlite/issues/detail?id=21 Hrmmm... well... that's interesting... it might be related, but maybe not? I'm not setting the isolation level when I create the engine. I don't think the explicit begin would help unless I specifically tell it to begin immediate transaction. I tried adding isolation_level='SERIALIZABLE' to the kwargs when I call create_engine, no change. I changed my explicit 'BEGIN IMMEIDATE TRANSACTION' to just a 'BEGIN' - and got a bunch of database is locked errors. I removed the explicit BEGIN all together and I was back to my race condition - which seems to be closer to what that bug is about... Still... good info, 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Re: sqlite transaction isolation, select for update, race condition
On Apr 27, 2011, at 11:18 AM, Clay Gerrard wrote: > > > On Apr 27, 9:19 am, Michael Bayer wrote: >> SQLite doesn't have support for SELECT..FOR UPDATE, and with_lockmode() >> ultimately has no impact when using SQLite as nothing is rendered. > > IDK why sqlite doesn't support a way to elevate the lock on a select > in the middle of a deferred transaction like it does with updates and > inserts; but yeah, better the dialect to noop than raise a syntax > error. > >> SQLite's concurrency model is based on a lock of the entire database file - >> hardly a row lock - I wouldn't think such a strategy applies on that backend >> ? > > I kinda feel the opposite, if I can't have a row lock - then yeah, go > ahead and lock the whole database! > > But here's the rub, generally speaking a select isn't going to place a > read lock (or "reserved lock") on a sqlite database - you get a > "shared" lock, so other processes can't do insert's or updates, but > they could still select the row I'm about to update even while I'm in > an uncommited transaction. In order to prevent another process from > reading the row I'm about to update form the database until I'm done > updating it - it seems that sqlite expects this unconventional "begin > IMMEDIATE transaction" notation, which will lock further reads against > the database until I commit. > > I'm just thinking there might be a better way in sqlalchemy to get at > these weird sqlite transaction levels - > http://www.sqlite.org/lang_transaction.html > - other than doing a session.execute? - then yes, for your case this is exactly the pysqlite bug Daniel mentions: http://code.google.com/p/pysqlite/issues/detail?id=21 . Pysqlite doesn't open the transaction until DML is encountered specifically to reduce file locks. This should be configurable, as well as the type of BEGIN emitted. SQLAlchemy itself never emits BEGIN. -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: sqlite transaction isolation, select for update, race condition
On Apr 27, 9:19 am, Michael Bayer wrote: > SQLite doesn't have support for SELECT..FOR UPDATE, and with_lockmode() > ultimately has no impact when using SQLite as nothing is rendered. IDK why sqlite doesn't support a way to elevate the lock on a select in the middle of a deferred transaction like it does with updates and inserts; but yeah, better the dialect to noop than raise a syntax error. > SQLite's concurrency model is based on a lock of the entire database file - > hardly a row lock - I wouldn't think such a strategy applies on that backend ? I kinda feel the opposite, if I can't have a row lock - then yeah, go ahead and lock the whole database! But here's the rub, generally speaking a select isn't going to place a read lock (or "reserved lock") on a sqlite database - you get a "shared" lock, so other processes can't do insert's or updates, but they could still select the row I'm about to update even while I'm in an uncommited transaction. In order to prevent another process from reading the row I'm about to update form the database until I'm done updating it - it seems that sqlite expects this unconventional "begin IMMEDIATE transaction" notation, which will lock further reads against the database until I commit. I'm just thinking there might be a better way in sqlalchemy to get at these weird sqlite transaction levels - http://www.sqlite.org/lang_transaction.html - other than doing a session.execute? - -clayg -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Facing problem with aliasing while using union_all
On Apr 27, 2011, at 10:58 AM, monster jacker wrote:
> >>The Query should return to you tuples that have names like "row_num" and
> >>"test_msg", which are linked to the "anon" names that >>it generates:
>
> >>for row in myquery:
> >>print row.row_num, row.test_msg
>
> >>that is, the "anon_x" names do not matter. they are an artifact of how the
> >>Query does its work and the result rows are translated >>back to the
> >>constructs you gave it originally.
>
> If i have method as below
>
> def second_method(self):
> for tbl in Table: for loop is used
> since we have 3 tables and doing union of them
> qry = some_method()
> query = query.union_all(qry) if query else qry
> return result_query
>
> def some_method(self):
> query = self.session.query(Table.row_num.label('row_num'),
>Table.test_msg.label('test_msg'),
>Table.crt_dt.label('crt_dt'),
>Table2.name_file.label('name_file')).join(
> (Table2, Table2.some_idn == Table.some_idn))
> return query
>
> record = second_method()
>
>
> The above method returns the query object . when we try record[0] we will
> get the result set
> [(1, None, datetime.datetime(2011, 2, 24, 12, 37, 58, 123000), 'test.txt')]
>
> when i try to get the keys record[0].__dict__.keys() it gives the result
>
> [u'%(215049772 anon)s_name_file', u'%(215049772 anon)s_crt_dt', u'%(215049772
> anon)s_row_num', '_labels', u'%(215049772 anon)s_test_msg']
>
> so as you mentioned in previous mail if i try
>
> for row in record:
> print row.row_num, row.test_msg
>
> i am getting the attribute error : *** AttributeError: 'NamedTuple'
> object has no attribute 'row_num'
also, while we're waiting for the bug, immediate workaround is:
import operator
def named_tuple(*names):
return type("MyTuple", (tuple,),
dict(
(name, property(operator.itemgetter(i)))
for i, name in enumerate(names)
)
)
my_workaround_row = named_tuple('row_num', 'test_msg', 'crt_dt', 'name_file')
return [my_workaround_row(row) for row in query]
--
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
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Facing problem with aliasing while using union_all
On Apr 27, 2011, at 10:58 AM, monster jacker wrote:
> >>The Query should return to you tuples that have names like "row_num" and
> >>"test_msg", which are linked to the "anon" names that >>it generates:
>
> >>for row in myquery:
> >>print row.row_num, row.test_msg
>
> >>that is, the "anon_x" names do not matter. they are an artifact of how the
> >>Query does its work and the result rows are translated >>back to the
> >>constructs you gave it originally.
>
> If i have method as below
>
> def second_method(self):
> for tbl in Table: for loop is used
> since we have 3 tables and doing union of them
> qry = some_method()
> query = query.union_all(qry) if query else qry
> return result_query
>
> def some_method(self):
> query = self.session.query(Table.row_num.label('row_num'),
>Table.test_msg.label('test_msg'),
>Table.crt_dt.label('crt_dt'),
>Table2.name_file.label('name_file')).join(
> (Table2, Table2.some_idn == Table.some_idn))
> return query
>
> record = second_method()
>
>
> The above method returns the query object . when we try record[0] we will
> get the result set
> [(1, None, datetime.datetime(2011, 2, 24, 12, 37, 58, 123000), 'test.txt')]
>
> when i try to get the keys record[0].__dict__.keys() it gives the result
>
> [u'%(215049772 anon)s_name_file', u'%(215049772 anon)s_crt_dt', u'%(215049772
> anon)s_row_num', '_labels', u'%(215049772 anon)s_test_msg']
>
> so as you mentioned in previous mail if i try
>
> for row in record:
> print row.row_num, row.test_msg
>
> i am getting the attribute error : *** AttributeError: 'NamedTuple'
> object has no attribute 'row_num'
thats a bug. Can you please formulate a full, reproducible, succinct test case
and attach here , or create a new ticket in trac ? Also can you confirm this
is 0.7 you are testing with ?
--
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
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Facing problem with aliasing while using union_all
>>The Query should return to you tuples that have names like "row_num" and
"test_msg", which are linked to the "anon" names that >>it generates:
>> for row in myquery:
>> print row.row_num, row.test_msg
>>that is, the "anon_x" names do not matter. they are an artifact of how
the Query does its work and the result rows are translated >>back to the
constructs you gave it originally.
If i have method as below
def second_method(self):
for tbl in Table: for loop is used
since we have 3 tables and doing union of them
qry = some_method()
query = query.union_all(qry) if query else qry
return result_query
def some_method(self):
query = self.session.query(Table.row_num.label('row_num'),
Table.test_msg.label('test_msg'),
Table.crt_dt.label('crt_dt'),
Table2.name_file.label('name_file')).join(
(Table2, Table2.some_idn == Table.some_idn))
return query
record = second_method()
The above method returns the query object . when we try record[0] we will
get the result set
[(1, None, datetime.datetime(2011, 2, 24, 12, 37, 58, 123000), 'test.txt')]
when i try to get the keys record[0].__dict__.keys() it gives the result
[u'%(215049772 anon)s_name_file', u'%(215049772 anon)s_crt_dt',
u'%(215049772 anon)s_row_num', '_labels', u'%(215049772 anon)s_test_msg']
so as you mentioned in previous mail if i try
for row in record:
print row.row_num, row.test_msg
i am getting the attribute error : *** AttributeError: 'NamedTuple'
object has no attribute 'row_num'
On Mon, Apr 18, 2011 at 4:40 PM, Michael Bayer wrote:
>
> On Apr 18, 2011, at 1:50 AM, monster jacker wrote:
>
> Hi Micheal,
>>
> As per your comment you want to say this issue whatever i am
> facing is resolved in 0.7 version of sqlalchemy?
> If i use the 0.7beta version this issue can be resolved?
>
>
> that is the case
>
>
>
> What i actually want to say is since i am refering in the code the
> column names as "row_num " but the query after the union is aliasing it as
> anon_1.anon_2_row_num so we are not getting any result set.
>
>
> I dont understand what "not getting any result set means". Here is the
> structure you showed me:
>
> select * from
> (
> select * from table1
> union all
> select * from table3
> ) as anon2
> union all
> select * from table4
> ) as anon1
>
> this looks kind of like you're saying
> query.union_all(query2).union_all(query3).UNION ALL is entirely
> commutative and associative. The above returns the equivalent rows as:
>
> select * from table1 union all select * from table3 union all select * from
> table4
>
> so you should get the same number of result rows as if no aliasing were
> used.
>
>
> if i change the code with "anon_1.anon_2_row_num" at that time if there
> are more than 2 unions at that time i wil get
> "anon_1.anon_2_anon_3_row_num" at that time will get the same issue what i
> am facing.so i want to know is there any way so that i can avoid aliasing
> as "anon_1_anon_2_row_num" for all the columns at the top level query.
>
>
> The Query should return to you tuples that have names like "row_num" and
> "test_msg", which are linked to the "anon" names that it generates:
>
> for row in myquery:
> print row.row_num, row.test_msg
>
> that is, the "anon_x" names do not matter. they are an artifact of how the
> Query does its work and the result rows are translated back to the
> constructs you gave it originally.
>
> If you'd like a SQL string that is constructed to an exact specification,
> use the SQL expression language instead of the ORM Query object.
>
> --
> 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
> sqlalchemy+unsubscr...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] sqlite transaction isolation, select for update, race condition
Is this pysqlite issue about SELECT not starting a transaction related? http://code.google.com/p/pysqlite/issues/detail?id=21 -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] sqlite transaction isolation, select for update, race condition
SQLite doesn't have support for SELECT..FOR UPDATE, and with_lockmode()
ultimately has no impact when using SQLite as nothing is rendered. SQLite's
concurrency model is based on a lock of the entire database file - hardly a row
lock - I wouldn't think such a strategy applies on that backend ?
On Apr 27, 2011, at 1:02 AM, Clay Gerrard wrote:
> Yesterday I was working with some code that needed a "select ... for
> update" concept to avoid a race condition.
> Adding .with_lockmode('update') works a treat on InnoDB and Postgres,
> but for sqlite I end up having to sneak in a "if session.bind.name ==
> 'sqlite'; session.execute('begin immediate transaction')" before doing
> the select.
>
> This seems to work for now, but it feels like cheating. Better way to
> do this?
>
> -clayg
>
> --
> 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
> sqlalchemy+unsubscr...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: API that allows me to do additional database operations just before insert execution for SQL Expression
I want to some updates before doing any insert. So I needed the above. Now the issue I am facing is that I need all these updates and the current insert to be in a transaction. That is either all should be successful or none. How is that possible with a connection proxy (something like below)? === class MyProxy(ConnectionProxy): def execute(self, conn, execute, clauseelement, *multiparams, **params): if isinstance(clauseelement, Insert): trans = self.connection.begin() try: # Do some updates # Execute the current insert also ... trans.commit() except: trans.rollback() = -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
RE: [sqlalchemy] Appending a where clause to a query
> -Original Message-
> From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com]
> On Behalf Of Luka Novsak
> Sent: 27 April 2011 05:32
> To: sqlalchemy
> Subject: [sqlalchemy] Appending a where clause to a query
>
> The docs on Select's where() method say:
>
> > return a new select() construct with the given expression added to
> its WHERE clause, joined to the existing clause via AND, if any.
>
Note: "return a new select() construct"
> But this doesn't seem to happen.
>
> This is my code:
>
> def posts_per_dow(self, start_date=None, end_date=None):
> q = select([func.date_part('isodow', t_posts.c.created_at),
> func.count(t_posts.c.id)],
>
> t_posts.c.user_id==self.id).group_by('1').order_by('1')
>
> if start_date: q.where(t_posts.c.created_at>=start_date)
> if end_date: q.where(t_posts.c.created_at
> Only the first where clause is actually used when I execute the query.
>
> If I'm just going about it wrong, then how do I append a where clause
> like this?
>
You need to store the return value of the 'where' method. eg:
if start_date:
q = q.where(t_posts.c.created_at>=start_date)
if end_date:
q = q.where(t_posts.c.created_athttp://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] sqlite transaction isolation, select for update, race condition
Yesterday I was working with some code that needed a "select ... for
update" concept to avoid a race condition.
Adding .with_lockmode('update') works a treat on InnoDB and Postgres,
but for sqlite I end up having to sneak in a "if session.bind.name ==
'sqlite'; session.execute('begin immediate transaction')" before doing
the select.
This seems to work for now, but it feels like cheating. Better way to
do this?
-clayg
--
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
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Appending a where clause to a query
The docs on Select's where() method say:
> return a new select() construct with the given expression added to its WHERE
> clause, joined to the existing clause via AND, if any.
But this doesn't seem to happen.
This is my code:
def posts_per_dow(self, start_date=None, end_date=None):
q = select([func.date_part('isodow', t_posts.c.created_at),
func.count(t_posts.c.id)],
t_posts.c.user_id==self.id).group_by('1').order_by('1')
if start_date: q.where(t_posts.c.created_at>=start_date)
if end_date: q.where(t_posts.c.created_athttp://groups.google.com/group/sqlalchemy?hl=en.
