Re: [sqlalchemy] Rolling back all changes between unit tests
Hi! A successful commit cannot be rolled back. Do not use commit. Use session.flush instead, then you could use rollback to revert changes. Cheers, Pau. 2013/7/23 Ib Lundgren ib.lundg...@gmail.com Hey all, I'd like to have a clean DB between tests, one approach that works is to drop tables and recreate fixtures. This can be quite slow and I'd rather have something more lean. If possible I'd like to create a snapshot before each test and rollback to the snapshot after each test. From browsing the docs it seems like SQLAlchemy might be able to achieve this using nested transactions or subtransactions. FWIW I am using Postgres 9.1+. I had a quick go at this and ended up with something similar to *# my_db.py* session = # setup connect session to postgres *# my_app.py* from .my_db import session def add_stuff(): session.add(stuff) session.commit() *# test.py* from .my_db import session from .my_app import add_stuff class MyTest(unittest.TestCase): def setUp(self): session.begin_nested() def tearDown(self): session.rollback() def test_app(self): add_stuff() but that does not work. The commit in add_stuff is not reverted by the rollback in tearDown. Am I going in the right direction? Cheers! -- 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. For more options, visit https://groups.google.com/groups/opt_out. -- -- Pau Tallada Crespí Dep. d'Astrofísica i Cosmologia Port d'Informació Científica (PIC) Tel: +34 93 586 8233 -- -- 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. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Rolling back all changes between unit tests
Hello. I am not familiar with the usage of nested transactions, though I am pretty sure what you want is possible. I would like to suggest a simpler approach. If you implement all your functions without commit and move the commit to one place (e.g. to the end of a web request processing), you can write tests like this: *# my_db.py* session = # setup connect session to postgres *# my_app.py* from .my_db import session def add_stuff(): session.add(stuff) *# test.py* from .my_db import session from .my_app import add_stuff class MyTest(unittest.TestCase): def tearDown(self): session.rollback() def test_app(self): add_stuff() session.flush() # assert against db Recap: * Do not use session.commit() in your code. Move it to one place outside the app logic. * Call session.flush() in your test whenever you want to assert something against the database. * Use rollback() at the end of a unit test. HTH, Ladislav Lenart On 23.7.2013 18:58, Ib Lundgren wrote: Hey all, I'd like to have a clean DB between tests, one approach that works is to drop tables and recreate fixtures. This can be quite slow and I'd rather have something more lean. If possible I'd like to create a snapshot before each test and rollback to the snapshot after each test. From browsing the docs it seems like SQLAlchemy might be able to achieve this using nested transactions or subtransactions. FWIW I am using Postgres 9.1+. I had a quick go at this and ended up with something similar to *# my_db.py* session = # setup connect session to postgres *# my_app.py* from .my_db import session def add_stuff(): session.add(stuff) session.commit() *# test.py* from .my_db import session from .my_app import add_stuff class MyTest(unittest.TestCase): def setUp(self): session.begin_nested() def tearDown(self): session.rollback() def test_app(self): add_stuff() but that does not work. The commit in add_stuff is not reverted by the rollback in tearDown. Am I going in the right direction? Cheers! -- 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. 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. For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] changing the column order of a select
Let's say I've built up a select statement but only after it's built do I know the order of the columns that I'd ultimately prefer. What's the best way to change the column order of a select? -- Jon Software Blacksmith -- 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. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] changing the column order of a select
you can do that using this: http://docs.sqlalchemy.org/en/rel_0_8/core/expression_api.html?highlight=select#sqlalchemy.sql.expression.Select.with_only_columns but you'd need to restate all the columns. On Jul 24, 2013, at 10:59 AM, Jon Nelson jnel...@jamponi.net wrote: Let's say I've built up a select statement but only after it's built do I know the order of the columns that I'd ultimately prefer. What's the best way to change the column order of a select? -- Jon Software Blacksmith -- 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. 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. For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] joined-table inheritance and ambiguous foreign keys
The code below produces the error message below. How do I tell SQLAlchemy
that the inheritance join condition should be b.id == a.id rather than
b.parent_a_id
== a.id? (I would think the primary_key=True could be a hint...) I can't
figure it out from the documentation.
class A(Base):
__tablename__ = 'a'
id = Column(Integer, primary_key=True)
class B(A):
__tablename__ = 'b'
id = Column(Integer, ForeignKey('a.id'), primary_key=True)
parent_a_id = Column(Integer, ForeignKey('a.id'))
sqlalchemy.exc.AmbiguousForeignKeysError: Can't determine join between 'a'
and 'b'; tables have more than one foreign key constraint relationship
between them. Please specify the 'onclause' of this join explicitly.
Thanks,
Seth
--
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.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] joined-table inheritance and ambiguous foreign keys
its a mapper arg called inherit_condition: __mapper_args__ =
{inherit_condition: id==A.id}
On Jul 24, 2013, at 3:42 PM, Seth P spadow...@gmail.com wrote:
The code below produces the error message below. How do I tell SQLAlchemy
that the inheritance join condition should be b.id == a.id rather than
b.parent_a_id == a.id? (I would think the primary_key=True could be a
hint...) I can't figure it out from the documentation.
class A(Base):
__tablename__ = 'a'
id = Column(Integer, primary_key=True)
class B(A):
__tablename__ = 'b'
id = Column(Integer, ForeignKey('a.id'), primary_key=True)
parent_a_id = Column(Integer, ForeignKey('a.id'))
sqlalchemy.exc.AmbiguousForeignKeysError: Can't determine join between 'a'
and 'b'; tables have more than one foreign key constraint relationship
between them. Please specify the 'onclause' of this join explicitly.
Thanks,
Seth
--
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.
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.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] joined-table inheritance and ambiguous foreign keys
Thank you.
On Wed, Jul 24, 2013 at 5:51 PM, Michael Bayer mike...@zzzcomputing.comwrote:
its a mapper arg called inherit_condition: __mapper_args__ =
{inherit_condition: id==A.id}
On Jul 24, 2013, at 3:42 PM, Seth P spadow...@gmail.com wrote:
The code below produces the error message below. How do I tell SQLAlchemy
that the inheritance join condition should be b.id == a.id rather than
b.parent_a_id
== a.id? (I would think the primary_key=True could be a hint...) I can't
figure it out from the documentation.
class A(Base):
__tablename__ = 'a'
id = Column(Integer, primary_key=True)
class B(A):
__tablename__ = 'b'
id = Column(Integer, ForeignKey('a.id'), primary_key=True)
parent_a_id = Column(Integer, ForeignKey('a.id'))
sqlalchemy.exc.AmbiguousForeignKeysError: Can't determine join between 'a'
and 'b'; tables have more than one foreign key constraint relationship
between them. Please specify the 'onclause' of this join explicitly.
Thanks,
Seth
--
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.
For more options, visit https://groups.google.com/groups/opt_out.
--
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/AaSChCQOTSk/unsubscribe.
To unsubscribe from this group and all its topics, 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.
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.
For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] oracle reflect with duplicated tables (schema casing)
Hi,
I got into an interesting issue where I receive duplicated tables if I use
capital letters schema in reflect method.
Ubuntu 12.04 with packages
oracle-instantclient11.2-basiclite-11.2.0.3.0-1.i386.rpm,
oracle-instantclient11.2-devel-11.2.0.3.0-1.i386.rpm,
oracle-instantclient11.2-sqlplus-11.2.0.3.0-1.i386.rpm installed using
alien; Python 2.7; SQLAlchemy 0.7.9 and 0.8.2; cx-Oracle 5.1.2
DB is Oracle 11.2 XE on Windows with two simple tables and relation between
them
Customer Address
Tables were created like: CREATE TABLE MIKE.CUSTOMER ... so they should
be case insensitive.
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
engine = create_engine('oracle://mike:pass@192.168.16.1/xe', echo=True)
Base = declarative_base(bind=engine)
Base.metadata.reflect(schema='mike')
tables = Base.metadata.tables.keys()
print tables
[u'mike.customer', u'mike.address']
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
engine = create_engine('oracle://mike:pass@192.168.16.1/xe', echo=True)
Base = declarative_base(bind=engine)
Base.metadata.reflect(schema='MIKE')
tables = Base.metadata.tables.keys()
print tables
[u'MIKE.customer', u'MIKE.address', u'mike.address']
What I found in sqlalchemy code is that table mike.address is mapped and
added to Base.metadata.tables dictionary when table MIKE.customer is being
mapped. I guess that's because mike.address parent table to MIKE.customer.
The thing is it's added lowercase. Next, MIKE.address is added in a normal
way.
Well I'm not sure if this is a bug or I'm missing something, that's why I
would like to know your opinion on that.
--
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.
For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] oracle reflect with duplicated tables (schema casing)
On Jul 24, 2013, at 7:34 PM, mdob mike.dobrzan...@gmail.com wrote: Hi, I got into an interesting issue where I receive duplicated tables if I use capital letters schema in reflect method. you wouldn't want to do that unless the table were actually created using a case-sensitive name, which is pretty unusual in Oracle. If Oracle's own system views show the names as ALL_UPPERCASE, that's a case-insensitive name. On the SQLAlchemy side, you should use all lower case names which will be similarly treated as case insensitive. Otherwise it will see a case-insensitive and a locally case-sensitive name as different, leading to the kinds of issues you're seeing. Tables were created like: CREATE TABLE MIKE.CUSTOMER ... so they should be case insensitive. I see there are quotes here, but Oracle will still log these as case insensitive (I just tried). So use all lower case on the SQLAlchemy side. What I found in sqlalchemy code is that table mike.address is mapped and added to Base.metadata.tables dictionary when table MIKE.customer is being mapped. I guess that's because mike.address parent table to MIKE.customer. The thing is it's added lowercase. Next, MIKE.address is added in a normal way. it sees mike.address in two different ways. One, as the table MIKE.address, because you asked for the schema MIKE, and the other, as the table mike.address, which is what MIKE.customer says it foreign keys out to. MIKE is not the same as mike, the former is case-sensitive on SQLA's side. -- 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. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] oracle reflect with duplicated tables (schema casing)
Hey Mike, thanks for chiming in. Popular tool Oracle SQL Develoepr generally produces DDL with quoted identifiers so in real life you will encounter a lot of situations where some tables were created using quoted and some unquoted as people work on maintaining the database. Using lowercase in SQLA will generally work unless somebody names a schema in mixed case i.e. Mike or all uppercase but containing non-alphanumerical characters i.e. MIKE IKE (valid name). Putting the weird aside, I am still puzzled why would the foreign key in MIKE.customer in the above example be seen as coming from mike.address and not MIKE.address? Shouldn't the schema name be consistent and thus in this example, case-sensitive uppercase MIKE? Thanks. On Wednesday, July 24, 2013 8:02:51 PM UTC-4, Michael Bayer wrote: On Jul 24, 2013, at 7:34 PM, mdob mike.do...@gmail.com javascript: wrote: Hi, I got into an interesting issue where I receive duplicated tables if I use capital letters schema in reflect method. you wouldn't want to do that unless the table were actually created using a case-sensitive name, which is pretty unusual in Oracle. If Oracle's own system views show the names as ALL_UPPERCASE, that's a case-insensitive name. On the SQLAlchemy side, you should use all lower case names which will be similarly treated as case insensitive. Otherwise it will see a case-insensitive and a locally case-sensitive name as different, leading to the kinds of issues you're seeing. Tables were created like: CREATE TABLE MIKE.CUSTOMER ... so they should be case insensitive. I see there are quotes here, but Oracle will still log these as case insensitive (I just tried). So use all lower case on the SQLAlchemy side. What I found in sqlalchemy code is that table mike.address is mapped and added to Base.metadata.tables dictionary when table MIKE.customer is being mapped. I guess that's because mike.address parent table to MIKE.customer. The thing is it's added lowercase. Next, MIKE.address is added in a normal way. it sees mike.address in two different ways. One, as the table MIKE.address, because you asked for the schema MIKE, and the other, as the table mike.address, which is what MIKE.customer says it foreign keys out to. MIKE is not the same as mike, the former is case-sensitive on SQLA's side. -- 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. For more options, visit https://groups.google.com/groups/opt_out.
