Remote read_sql
Read data into a pandas data frame from a remote mysql server
Installation
pip install remote-read-sql
Usage
In this example, remote_read_sql opens an ssh tunnel and connects to the mysql server locally on port 3306. The SQL query is sanitized and passed to pandas read_sql.
After reading the data into the dataframe, the ssh and db connections are closed.
Storing your credentials in files
You should read your credentials from a file or files. Do not write credentials directly in your notebook.
In this example, the ssh credentials are in a ENV file that might look something like this:
SSH_SERVER_IP=server.example.com SSH_USER=user SSH_KEY_PATH=~/.ssh/id_rsa SSH_KEY_PASS= LOCAL_BIND_PORT=3307 REMOTE_HOST=127.0.0.1 LOCAL_BIND_PORT=3307 REMOTE_DB_PORT=3306
and the mysql credentials are in the my.cnf file and might look like this:
[remote_server] user=user_readonly password=password default-character-set=utf8 host=127.0.0.1 port=3306
Preparing your credentials
Since you may be calling remote_read_sql several times in the same notebook, store the paths to your credentials in a dictionary as a convenience.
# change to your own paths
ssh_config_path = Path("~/.my_ssh_config")
my_cnf_path = Path("~/.my.cnf")
db_name = "my_database"
# combine kwargs into a dictionary
conn_opts = {
"ssh_config_path": ssh_config_path,
"my_cnf_path": my_cnf_path,
"my_cnf_connection_name": "remote_server",
"db_name": db_name,
}
Running a single query
To run a single query and return a Dataframe, pass the SQL query to remote_read_sql along with your conn_opts from above. The SQL query must be a valid SELECT query.
# open ssh, open db, read SQL into dataframe, close db, close ssh
df = remote_read_sql("SELECT * FROM subject_glucose", **conn_opts)
# inspect the dataframe
df.head()
Running multiple queries
When running remote_read_sql with the SQL query as above, the connection closes immediately after running the SQL statement. If you want to run several SQL queries using the same connection, use remote_connect as a context manager.
remote_connect opens the connection.
call pd.read_sql() for multiple SQL queries within the with statement
Once you leave the with statement, remote_connect closes the connection.
If you have read/write permissions to your database, you may want to pass your query through safe_sql before you pass it to pandas read_sql.
import pandas as pd
from remote_read_sql import remote_connect, safe_sql
with remote_connect(**conn_opts) as db_conn:
# connection db_conn is open
# read sql
df_glucose = pd.read_sql(safe_sql("SELECT * FROM subject_glucose"), db_conn)
# read sql
df_bp = pd.read_sql(safe_sql("SELECT * FROM subject_bp"), db_conn)
# connection db_conn is closed
# view your Dataframes
df_glucose.head()
df_bp.head()
Metadata
Release files for remote-read-sql 1.4.0
For a detailed explanation of source distributions (sdists) and built distributions (wheels), please see the package formats documentation.
Source distribution (sdist)
| File | Size | Uploaded | Provenance | Actions |
|---|---|---|---|---|
| remote_read_sql-1.4.0.tar.gz | 5.4 kB | Without provenance | Details for remote_read_sql-1.4.0.tar.gz |
Built distribution (wheel)
| File | Interpreter | ABI | Platform | Actions Reset |
|---|---|---|---|---|
| remote_read_sql-1.4.0-py3-none-any.whl | Python 3 | none | any | Details for remote_read_sql-1.4.0-py3-none-any.whl |
Total release size: 12.9 kB
Release files / remote_read_sql-1.4.0.tar.gz
| Download URL | remote_read_sql-1.4.0.tar.gz |
|---|---|
| Size | 5.4 kB |
| Tags | Source |
|
SHA-256 checksum How to use checksums |
d33fc8efb2abefe8947d00f80f68a695bcef56f6ad31297f6777466d9079bb40
|
|
BLAKE2b-256 checksum How to use checksums |
2a9f2cd81773d45b8f3629e8e0b0ed8ac50b83301316fa3721de9e9304ae55bd
|
| Upload date | |
|
Uploaded using Trusted Publishing? What is trusted publishing? |
No |
| Uploaded via |
uv/0.11.18 {"installer":{"name":"uv","version":"0.11.18","subcommand":["publish"]},"python":null,"implementation":{"name":null,"version":null},"distro":{"name":"macOS","version":null,"id":null,"libc":null},"system":{"name":null,"release":null},"cpu":null,"openssl_version":null,"setuptools_version":null,"rustc_version":null,"ci":null}
|
Release files / remote_read_sql-1.4.0-py3-none-any.whl
| Download URL | remote_read_sql-1.4.0-py3-none-any.whl |
|---|---|
| Size | 7.5 kB |
| Tags | Python 3 |
|
SHA-256 checksum How to use checksums |
d8948d8e2ab8aa25fd8406c9fd64ce66524a22e9a3f369a343c1d90caa18d936
|
|
BLAKE2b-256 checksum How to use checksums |
85cb5731aae5a404f6bababb4cb8700338ff0d906f6b0cbc83dbc04f8af0fb4e
|
| Upload date | |
|
Uploaded using Trusted Publishing? What is trusted publishing? |
No |
| Uploaded via |
uv/0.11.18 {"installer":{"name":"uv","version":"0.11.18","subcommand":["publish"]},"python":null,"implementation":{"name":null,"version":null},"distro":{"name":"macOS","version":null,"id":null,"libc":null},"system":{"name":null,"release":null},"cpu":null,"openssl_version":null,"setuptools_version":null,"rustc_version":null,"ci":null}
|
Security for remote-read-sql 1.4.0
Provenance
Provenance describes where a file came from. On PyPI, provenance is shared via attestations, which provide a verifiable record of the build or publishing details. View details, limitations and caveats.
No provenance found
None of the files in this release were uploaded with provenance metadata.
A note on package security
PyPI is a repository for distributing packages, not a service for auditing them. While third-party security researchers and our staff do their best to detect and remove malicious packages, PyPI cannot guarantee the quality, security or suitability of any given package.
To learn more about protecting yourself from malicious actors, visit our package security documentation.