Re: [sqlalchemy] Potential issue with SQLAlchemy/cx_Oracle (under Python 3)
On Fri, Feb 8, 2013, at 18:43, Michael Bayer wrote: > > On Feb 8, 2013, at 12:31 PM, Michael Bayer wrote: > > > > On Feb 8, 2013, at 10:29 AM, Thijs Engels wrote: > > > > > > Works fine with both Python 2.7 and 3.3. Running the (even more) direct > > > statement as in your email end up with the same results. > > > > > > More than willing to try things out if the feedback on this is > > > considered useful. > > > > your program will work right now if you pass auto_setinputsizes=False to > > create_engine(). > > > > An encoding step in setinputsizes() will be removed in the SQLAlchemy > > dialect to resolve. > > this fix is in tip and will be in 0.8.0. I can confirm the additional argument for create_engine() works. Thank you very much for the quick response, answer and fix! Thijs -- 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] How to create a temporary table in SA ORM
In SQL language, we can create a temporary table like: CREATE TEMPORARY TABLE t1 ( select t2.id, t3.name from t2, t3 where t2.id = t3.id); Can we do the same thing in SA ORM? I searched online, and some answers seemed not clear to me. By the way, when we should use view, and when to use temporary table? Thanks and Best Regards! YHL -- 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] orm_exc.NoResultFound safe with autoflush?
If I could have Python3's nice system of chaining exceptions all the time, this would be an easy one. But a little hesitant at the moment. On Feb 8, 2013, at 1:01 PM, Kent wrote: > right... that's exactly what I've determined to do for my session subclass. > > On Friday, February 8, 2013 12:34:36 PM UTC-5, Michael Bayer wrote: > > On Feb 8, 2013, at 12:22 PM, Kent wrote: > > > We often use this pattern: > > > > try: > > session.query().one() > > except orm_exc.NoResultFound: > > gracefully deal with it > > > > If the query() execution causes an autoflush, I just want to make sure that > > an autoflush will never raise orm_exc.NoResultFound, or we could be > > catching the wrong error. Were that the case, to be safe, we'd always > > need: > > > > session.flush() > > try: > > session.query().one() > > except orm_exc.NoResultFound: > > gracefully deal with it > > > Well, it wont raise that right now, no, but if you had something going on in > a flush event that did, then it could. > > I suppose flush() should be wrapping that kind of exception so that this use > case can proceed. > > > > -- > 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] orm_exc.NoResultFound safe with autoflush?
right... that's exactly what I've determined to do for my session subclass. On Friday, February 8, 2013 12:34:36 PM UTC-5, Michael Bayer wrote: > > > On Feb 8, 2013, at 12:22 PM, Kent wrote: > > > We often use this pattern: > > > > try: > > session.query().one() > > except orm_exc.NoResultFound: > > gracefully deal with it > > > > If the query() execution causes an autoflush, I just want to make sure > that an autoflush will never raise orm_exc.NoResultFound, or we could be > catching the wrong error. Were that the case, to be safe, we'd always > need: > > > > session.flush() > > try: > > session.query().one() > > except orm_exc.NoResultFound: > > gracefully deal with it > > > Well, it wont raise that right now, no, but if you had something going on > in a flush event that did, then it could. > > I suppose flush() should be wrapping that kind of exception so that this > use case can proceed. > > > -- 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] Potential issue with SQLAlchemy/cx_Oracle (under Python 3)
On Feb 8, 2013, at 12:31 PM, Michael Bayer wrote: > > On Feb 8, 2013, at 10:29 AM, Thijs Engels wrote: > >> >> Works fine with both Python 2.7 and 3.3. Running the (even more) direct >> statement as in your email end up with the same results. >> >> More than willing to try things out if the feedback on this is >> considered useful. >> > > > your program will work right now if you pass auto_setinputsizes=False to > create_engine(). > > An encoding step in setinputsizes() will be removed in the SQLAlchemy dialect > to resolve. this fix is in tip and will be in 0.8.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] orm_exc.NoResultFound safe with autoflush?
On Feb 8, 2013, at 12:22 PM, Kent wrote: > We often use this pattern: > > try: > session.query().one() > except orm_exc.NoResultFound: > gracefully deal with it > > If the query() execution causes an autoflush, I just want to make sure that > an autoflush will never raise orm_exc.NoResultFound, or we could be catching > the wrong error. Were that the case, to be safe, we'd always need: > > session.flush() > try: > session.query().one() > except orm_exc.NoResultFound: > gracefully deal with it Well, it wont raise that right now, no, but if you had something going on in a flush event that did, then it could. I suppose flush() should be wrapping that kind of exception so that this use case can proceed. -- 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] Potential issue with SQLAlchemy/cx_Oracle (under Python 3)
On Feb 8, 2013, at 10:29 AM, Thijs Engels wrote: > > Works fine with both Python 2.7 and 3.3. Running the (even more) direct > statement as in your email end up with the same results. > > More than willing to try things out if the feedback on this is > considered useful. > your program will work right now if you pass auto_setinputsizes=False to create_engine(). An encoding step in setinputsizes() will be removed in the SQLAlchemy dialect to resolve. Here is a cx_Oracle script that reproduces the error - however it only reproduces it sporadically. import cx_Oracle connection = cx_Oracle.Connection("scott/tiger@localhost:1521/xe") cursor = connection.cursor() cursor.execute("create table users (id integer primary key, name varchar(20), " "extra varchar(20) not null)") try: # this should be 'id', not b'id'.But strange that # it fails only sporadically. cursor.setinputsizes(**{b'id': cx_Oracle.NUMBER}) cursor.execute( "INSERT INTO users (id, name, extra) VALUES (:id, :name, :extra)", {'extra': 2013, 'id': 2, 'name': 'Ed Jones'} ) finally: cursor.execute("drop table users") -- 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] orm_exc.NoResultFound safe with autoflush?
We often use this pattern: try: session.query().one() except orm_exc.NoResultFound: gracefully deal with it If the query() execution causes an autoflush, I just want to make sure that an autoflush will never raise orm_exc.NoResultFound, or we could be catching the wrong error. Were that the case, to be safe, we'd always need: session.flush() try: session.query().one() except orm_exc.NoResultFound: gracefully deal with it -- 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] Potential issue with SQLAlchemy/cx_Oracle (under Python 3)
On Fri, Feb 8, 2013, at 15:49, Michael Bayer wrote: > > On Feb 8, 2013, at 5:47 AM, Thijs Engels wrote: > > > > > Running this script on 3.3 results in this error message: > > > > sqlalchemy.exc.IntegrityError: (IntegrityError) ORA-01400: cannot insert > > NULL into ("SCOTT"."USERS"."ID") > > 'INSERT INTO users (id, name, extra) VALUES (:id, :name, :extra)' > > {'extra': 2013, 'name': 'Ed Jones', 'id': 2} > > > > (more details available at request) > > > > This seems odd, as the insert statement indicates the column id is > > assigned with a correct value (2). > > > > Same script, again Python 3, but now with SQLite (in memory): no errors. > > Using Python 2 (v2.7.3) and Oracle; again no errors. > > > > Is it correct to assume this would be related to SQLAlchemy? > > I've not attempted running cx_oracle itself with Python 3 at all, and > haven't made any attempt to test SQLAlchemy's dialect with python 3 + > cx_Oracle. > > What happens if you run a plain cx_Oracle script in Python 3 that runs > the equivalent statement, that is, > > > import cx_Oracle > conn = cx_Oracle.connect(username, password, dsn) > cursor = conn.cursor() > cursor.execute("insert into users (id, name, extra) values (:id, :name, > :extra)", {"extra":2013, "name": "Ed Jones", "id": 2}) I knew that question was coming; I forgot to mentioned in my original email that I did try this: - import cx_Oracle connection = cx_Oracle.Connection("scott/tiger@localhost:1521/xe") cursor = connection.cursor() cursor.prepare("INSERT INTO users (id, name, extra) VALUES (:id, :name, :extra)") cursor.execute(None, {'extra': 2013, 'id': 2, 'name': 'Ed Jones'}) connection.commit() - Works fine with both Python 2.7 and 3.3. Running the (even more) direct statement as in your email end up with the same results. More than willing to try things out if the feedback on this is considered useful. Thijs -- 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] Potential issue with SQLAlchemy/cx_Oracle (under Python 3)
On Feb 8, 2013, at 5:47 AM, Thijs Engels wrote: > > Running this script on 3.3 results in this error message: > > sqlalchemy.exc.IntegrityError: (IntegrityError) ORA-01400: cannot insert > NULL into ("SCOTT"."USERS"."ID") > 'INSERT INTO users (id, name, extra) VALUES (:id, :name, :extra)' > {'extra': 2013, 'name': 'Ed Jones', 'id': 2} > > (more details available at request) > > This seems odd, as the insert statement indicates the column id is > assigned with a correct value (2). > > Same script, again Python 3, but now with SQLite (in memory): no errors. > Using Python 2 (v2.7.3) and Oracle; again no errors. > > Is it correct to assume this would be related to SQLAlchemy? I've not attempted running cx_oracle itself with Python 3 at all, and haven't made any attempt to test SQLAlchemy's dialect with python 3 + cx_Oracle. What happens if you run a plain cx_Oracle script in Python 3 that runs the equivalent statement, that is, import cx_Oracle conn = cx_Oracle.connect(username, password, dsn) cursor = conn.cursor() cursor.execute("insert into users (id, name, extra) values (:id, :name, :extra)", {"extra":2013, "name": "Ed Jones", "id": 2}) -- 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] Potential issue with SQLAlchemy/cx_Oracle (under Python 3)
On Fri, Feb 8, 2013, at 11:47, Thijs Engels wrote: > > Is it correct to assume this would be related to SQLAlchemy? > I forgot to mention that a raw insert using cx_Oracle directly is fine for both Python 2.7 and 3.3. - import cx_Oracle connection = cx_Oracle.Connection("scott/tiger@localhost:1521/xe") cursor = connection.cursor() cursor.prepare("INSERT INTO users (id, name, extra) VALUES (:id, :name, :extra)") cursor.execute(None, {'extra': 2013, 'id': 2, 'name': 'Ed Jones'}) connection.commit() - Thijs -- 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] Potential issue with SQLAlchemy/cx_Oracle (under Python 3)
Hello, I am currently investigating whether all my current Python work/scripts will work with Python 3 (v3.3.0). And I am running into an issue which seems to be related to this. I have made an example (or actually took the one from the documentation) to illustrate the issue. Currently running Python 3.3.0 with SQLAlchemy 0.7.10 and cx_Oracle 5.1.2 (all 32 bit, on Windows) - from sqlalchemy import create_engine from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column, Integer, String from sqlalchemy.orm import sessionmaker # engine = create_engine('sqlite:///:memory:', echo=True) engine = create_engine('oracle://scott:tiger@localhost:1521/xe', echo=True) Base = declarative_base() Session = sessionmaker() Session.configure(bind=engine) session = Session() class User(Base): __tablename__ = 'users' id = Column(Integer, primary_key=True) name = Column(String(32)) extra = Column(Integer, nullable=False) def __init__(self, id, name, extra): self.id = id self.name = name self.extra = extra def __repr__(self): return "" % (self.id, self.name, self.extra) Base.metadata.create_all(engine) ed_user = User(2, 'Ed Jones', 2013) session.add(ed_user) session.commit() - Not willing to be dependent on sequences required by Oracle I added the id explicitly. Running this script on 3.3 results in this error message: sqlalchemy.exc.IntegrityError: (IntegrityError) ORA-01400: cannot insert NULL into ("SCOTT"."USERS"."ID") 'INSERT INTO users (id, name, extra) VALUES (:id, :name, :extra)' {'extra': 2013, 'name': 'Ed Jones', 'id': 2} (more details available at request) This seems odd, as the insert statement indicates the column id is assigned with a correct value (2). Same script, again Python 3, but now with SQLite (in memory): no errors. Using Python 2 (v2.7.3) and Oracle; again no errors. Is it correct to assume this would be related to SQLAlchemy? Kind regards, Thijs -- 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.