How to interface with another database effectively using python

Question

I have an application that needs to interface with another app's database. I have read access but not write.

Currently I'm using sql statements via pyodbc to grab the rows and using python manipulate the data. Since I don't cache anything this can be quite costly.

I'm thinking of using an ORM to solve my problem. The question is if I use an ORM like "sql alchemy" would it be smart enough to pick up changes in the other database?

E.g. sql alchemy accesses a table and retrieves a row. If that row got modified outside of sql alchemy would it be smart enough to pick it up?

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Edit: To be more clear

I have one application that is simply a reporting tool lets call App A.

I have another application that handles various financial transactions called App B.

A has access to B's database to retrieve the transactions and generates various reports. There's hundreds of thousands of transactions. We're currently caching this info manually in python, if we need an updated report we refresh the cache. If we get rid of the cache, the sql queries combined with the calculations becomes unscalable.

What's your problem exactly? What are you trying to achieve? Do you want to keep synchronized data between databases? — hymloth
– hymloth, Commented Sep 15, 2011 at 7:55
Don't these objects have 'last_modified' column? You could utilize this... — plaes
– plaes, Commented Sep 16, 2011 at 8:37

Alex Marandon · Accepted Answer · 2011-09-16 08:34:35Z

2

I don't think an ORM is the solution to your problem of performance. By default ORMs tend to be less efficient than row SQL because they might fetch data that you're not going to use (eg. doing a SELECT * when you need only one field), although SQLAlchemy allows fine-grained control over the SQL generated.

Now to implement a caching mechanism, depending on your application, you could use a simple dictionary in memory or a specialized system such as memcached or Redis.

To keep your cached data relatively fresh, you can poll the source at regular intervals, which might be OK if your application can tolerate a little delay. Otherwise you'll need the application that has write access to the db to notify your application or your cache system when an update occurs.

Edit: since you seem to have control over app B, and you've already got a cache system in app A, the simplest way to solve your problem is probably to create a callback in app A that app B can call to expire cached items. Both apps need to agree on a convention to identify cached items.

edited Sep 16, 2011 at 8:34

answered Sep 15, 2011 at 10:54

Alex Marandon

4,1232 gold badges19 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

John Jiang Over a year ago

Thanks for your response, I edited my reply so hopefully it's slightly clearer.

9000 Over a year ago

There's no magical way to be notified about a database change. Re-fetching every record you access (via SQLAlchemy or otherwise) is most probably too wasteful. Certain databases allow SPs and triggers o access things like files or sockets (Oracle has DBMS_PIPE, Postgres has DMBS_PIPE and Listen/Notify, don't know about MySQL). Adding triggers to B's database that send notifications could solve this.

Collectives™ on Stack Overflow

How to interface with another database effectively using python

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related