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='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
- dbstring
Database name
- driverstring
Driver for the DB connection (‘psycopg2’ 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='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:
- 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
- dbstring
The name of the database
- driverstring, optional
The sql driver to be used (psycopg2 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
- 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 ‘psycopg2’ 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, notNamed=False)¶
Retrieve the database cursor
- 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:
- 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='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:
- 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 “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)