API reference
Querying
- sqlutilpy.get(query, params=None, *, db='wsdb', driver='psycopg', user=None, password=None, host=None, port=None, conn=None, preamb=None, timeout=None, strLength=20, batched=True, asDict=False, intNullVal=-9999, strNullVal='None', nthreads=1, notNamed=None)
Execute the SQL query and return the result as a tuple or dictionary of numpy arrays, one array per column.
- Parameters:
- querystring
The query to execute. It can contain placeholders for the query parameters: %s for PostgreSQL, ? for SQLite and DuckDB
- paramstuple, optional
Query parameters
- dbstring
The name of the database (PostgreSQL) or the filename of the database (SQLite, DuckDB)
- driverstring, optional
The database driver: ‘psycopg’ (PostgreSQL, the default), ‘sqlite3’ or ‘duckdb’
- userstring, optional
User name for the DB connection (PostgreSQL only)
- passwordstring, optional
DB connection password (PostgreSQL only)
- hoststring, optional
Hostname of the database (PostgreSQL only)
- portinteger, optional
Port of the database (PostgreSQL only)
- connobject, optional
An existing connection to the DB (see getConnection()), to avoid reconnecting. The connection is left open afterwards. The driver argument must still match the connection
- preambstring, optional
SQL to be executed before the query
- timeoutinteger, optional
How long to wait for a locked database, in seconds (SQLite only)
- strLengthinteger, optional
The minimum width allocated for string columns (PostgreSQL only). If longer strings are encountered the width is extended to accommodate them. With SQLite and DuckDB the width is always inferred from the data
- batchedbool, optional
PostgreSQL only. With the default of True the results are retrieved through a server-side (named) cursor in batches of config.arraysize rows and converted to numpy on the fly, so the memory use is about the size of the final arrays. With False a client-side cursor is used and the whole result is fetched at once, which needs about twice the memory, but lets PostgreSQL use parallel query plans, which can be several times faster for heavy queries. The server-side cursor only accepts SELECT, VALUES, TABLE and WITH statements, so statements such EXPLAIN, SHOW must be run with batched=False
- asDictbool, optional
If True, return an ordered dictionary keyed by column name instead of a tuple of arrays. Repeated column names get a numeric suffix
- intNullValinteger or None, optional
The value that replaces nulls in integer columns (-9999 by default). If None, integer columns with nulls are returned as object arrays with None in place of the nulls
- strNullValstring, optional
The value that replaces nulls in string columns (‘None’ by default)
- nthreadsinteger, optional
The number of threads used to convert the batches to numpy in the batched PostgreSQL mode
- notNamedbool, optional
Deprecated spelling from before 0.27: notNamed=True is the same as batched=False (a client-side cursor). It will be removed in a future release
- Returns:
- rettuple or dictionary
A tuple with one numpy array per column of the query result, or, with asDict=True, an ordered dictionary mapping the column names to the arrays. Nulls in floating point columns become NaN, in timestamp columns NaT; see intNullVal and strNullVal for the integer and string columns
Examples
>>> ra, dec, d25 = sqlutilpy.get('select ra, dec, d25 from rc3', ... host='myhost', db='mydb')
With query parameters:
>>> ra, dec = sqlutilpy.get('select ra, dec from rc3 where name = %s', ... ('NGC 3166', ), host='myhost', db='mydb')
From a SQLite file:
>>> a, b = sqlutilpy.get('select a, b from tmp where a > ?', (3, ), ... db='mydata.db', driver='sqlite3')
- sqlutilpy.execute(query, params=None, *, db='wsdb', driver='psycopg', user=None, password=None, host=None, port=None, conn=None, preamb=None, timeout=None, noCommit=False)
Execute an SQL statement without returning any results
- Parameters:
- querystring
The statement to execute. It can contain placeholders for the parameters: %s for PostgreSQL, ? for SQLite and DuckDB
- paramstuple, optional
Parameters of the statement
- dbstring
The name of the database (PostgreSQL) or the filename of the database (SQLite, DuckDB)
- driverstring, optional
The database driver: ‘psycopg’ (PostgreSQL, the default), ‘sqlite3’ or ‘duckdb’
- userstring, optional
User name for the DB connection (PostgreSQL only)
- passwordstring, optional
DB connection password (PostgreSQL only)
- hoststring, optional
Hostname of the database (PostgreSQL only)
- portinteger, optional
Port of the database (PostgreSQL only)
- connobject, optional
An existing connection to the DB (see getConnection()), to avoid reconnecting. The connection is left open afterwards
- preambstring, optional
SQL to be executed before the statement
- timeoutinteger, optional
How long to wait for a locked database, in seconds (SQLite only)
- noCommitbool, optional
By default the statement is committed. With noCommit=True the commit is not issued
Examples
>>> sqlutilpy.execute('drop table mytab', conn=conn) >>> sqlutilpy.execute('create table mytab (a int)', db='mydb') >>> sqlutilpy.execute('insert into mytab values (%s)', (1, ), db='mydb')
Uploading (PostgreSQL)
- sqlutilpy.upload(tableName, arrays, names=None, *, db='wsdb', driver='psycopg', user=None, password=None, host=None, port=None, conn=None, preamb=None, timeout=None, noCommit=False, temp=False, analyze=False, createTable=True, delimiter='|')
Upload the data stored in a tuple of arrays (or a table) into a new table in the DB. PostgreSQL only.
- Parameters:
- tableNamestring
The name of the table where the data will be uploaded
- arraystuple, astropy.table.Table, pandas.DataFrame or dict
A tuple of arrays that will become the columns of the table, or an astropy Table, pandas DataFrame or dictionary of arrays, in which case the column names are taken from it and the names argument must be omitted. Integer, floating point, boolean, string (unicode or bytes; bytes are decoded as utf-8), and datetime64 arrays are supported, as well as object arrays holding numpy arrays, which become array columns
- namestuple, optional
The column names (one per array). Characters that are not allowed in identifiers (spaces, brackets, dashes) are replaced by underscores, with a warning
- dbstring
Database name
- driverstring
The database driver; only ‘psycopg’ is supported
- 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
- connobject, optional
An existing connection to the DB (see getConnection()), to avoid reconnecting. The connection is left open afterwards
- preambstring, optional
SQL to be executed before the upload
- timeoutinteger, optional
Unused for PostgreSQL
- noCommitbool, optional
If True, the commit is not executed and the table goes away when the connection is closed
- tempbool, optional
If True, a temporary table is created
- analyzebool, optional
If True, the table is analyzed after the upload
- createTablebool, optional
If True (the default), the table is created before uploading. With False the data is appended to an existing table
- delimiterstring, optional
The column delimiter used when sending the data to the DB (the default is
|). Strings containing the delimiter are escaped, so it can be any character
Examples
>>> x = np.arange(10) >>> y = x**.5 >>> sqlutilpy.upload('mytable', (x, y), ('xcol', 'ycol'))
>>> T = astropy.table.Table({'x': [1, 2, 3], 'y': ['a', 'b', 'c']}) >>> sqlutilpy.upload('mytable', T)
- 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, asDict=False, intNullVal=-9999, strNullVal='None')
Join your local data in python with the data in the database. PostgreSQL only.
This uploads the arrays into a temporary table and then runs the query, which can refer to that table. The temporary table exists only for the duration of the call.
- Parameters:
- querystring
The query to be executed; it can refer to the table tableName
- tableNamestring
The name of the temporary table holding your data
- arraystuple
The tuple of arrays with the data to be loaded in the DB
- namestuple
The column names of the temporary table (one per array)
- dbstring
Database name
- driverstring
The database driver; only ‘psycopg’ is supported
- 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
- connobject, optional
An existing connection to the DB (see getConnection()), to avoid reconnecting. The connection is left open afterwards
- preambstring, optional
SQL to be executed before the query
- timeoutinteger, optional
Unused for PostgreSQL
- strLengthinteger, optional
The minimum width allocated for string columns (see get())
- asDictbool, optional
If True, return an ordered dictionary keyed by column name instead of a tuple of arrays
- intNullValinteger or None, optional
The value that replaces nulls in integer columns (see get())
- strNullValstring, optional
The value that replaces nulls in string columns (see get())
- Returns
- ——-
- rettuple or dictionary
The result of the query, as returned by get()
Examples
This extracts the rows of sometable matching the values of the 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.x''', ... 'mytable', (x, y), ('x', 'y'))
Connections
- 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 (PostgreSQL) or the filename of the database (SQLite, DuckDB). For DuckDB ‘:memory:’ gives an in-memory database. For PostgreSQL, if this and the other connection details are not given, the libpq defaults and environment variables (PGDATABASE, PGHOST, PGPORT, PGUSER) are used
- driverstring
The database driver: ‘psycopg’ (PostgreSQL), ‘sqlite3’ or ‘duckdb’
- userstring, optional
Username (PostgreSQL only)
- passwordstring, optional
Password (PostgreSQL only)
- hoststring, optional
Hostname (PostgreSQL only)
- portinteger, optional
Connection port (PostgreSQL only, by default 5432)
- timeoutinteger, optional
How long to wait for a locked database, in seconds (SQLite only, by default 5)
- Returns:
- connobject
Database connection
Examples
>>> conn = sqlutilpy.getConnection(db='mydb', host='myhost', ... driver='psycopg') >>> conn = sqlutilpy.getConnection(db='mydata.db', driver='sqlite3') >>> conn = sqlutilpy.getConnection(db=':memory:', driver='duckdb')
- sqlutilpy.getCursor(conn, *, driver=None, preamb=None, named=True, notNamed=None)
Retrieve the database cursor
- Parameters:
- connobject
Database connection
- driverstring
The database driver: ‘psycopg’, ‘sqlite3’ or ‘duckdb’
- preambstring, optional
SQL to be executed on the cursor before anything else
- namedbool
If True (the default) and the driver is ‘psycopg’, a server-side (named) cursor is returned, so that the results can be fetched in batches; this is what get(batched=True) uses. If False a client-side cursor is returned, as for get(batched=False). Ignored for the other drivers
- notNamedbool, optional
Deprecated spelling from before 0.27: notNamed=True is the same as named=False. It will be removed in a future release
- Returns:
- curobject
Database cursor
Exceptions
- exception sqlutilpy.SqlUtilException