GitHub - Quansight/npyodbc: pyodbc, but with native numpy array support

A python interface to Open Database Connectivity (ODBC) drivers built on top of pyodbc that incorporates native support for numpy.

Features

Native support for numpy arrays
Compatible with many ODBC drivers
Can be used anywhere pyodbc is used

Requirements

You'll need a database and an ODBC driver to use npyodbc. There are many different options:

Google BigQuery
Hive from Ubuntu/Debian
Microsoft Access
Microsoft Excel
Microsoft SQL Server
MySQL
Netezza
Oracle
PostgreSQL
SQLite
Teradata
Vertica

Install the appropriate ODBC driver for the database you want to connect to before continuing.

Installation

You can install npyodbc via pip:

Development installation

If you've cloned this repository and want to work on it locally,

Development installation using conda

If you're using conda, use the included environment.yaml to install requirements:

conda env create --file environment.yaml
conda activate npyodbc-dev
pip install -e .

Usage

Let's set up a containerized database as an example (you'll need docker before we begin):

Create a docker-compose.yml containing the following:

services:
  postgres:
    image: postgres:11
    environment:
      - POSTGRES_DB=postgres_db
      - POSTGRES_USER=postgres_user
      - POSTGRES_PASSWORD=postgres_pwd
      - POSTGRES_HOST_AUTH_METHOD=trust

    ports:
      - "5432:5432"

Start the container: docker compose up --build
Configure your ODBC driver by setting /etc/odbc.ini:

[PostgreSQL Unicode]
Description = PostgreSQL connection to database
Driver = PostgreSQL Unicode
Servername = localhost
Port = 5432
Database = postgres_db
Username = postgres_user
Password = postgres_pwd

We also need to configure /etc/odbcinst.ini:

[PostgreSQL Unicode]
Description = PostgreSQL ODBC driver (Unicode)
Driver = /usr/lib/psqlodbcw.so

Now your system is ready to connect.

Create a database to connect to:

# Set your environment to match the settings in the container
export PGHOST=localhost
export PGPORT=5432
export PGDATABASE=postgres_db
export PGUSER=postgres_user
export PGPASSWORD=postgres_pwd

# Create a new database
psql -c "CREATE DATABASE test WITH encoding='UTF8' LC_COLLATE='en_US.utf8' LC_CTYPE='en_US.utf8'"

Connect with npyodbc:

import npyodbc

# Set the connection string to match the settings in your `odbc.ini` and `odbcinst.ini`
connection = npyodbc.connect(
    "DRIVER={PostgreSQL Unicode};SERVER=localhost;PORT=5432;UID=postgres_user;PWD=postgres_pwd;DATABASE=test"
)

cursor = connection.cursor()

# Create a table and insert some values
cursor.execute('create table t1(col text)')
cursor.execute('insert into t1 values (?)', 'a test string')

# Retrieve entries from the table as Python objects
rows = cursor.execute('select * from t1').fetchall()

# Returns [('a test string',)]
print(rows)