A SQLAlchemy dialect for connecting to a Flight SQL server with ADBC
Project description
SQLAlchemy Flight SQL ADBC Dialect
Basic SQLAlchemy dialect for the Flight SQL Server Example
Installation
Option 1 - from PyPi
$ pip install sqlalchemy-flight-sql-adbc-dialect
Option 2 - from source - for development
git clone https://github.com/prmoore77/sqlalchemy-flight-sql-adbc-dialect
cd sqlalchemy-flight-sql-adbc-dialect
# Create the virtual environment
python3 -m venv .venv
# Activate the virtual environment
. .venv/bin/activate
# Upgrade pip, setuptools, and wheel
pip install --upgrade pip setuptools wheel
# Install SQLAlchemy Flight SQL ADBC Dialect - in editable mode with dev dependencies
pip install --editable .[dev]
Note
For the following commands - if you running from source and using --editable
mode (for development purposes) - you will need to set the PYTHONPATH environment variable as follows:
export PYTHONPATH=$(pwd)/src
Usage
Once you've installed this package, you should be able to just use it, as SQLAlchemy does a python path search
Start a Flight SQL Server - example below - see https://github.com/voltrondata/flight-sql-server-example for more details
docker run --name flight-sql \
--detach \
--rm \
--tty \
--init \
--publish 31337:31337 \
--env TLS_ENABLED="1" \
--env FLIGHT_PASSWORD="flight_password" \
--env PRINT_QUERIES="1" \
--pull missing \
voltrondata/flight-sql:latest
Connect with the SQLAlchemy Flight SQL ADBC Dialect
import os
import logging
from sqlalchemy import create_engine, MetaData, Table, select, Column, text, Integer, String, Sequence
from sqlalchemy.orm import Session
from sqlalchemy.orm import declarative_base
from sqlalchemy.engine.url import URL
# Setup logging
logging.basicConfig()
logging.getLogger('sqlalchemy.engine').setLevel(logging.INFO)
Base = declarative_base()
class FakeModel(Base): # type: ignore
__tablename__ = "fake"
id = Column(Integer, Sequence("fakemodel_id_sequence"), primary_key=True)
name = Column(String)
# Build the URL
url = URL(drivername="flight_sql",
host="localhost",
port=31337,
database=None,
username=os.getenv("FLIGHT_USERNAME", "flight_username"),
password=os.getenv("FLIGHT_PASSWORD", "flight_password"),
query={"disableCertificateVerification": "True",
"useEncryption": "True"
}
)
engine = create_engine(url=url)
Base.metadata.create_all(bind=engine)
metadata = MetaData()
metadata.reflect(bind=engine)
for table_name in metadata.tables:
print(f"Table name: {table_name}")
with Session(bind=engine) as session:
# Try ORM
session.add(FakeModel(id=1, name="Joe"))
session.commit()
joe = session.query(FakeModel).filter(FakeModel.name == "Joe").first()
assert joe.name == "Joe"
# Execute some raw SQL
results = session.execute(statement=text("SELECT * FROM fake")).fetchall()
print(results)
# Try a SQLAlchemy table select
fake: Table = metadata.tables["fake"]
stmt = select(fake.c.name)
results = session.execute(statement=stmt).fetchall()
print(results)
Credits
Much code and inspiration was taken from repo: https://github.com/Mause/duckdb_engine
Project details
Release history Release notifications | RSS feed
Download files
Download the file for your platform. If you're not sure which to choose, learn more about installing packages.
Source Distribution
Built Distribution
Close
Hashes for sqlalchemy_flight_sql_adbc_dialect-0.0.5.tar.gz
Algorithm | Hash digest | |
---|---|---|
SHA256 | 82b8fce6bf32a2cfae51250943df7c2f282f91d3ca795a7e8d95f711800ccea2 |
|
MD5 | 7b35111f7f1e122966fe29d8e49a985f |
|
BLAKE2b-256 | 7e97ef93a215985109937c3127832b2a1752af1a65d8a90bdb34a0bba5c185a5 |
Close
Hashes for sqlalchemy_flight_sql_adbc_dialect-0.0.5-py3-none-any.whl
Algorithm | Hash digest | |
---|---|---|
SHA256 | 86ca31bb6fe180bb6f579be67cd16bcc729d297fcc316a345ed1620487e326b2 |
|
MD5 | b8a08726b2aa37782a26ff3eaae98674 |
|
BLAKE2b-256 | bea7eeecfe1bc90447090be721a266a54db7407caf395c762182ea4ede28ea66 |