Quick questions about globals and database connections

[Dan Sommers]

On Wed, 05 Apr 2017 14:56:12 -0400, DFS wrote:

> I split the database connection code and settings into a module, so
> the connection and setting values are available to other code modules
> I have.

Good work.

> dbset.py
> -------------------------------------------------
> import sqlite3, psycopg2, cx_Oracle
> 
> def openconnection(dbtype):
>    if dbtype == "sqlite":
> 	dbName = "DB.sqlite"
> 	conn   = sqlite3.connect(dbName)
> 	conn.text_factory = str
> 	ps     = '?'   #query parameter symbol
> 
>    if dbtype == "postgres":
> 	dbName = "DB on Postgres"
> 	connectstring = ""         \
> 	" host     = 'localhost' " \
> 	" dbname   = 'dbname' "    \
> 	" user     = 'USR' "       \
> 	" password = 'PW' "
> 	conn = psycopg2.connect(connectstring)
> 	ps     = '%s'   #query parameter symbol
> 
>    if dbtype == "oracle":
>         'settings

Consider adding some sort of error checking.  One way would be to use
elif throughout and an else clause at the end to catch the errors,
something like this:

  if dbtype == '"x":
    dbName = ...
  elif dbtype == "y":
    dbName = ...
  :
  :
  :
  else:
    raise ValueError("unknown database type: %s" % dbtype)

>    db = conn.cursor()
>    return [conn,db,dbName,ps]

These values could be encapsulated into a class, but a list or a tuple
works.  A small step might be a named tuple (one of the batteries
included with Python).

Clunky is in the eye of the beholder.  That appears to be clear,
effective, and easily extensible if you ever add another database
server.  And never underestimate code that works.  Ever.

> In other modules, add:
> ----------------------------------------------------
> import dbset
> 
> dbconnect = dbset.openconnection(dbtype)
> conn   = dbconnect[0]
> db     = dbconnect[1]
> dbName = dbconnect[2]
> ps     = dbconnect[3]
> 
> or shorter version:
> 
> c = dbset.openconnection(dbtype)
> conn,db,dbName,ps = c[0],c[1],c[2],c[3]
> ----------------------------------------------------

Or even shorter version:

  conn,db,dbName,ps = dbset.openconnection(dbtype)

Python will unpack that list for you.

> With that in place, I can do stuff like:
> 
> print "updating " + dbName
> db.execute("DML code")
> conn.commit()
> conn.close()
> db.close()

Yep.  :-)

And if you find yourself repeating that sequence of statements over and
over, then wrap them into a function:

  def execute_database_command(dbName, conn, db, dml_statement):
    print "updating " + dbName
    db.execute(dml_statement)
    conn.commit()
    conn.execute()
    db.close()

and then the rest of your code is just:

  execute_database_command(db, conn, "DML code")