Database query code returning numpy arrays
Python module to query SQL databases and return numpy arrays, upload tables and run join queries involving local arrays and the tables in the DB. The module only works with PostgreSQL and SQLite databases.
The full documentation is available here
Author: Sergey Koposov (Uni of Cambridge/CMU/Uni of Edinburgh)
To install the package you just need to do pip install.
pip install sqlutilpy
Throughout this readme, I'll assume that the .pgpass file ( https://www.postgresql.org/docs/11/libpq-pgpass.html ) has been created with the login/password details for Postgresql. If that is not the case, all of the commands given above will also need user='....' and password='...' options
Querying the database and retrieving the results
import sqlutilpy ra,dec = squtilpy.get('select ra,dec from mytable', host='HOST_NAME_OF_MY_PG_SERVER', db='THE_NAME_OF_MY_DB')
By default sqlutilpy.get executes the result and returns the tuple of results
Uploading your arrays as column in a table
x = np.arange(10) y = x**.5 sqlutilpy.upload('mytable',(x,y),('xcol','ycol'))
This will create a table called mytable with columns xcol and ycol
Join query involving your local data and the database table
Imagine you have arrays myid and y and you want to to extract all the information from somebigtable for objects with id=myid. In principle you could upload the arrays in the DB and run a query, but local_join function does that for you.
myid = np.arange(10) y = x**.5 R=sqlutilpy.local_join('select * from mytmptable as m, somebigtable as s where s.id=m.myid order by m.myid', 'mytmptable',(x,y),('myid','ycol'))
It executes a query as if you arrays where in a mytmptable. ( behind the scenes it uploads the data to the db and runs a query)
Release history Release notifications | RSS feed
Download the file for your platform. If you're not sure which to choose, learn more about installing packages.
|Filename, size||File type||Python version||Upload date||Hashes|
|Filename, size sqlutilpy-0.16.0-py3-none-any.whl (9.9 kB)||File type Wheel||Python version py3||Upload date||Hashes View|
|Filename, size sqlutilpy-0.16.0.tar.gz (16.7 kB)||File type Source||Python version None||Upload date||Hashes View|
Hashes for sqlutilpy-0.16.0-py3-none-any.whl