TechPrufer
Get started
Hex
Hex logoHex · Feature trail

Python SQL Server Integration

2 posts · Feb 2023 – Apr 2023

What this trail covers

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

All Hex trails
  1. Connecting to and querying SQL Server with PythonThis post details the technical steps for connecting to and querying SQL Server using Python. It covers installing the pyodbc library (including platform-specific instructions for macOS and Apple Silicon), building a Python connection string using Azure SQL parameters, creating and managing database connections with the 'with' statement, executing SQL queries for table creation and data insertion (both single and bulk inserts using executemany), and running various SQL queries like SELECT with WHERE and ORDER BY clauses. It also highlights the use of Mockaroo for data generation and the return types from pyodbc queries.
  2. How to query a SQL database from PandasThis post details how to query SQL databases directly into Pandas DataFrames using Python. It covers establishing connections via SQLAlchemy and SQLite, and demonstrates using `pandas.read_sql` with various SQL queries, including filtering, limiting, and dynamic query construction. It also highlights the `chunksize` argument for handling large datasets and shows an example of plotting data directly from a SQL query.