[sqlalchemy] Re: wx and SqlAlchemy

[Werner F. Bruhin]

Mike,

Mike wrote:



 I found the issue. For some reason, SqlAlchemy is passing a unicode
 string to a varchar field in SQL Server, which is causing an error to
 be raised. If I explicitly set the value to an integer or a string, it
 works fine. I'm not sure how it's getting cast to unicode, but I think
 I can work around this.
   
How is your model defined?  Maybe the column is incorrectly defined in 
the model or if you use autoload then maybe there is a problem with the 
autoload code.

I would also work around it, but provide some more details here in a new 
thread as it might point to a problem in SA.

Werner

--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: wx and SqlAlchemy

[Mike]

Werner,

On Aug 29, 2:21 am, Werner F. Bruhin [EMAIL PROTECTED] wrote:
 Mike,

 Mike wrote:

 

  I found the issue. For some reason, SqlAlchemy is passing a unicode
  string to a varchar field in SQL Server, which is causing an error to
  be raised. If I explicitly set the value to an integer or a string, it
  works fine. I'm not sure how it's getting cast to unicode, but I think
  I can work around this.

 How is your model defined?  Maybe the column is incorrectly defined in
 the model or if you use autoload then maybe there is a problem with the
 autoload code.

 I would also work around it, but provide some more details here in a new
 thread as it might point to a problem in SA.

 Werner


I'm not sure I really have a model. I created a separate Python file
to hold my table classes, but in my wx.App, I import those classes and
then map them to autoloaded tables. Then I bind my session object.
This may not be the correct way to integrate SA into wx (or any other
project), but I haven't seen any SA code in anything except for
TurboGears examples.

Mike
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: wx and SqlAlchemy

[Mike]

Werner,

On Aug 29, 2:21 am, Werner F. Bruhin [EMAIL PROTECTED] wrote:
 Mike,

 Mike wrote:

 

  I found the issue. For some reason, SqlAlchemy is passing a unicode
  string to a varchar field in SQL Server, which is causing an error to
  be raised. If I explicitly set the value to an integer or a string, it
  works fine. I'm not sure how it's getting cast to unicode, but I think
  I can work around this.

 How is your model defined?  Maybe the column is incorrectly defined in
 the model or if you use autoload then maybe there is a problem with the
 autoload code.

 I would also work around it, but provide some more details here in a new
 thread as it might point to a problem in SA.

 Werner

As I was working on creating my new thread, I realized that it's more
than likely that it's my fault. The value I am setting is derived from
what pay period I am currently viewing in my application. I figure
this out using a hack where I grab my frame's title. The title returns
unicode which is something that SQL Server doesn't like since the
field is set to varchar, not nvarchar. For some reason, this works
using pymssql, but not with SqlAlchemy. I will write a separate post
to that effect.

Mike
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: wx and SqlAlchemy

[Werner F. Bruhin]

Mike,

Mike wrote:
 Hi,

 I am working on a wxPython application that saves data to various
 tables in our MS SQL Server 2000. I connect to one table and get data
 using a session. This works great. I then do a session.close() and
 then a conn.close() where conn = engine.connect().  This seems to work
 as expected as well. The problem happens when I connect to the second
 table. I get the data from the second table just fine, but when I try
 to update it using my 2nd session object, I get the following error:

 Traceback (most recent call last):
   File \\debianis\loginscript$\PythonPackages\Development\Timesheet_sa
 \ts_worksheet_MT.py, line 689, in onClose
 session.flush()
   File c:\python25\lib\site-packages\sqlalchemy-0.4.7-py2.5.egg
 \sqlalchemy\orm\session.py, line 789, in flush
 self.uow.flush(self, objects)
   File c:\python25\lib\site-packages\sqlalchemy-0.4.7-py2.5.egg
 \sqlalchemy\orm\unitofwork.py, line 233, in flush
 flush_context.execute()
   File c:\python25\lib\site-packages\sqlalchemy-0.4.7-py2.5.egg
 \sqlalchemy\orm\unitofwork.py, line 445, in execute
 UOWExecutor().execute(self, tasks)
   File c:\python25\lib\site-packages\sqlalchemy-0.4.7-py2.5.egg
 \sqlalchemy\orm\unitofwork.py, line 930, in execute
 self.execute_save_steps(trans, task)
   File c:\python25\lib\site-packages\sqlalchemy-0.4.7-py2.5.egg
 \sqlalchemy\orm\unitofwork.py, line 945, in execute_save_steps
 self.save_objects(trans, task)
   File c:\python25\lib\site-packages\sqlalchemy-0.4.7-py2.5.egg
 \sqlalchemy\orm\unitofwork.py, line 936, in save_objects
 task.mapper._save_obj(task.polymorphic_tosave_objects, trans)
   File c:\python25\lib\site-packages\sqlalchemy-0.4.7-py2.5.egg
 \sqlalchemy\orm\mapper.py, line 1144, in _save_obj
 c = connection.execute(statement.values(value_params), params)
   File c:\python25\lib\site-packages\sqlalchemy-0.4.7-py2.5.egg
 \sqlalchemy\engine\base.py, line 844, in execute
 return Connection.executors[c](self, object, multiparams, params)
   File c:\python25\lib\site-packages\sqlalchemy-0.4.7-py2.5.egg
 \sqlalchemy\engine\base.py, line 895, in execute_clauseelement
 return self._execute_compiled(elem.compile(dialect=self.dialect,
 column_keys=keys, inline=len(params)  1), distilled_params=params)
   File c:\python25\lib\site-packages\sqlalchemy-0.4.7-py2.5.egg
 \sqlalchemy\engine\base.py, line 907, in _execute_compiled
 self.__execute_raw(context)
   File c:\python25\lib\site-packages\sqlalchemy-0.4.7-py2.5.egg
 \sqlalchemy\engine\base.py, line 916, in __execute_raw
 self._cursor_execute(context.cursor, context.statement,
 context.parameters[0], context=context)
   File c:\python25\lib\site-packages\sqlalchemy-0.4.7-py2.5.egg
 \sqlalchemy\engine\base.py, line 960, in _cursor_execute
 self._handle_dbapi_exception(e, statement, parameters, cursor)
   File c:\python25\lib\site-packages\sqlalchemy-0.4.7-py2.5.egg
 \sqlalchemy\engine\base.py, line 942, in _handle_dbapi_exception
 raise exceptions.DBAPIError.instance(statement, parameters, e,
 connection_invalidated=is_disconnect)
 sqlalchemy.exceptions.DatabaseError: (DatabaseError) internal error:
 None 'UPDATE [tbl_Acct_Prefs] SET pref_value=%(pref_value)s WHERE
 [tbl_Acct_Prefs].[empID] = %(tbl_Acct_Prefs_empID)s AND
 [tbl_Acct_Prefs].pref_name = %
 (tbl_Acct_Prefs_pref_name)s' {'pref_value': u'4',
 'tbl_Acct_Prefs_pref_name': 'last_payPeriod', 'tbl_Acct_Prefs_empID':
 258}


 To get the data, I do the following:

 pref = session.query(Acct_Prefs).filter_by(empID=self.emp_id,
 pref_name='last_payPeriod').first()

 Then I change a value like this:

 pref.pref_value = someValue

 Finally, I do a session.flush() and session.commit(). I've tried using
 just a commit(), but I get the same error. Since I can run this second
 session by itself in IDLE, I think the 1st session or connection or
 something is somehow messing up the second one. Any ideas on what is
 going on would be great. I'm still pretty green with SA, by the way.

 I am developing on Windows XP, Python 2.5.2 and using SqlAlchemy
 0.4.7. I can upgrade if you guys think that's the issue.
   
I am no expert in SA, but I am converting my application over to it.

Why are you closing the session?

You can work with many tables and do all your queries all in the one 
session.

self.session = Session()
pref = self.session.query(db.Preferences).get(1)
# change pref here
self.session.commit()

cellar = self.session.query(db.Cellar).get(1)
# change cellar here
self.session.commit()

etc etc

I think in most wxPython application you would create the session in 
wx.App.OnInit and close it in wx.App.OnExit.

Should you package your application with py2exe you should probably 
include this in your OnExit method:

self.session.close_all()
self.engine.dispose()


 Thanks,

 Mike

 


   


--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 

[sqlalchemy] Re: wx and SqlAlchemy

[Werner F. Bruhin]

Mike,

Sent this a bit to quickly

Mike wrote:
 Hi,

 I am working on a wxPython application that saves data to various
 tables in our MS SQL Server 2000. I connect to one table and get data
 using a session. This works great. I then do a session.close() and
 then a conn.close() where conn = engine.connect().  This seems to work
 as expected as well. The problem happens when I connect to the second
 table. I get the data from the second table just fine, but when I try
 to update it using my 2nd session object, I get the following error:

 Traceback (most recent call last):
   File \\debianis\loginscript$\PythonPackages\Development\Timesheet_sa
 \ts_worksheet_MT.py, line 689, in onClose
 session.flush()
   File c:\python25\lib\site-packages\sqlalchemy-0.4.7-py2.5.egg
 \sqlalchemy\orm\session.py, line 789, in flush
 self.uow.flush(self, objects)
   File c:\python25\lib\site-packages\sqlalchemy-0.4.7-py2.5.egg
 \sqlalchemy\orm\unitofwork.py, line 233, in flush
 flush_context.execute()
   File c:\python25\lib\site-packages\sqlalchemy-0.4.7-py2.5.egg
 \sqlalchemy\orm\unitofwork.py, line 445, in execute
 UOWExecutor().execute(self, tasks)
   File c:\python25\lib\site-packages\sqlalchemy-0.4.7-py2.5.egg
 \sqlalchemy\orm\unitofwork.py, line 930, in execute
 self.execute_save_steps(trans, task)
   File c:\python25\lib\site-packages\sqlalchemy-0.4.7-py2.5.egg
 \sqlalchemy\orm\unitofwork.py, line 945, in execute_save_steps
 self.save_objects(trans, task)
   File c:\python25\lib\site-packages\sqlalchemy-0.4.7-py2.5.egg
 \sqlalchemy\orm\unitofwork.py, line 936, in save_objects
 task.mapper._save_obj(task.polymorphic_tosave_objects, trans)
   File c:\python25\lib\site-packages\sqlalchemy-0.4.7-py2.5.egg
 \sqlalchemy\orm\mapper.py, line 1144, in _save_obj
 c = connection.execute(statement.values(value_params), params)
   File c:\python25\lib\site-packages\sqlalchemy-0.4.7-py2.5.egg
 \sqlalchemy\engine\base.py, line 844, in execute
 return Connection.executors[c](self, object, multiparams, params)
   File c:\python25\lib\site-packages\sqlalchemy-0.4.7-py2.5.egg
 \sqlalchemy\engine\base.py, line 895, in execute_clauseelement
 return self._execute_compiled(elem.compile(dialect=self.dialect,
 column_keys=keys, inline=len(params)  1), distilled_params=params)
   File c:\python25\lib\site-packages\sqlalchemy-0.4.7-py2.5.egg
 \sqlalchemy\engine\base.py, line 907, in _execute_compiled
 self.__execute_raw(context)
   File c:\python25\lib\site-packages\sqlalchemy-0.4.7-py2.5.egg
 \sqlalchemy\engine\base.py, line 916, in __execute_raw
 self._cursor_execute(context.cursor, context.statement,
 context.parameters[0], context=context)
   File c:\python25\lib\site-packages\sqlalchemy-0.4.7-py2.5.egg
 \sqlalchemy\engine\base.py, line 960, in _cursor_execute
 self._handle_dbapi_exception(e, statement, parameters, cursor)
   File c:\python25\lib\site-packages\sqlalchemy-0.4.7-py2.5.egg
 \sqlalchemy\engine\base.py, line 942, in _handle_dbapi_exception
 raise exceptions.DBAPIError.instance(statement, parameters, e,
 connection_invalidated=is_disconnect)
 sqlalchemy.exceptions.DatabaseError: (DatabaseError) internal error:
 None 'UPDATE [tbl_Acct_Prefs] SET pref_value=%(pref_value)s WHERE
 [tbl_Acct_Prefs].[empID] = %(tbl_Acct_Prefs_empID)s AND
 [tbl_Acct_Prefs].pref_name = %
 (tbl_Acct_Prefs_pref_name)s' {'pref_value': u'4',
 'tbl_Acct_Prefs_pref_name': 'last_payPeriod', 'tbl_Acct_Prefs_empID':
 258}


 To get the data, I do the following:

 pref = session.query(Acct_Prefs).filter_by(empID=self.emp_id,
 pref_name='last_payPeriod').first()

 Then I change a value like this:

 pref.pref_value = someValue

 Finally, I do a session.flush() and session.commit(). I've tried using
 just a commit(), but I get the same error. Since I can run this second
 session by itself in IDLE, I think the 1st session or connection or
 something is somehow messing up the second one. Any ideas on what is
 going on would be great. I'm still pretty green with SA, by the way.

 I am developing on Windows XP, Python 2.5.2 and using SqlAlchemy
 0.4.7. I can upgrade if you guys think that's the issue.
   
I am no expert in SA, but I am converting my application over to it.

Why are you closing the session?

You can work with many tables and do all your queries all in the one
session.

self.session = Session()
pref = self.session.query(db.Preferences).get(1)
# change pref here
self.session.commit()

cellar = self.session.query(db.Cellar).get(1)
# change cellar here
self.session.commit()

etc etc

I think in most wxPython application you would create the session in
wx.App.OnInit and close it in wx.App.OnExit.

Should you package your application with py2exe you should probably
include this in your OnExit method:

self.session.close_all()
self.engine.dispose()

I was seeing that my application would hang on closing if I did not do 
this.  I am using Firebird SQL, it might be something to do 

[sqlalchemy] Re: wx and SqlAlchemy

[Mike]

Hi Werner,

On Aug 28, 1:39 am, Werner F. Bruhin [EMAIL PROTECTED] wrote:
 Mike,

 Sent this a bit to quickly



 Mike wrote:
  Hi,

  I am working on a wxPython application that saves data to various
  tables in our MS SQL Server 2000. I connect to one table and get data
  using a session. This works great. I then do a session.close() and
  then a conn.close() where conn = engine.connect().  This seems to work
  as expected as well. The problem happens when I connect to the second
  table. I get the data from the second table just fine, but when I try
  to update it using my 2nd session object, I get the following error:


traceback snipped

  To get the data, I do the following:

  pref = session.query(Acct_Prefs).filter_by(empID=self.emp_id,
  pref_name='last_payPeriod').first()

  Then I change a value like this:

  pref.pref_value = someValue

  Finally, I do a session.flush() and session.commit(). I've tried using
  just a commit(), but I get the same error. Since I can run this second
  session by itself in IDLE, I think the 1st session or connection or
  something is somehow messing up the second one. Any ideas on what is
  going on would be great. I'm still pretty green with SA, by the way.

  I am developing on Windows XP, Python 2.5.2 and using SqlAlchemy
  0.4.7. I can upgrade if you guys think that's the issue.

 I am no expert in SA, but I am converting my application over to it.

 Why are you closing the session?



As I walked in this morning, I realized that closing one session and
opening a second one was probably stupid. I was trying to avoid
mapping tables unless I needed them, but I think I'll just map all of
them.



 You can work with many tables and do all your queries all in the one
 session.

 self.session = Session()
 pref = self.session.query(db.Preferences).get(1)
 # change pref here
 self.session.commit()

 cellar = self.session.query(db.Cellar).get(1)
 # change cellar here
 self.session.commit()

 etc etc


Does this work for multiple databases? This particular program I am
working on will be connecting to 2 or 3 databases and a table or three
in each of those. I'm pretty sure I have to create separate engines
for each db and probably bind separate sessions for those.




 I think in most wxPython application you would create the session in
 wx.App.OnInit and close it in wx.App.OnExit.

 Should you package your application with py2exe you should probably
 include this in your OnExit method:

             self.session.close_all()
             self.engine.dispose()

 I was seeing that my application would hang on closing if I did not do
 this.  I am using Firebird SQL, it might be something to do with that db
 as I heard from others using SA that they did not see this problem.

 Werner

Thanks for the quick reply.

Mike
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: wx and SqlAlchemy

[Cecil Westerhof]

2008/8/28 Mike [EMAIL PROTECTED]:
 Does this work for multiple databases? This particular program I am
 working on will be connecting to 2 or 3 databases and a table or three
 in each of those. I'm pretty sure I have to create separate engines
 for each db and probably bind separate sessions for those.

Nope. I use two databases, no engine and only one session. Maybe not
optimal (I just started learning sqlalchemy and wxpython), but it is
possible.

-- 
Cecil Westerhof

--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: wx and SqlAlchemy

[Werner F. Bruhin]

Mike,

Mike wrote:

...

 Does this work for multiple databases? This particular program I am
 working on will be connecting to 2 or 3 databases and a table or three
 in each of those. I'm pretty sure I have to create separate engines
 for each db and probably bind separate sessions for those.
   
I don't think so, using sessions and engines I would think you have to 
have one per database.

You probably need to explain a bit more what you are doing with these 
databases, i.e. are you moving data from one to the other, or are they 
independent databases or ..

Werner

--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: wx and SqlAlchemy

[Mike]

Hi,

Here's what I'm doing. I have a timesheet application written in
wxPython. It works, but I think it would work better if I changed the
database calls into SA calls. There are 3 databases. I created one and
the other two are pre-existing. The one I created is the one I store
all the user entered data into. I read from the official accounting
database and I read from a couple of views in the third database. The
two databases I read from are for checking purposes to make sure that
I am doing the calculations correctly in my program and also for
authentication and certain bits of user data, such as employee number,
name and such.

Anyway, I dumped the close calls I had and put them in the OnExit
method, which is something I've never used before. I'm not sure that I
have that set up right, but at this point it doesn't matter. I am
still receiving the same error.

If I take all of my SA setup out of the wxPython code and stick it in
my own module, it works. Here's what that looks like:


code

import ts_info
from db_tables import Acct_Prefs, TimeEntries
from sqlalchemy import Table
from sqlalchemy.orm import mapper, sessionmaker

# Connect to the database
print 'connecting to MCISAccounting DB...'
conn, engine, meta = ts_info.setupDB('acct')

# Load the tables
print 'loading tables...'
entry_table = Table('tbl_TimeEntries', meta, autoload=True)
prefs_table = Table('tbl_Acct_Prefs', meta, autoload=True)

# Map the tables
print 'mapping tables...'
mapper(TimeEntries, entry_table)
mapper(Acct_Prefs, prefs_table)

# Create a session object
print 'creating session...'
Session = sessionmaker(bind=engine)
session = Session()

pref = self.session.query(Acct_Prefs).filter_by(empID=self.emp_id,
pref_name='last_payPeriod').first()
pref.pref_value = SomeValue
self.session.commit()

/code

For some weird reason, if I do those last three lines in one of my
wxPython methods, I get an error. I know it has to be something really
stupid, but I'm just not seeing it...

Mike




On Aug 28, 10:29 am, Werner F. Bruhin [EMAIL PROTECTED] wrote:
 Mike,

 Mike wrote:

 ...

  Does this work for multiple databases? This particular program I am
  working on will be connecting to 2 or 3 databases and a table or three
  in each of those. I'm pretty sure I have to create separate engines
  for each db and probably bind separate sessions for those.

 I don't think so, using sessions and engines I would think you have to
 have one per database.

 You probably need to explain a bit more what you are doing with these
 databases, i.e. are you moving data from one to the other, or are they
 independent databases or ..

 Werner
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: wx and SqlAlchemy

[Werner F. Bruhin]

Mike,

Mike wrote:
 Hi,

 Here's what I'm doing. I have a timesheet application written in
 wxPython. It works, but I think it would work better if I changed the
 database calls into SA calls. There are 3 databases. I created one and
 the other two are pre-existing. The one I created is the one I store
 all the user entered data into. I read from the official accounting
 database and I read from a couple of views in the third database. The
 two databases I read from are for checking purposes to make sure that
 I am doing the calculations correctly in my program and also for
 authentication and certain bits of user data, such as employee number,
 name and such.

 Anyway, I dumped the close calls I had and put them in the OnExit
 method, which is something I've never used before. I'm not sure that I
 have that set up right, but at this point it doesn't matter. I am
 still receiving the same error.

 If I take all of my SA setup out of the wxPython code and stick it in
 my own module, it works. Here's what that looks like:


 code

 import ts_info
 from db_tables import Acct_Prefs, TimeEntries
 from sqlalchemy import Table
 from sqlalchemy.orm import mapper, sessionmaker

 # Connect to the database
 print 'connecting to MCISAccounting DB...'
 conn, engine, meta = ts_info.setupDB('acct')

 # Load the tables
 print 'loading tables...'
 entry_table = Table('tbl_TimeEntries', meta, autoload=True)
 prefs_table = Table('tbl_Acct_Prefs', meta, autoload=True)

 # Map the tables
 print 'mapping tables...'
 mapper(TimeEntries, entry_table)
 mapper(Acct_Prefs, prefs_table)

 # Create a session object
 print 'creating session...'
 Session = sessionmaker(bind=engine)
 session = Session()

 pref = self.session.query(Acct_Prefs).filter_by(empID=self.emp_id,
 pref_name='last_payPeriod').first()
 pref.pref_value = SomeValue
 self.session.commit()

 /code

 For some weird reason, if I do those last three lines in one of my
 wxPython methods, I get an error. I know it has to be something really
 stupid, but I'm just not seeing it...

 Mike

   
I did a small test based on the test/demo project I am working on.

Just duplicated the database and changed the application to read from 
one database and show a list of countries and to read/update from a 
second database.  I can view both without problem and update the 2nd db 
without getting an error.

The way I set them up is:
class BoaApp(wx.App):
def OnInit(self):
   
self.ConnectDb()
   
self.main = demoFrame2db.create(None)
self.main.Show()
self.SetTopWindow(self.main)
return True
   
def OnExit(self):
self.session.close_all()
self.engine.dispose()
   
def ConnectDb(self):
# db 1
database = u'C:/Dev/BoaTest04/dbsampleSAnew/database.sqldb'
dburl = sa.engine.url.URL('sqlite', username=None, 
password=None, host=None, port=None, database=database)
self.engine = sa.create_engine(dburl, encoding='utf8', echo=False)
Session = sao.sessionmaker()
Session.configure(bind=self.engine)
self.session = Session()
   
# db 2
database2 = u'C:/Dev/BoaTest04/dbsampleSAnew/database2.sqldb'
dburl = sa.engine.url.URL('sqlite', username=None, 
password=None, host=None, port=None, database=database2)
self.engine2 = sa.create_engine(dburl, encoding='utf8', echo=False)
Session2 = sao.sessionmaker()
Session2.configure(bind=self.engine2)
self.session2 = Session2()
   
def Getds(self):
return self.session

def Getds2(self):
return self.session2

In my primary frame I then do:
   self.theList.SetSession(wx.GetApp().Getds())
self.theList.InitObjectListView()
self.theList.Bind(wx.EVT_LIST_ITEM_SELECTED, self.OnItemSelected)

# a list from a 2 db
self.theList2.SetSession(wx.GetApp().Getds2())
self.theList2.InitObjectListView()
 
when selecting items from theList I display some details and I can 
update these details and commit.

Maybe this helps, but maybe it just causes more confusion?

Werner



 On Aug 28, 10:29 am, Werner F. Bruhin [EMAIL PROTECTED] wrote:
   
 Mike,

 Mike wrote:

 ...

 
 Does this work for multiple databases? This particular program I am
 working on will be connecting to 2 or 3 databases and a table or three
 in each of those. I'm pretty sure I have to create separate engines
 for each db and probably bind separate sessions for those.
   
 I don't think so, using sessions and engines I would think you have to
 have one per database.

 You probably need to explain a bit more what you are doing with these
 databases, i.e. are you moving data from one to the other, or are they
 independent databases or ..

 Werner
 
 


   


--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to 

[sqlalchemy] Re: wx and SqlAlchemy

[Mike]

Werner,

On Aug 28, 11:24 am, Werner F. Bruhin [EMAIL PROTECTED] wrote:
 Mike,



 Mike wrote:
  Hi,

  Here's what I'm doing. I have a timesheet application written in
  wxPython. It works, but I think it would work better if I changed the
  database calls into SA calls. There are 3 databases. I created one and
  the other two are pre-existing. The one I created is the one I store
  all the user entered data into. I read from the official accounting
  database and I read from a couple of views in the third database. The
  two databases I read from are for checking purposes to make sure that
  I am doing the calculations correctly in my program and also for
  authentication and certain bits of user data, such as employee number,
  name and such.

  Anyway, I dumped the close calls I had and put them in the OnExit
  method, which is something I've never used before. I'm not sure that I
  have that set up right, but at this point it doesn't matter. I am
  still receiving the same error.

  If I take all of my SA setup out of the wxPython code and stick it in
  my own module, it works. Here's what that looks like:

  code

  import ts_info
  from db_tables import Acct_Prefs, TimeEntries
  from sqlalchemy import Table
  from sqlalchemy.orm import mapper, sessionmaker

  # Connect to the database
  print 'connecting to MCISAccounting DB...'
  conn, engine, meta = ts_info.setupDB('acct')

  # Load the tables
  print 'loading tables...'
  entry_table = Table('tbl_TimeEntries', meta, autoload=True)
  prefs_table = Table('tbl_Acct_Prefs', meta, autoload=True)

  # Map the tables
  print 'mapping tables...'
  mapper(TimeEntries, entry_table)
  mapper(Acct_Prefs, prefs_table)

  # Create a session object
  print 'creating session...'
  Session = sessionmaker(bind=engine)
  session = Session()

  pref = self.session.query(Acct_Prefs).filter_by(empID=self.emp_id,
  pref_name='last_payPeriod').first()
  pref.pref_value = SomeValue
  self.session.commit()

  /code

  For some weird reason, if I do those last three lines in one of my
  wxPython methods, I get an error. I know it has to be something really
  stupid, but I'm just not seeing it...

  Mike

 I did a small test based on the test/demo project I am working on.

 Just duplicated the database and changed the application to read from
 one database and show a list of countries and to read/update from a
 second database.  I can view both without problem and update the 2nd db
 without getting an error.

 The way I set them up is:
 class BoaApp(wx.App):
     def OnInit(self):

         self.ConnectDb()

         self.main = demoFrame2db.create(None)
         self.main.Show()
         self.SetTopWindow(self.main)
         return True

     def OnExit(self):
         self.session.close_all()
         self.engine.dispose()

     def ConnectDb(self):
         # db 1
         database = u'C:/Dev/BoaTest04/dbsampleSAnew/database.sqldb'
         dburl = sa.engine.url.URL('sqlite', username=None,
 password=None, host=None, port=None, database=database)
         self.engine = sa.create_engine(dburl, encoding='utf8', echo=False)
         Session = sao.sessionmaker()
         Session.configure(bind=self.engine)
         self.session = Session()

         # db 2
         database2 = u'C:/Dev/BoaTest04/dbsampleSAnew/database2.sqldb'
         dburl = sa.engine.url.URL('sqlite', username=None,
 password=None, host=None, port=None, database=database2)
         self.engine2 = sa.create_engine(dburl, encoding='utf8', echo=False)
         Session2 = sao.sessionmaker()
         Session2.configure(bind=self.engine2)
         self.session2 = Session2()

     def Getds(self):
         return self.session

     def Getds2(self):
         return self.session2

 In my primary frame I then do:
        self.theList.SetSession(wx.GetApp().Getds())
         self.theList.InitObjectListView()
         self.theList.Bind(wx.EVT_LIST_ITEM_SELECTED, self.OnItemSelected)

         # a list from a 2 db
         self.theList2.SetSession(wx.GetApp().Getds2())
         self.theList2.InitObjectListView()

 when selecting items from theList I display some details and I can
 update these details and commit.

 Maybe this helps, but maybe it just causes more confusion?

 Werner


Your code makes sense. I tried to create a small runnable example, but
my sample works and my original does not. I'm not seeing the
difference, but obviously something is wrong with my original code. I
may have to just re-write it from scratch. I have a feeling that the
end of the traceback has a clue, but I don't know how to read it:

raise exceptions.DBAPIError.instance(statement, parameters, e,
connection_invalidated=is_disconnect)
sqlalchemy.exceptions.DatabaseError: (DatabaseError) internal error:
None 'UPDATE [tbl_Acct_Prefs] SET pref_value=%(pref_value)s WHERE
[tbl_Acct_Prefs].[empID] = %(tbl_Acct_Prefs_empID)s AND
[tbl_Acct_Prefs].pref_name = %
(tbl_Acct_Prefs_pref_name)s' {'pref_value': u'4',
'tbl_Acct_Prefs_pref_name':