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