[wxPython-users] wxPython and database

[Stefano Bartaletti]

Alle lunedì 07 gennaio 2008, fde ha scritto:

> 2) The application insert data from more than one workstation. So there
> is a while when the data the user see are not the real data inserted in
> the db, so I have to refresh the grid. For this purpose do I have to
> refresh the full table with a "select * from table" or there is an other
> way ?

I'm developing an application for internal use at our company, based on a 
Postgres database and wxPython interface.

There will be about 30 to 40 clients at the end of the story, all accessing 
data with different functions but definetly everyone needs to know if some 
data has changed.

The task of syncronizing the different clients is done by a mix of SQLAlchemy 
(www.sqlalchemy.org) and Pyro (pyro.sourceforge.net) and works more or less 
like this:

- a client reads its data (with a specific interface or a grid depending on 
what the client does) through SQLAlchemy and subscribes to a Pyro server for 
news

- whenever a client changes some data, the database "intercepts" changes on 
COMMIT and "informs" other clients through the Pyro server: basically the 
database works out the "type" of the data being changed/inserted and 
broadcasts the IDs with Pyro.

The only drawback I can see is that at COMMIT point many clients are likely to 
access the database and refresh informations: so far this is a minor problem, 
since data do not change that much and most clients are for production 
tracking so they read few database rows at one time.

Answering your question, with a similar method any client would easily know if 
rows were inserted or deleted with count(), as well as retrieve and refresh 
changed rows.

Personal note: I would really recommend to study and use SQLAlchemy, because 
it is very flexible: in the end the db abstraction and access looks very 
pythonic, and you can mostly forget to write and maintain SQL code.

Here's a small example of how to browse a table with changes and deletions:

	from myDatabase import database, mappers
	db = database.myDb(engine="postgres://dbuser@localhost:5432/database")
	db.session.begin()
	try:
		for c in mappers.Customer.query.all():
			if c.name == '':
				db.session.delete(c)
			else:
				c.name = c.name.upper()
		db.session.commit()
	except:
		db.session.rollback()
		raise
	finally:
		db.session.close()

-- 
Cordialmente

Stefano Bartaletti
Responsabile Software

G.Tosi Spa Tintoria

Skype account: stefano.bartaletti
ICQ contact  : 1271960

Viale dell'Industria 61
21052 Busto Arsizio (VA)

Tel. +39 0331 34 48 11
Fax  +39 0331 35 21 23