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)
def main():
# Build the URL
url = URL.create(drivername="flight_sql",
host="localhost",
port=31337,
username=os.getenv("FLIGHT_USERNAME", "flight_username"),
password=os.getenv("FLIGHT_PASSWORD", "flight_password"),
query={"disableCertificateVerification": "True",
"useEncryption": "True"
}
)
print(f"Database URL: {url}")
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)
if __name__ == "__main__":
main()
Credits
Much code and inspiration was taken from repo: https://github.com/Mause/duckdb_engine
Project details
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.10.tar.gz
Algorithm | Hash digest | |
---|---|---|
SHA256 | 63eaadb68538ebb06646244d82b3a5c181be3cf2479a232056ebf0001563c003 |
|
MD5 | 760f811cb63f516d9cd4a9e17025ebfe |
|
BLAKE2b-256 | 450a1558f880eb3791e2cf95004a1bf9d28f639d4d517f120854734b70e35668 |
Close
Hashes for sqlalchemy_flight_sql_adbc_dialect-0.0.10-py3-none-any.whl
Algorithm | Hash digest | |
---|---|---|
SHA256 | 69f15d34f163800997b710a372903c313640800ee39c8f22b412d7671581def1 |
|
MD5 | 36cf7a68dfb5c8df1667a80c84c4f1ae |
|
BLAKE2b-256 | 2ab41028926397b0b6790585aaf01438866c0ddcfa02be7559c8cef2ae45df91 |