5

I'm playing around with SQLAlchemy core in Python, and I've read over the documentation numerous times and still need clarification about engine.execute() vs connection.execute().

As I understand it, engine.execute() is the same as doing connection.execute(), followed by connection.close().

The tutorials I've followed let me to use this in my code:

Initial setup in script

try:
    engine = db.create_engine("postgres://user:pass@ip/dbname", connect_args={'connect_timeout': 5})
    connection = engine.connect()
    metadata = db.MetaData()
except exc.OperationalError:
    print_error(f":: Could not connect to {db_ip}!")
    sys.exit()

Then, I have functions that handle my database access, for example:

def add_user(a_username):
    query = db.insert(table_users).values(username=a_username)
    connection.execute(query)

Am I supposed to be calling connection.close() before my script ends? Or is that handled efficiently enough by itself? Would I be better off closing the connection at the end of add_user(), or is that inefficient?

If I do need to be calling connection.close() before the script ends, does that mean interrupting the script will cause hanging connections on my Postgres DB?

Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
Max
  • 597
  • 7
  • 21

1 Answers1

1

I found this post helpful to better understand the different interaction paradigms in sqlalchemy, in case you haven't read it yet.

Regarding your question as to when to close your db connection: It is indeed very inefficient to create and close connections for every statement execution. However you should make sure that your application does not have connection leaks in it's global flow.

Florian Drawitsch
  • 685
  • 5
  • 16