Dopplershift
Pythonic SQL for mere mortals.
Install
pip install -U dopplershift
Usage
from dopplershift import Connection
host = 'rayleigh.3onystnrm1rn.us-east-1.redshift.amazonaws.com'
dbname = 'testing'
port = '1234'
user = 'alvations'
pwd = 'JjMKkdW8vJABswyT'
con = Connection(dbname, host, port, user, pwd)
Show all schemas and tables in DB.
con.show_all_tables()
[out]:
table_schema | table_name
--------------+----------------
testing | data
stagging | results
production | results
Show columns in specific table.
con.show_column_names('testing.data')
[out]:
view_schema name | view_name name | col_name name | col_type varchar | col_num int
---------------------------------------------------------------------------------------------------
testing | data | id | integer | 1
testing | data | text | character varying(1024) | 2
testing | data | language | character varying(3) | 4
testing | data | timestamp | timestamp without time zone | 5
Other Functions.
# Get column names.
>>> con.get_column_names('testing.data')
('id', 'text', 'language', 'timestamp')
# Get top N rows from table.
>>> con.topn_rows('testing.data', n=3, column_names="id, text, language")
[(1, "hello world", "en"), (2, "hallo welt", "de"), (3, "ä½ å¥½ï¼Œä¸–ç•Œã€‚", "zh")]
# Execute queries and fetch results.
>>> con.execute_fetchall("SELECT TOP 2 * FROM testing.data")
[ (1, "hello world", "en", datetime.datetime(2019, 9, 2, 14, 5, 58)),
(2, "hallo welt", "de", datetime.datetime(2019, 9, 2, 14, 6, 15)) ]