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.

exception sqlutilpy.SqlUtilException

Bases: Exception

sqlutilpy.execute(query, params=None, db='wsdb', driver='psycopg', 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

dbstring

Database name

driverstring

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

userstring, optional

user name for the DB connection

passwordstring, optional

DB connection password

hoststring, optional

Hostname of the database

portinteger, 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.get(query, params=None, db='wsdb', driver='psycopg', user=None, password=None, host=None, preamb=None, conn=None, port=None, strLength=20, timeout=None, batched=True, asDict=False, intNullVal=-9999, strNullVal='None', nthreads=1)

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

Parameters:
querystring

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

paramstuple

Query parameters

connobject

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

asDictboolean

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

strLengthinteger

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

intNullValinteger, optional

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

strNullValstring, optional

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

dbstring

The name of the database

driverstring, optional

The sql driver to be used (psycopg or sqlite3)

userstring, optional

User name for the DB connection

passwordstring, optional

DB connection password

hoststring, optional

Hostname of the database

portinteger, optional

Port of the database

preambstring

SQL code to be executed before the query

batchedbool, optional

Whether to use named or not-named cursor with PostgreSQL The default setting of True leads to retrieval of results in batches and on the fly conversion of retrieved results into numpy, thus it will not use more memory than needed to store the results of the query. The The batched=False will use at least twice the amount of memory needed to store the results, but with the benefit of faster query execution, because these queries use PostgreSQL parallelism and thus can be ~ a factor of few faster

Returns
——-
retTuple 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.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:
dbstring

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

driverstring

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

userstring, optional

Username

password: string, optional

Password

hoststring, optional

Host-name

portinteger

Connection port (by default 5432 for PostgreSQL)

timeoutinteger

Connection timeout for sqlite

Returns:
connobject

Database Connection

sqlutilpy.getCursor(conn, driver=None, preamb=None, named=True)

Retrieve the database cursor

sqlutilpy.local_join(query, tableName, arrays, names, db=None, driver='psycopg', user=None, password=None, host=None, port=None, conn=None, preamb=None, timeout=None, strLength=20, intNullVal=-9999, strNullVal='None', 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:
queryString with the query to be executed
tableNameThe name of the temporary table that is going to be created
arraysThe tuple with list of arrays with the data to be loaded in the DB
namesThe 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'))
sqlutilpy.upload(tableName, arrays, names=None, db='wsdb', driver='psycopg', 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:
tableNamestring

The name of the table where the data will be uploaded

arrays_or_tabletuple

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

namestuple
db: string

Databas name

driver: string

Python database driver “psycopg”,

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)

Indices and tables