TechPrufer
Get started
Hex
Hex logoHex · Feature trail

Python SQL Integration

5 posts · Jul 2022 – Nov 2022

What this trail covers

Hex has evolved its data integration capabilities by enabling users to connect to and query SQL databases like MySQL and SQL Server using Python. This allows for seamless data loading, manipulation, and analysis directly within the Hex environment, leveraging the robustness of SQL databases for data storage and Python for advanced analytics. The integration supports creating tables, inserting data in bulk, and running complex SQL queries for filtering and sorting.

All Hex trails
  1. Connecting to and querying MySQL from PythonThis post details how to connect to and query MySQL databases from Python using two primary methods: `mysql-connector-python` for direct SQL execution and `sqlalchemy` with Pandas for deeper analysis. It covers obtaining database credentials, securely storing them as environment variables, and executing SQL queries to retrieve data into Python. The post highlights the advantages of using SQLAlchemy with Pandas for a more Pythonic and analysis-friendly data handling experience.
  2. Connecting to and querying BigQuery from PythonThis post details how to connect to and query Google BigQuery from Python using the google-cloud-bigquery client library. It covers installation, authentication with service account credentials, running SQL queries, reading results into pandas DataFrames, and writing DataFrames back to BigQuery. It also mentions the availability of Hex for a more integrated experience.
  3. How to write SQL in Python with PandasThis post introduces two primary methods (PandaSQL with SQLite and DuckDB) and a supplementary method (.query() with Pandas) for querying Pandas DataFrames using SQL syntax. It details the installation, usage, and performance characteristics of each approach, highlighting DuckDB's speed advantage for analytical queries and PandaSQL's simplicity. The post also touches upon the limitations and specific syntax nuances of each method.
  4. Connecting to and querying Snowflake from PythonThis post details how to connect to and query Snowflake from Python using the `snowflake-connector-python` library. It covers installing the connector, authenticating with Snowflake credentials (emphasizing secure storage via environment variables), running basic SQL queries, and fetching results directly into a pandas DataFrame using `fetch_pandas_all()`. It also briefly mentions alternative methods for integration.
  5. Connecting to and querying Redshift from PythonThis post details how to connect to and query Amazon Redshift from Python using the official `redshift_connector` package. It covers installation, authentication using environment variables, running basic SQL queries, and reading results into pandas DataFrames. It also provides troubleshooting tips for common connection and transaction errors.