Welcome to sqlutilpy’s documentation!

This is the package that allows you to query PostgreSQL or SQLite database and retrieve the results as numpy arrays.

sqlutilpy.getConnection(db=None, driver=None, user=None, password=None, host=None, port=None, timeout=None)

Obtain the connection object to the DB. It may be useful to avoid reconnecting to the DB repeatedly.

Parameters:
db : string

The name of the database (in case of PostgreSQL) or filename in case of sqlite db

driver : string

The db driver (either ‘psycopg2’ or ‘sqlite3’)

user : string, optional

Username

password: string, optional

Password

host : string, optional

Host-name

port : integer

Connection port (by default 5432 for PostgreSQL)

timeout : integer

Connection timeout for sqlite

Returns:
conn : object

Database Connection

sqlutilpy.getCursor(conn, driver=None, preamb=None, notNamed=False)

Retrieve the database cursor

sqlutilpy.get(query, params=None, db='wsdb', driver='psycopg2', user=None, password=None, host=None, preamb=None, conn=None, port=None, strLength=20, timeout=None, notNamed=False, asDict=False, intNullVal=-9999)

Executes the sql query and returns the tuple or dictionary with the numpy arrays.

Parameters:
query : string

Query you want to execute, can include question marks to refer to query parameters

params : tuple

Query parameters

conn : object

The connection object to the DB (optional) to avoid reconnecting

asDict : boolean

Flag whether to retrieve the results as a dictionary with column names as keys

strLength : integer

The maximum length of the string. Strings will be truncated to this length

intNullVal : integer, optional

All the integer columns with nulls will have null replaced by this value

db : string

The name of the database

driver : string, optional

The sql driver to be used (psycopg2 or sqlite3)

user : string, optional

User name for the DB connection

password : string, optional

DB connection password

host : string, optional

Hostname of the database

port : integer, optional

Port of the database

preamb : string

SQL code to be executed before the query

Returns:
ret : Tuple or dictionary

By default you get a tuple with numpy arrays for each column in your query. If you specified asDict keyword then you get an ordered dictionary with your columns.

Examples

>>> a, b, c = sqlutilpy.get('select ra,dec,d25 from rc3')

You can also use the parameters in your query:

>>> a, b = sqlutilpy.get('select ra,dec from rc3 where name=?', "NGC 3166")
sqlutilpy.execute(query, params=None, db='wsdb', driver='psycopg2', user=None, password=None, host=None, conn=None, preamb=None, timeout=None, noCommit=False)

Execute a given SQL command without returning the results

Parameters:
query: string

The query or command you are executing

params: tuple, optional

Optional parameters of your query

db : string

Database name

driver : string

Driver for the DB connection (‘psycopg2’ or ‘sqlite3’)

user : string, optional

user name for the DB connection

password : string, optional

DB connection password

host : string, optional

Hostname of the database

port : integer, optional

Port of the database

noCommit: bool

By default execute() will commit your command. If you say noCommit, the commit won’t be issued.

Examples

>>> sqlutil.execute('drop table mytab', conn=conn)
>>> sqlutil.execute('create table mytab (a int)', db='mydb')
sqlutilpy.upload(tableName, arrays, names=None, db='wsdb', driver='psycopg2', user=None, password=None, host=None, conn=None, preamb=None, timeout=None, noCommit=False, temp=False, analyze=False, createTable=True, delimiter='|')

Upload the data stored in the tuple of arrays in the DB

Parameters:
tableName : string

The name of the table where the data will be uploaded

arrays_or_table : tuple

Tuple of arrays that will be columns of the new table If names are not specified, I this parameter can be pandas or astropy table

names : tuple
db: string

Databas name

driver: string

Python database driver “psycopg2”,

user: string,
password: string
host: string
conn: object

SQL connection

preamb: string

The string/query to be executed before your command

noCommit: bool

If true, the commit is not executed and the table will go away after the disconnect

temp: bool

If true a temporary table will be created

analyze: bool

if True, the table will be analyzed after the upload

createTable: bool

if True the table will be created before uploading (default)

delimiter: string

the string used for delimiting the input data when ingesting into the db (default is |)

Examples

>>> x = np.arange(10)
>>> y = x**.5
>>> sqlutilpy.upload('mytable', (x, y), ('xcol', 'ycol'))
>>> T = astropy.Table({'x':[1, 2, 3], 'y':['a', 'b', 'c'])
>>> sqlutilpy.upload('mytable', T)
sqlutilpy.local_join(query, tableName, arrays, names, db=None, driver='psycopg2', user=None, password=None, host=None, port=None, conn=None, preamb=None, timeout=None, strLength=20, intNullVal=-9999, asDict=False)

Join your local data in python with the data in the database This command first uploads the data in the DB creating a temporary table and then runs a user specified query that can your local data.

Parameters:
query : String with the query to be executed
tableName : The name of the temporary table that is going to be created
arrays : The tuple with list of arrays with the data to be loaded in the DB
names : The tuple with the column names for the user table

Examples

This will extract the rows from the table sometable matching to the provided array x

>>> x = np.arange(10)
>>> y = x**.5
>>> sqlutilpy.local_join('''
... SELECT s.* FROM mytable AS m LEFT JOIN sometable AS s
... ON s.x = m.x ORDER BY m.xcol''',
... 'mytable', (x, y), ('x', 'y'))
exception sqlutilpy.SqlUtilException

Bases: exceptions.Exception

Indices and tables