soton_corenlppy.PostgresqlHandler module

Postgresql database io handler using psycopg2:

• see http://initd.org/psycopg/docs/module.html

• see http://initd.org/psycopg/docs/usage.html#

• see http://www.postgresql.org/docs/9.1/static/libpq-connect.html

• see http://www.postgresql.org/docs/current/static/runtime-config.html

note on spotting connection failures in Postgres via psycopg2:

• connection.closed –> tells us if a connection has been closed using close(). this is useless if the network fails and connection is closed this way

• connection.status –> tells us about a transaction status (ready, prepared etc). this lets us know if a statements are prepared but not committed but has nothing to say aboyt if the connection is alive or not

• connection.reset() –> reset transaction not the connection

• the only sure way to know if a connection is alive is to execute a statement and catch the exception, then reconnect using original details

class soton_corenlppy.PostgresqlHandler.PostgresqlHandler(user, passw, hostname, port, database, timeout_statement=60)

Bases: soton_corenlppy.SqlHandler.SqlHandler

Postgresql handler class to allow clients to execute robust, retry on failure type SQL statements.

check_for_disconnect(err)

special connection failure parsing

Parameters

err (psycopg2.Error) – SQL exception object

Returns

True if the error indicates we are disconnected

Return type

bool

close()

close connecton and flag it as closed to prevent restarts

note : once flagged closed the connection will not be restarted using reconnect()

evaluate_sql_error(err)

evaluate an sql error to see if we need to abort

Parameters

err (psycopg2.Error) – SQL exception object

Returns

True if the SQL query or statement can be retried (e.g. network connection error that might go away), or False if it should be aborted (e.g. SQL syntax error)

Return type

bool

execute_sql_query(query, timeout_statement=60, timeout_overall=180)

execute a single SQL query and return the result (if any)

note : use variables for all data that has escape characters, non-ascii encoding or is simply large as opposed to niavely serializing the data into an SQL query string

note : unicode characters are returned as <str> 8-bit UTF-8 encoded strings. You can get back to <unicode> using strResultString.decode( ‘utf-8’ ).

Parameters

• query (tuple) – SQL query tuple to execute to get config JSON object. e.g. ( “SELECT var FROM mytable WHERE var = %s”, (‘match_value’,) ). if there is no data part to the query None can be provided e.g. ( “SELECT * FROM mytable”, None )

• timeout_statement (int) – number of seconds to allow each SQL statement

• timeout_overall (int) – number of seconds total to allow each SQL statement (including retries)

Returns

Python list with result row data OR empty list (no data)

Return type

list

execute_sql_query_batch(query_list, timeout_statement=60, timeout_overall=180)

execute a batch of SQL queries and return the concatenated result (if any)

note : non-query statements ARE allowed here (e.g. LOCK) to allow INSERT or LOCK in the transaction before a query

note : use variables for all data that has escape characters, non-ascii encoding or is simply large as opposed to niavely serializing the data into an SQL query string

note : unicode characters are returned as <str> 8-bit UTF-8 encoded strings. You can get back to <unicode> using strResultString.decode( ‘utf-8’ ).

Parameters

• query_list (list) – list of SQL query tuples to execute to get config JSON object. e.g. [ ( “SELECT var FROM mytable WHERE var = %s”, (‘match_value’,) ), … ]. if there is no data part to the query None can be provided e.g. [( “SELECT * FROM mytable”, None ), … ]

• timeout_statement (int) – number of seconds to allow each SQL statement

• timeout_overall (int) – number of seconds total to allow each SQL statement (including retries)

Returns

Python list with result row data OR empty list (no data)

Return type

list

execute_sql_statement(statement_list, timeout_statement=60, timeout_overall=180)

execute a set of SQL statements (insert, create etc) with no result expected

note : use variables for all data that has escape characters, non-ascii encoding or is simply large as opposed to niavely serializing the data into an SQL query string

Parameters

• statement_list (list) – list of SQL statements in tuple form to execute in a single commit e.g. [ ( “INSERT INTO mytable VALUES(%s,%s)”, (‘value1’,’value2’) ), … ]. if there is no data part to the query None can be provided e.g. ( “INSERT INTO mytable VALUES(1)”, None )

• timeout_statement (int) – number of seconds to allow each SQL statement

• timeout_overall (int) – number of seconds total to allow each SQL statement (including retries)

reconnect()

reconnect using previously cached connection params

note : UTF8 client encoding is set for all connections