Connecting to multiple DBs

[evan-burke]

Hi, thanks for writing PugSQL! I'm trying to use it for an ETL use case moving data from one db to another. I thought I'd try something like this:

source_db_queries = pugsql.module('pugsql_test_queries')
source_db_queries.connect(source_db_url)

dest_db_queries = pugsql.module('pugsql_test_queries')
dest_db_queries .connect(dest_db_url)

But what seems to happen is the 'source_db_queries' instance gets connected to the destination db; I'd assume these would be separate due to the separate instances, despite reading from the same folder. I'm using "mysql+mysqlconnector" URLs, if that makes any difference.

It works fine when I organize queries into separate folders based on the db where they'll be run (which probably makes more sense anyway), but thought I'd highlight this unexpected behavior.

[mcfunley]

Oh hey, thanks! Yeah this is happening because the modules are cached by source path. So both calls to pugsql.module are returning the same object. And then the second call to connect is modifying source_db_queries.

I'm not sure what the best thing to do here is, but the options are:

• The caching may be causing more confusion than it's worth, and we should remove it.
• We could parameterize module or make a second function that doesn't cache.
• We could leave it as-is and make the documentation clearer somehow.

I guess I'm leaning towards removing the caching.

[evan-burke]

Thanks - I don't know enough about the benefits of caching for various use cases to have a strong opinion, but removing it or parameterizing with caching default-off seem like good ways to prevent others from running into the issue.

[potem]

Any update on the issue? I like the library but multiple database connections are a must.

[Yeganloo]

I think it's better to let users to decide how to cache their queries. In a well structured application, user will use his/her own DI system and an extra cache in Pugsql just create complexity! But this is important to clearly describe to users, what should be cached and what should not be cached!

[mcfunley]

Removed the caching in version 0.2.0 - pip install pugsql --upgrade will drop it.

This also removes the get_modules function and related docs.