`npyodbc`¶

npyodbc is 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

Quickstart¶

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:

pip install npyodbc

If you've cloned this repository and want to develop npyodbc,

pip install -e '.[dev,test]'

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)

npyodbc¶