Re: [sqlalchemy] dealing with multiple databases when using declarative
On Dec 3, 2009, at 2:39 PM, Victor Olex wrote: > By database we refer to a separate instance (could be on another > server) of equvalent database i.e. production and development > environments. > > Using sqlalchemy.ext.declarative, what would be a correct pattern to > enable one model to connect to two equivalent databases? > > Consider: > > --- model.py --- > Base = declarative_base() > metadata = Base.metadata > > class MyTable(Base): >__tablename__ = 'my_table' >id = Column(Integer, primary_key=True, nullable=False) >description = Column(CHAR(length=100), nullable=False) > > _Session = sessionmaker() > > def make_session(uri): >metadata.bind = engine >session = Session(bind=engine) >return session > > --- program.py -- > import model > > """copy some data between databases""" > srcdb = model.make_session(srcdb_uri) > destdb = model.make_session(srcdb_uri) > > items = srcdb.query(model.MyTable).all() > > for i in items: >destdb.merge(i) > > destdb.commit() > --- > > We have two distinct sessions bound to to different databases but > because metadata is a module level variable it gets rebound to the > database of last call. never bind your metadata to an engine if you do not require this functionality. it is an entirely optional convenience feature. > > Also I am somewhat unclear on the difference between metadata binding > to engine and session binding to engine, especially when they end up > different. What is relevance of MetaData binding? it is discussed here: http://www.sqlalchemy.org/docs/05/metadata.html#binding-metadata-to-an-engine-or-connection in the upcoming 0.6 documentation, I've expounded upon this a bit: http://www.sqlalchemy.org/docs/06/metadata.html#binding-metadata-to-an-engine-or-connection Should you use bind ? It’s probably best to start without it. If you find yourself constantly needing to specify the same Engine object throughout the entire application, consider binding as a convenience feature which is applicable to applications that don’t have multiple engines in use and don’t have the need to reference connections explicitly. It should also be noted that an application which is focused on using the SQLAlchemy ORM will not be dealing explicitly with Engine or Connection objects very much in any case, so it’s probably less confusing and more “future proof” to not use the bind attribute. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@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] FlushError: instance is in unsaved, pending instance...
On Dec 3, 2009, at 1:23 PM, Alessandro Dentella wrote: > Hi, > > I'm stuck with the code below raise FlushError complaining: > > Traceback (most recent call last): > File "", line 49, in > File "/misc/src/sqlalchemy/sqlalchemy/lib/sqlalchemy/orm/session.py", > line 673, in commit > self.transaction.commit() > File "/misc/src/sqlalchemy/sqlalchemy/lib/sqlalchemy/orm/session.py", > line 378, in commit > self._prepare_impl() > File "/misc/src/sqlalchemy/sqlalchemy/lib/sqlalchemy/orm/session.py", > line 362, in _prepare_impl > self.session.flush() > File "/misc/src/sqlalchemy/sqlalchemy/lib/sqlalchemy/orm/session.py", > line 1356, in flush > self._flush(objects) > File "/misc/src/sqlalchemy/sqlalchemy/lib/sqlalchemy/orm/session.py", > line 1416, in _flush > mapperutil.state_str(state), path)) > sqlalchemy.orm.exc.FlushError: Instance is an > unsaved, pending instance and is an orphan (is not attached to any parent > 'Project' instance via that classes' 'deliveries' attribute) > > I don't understand what I should do to fix it. > > What I can't understand is why it is considered an orphan: I *do* set job_id > on it and it should be enought to build the relation (I *do* need > delete-orphan). job_id is not going to be recognized within the SQLA's orphan test, which is only looking at object associations. see the FAQ entry on this. > > Is the only solution to attach an instance (u.job = myjob) or is there > another solution that doesn't require me to build the instance? if you want SQLA's delete-orphan capability, that's the only way. If you want to rely upon CASCADE rules in your DB to handle it instead, that's another way to go. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@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: Oracle timestamp and postgres datetime compatibility
On Dec 3, 2009, at 6:12 AM, Nicola Lacquaniti wrote: > I tried the same thing, but I obtain an 'ORA-01843: not a valid month' > message from the backend. can you try 0.6 trunk please, and ensure you're on the latest version of cx_oracle as well. Oracle test coverage is not at 100% in 0.5 but is in trunk. You shouldnt need to affect any nls_date_format stuff. its possible we dont have coverage for Oracle TIMESTAMP and there's issues we haven't covered yet. > This is my model: > > class BaseObject(Base, Dictionaryable): > __tablename__ = 'baseobj' > > id = Column(Integer, Sequence((__tablename__+'id_seq')[-30:]), > autoincrement=True, primary_key=True) > realtype = Column(Unicode(30), nullable=False, index=True) > > codice = Column(Integer, nullable=True) > nome = Column(Unicode(255), nullable=False) > nonattivo = Column(TIMESTAMP , nullable=True) > notanonattivo = Column(Unicode(255), nullable=True) > > After a commit I have: > DatabaseError: (DatabaseError) ORA-01843: not a valid month > 'UPDATE baseapparati SET nonattivo=:nonattivo, > notanonattivo=:notanonattivo WHERE baseapparati.id > = :baseapparati_id' {'baseapparati_id': 1, 'notanonattivo': '', > 'nonattivo': '2009-12-03 11:41:07.457000'} > > I assigned to the object of type BaseObject a value > datetime.datetime.now() > > Perhaps I can give the connection/session whatever a command like > > alter session set nls_date_format='DD/MM/'; > > but I think I've done wrong something instead. So two questions: > > 1) where I was wrong? (better answare) > 2) as an alternative, where can I give the alter session... command to > get it activated in all sessions? > > thanks for the help! > > -- > > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To post to this group, send email to sqlalch...@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 sqlalch...@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] Requirement for Oracle Apps Consultants in Saudi Arabia
Connexion Technologies is a Business Consulting, IT Solutions and Services company with operations in UAE, Qatar, Bahrain, KSA & India. We are looking for Oracle Apps Consultants for our client in Saudi Arabia Job Description: Broad Functions: •Functional lead in the department to handle Oracle Applications (ERP) and Sales & Distribution applications (Back office & Handheld) •Primary Support to two functional areas (at least 4 modules) from the following 4 areas. Oracle Distribution, Finance, Manufacturing and HRMS Modules. Secondary Support to Sales Force Automation Software operation Main Responsibilities: •Maintain and update user access to the Oracle Applications (ERP) and S&D system (HH and Back office) •Primary Support to two functional areas from the following 4 areas. Oracle Distribution, Finance, Manufacturing and HRMS Modules. •Maintain proper integration with ERP & HH Operations •Provide Secondary Support to Sales Force Automation Software •Maintain proper integration with Oracle ERP •Maintain proper backups of the data and application •Ensure 100% availability of the Application to the users •S&D database administration. •Ensures deadlines are met. •Ensures benefits are re realized. •Develop and maintain operational procedures. •Provide day to day technical support in maintaining the application. •Identify, analyze and resolve application problems. •Evaluate, design, test and install approved enhancements to the system. Educational Qualification: Engineering Degree or Masters Degree in Computers Science Technical Knowledge: •Strong Oracle forms & report writing development skills •Strong discoverer / crystal reporting tools •Strong Functional knowledge of any 4 modules from the listed Oracle Applications modules below: -> Procurement, Inventory, Order Management, Account Payables, Account Receivables, Fixed Assets, GL, WIP,BOM, Costing, Quality, Supply Chain Planning, Core HR & Payroll Desired Experience: •Minimum 7 years solid experience in Development, implementation & Support of Oracle E-Business Suite. •Must be familiar with any ERP implementation project. •Should have managed a team of Developers •Should have good experience of providing Support to User/ Customers Please respond with your updated Resumes at “im...@connexiontechnologies.com” Thanks And regards, Mr. Imran Business consultant, Official Email: im...@connexiontechnologies.com Personal email : imran.connex...@gmail.com Mobile: +91-(0)9030388779 www.connexiontechnologies.com Raj Connexion Technologies Private Limited. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@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] how to change a database
Why not create a different engine for that database ? --- On Thu, 12/3/09, Peter wrote: > From: Peter > Subject: [sqlalchemy] how to change a database > To: sqlalchemy@googlegroups.com > Date: Thursday, December 3, 2009, 4:37 AM > Hi > > Lets suppose I created an engine on database 'foo' and I > want to create > a database 'bar' and then do my work on 'bar'. > What is the recommended way to do this ? > > connection.execute('CREATE DATABASE IF NOT EXISTS bar') > connection.execute('USE bar') > > The former command succeeds with a warning ( already > discussed on this > list ) but the latter seems to be the wrong approach: > > ... > 2009-12-03 13:28:39,221 INFO > sqlalchemy.engine.base.Engine.0x...b0ec COMMIT > TypeError: "'NoneType' object is not callable" in > at > 0x8821bc4> ignored > > > Thanks a lot for your advice > Peter > > -- > > You received this message because you are subscribed to the > Google Groups "sqlalchemy" group. > To post to this group, send email to sqlalch...@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 sqlalch...@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] dealing with multiple databases when using declarative
By database we refer to a separate instance (could be on another server) of equvalent database i.e. production and development environments. Using sqlalchemy.ext.declarative, what would be a correct pattern to enable one model to connect to two equivalent databases? Consider: --- model.py --- Base = declarative_base() metadata = Base.metadata class MyTable(Base): __tablename__ = 'my_table' id = Column(Integer, primary_key=True, nullable=False) description = Column(CHAR(length=100), nullable=False) _Session = sessionmaker() def make_session(uri): metadata.bind = engine session = Session(bind=engine) return session --- program.py -- import model """copy some data between databases""" srcdb = model.make_session(srcdb_uri) destdb = model.make_session(srcdb_uri) items = srcdb.query(model.MyTable).all() for i in items: destdb.merge(i) destdb.commit() --- We have two distinct sessions bound to to different databases but because metadata is a module level variable it gets rebound to the database of last call. Also I am somewhat unclear on the difference between metadata binding to engine and session binding to engine, especially when they end up different. What is relevance of MetaData binding? -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@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] FlushError: instance is in unsaved, pending instance...
Hi, I'm stuck with the code below raise FlushError complaining: Traceback (most recent call last): File "", line 49, in File "/misc/src/sqlalchemy/sqlalchemy/lib/sqlalchemy/orm/session.py", line 673, in commit self.transaction.commit() File "/misc/src/sqlalchemy/sqlalchemy/lib/sqlalchemy/orm/session.py", line 378, in commit self._prepare_impl() File "/misc/src/sqlalchemy/sqlalchemy/lib/sqlalchemy/orm/session.py", line 362, in _prepare_impl self.session.flush() File "/misc/src/sqlalchemy/sqlalchemy/lib/sqlalchemy/orm/session.py", line 1356, in flush self._flush(objects) File "/misc/src/sqlalchemy/sqlalchemy/lib/sqlalchemy/orm/session.py", line 1416, in _flush mapperutil.state_str(state), path)) sqlalchemy.orm.exc.FlushError: Instance is an unsaved, pending instance and is an orphan (is not attached to any parent 'Project' instance via that classes' 'deliveries' attribute) I don't understand what I should do to fix it. What I can't understand is why it is considered an orphan: I *do* set job_id on it and it should be enought to build the relation (I *do* need delete-orphan). Is the only solution to attach an instance (u.job = myjob) or is there another solution that doesn't require me to build the instance? Thanks in advance sandro *:-) -- Sandro Dentella *:-) http://sqlkit.argolinux.orgSQLkit home page - PyGTK/python/sqlalchemy import datetime from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Table, Column, ForeignKey, text, func from sqlalchemy.types import * from sqlalchemy import orm Base = declarative_base() Base.metadata.bind = 'sqlite://' #Base.metadata.bind = 'postgres://localhost/fossati' Session = orm.sessionmaker(bind=Base.metadata.bind) sess = Session() class Project(Base): __tablename__ = "project" id = Column(Integer, primary_key=True) name = Column(String(30), nullable=False) def __str__(self): return self.name class Delivery(Base): __tablename__ = 'delivery' id= Column(Integer, primary_key=True) data = Column(Date, nullable=True) job_id= Column(ForeignKey(Project.id), nullable=False) note = Column(Text) job = orm.relation(Project, backref=orm.backref('deliveries', cascade="all, delete-orphan")) # job = orm.relation(Project, backref='deliveries', lazy=False) def __repr__(self): return self.note __str__ = __repr__ Base.metadata.create_all() Base.metadata.bind.echo=True p = Project(name='test 1') sess.add(p) sess.commit() print "DELIVERY" u = Delivery() u.job_id = p.id # u.job = p## this way it works u.data = datetime.date(2009, 12, 3) sess.add(u) sess.commit() -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@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] how to change a database
Hi Lets suppose I created an engine on database 'foo' and I want to create a database 'bar' and then do my work on 'bar'. What is the recommended way to do this ? connection.execute('CREATE DATABASE IF NOT EXISTS bar') connection.execute('USE bar') The former command succeeds with a warning ( already discussed on this list ) but the latter seems to be the wrong approach: ... 2009-12-03 13:28:39,221 INFO sqlalchemy.engine.base.Engine.0x...b0ec COMMIT TypeError: "'NoneType' object is not callable" in at 0x8821bc4> ignored Thanks a lot for your advice Peter -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@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: Oracle timestamp and postgres datetime compatibility
I tried the same thing, but I obtain an 'ORA-01843: not a valid month' message from the backend. This is my model: class BaseObject(Base, Dictionaryable): __tablename__ = 'baseobj' id = Column(Integer, Sequence((__tablename__+'id_seq')[-30:]), autoincrement=True, primary_key=True) realtype = Column(Unicode(30), nullable=False, index=True) codice = Column(Integer, nullable=True) nome = Column(Unicode(255), nullable=False) nonattivo = Column(TIMESTAMP , nullable=True) notanonattivo = Column(Unicode(255), nullable=True) After a commit I have: DatabaseError: (DatabaseError) ORA-01843: not a valid month 'UPDATE baseapparati SET nonattivo=:nonattivo, notanonattivo=:notanonattivo WHERE baseapparati.id = :baseapparati_id' {'baseapparati_id': 1, 'notanonattivo': '', 'nonattivo': '2009-12-03 11:41:07.457000'} I assigned to the object of type BaseObject a value datetime.datetime.now() Perhaps I can give the connection/session whatever a command like alter session set nls_date_format='DD/MM/'; but I think I've done wrong something instead. So two questions: 1) where I was wrong? (better answare) 2) as an alternative, where can I give the alter session... command to get it activated in all sessions? thanks for the help! -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@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.