AoikQueryHurry

Database client libs' quickstart doc and demo.

AoikQueryHurry Setup

AoikQueryHurry Setup via git
AoikQueryHurry Setup via pip

AoikQueryHurry Setup via git

Clone this git repository to local:

git clone https://github.com/AoiKuiyuyou/AoikQueryHurry

In the local repository directory, the entry program can be run directly without further installation:

python src/aoikqueryhurry/main/aoikqueryhurry.py

If you prefer an installation, run the setup.py file in the local repository directory:

python setup.py install

The installation will create an executable file in Python's "script directory". If Python's "script directory" has been added to your command console's PATH environment variable, the entry program should be accessible in short name:

aoikqueryhurry

AoikQueryHurry Setup via pip

Run:

pip install git+https://github.com/AoiKuiyuyou/AoikQueryHurry

Installing via pip is equivalent to cloning this git repository to local and running the setup.py file in the local repository directory.

SQLite

Client libs:

sqlite3

sqlite3

Doc is here.

Tested working with:

sqlite3
SQLAlchemy 1.0.6
Python 2.7, 3.4
Windows, Linux

Setup and Usage:

sqlite3 - Setup on Windows
sqlite3 - Setup on Linux
sqlite3 - Verify setup
sqlite3 - Usage
sqlite3 - Usage with SQLAlchemy

sqlite3 - Setup on Windows

sqlite3 is available in Python's standard lib.

sqlite3 - Setup on Linux

sqlite3 is available in Python's standard lib.

If you are building Python from source, remember to install dependency libs:

apt-get install -y libsqlite3-dev

sqlite3 - Verify setup

Run:

python -c "import sqlite3"

sqlite3 - Usage

Run:

aoikqueryhurry -R aoikqueryhurry.db.sqlite.sqlite3.main::rfunc -d "demo_db" -q "SELECT 'hello'"

The file is here.

sqlite3 - Usage with SQLAlchemy

Run:

aoikqueryhurry -R aoikqueryhurry.db.sqlite.sqlite3.sa::rfunc -d "demo_db" -q "SELECT 'hello'"

The file is here.

SQLite ODBC

Setup and Usage:

sqliteodbc
SQLite ODBC Usage

sqliteodbc

Doc is here.

Setup and Usage:

sqliteodbc - Setup on Windows
sqliteodbc - Setup on Linux
sqliteodbc - Add driver definition on Windows
sqliteodbc - Add driver definition on Linux
sqliteodbc - Query driver definition on Windows
sqliteodbc - Query driver definition on Linux
sqliteodbc - Add DSN definition on Windows
sqliteodbc - Add DSN definition on Linux
sqliteodbc - Query DSN definition on Windows
sqliteodbc - Query DSN definition on Linux
sqliteodbc - Verify DSN connectivity on Windows
sqliteodbc - Verify DSN connectivity on Linux

sqliteodbc - Setup on Windows

Download installer from here:

curl -O http://www.ch-werner.de/sqliteodbc/sqliteodbc_w64.exe

sqliteodbc - Setup on Linux

Install dependency libs:

apt-get install -y libsqlite3-dev
apt-get install -y unixodbc-dev

Download source code from here:

curl -O http://www.ch-werner.de/sqliteodbc/sqliteodbc-0.9991.tar.gz

Compile and install:

tar xvf sqliteodbc-0.9991.tar.gz

cd sqliteodbc-0.9991

./configure

make

make install

sqliteodbc - Add driver definition on Windows

See here.

sqliteodbc - Add driver definition on Linux

Doc is here.

Find libsqlite3odbc.so:

updatedb

locate libsqlite3odbc.so

Result is /usr/local/lib/libsqlite3odbc.so, YMMV.

Add to /etc/odbcinst.ini:

cat <<'ZZZ' | odbcinst -i -d -r 
[SQLite3]
Driver=/usr/local/lib/libsqlite3odbc.so
Setup=/usr/local/lib/libsqlite3odbc.so
ZZZ

Arguments are explained here.

sqliteodbc - Query driver definition on Windows

See here.

sqliteodbc - Query driver definition on Linux

odbcinst -q -d -n SQLite3

Arguments are explained here.

sqliteodbc - Add DSN definition on Windows

See here.

sqliteodbc - Add DSN definition on Linux

Run:

cat <<'ZZZ' | odbcinst -i -s -l -r
[SQLiteDemoDSN]
Driver       = SQLite3
Database     =
ZZZ

SQLiteDemoDSN specifies the DSN definition's name.
Driver specifies the driver definition to use, as defined in /etc/odbcinst.ini.
odbcinst's arguments are explained here.

sqliteodbc - Query DSN definition on Windows

See here.

sqliteodbc - Query DSN definition on Linux

Run:

odbcinst -q -s -n SQLiteDemoDSN

Arguments are explained here.

sqliteodbc - Verify DSN connectivity on Windows

See here.

sqliteodbc - Verify DSN connectivity on Linux

Run:

isql -v SQLiteDemoDSN

Arguments are explained here.

SQLite ODBC Usage

Default ODBC driver names:

Windows
- SQLite3 ODBC Driver
Linux
- SQLite3

Run:

# Windows, Linux, DSN
aoikqueryhurry -R "aoikqueryhurry.db.sqlite.odbc.rfunc::rfunc" -C "aoikqueryhurry.db.odbc.ceodbc.cfunc::CFUNC" -S "SQLiteDemoDSN" -d "demo_db" -q "SELECT 'hello'"

aoikqueryhurry -R "aoikqueryhurry.db.sqlite.odbc.rfunc::rfunc" -C "aoikqueryhurry.db.odbc.pyodbc.cfunc::CFUNC" -S "SQLiteDemoDSN" -d "demo_db" -q "SELECT 'hello'"

aoikqueryhurry -R "aoikqueryhurry.db.sqlite.odbc.rfunc::rfunc" -C "aoikqueryhurry.db.odbc.pypyodbc.cfunc::CFUNC" -S "SQLiteDemoDSN" -d "demo_db" -q "SELECT 'hello'"

# Windows, DSN-Less
aoikqueryhurry -R "aoikqueryhurry.db.sqlite.odbc.rfunc::rfunc" -C "aoikqueryhurry.db.odbc.ceodbc.cfunc::CFUNC" -O "SQLite3 ODBC Driver" -d "demo_db" -q "SELECT 'hello'"

aoikqueryhurry -R "aoikqueryhurry.db.sqlite.odbc.rfunc::rfunc" -C "aoikqueryhurry.db.odbc.pyodbc.cfunc::CFUNC" -O "SQLite3 ODBC Driver" -d "demo_db" -q "SELECT 'hello'"

aoikqueryhurry -R "aoikqueryhurry.db.sqlite.odbc.rfunc::rfunc" -C "aoikqueryhurry.db.odbc.pypyodbc.cfunc::CFUNC" -O "SQLite3 ODBC Driver" -d "demo_db" -q "SELECT 'hello'"

# Linux, DSN-Less
aoikqueryhurry -R "aoikqueryhurry.db.sqlite.odbc.rfunc::rfunc" -C "aoikqueryhurry.db.odbc.ceodbc.cfunc::CFUNC" -O "SQLite3" -d "demo_db" -q "SELECT 'hello'"

aoikqueryhurry -R "aoikqueryhurry.db.sqlite.odbc.rfunc::rfunc" -C "aoikqueryhurry.db.odbc.pyodbc.cfunc::CFUNC" -O "SQLite3" -d "demo_db" -q "SELECT 'hello'"

aoikqueryhurry -R "aoikqueryhurry.db.sqlite.odbc.rfunc::rfunc" -C "aoikqueryhurry.db.odbc.pypyodbc.cfunc::CFUNC" -O "SQLite3" -d "demo_db" -q "SELECT 'hello'"

Argument -R specifies runner function.
The file is here.
Argument -C specifies connect function.
Argument -S specifies data source name.
Argument -O specifies ODBC driver name.

MySQL

Tested working with:

MySQL Community Server 5.6.25

Client libs:

MySQL - Preparation
cymysql
MySQL Connector Python
mysqlclient
MySQL-Python
MySQLdb
oursql
pymysql
umysql

MySQL - Preparation

MySQL Preparation - Log in as superuser
MySQL Preparation - Create database
MySQL Preparation - Create user
MySQL Preparation - Verify user login
MySQL Preparation - Install dependency libs

MySQL Preparation - Log in as superuser

Run:

mysql -h 127.0.0.1 -P 3306 -u root

MySQL Preparation - Create database

Run:

CREATE DATABASE demo_db DEFAULT CHARACTER SET utf8mb4;

MySQL Preparation - Create user

Run:

GRANT ALL ON demo_db.* TO demo_user@'127.0.0.1' IDENTIFIED BY 'demo_pass';

MySQL Preparation - Verify user login

Run:

select *
from (
  select user, host, '' as db, '' as tb,
  if(select_priv='Y', 'Global', '') AS 'SELECT',
  if(insert_priv='Y', 'Global', '') AS 'INSERT',
  if(update_priv='Y', 'Global', '') AS 'UPDATE',
  if(delete_priv='Y', 'Global', '') AS 'DELETE',
  if(create_priv='Y', 'Global', '') AS 'CREATE',
  if(drop_priv='Y', 'Global', '') AS 'DROP',
  if(index_priv='Y', 'Global', '') AS 'INDEX',
  if(alter_priv='Y', 'Global', '') AS 'ALTER'
  from mysql.user
  UNION
  select user, host, db, '' as tb,
  if(select_priv='Y', 'Database', ''),
  if(insert_priv='Y', 'Database', ''),
  if(update_priv='Y', 'Database', ''),
  if(delete_priv='Y', 'Database', ''),
  if(create_priv='Y', 'Database', ''),
  if(drop_priv='Y', 'Database', ''),
  if(index_priv='Y', 'Database', ''),
  if(alter_priv='Y', 'Database', '')
  from mysql.db
  UNION
  select user, host, db, table_name,
  if(table_priv & 1, 'Table', ''),
  if(table_priv & 2, 'Table', ''),
  if(table_priv & 4, 'Table', ''),
  if(table_priv & 8, 'Table', ''),
  if(table_priv & 16, 'Table', ''),
  if(table_priv & 32, 'Table', ''),
  if(table_priv & 256, 'Table', ''),
  if(table_priv & 512, 'Table', '')
  from mysql.tables_priv
) as t1
where user = 'demo_user'
order by user, host, db, tb

MySQL Preparation - Install dependency libs

On Linux, run:

apt-get install -y python-dev
apt-get install -y python3-dev
apt-get install -y libmysqlclient-dev

cymysql

PyPI
Github

Tested working with:

cymysql 0.8.1
SQLAlchemy 1.0.6
Python 2.7, 3.4
Windows, Linux

Setup and Usage:

cymysql - Setup
cymysql - Verify setup
cymysql - Usage
cymysql - Usage with SQLAlchemy

cymysql - Setup

On both Windows and Linux, install using pip:

pip install cymysql

cymysql - Verify setup

Run:

python -c "import cymysql"

cymysql - Usage

Run:

aoikqueryhurry -R "aoikqueryhurry.db.mysql.cymysql.main::rfunc" -H "127.0.0.1" -P "3306" -u "demo_user" -p "demo_pass" -d "demo_db" -q "SELECT 'hello'"

The file is here.
The combination of cymysql 0.8.1 and Python 2.7 is returning unexpected None result. Python 3.4 works.

cymysql - Usage with SQLAlchemy

Run:

aoikqueryhurry -R "aoikqueryhurry.db.mysql.cymysql.sa::rfunc" -H "127.0.0.1" -P "3306" -u "demo_user" -p "demo_pass" -d "demo_db" -q "SELECT 'hello'"

The file is here.
The combination of cymysql 0.8.1 and Python 2.7 is returning unexpected None result. Python 3.4 works.

MySQL Connector Python

PyPI
Home

MySQL Connector/Python is the official client lib for MySQL.
Its code package is mysql.connector.

Tested working with:

MySQL Connector/Python 2.0.4
SQLAlchemy 1.0.6
Python 2.7, 3.4
Windows, Linux

Setup and Usage:

MySQL Connector Python - Setup
MySQL Connector Python - Verify setup
MySQL Connector Python - Usage
MySQL Connector Python - Usage with SQLAlchemy

MySQL Connector Python - Setup

On both Windows and Linux, download Platform Independent source code from here:

curl -O http://cdn.mysql.com/Downloads/Connector-Python/mysql-connector-python-2.0.4.tar.gz

Install from source code:

pip install mysql-connector-python-2.0.4.tar.gz

MySQL Connector Python - Verify setup

Run:

python -c "import mysql.connector"

MySQL Connector Python - Usage

Run:

aoikqueryhurry -R "aoikqueryhurry.db.mysql.mysql_connector.main::rfunc" -H "127.0.0.1" -P "3306" -u "demo_user" -p "demo_pass" -d "demo_db" -q "SELECT 'hello'"

The file is here.

MySQL Connector Python - Usage with SQLAlchemy

Run:

aoikqueryhurry -R "aoikqueryhurry.db.mysql.mysql_connector.sa::rfunc" -H "127.0.0.1" -P "3306" -u "demo_user" -p "demo_pass" -d "demo_db" -q "SELECT 'hello'"

The file is here.

mysqlclient

PyPI
Github

mysqlclient is a fork of MySQL-Python.
Its code package is MySQLdb, same as MySQL-Python's.

Tested working with:

mysqlclient 1.3.6
SQLAlchemy 1.0.6
Python 2.7, 3.4
Windows, Linux

Setup and Usage:

mysqlclient - Setup
mysqlclient - Verify setup
mysqlclient - Usage

mysqlclient - Setup

On both Windows and Linux, install using pip:

pip install mysqlclient

On Windows, another method is to download installer from here.

mysqlclient - Verify setup

Run:

python -c "import MySQLdb"

mysqlclient - Usage

The usage is same as MySQL-Python's.

MySQL-Python

PyPI
Github
SourceForge

Its code package is MySQLdb.

There is a low-level module _mysql that implements MySQL C API.

Tested working with:

MySQL-Python 1.2.5
SQLAlchemy 1.0.6
Python 2.7
Python 3 is not supported. See also mysqlclient, which is a fork that supports Python 3.
Windows, Linux

Setup and Usage:

MySQL-Python - Setup on Windows
MySQL-Python - Setup on Linux
MySQL-Python - Verify setup
MySQL-Python - Usage
MySQL-Python - Usage with SQLAlchemy

MySQL-Python - Setup on Windows

Download wheel file from here:

MySQL_python-1.2.5-cp27-none-win_amd64.whl

Install from wheel file:

pip install MySQL_python-1.2.5-cp27-none-win_amd64.whl

MySQL-Python - Setup on Linux

Install using pip:

pip install MySQL-Python

MySQL-Python - Verify setup

Run:

python -c "import MySQLdb"

python -c "import _mysql"

MySQL-Python - Usage

Run:

aoikqueryhurry -R "aoikqueryhurry.db.mysql.mysql_python.main::rfunc" -H "127.0.0.1" -P "3306" -u "demo_user" -p "demo_pass" -d "demo_db" -q "SELECT 'hello'"

The file is here.

Run:

aoikqueryhurry -R "aoikqueryhurry.db.mysql.mysql_python.main_c_api::rfunc" -H "127.0.0.1" -P "3306" -u "demo_user" -p "demo_pass" -d "demo_db" -q "SELECT 'hello'"

The file is here.

MySQL-Python - Usage with SQLAlchemy

Run:

aoikqueryhurry -R "aoikqueryhurry.db.mysql.mysql_python.sa::rfunc" -H "127.0.0.1" -P "3306" -u "demo_user" -p "demo_pass" -d "demo_db" -q "SELECT 'hello'"

The file is here.

MySQLdb

MySQLdb is the code package name of MySQL-Python.

oursql

PyPI
Launchpad

Tested working with:

oursql 0.9.3.1, 0.9.4
SQLAlchemy 1.0.6
Python 2.7
Python 3 is not supported.
Windows, Linux

Setup and Usage:

oursql - Setup on Windows
oursql - Setup on Linux
oursql - Verify setup
oursql - Usage
oursql - Usage with SQLAlchemy

oursql - Setup on Windows

Download wheel file from here:

oursql-0.9.3.1-cp27-none-win_amd64.whl
# or
oursql-0.9.4-cp34-none-win_amd64.whl

Install from wheel file:

pip install oursql-0.9.3.1-cp27-none-win_amd64.whl
# or
pip install oursql-0.9.4-cp34-none-win_amd64.whl

oursql - Setup on Linux

Install using pip:

pip install oursql

oursql - Verify setup

Run:

python -c "import oursql"

oursql - Usage

Run:

aoikqueryhurry -R "aoikqueryhurry.db.mysql.oursql.main::rfunc" -H "127.0.0.1" -P "3306" -u "demo_user" -p "demo_pass" -d "demo_db" -q "SELECT 'hello'"

The file is here.

oursql - Usage with SQLAlchemy

Run:

aoikqueryhurry -R "aoikqueryhurry.db.mysql.oursql.sa::rfunc" -H "127.0.0.1" -P "3306" -u "demo_user" -p "demo_pass" -d "demo_db" -q "SELECT 'hello'"

The file is here.

pymysql

PyPI
Github

Tested working with:

pymysql 0.6.6
SQLAlchemy 1.0.6
Python 2.7, 3.4
Windows, Linux

Setup and Usage:

pymysql - Setup
pymysql - Verify setup
pymysql - Usage
pymysql - Usage with SQLAlchemy

pymysql - Setup

On both Windows and Linux, install using pip:

pip install pymysql

pymysql - Verify setup

Run:

python -c "import pymysql"

pymysql - Usage

Run:

aoikqueryhurry -R "aoikqueryhurry.db.mysql.pymysql.main::rfunc" -H "127.0.0.1" -P "3306" -u "demo_user" -p "demo_pass" -d "demo_db" -q "SELECT 'hello'"

The file is here.

pymysql - Usage with SQLAlchemy

Run:

aoikqueryhurry -R "aoikqueryhurry.db.mysql.pymysql.sa::rfunc" -H "127.0.0.1" -P "3306" -u "demo_user" -p "demo_pass" -d "demo_db" -q "SELECT 'hello'"

The file is here.

umysql

PyPI
Github

Tested working with:

umysql 2.61
Python 2.7
Python 3 is not supported.
Windows, Linux

Setup and Usage:

umysql - Setup on Windows
umysql - Setup on Linux
umysql - Verify setup
umysql - Usage

umysql - Setup on Windows

Download wheel file from here:

umysql-2.61-cp27-none-win_amd64.whl

Install from wheel file:

pip install umysql-2.61-cp27-none-win_amd64.whl

umysql - Setup on Linux

Install using pip:

pip install umysql

umysql - Verify setup

Run:

python -c "import umysql"

umysql - Usage

Run:

aoikqueryhurry -R "aoikqueryhurry.db.mysql.umysql.main::rfunc" -H "127.0.0.1" -P "3306" -u "demo_user" -p "demo_pass" -d "demo_db" -q "SELECT 'hello'"

The file is here.

MySQL ODBC

Tested working with:

MySQL Connector ODBC 5.3.4 (Windows)
MySQL Connector ODBC 5.2.6 (Linux)
Python 2.7, 3.4
Windows, Linux

Setup and Usage:

MySQL Connector ODBC
MySQL ODBC Usage
MySQL ODBC Usage with SQLAlchemy

MySQL Connector ODBC

Doc is here.

Setup and Usage:

MySQL Connector ODBC - Setup on Windows
MySQL Connector ODBC - Setup on Linux
- MySQL Connector ODBC - Setup on Linux - Install from package
- MySQL Connector ODBC - Setup on Linux - Build from source code:
MySQL Connector ODBC - Add driver definition on Windows
MySQL Connector ODBC - Add driver definition on Linux
MySQL Connector ODBC - Query driver definition on Windows
MySQL Connector ODBC - Query driver definition on Linux
MySQL Connector ODBC - Add DSN definition on Windows
MySQL Connector ODBC - Add DSN definition on Linux
MySQL Connector ODBC - Query DSN definition on Windows
MySQL Connector ODBC - Query DSN definition on Linux
MySQL Connector ODBC - Verify DSN connectivity on Windows
MySQL Connector ODBC - Verify DSN connectivity on Linux

MySQL Connector ODBC - Setup on Windows

Download installer from here:

Connector/ODBC 5.3.4
Windows (x86, 64-bit), MSI Installer

MySQL Connector ODBC - Setup on Linux

MySQL Connector/ODBC's Linux (Debian or Ubuntu) package is libmyodbc.

Setup:

MySQL Connector ODBC - Setup on Linux - Install from package
MySQL Connector ODBC - Setup on Linux - Build from source code:

MySQL Connector ODBC - Setup on Linux - Install from package

Run:

apt-get install -y libmyodbc

MySQL Connector ODBC - Setup on Linux - Build from source code:

Doc is here.

Install dependency libs:

apt-get install -y libmysqlclient-dev

Check libmysqlclient-dev's version

dpkg -l libmysqlclient-dev

Download source code from here. The version of mysql-connector-odbc to compile must match that of libmysqlclient-dev. On my Linux distro, libmysqlclient-dev is for MySQL 5.5, but the latest mysql-connector-odbc-5.3.4 is for MySQL 5.6, so we use the older version mysql-connector-odbc-5.2.6 instead:

curl -O http://downloads.mysql.com/archives/get/file/mysql-connector-odbc-5.2.6-src.tar.gz

Compile and install:

tar xvf mysql-connector-odbc-5.2.6-src.tar.gz

cd mysql-connector-odbc-5.2.6-src

cmake -G "Unix Makefiles" -DWITH_UNIXODBC=1

make

make install

-DWITH_UNIXODBC=1 is needed to work with ODBC manager unixODBC, instead of iODBC.
By default make install will put files to /use/local.
This might not be desirable. Specify a prefix dir of your choice.

MySQL Connector ODBC - Add driver definition on Windows

See here.

MySQL Connector ODBC - Add driver definition on Linux

Find libmyodbc.so:

dpkg -L libmyodbc | grep "libmyodbc.so"

Result is /usr/lib/x86_64-linux-gnu/odbc/libmyodbc.so.

Find example odbcinst.ini:

dpkg -L libmyodbc | grep odbcinst.ini

Result is /usr/share/libmyodbc/odbcinst.ini.

The example odbcinst.ini is like:

[MySQL]
Description = MySQL driver
Driver      = libmyodbc.so
Setup       = libodbcmyS.so
CPTimeout   =
CPReuse     =

Add to /etc/odbcinst.ini:

odbcinst -i -d -f /usr/share/libmyodbc/odbcinst.ini

Arguments are explained here.

MySQL Connector ODBC - Query driver definition on Windows

See here.

MySQL Connector ODBC - Query driver definition on Linux

odbcinst -q -d -n MySQL

Arguments are explained here.

MySQL Connector ODBC - Add DSN definition on Windows

See here.

MySQL Connector ODBC - Add DSN definition on Linux

Find example odbc.ini:

dpkg -L libmyodbc | grep odbc.ini

Result is /usr/share/doc/libmyodbc/examples/odbc.ini.

Add DSN definition to /etc/odbc.ini:

cat <<'ZZZ' | odbcinst -i -s -l -r
[MySQLDemoDSN]
Driver       = MySQL
Server       = 127.0.0.1
Port         = 3306
User         =
Password     =
Database     =
Option       = 3
ZZZ

odbcinst's arguments are explained here.
See doc for the connection parameters.

MySQL Connector ODBC - Query DSN definition on Windows

See here.

MySQL Connector ODBC - Query DSN definition on Linux

Run:

odbcinst -q -s -n MySQLDemoDSN

Arguments are explained here.

MySQL Connector ODBC - Verify DSN connectivity on Windows

See here.

MySQL Connector ODBC - Verify DSN connectivity on Linux

isql -v MySQLDemoDSN demo_user demo_pass

Arguments are explained here.

MySQL ODBC Usage

Default ODBC driver names:

Windows
- MySQL ODBC 5.3 ANSI Driver
- MySQL ODBC 5.3 Unicode Driver
Linux
- MySQL

Run:

# Windows, Linux, DSN
aoikqueryhurry -R "aoikqueryhurry.db.mysql.odbc.rfunc::rfunc" -C "aoikqueryhurry.db.odbc.ceodbc.cfunc::CFUNC" -S "MySQLDemoDSN" -d "demo_db" -q "SELECT 'hello'"

aoikqueryhurry -R "aoikqueryhurry.db.mysql.odbc.rfunc::rfunc" -C "aoikqueryhurry.db.odbc.pyodbc.cfunc::CFUNC" -S "MySQLDemoDSN" -d "demo_db" -q "SELECT 'hello'"

aoikqueryhurry -R "aoikqueryhurry.db.mysql.odbc.rfunc::rfunc" -C "aoikqueryhurry.db.odbc.pypyodbc.cfunc::CFUNC" -S "MySQLDemoDSN" -d "demo_db" -q "SELECT 'hello'"

# Windows, DSN-Less
aoikqueryhurry -R "aoikqueryhurry.db.mysql.odbc.rfunc::rfunc" -C "aoikqueryhurry.db.odbc.ceodbc.cfunc::CFUNC" -O "MySQL ODBC 5.3 Unicode Driver" -H "127.0.0.1" -P "3306" -u "demo_user" -p "demo_pass" -d "demo_db" -q "SELECT 'hello'"

aoikqueryhurry -R "aoikqueryhurry.db.mysql.odbc.rfunc::rfunc" -C "aoikqueryhurry.db.odbc.pyodbc.cfunc::CFUNC" -O "MySQL ODBC 5.3 Unicode Driver" -H "127.0.0.1" -P "3306" -u "demo_user" -p "demo_pass" -d "demo_db" -q "SELECT 'hello'"

aoikqueryhurry -R "aoikqueryhurry.db.mysql.odbc.rfunc::rfunc" -C "aoikqueryhurry.db.odbc.pypyodbc.cfunc::CFUNC" -O "MySQL ODBC 5.3 Unicode Driver" -H "127.0.0.1" -P "3306" -u "demo_user" -p "demo_pass" -d "demo_db" -q "SELECT 'hello'"

# Linux, DSN-Less
aoikqueryhurry -R "aoikqueryhurry.db.mysql.odbc.rfunc::rfunc" -C "aoikqueryhurry.db.odbc.ceodbc.cfunc::CFUNC" -O "MySQL" -H "127.0.0.1" -P "3306" -u "demo_user" -p "demo_pass" -d "demo_db" -q "SELECT 'hello'"

aoikqueryhurry -R "aoikqueryhurry.db.mysql.odbc.rfunc::rfunc" -C "aoikqueryhurry.db.odbc.pyodbc.cfunc::CFUNC" -O "MySQL" -H "127.0.0.1" -P "3306" -u "demo_user" -p "demo_pass" -d "demo_db" -q "SELECT 'hello'"

aoikqueryhurry -R "aoikqueryhurry.db.mysql.odbc.rfunc::rfunc" -C "aoikqueryhurry.db.odbc.pypyodbc.cfunc::CFUNC" -O "MySQL" -H "127.0.0.1" -P "3306" -u "demo_user" -p "demo_pass" -d "demo_db" -q "SELECT 'hello'"

Argument -R specifies runner function.
The file is here.
Argument -C specifies connect function.
Argument -S specifies data source name.
Argument -O specifies ODBC driver name.

MySQL ODBC Usage with SQLAlchemy

List dialects available in SQLAlchemy:

python -c "import os; import sqlalchemy.dialects.mysql as m; print('\n'.join('mysql+'+x-3 for x in (os.listdir(os.path.dirname(m.__file__))) if (x.endswith('.py') and x != '__init__.py')))"

mysql+pyodbc

Run:

# Windows, Linux, DSN
aoikqueryhurry -R "[:v(2w30Kz6)]" -D "mysql+pyodbc" -S "MySQLDemoDSN"

# Windows, DSN-Less
aoikqueryhurry -R "[:v(2w30Kz6)]" -D "mysql+pyodbc" -O "MySQL ODBC 5.3 Unicode Driver" -d="demo_db"

# Linux, DSN-Less
aoikqueryhurry -R "[:v(2w30Kz6)]" -D "mysql+pyodbc" -O "MySQL" -d="demo_db"

Argument -R specifies the demo function to run.
The file is here.
Argument -S specifies the data source name.
Argument -O specifies the ODBC driver name.
Argument -d specifies the database.

PostgreSQL

Tested working with:

PostgreSQL 9.4.4

Client libs:

PostgreSQL Preparation
minipg
psycopg2
py-postgresql
pypq
python-pgsql

PostgreSQL Preparation

PostgreSQL Preparation - Log in as superuser
PostgreSQL Preparation - Create user
PostgreSQL Preparation - Create database
PostgreSQL Preparation - Configure user login
PostgreSQL Preparation - Verify user login
PostgreSQL Preparation - Install dependency libs

PostgreSQL Preparation - Log in as superuser

Run:

psql -h 127.0.0.1 -p 5432 -U postgres

PostgreSQL Preparation - Create user

Run:

CREATE USER demo_user WITH PASSWORD 'demo_pass';

PostgreSQL Preparation - Create database

Run:

CREATE DATABASE demo_db OWNER demo_user;

PostgreSQL Preparation - Configure user login

Config file is pg_hba.conf.

Find where is pg_hba.conf:

psql -c "SHOW config_file;" -h 127.0.0.1 -p 5432 -U postgres

pg_hba.conf should be located in the same directory of postgresql.conf.

Edit pg_hba.conf to add login options for the newly created user:

host demo_db demo_user 127.0.0.1/32 md5

PostgreSQL Preparation - Verify user login

Run:

# If your Windows command console's code page is cp936
set LANG=cp936

psql -h 127.0.0.1 -p 5432 -U demo_user demo_db

PostgreSQL Preparation - Install dependency libs

Run:

apt-get install -y libpq-dev

Verify pg_config is available:

which pg_config

minipg

PyPI Github

Tested working with:

minipg 0.5.3
Python 2.7, 3.4
Windows, Linux

Setup and Usage:

minipg - Setup
minipg - Verify setup
minipg - Bugfix
minipg - Usage

minipg - Setup

On both Windows and Linux, install using pip:

pip install minipg

minipg - Verify setup

Run:

python -c "import minipg"

minipg - Bugfix

Find where is minipg.py:

python -c "import minipg.minipg; print(minipg.minipg.__file__)"

Change minipg.py's line 486 from

self._send_message(b'p', b''.join([b'md5',hash2,'\x00']))

to

self._send_message(b'p', b''.join([b'md5',hash2,b'\x00']))

. Without this change it does not work in Python 3.

minipg - Usage

Run:

aoikqueryhurry -R "aoikqueryhurry.db.postgresql.minipg.main::rfunc" -H "127.0.0.1" -P "5432" -u "demo_user" -p "demo_pass" -d "demo_db" -q "SELECT 'hello'"

The file is here.

psycopg2

PyPI
Home

Tested working with:

psycopg2 2.6.1
SQLAlchemy 1.0.6
Python 2.7, 3.4
Windows, Linux

Setup and Usage:

psycopg2 - Setup on Windows
psycopg2 - Setup on Linux
psycopg2 - Verify setup
psycopg2 - Usage
psycopg2 - Usage with SQLAlchemy

psycopg2 - Setup on Windows

Download wheel file from here.

psycopg2-2.6.1-cp27-none-win_amd64.whl
# or
psycopg2-2.6.1-cp34-none-win_amd64.whl

Install from wheel file:

pip install psycopg2-2.6.1-cp27-none-win_amd64.whl
# or
pip install psycopg2-2.6.1-cp34-none-win_amd64.whl

psycopg2 - Setup on Linux

Install using pip:

pip install psycopg2

psycopg2 - Verify setup

Run:

python -c "import psycopg2"

psycopg2 - Usage

Run:

aoikqueryhurry -R "aoikqueryhurry.db.postgresql.psycopg2.main::rfunc" -H "127.0.0.1" -P "5432" -u "demo_user" -p "demo_pass" -d "demo_db" -q "SELECT 'hello'"

The file is here.

psycopg2 - Usage with SQLAlchemy

Run:

aoikqueryhurry -R "aoikqueryhurry.db.postgresql.psycopg2.sa::rfunc" -H "127.0.0.1" -P "5432" -u "demo_user" -p "demo_pass" -d "demo_db" -q "SELECT 'hello'"

The file is here.

py-postgresql

PyPI
Home

Its code package is postgresql.

Tested working with:

py-postgresql 1.1.0
SQLAlchemy 1.0.6
Python 3.4
Python 2 is not supported.
Windows, Linux

Setup and Usage:

py-postgresql - Setup
py-postgresql - Verify setup
py-postgresql - Usage
py-postgresql - Usage with SQLAlchemy

py-postgresql - Setup

On both Windows and Linux, install using pip:

pip install py-postgresql

py-postgresql - Verify setup

Run:

python -c "import postgresql"

py-postgresql - Usage

Run:

aoikqueryhurry -R "aoikqueryhurry.db.postgresql.py_postgresql.main::rfunc" -H "127.0.0.1" -P "5432" -u "demo_user" -p "demo_pass" -d "demo_db" -q "SELECT 'hello'"

The file is here.

py-postgresql - Usage with SQLAlchemy

Run:

aoikqueryhurry -R "aoikqueryhurry.db.postgresql.py_postgresql.sa::rfunc" -H "127.0.0.1" -P "5432" -u "demo_user" -p "demo_pass" -d "demo_db" -q "SELECT 'hello'"

The file is here.

pypq

PyPI

Tested working with:

pypq 0.1.3
Python 2.7
Python 3 is not supported.
Linux

Setup and Usage:

pypq - Setup on Linux
pypq - Verify setup
pypq - Usage

pypq - Setup on Linux

Install using pip:

pip install pypq

pypq - Verify setup

Run:

python -c "import pypq"

pypq - Usage

Run:

aoikqueryhurry -R "aoikqueryhurry.db.postgresql.pypq.main::rfunc" -H "127.0.0.1" -P "5432" -u "demo_user" -p "demo_pass" -d "demo_db" -q "SELECT 'hello'"

The file is here.

python-pgsql

PyPI
Home

Its code package is pgsql.

Tested working with:

python-pgsql 1.1
Python 3.4
Python 2 is not supported.
Linux

Setup and Usage:

python-pgsql - Setup on Linux
python-pgsql - Verify setup
python-pgsql - Usage

python-pgsql - Setup on Linux

Verify PostgreSQL's dev headers are available:

ls -l /usr/include/postgresql

Install using pip:

pip install pgsql

python-pgsql - Verify setup

Run:

python -c "import pgsql"

python-pgsql - Usage

Run:

aoikqueryhurry -R "aoikqueryhurry.db.postgresql.python_pgsql.main::rfunc" -H "127.0.0.1" -P "5432" -u "demo_user" -p "demo_pass" -d "demo_db" -q "SELECT 'hello'"

The file is here.

PostgreSQL ODBC

Tested working with:

psqlODBC 9.03
Python 2.7, 3.4
ANSI drivers only work with Python 2.
Windows, Linux

Setup and Usage:

psqlODBC
PostgreSQL ODBC Usage
PostgreSQL ODBC Usage with SQLAlchemy

psqlODBC

Doc is here.

Setup and Usage:

psqlODBC - Setup on Windows
psqlODBC - Setup on Linux
psqlODBC - Add driver definition on Windows
psqlODBC - Add driver definition on Linux
psqlODBC - Query driver definition on Windows
psqlODBC - Query driver definition on Linux
psqlODBC - Add DSN definition on Windows
psqlODBC - Add DSN definition on Linux
psqlODBC - Query DSN definition on Windows
psqlODBC - Query DSN definition on Linux
psqlODBC - Verify DSN connectivity on Windows
psqlODBC - Verify DSN connectivity on Linux

psqlODBC - Setup on Windows

Download installer psqlodbc_09_03_0300-x64-1.zip from here.

psqlODBC - Setup on Linux

psqlODBC's Linux (Debian or Ubuntu) package is called odbc-postgresql.

Install from package:

apt-get install -y odbc-postgresql

psqlODBC - Add driver definition on Windows

See here.

psqlODBC - Add driver definition on Linux

Find psqlodbca.so and psqlodbcw.so:

dpkg -L odbc-postgresql | grep "psqlodbca.so"
dpkg -L odbc-postgresql | grep "psqlodbcw.so"

Result is /usr/lib/x86_64-linux-gnu/odbc/psqlodbca.so and /usr/lib/x86_64-linux-gnu/odbc/psqlodbcw.so.
The a and w mean ANSI and Unicode respectively.

Find example odbcinst.ini:

dpkg -L odbc-postgresql | grep odbcinst.ini

Result is /usr/share/psqlodbc/odbcinst.ini.template.

The example odbcinst.ini is like:

[PostgreSQL ANSI]
Description = PostgreSQL ODBC driver (ANSI version)
Driver      = psqlodbca.so
Setup       = libodbcpsqlS.so
Debug       = 0
CommLog     = 1

[PostgreSQL Unicode]
Description = PostgreSQL ODBC driver (Unicode version)
Driver      = psqlodbcw.so
Setup       = libodbcpsqlS.so
Debug       = 0
CommLog     = 1

Add to /etc/odbcinst.ini:

odbcinst -i -d -f /usr/share/psqlodbc/odbcinst.ini.template

Arguments are explained here.

psqlODBC - Query driver definition on Windows

See here.

psqlODBC - Query driver definition on Linux

odbcinst -q -d -n "PostgreSQL ANSI"

odbcinst -q -d -n "PostgreSQL Unicode"

Arguments are explained here.

psqlODBC - Add DSN definition on Windows

See here.

psqlODBC - Add DSN definition on Linux

Find example odbc.ini:

dpkg -L odbc-postgresql | grep odbc.ini

Result is /usr/share/doc/odbc-postgresql/examples/odbc.ini.template.

Add DSN definition to /etc/odbc.ini.

cat <<'ZZZ' | odbcinst -i -s -l -r
[PostgreSQLDemoDSN]
Description         = 
Driver              = PostgreSQL Unicode
Trace               = No
TraceFile           = 
Database            = demo_db
Servername          = 127.0.0.1
UserName            =
Password            =
Port                = 5432
ReadOnly            = No
RowVersioning       = No
ShowSystemTables    = No
ShowOidColumn       = No
FakeOidIndex        = No
ConnSettings        =
ZZZ

odbcinst's arguments are explained here.

psqlODBC - Query DSN definition on Windows

See here.

psqlODBC - Query DSN definition on Linux

Run:

odbcinst -q -s -n PostgreSQLDemoDSN

Arguments are explained here.

psqlODBC - Verify DSN connectivity on Windows

See here.

psqlODBC - Verify DSN connectivity on Linux

isql -v PostgreSQLDemoDSN demo_user demo_pass

Arguments are explained here.

PostgreSQL ODBC Usage

Default ODBC driver names:

Windows
- PostgreSQL ANSI(x64)
- PostgreSQL Unicode(x64)
Linux
- PostgreSQL ANSI
- PostgreSQL Unicode

Run:

# Windows, Linux, DSN
aoikqueryhurry -R "aoikqueryhurry.db.postgresql.odbc.rfunc::rfunc" -C "aoikqueryhurry.db.odbc.ceodbc.cfunc::CFUNC" -S "PostgreSQLDemoDSN" -d "demo_db" -q "SELECT 'hello'"

aoikqueryhurry -R "aoikqueryhurry.db.postgresql.odbc.rfunc::rfunc" -C "aoikqueryhurry.db.odbc.pyodbc.cfunc::CFUNC" -S "PostgreSQLDemoDSN" -d "demo_db" -q "SELECT 'hello'"

aoikqueryhurry -R "aoikqueryhurry.db.postgresql.odbc.rfunc::rfunc" -C "aoikqueryhurry.db.odbc.pypyodbc.cfunc::CFUNC" -S "PostgreSQLDemoDSN" -d "demo_db" -q "SELECT 'hello'"

# Windows, DSN-Less
aoikqueryhurry -R "aoikqueryhurry.db.postgresql.odbc.rfunc::rfunc" -C "aoikqueryhurry.db.odbc.ceodbc.cfunc::CFUNC" -O "PostgreSQL Unicode(x64)" -d="demo_db" -H "127.0.0.1" -P "5432" -u "demo_user" -p "demo_pass" -d "demo_db" -q "SELECT 'hello'"

aoikqueryhurry -R "aoikqueryhurry.db.postgresql.odbc.rfunc::rfunc" -C "aoikqueryhurry.db.odbc.pyodbc.cfunc::CFUNC" -O "PostgreSQL Unicode(x64)" -d="demo_db" -H "127.0.0.1" -P "5432" -u "demo_user" -p "demo_pass" -d "demo_db" -q "SELECT 'hello'"

aoikqueryhurry -R "aoikqueryhurry.db.postgresql.odbc.rfunc::rfunc" -C "aoikqueryhurry.db.odbc.pypyodbc.cfunc::CFUNC" -O "PostgreSQL Unicode(x64)" -d="demo_db" -H "127.0.0.1" -P "5432" -u "demo_user" -p "demo_pass" -d "demo_db" -q "SELECT 'hello'"

# Linux, DSN-Less
aoikqueryhurry -R "aoikqueryhurry.db.postgresql.odbc.rfunc::rfunc" -C "aoikqueryhurry.db.odbc.ceodbc.cfunc::CFUNC" -O "PostgreSQL Unicode" -d="demo_db" -H "127.0.0.1" -P "5432" -u "demo_user" -p "demo_pass" -d "demo_db" -q "SELECT 'hello'"

aoikqueryhurry -R "aoikqueryhurry.db.postgresql.odbc.rfunc::rfunc" -C "aoikqueryhurry.db.odbc.pyodbc.cfunc::CFUNC" -O "PostgreSQL Unicode" -d="demo_db" -H "127.0.0.1" -P "5432" -u "demo_user" -p "demo_pass" -d "demo_db" -q "SELECT 'hello'"

aoikqueryhurry -R "aoikqueryhurry.db.postgresql.odbc.rfunc::rfunc" -C "aoikqueryhurry.db.odbc.pypyodbc.cfunc::CFUNC" -O "PostgreSQL Unicode" -d="demo_db" -H "127.0.0.1" -P "5432" -u "demo_user" -p "demo_pass" -d "demo_db" -q "SELECT 'hello'"

Argument -R specifies the demo function to run.
The file is here.
Argument -C specifies the connect function to use.
Argument -S specifies the data source name.
Argument -O specifies the ODBC driver name.
Argument -d specifies the database.
The combination of ceODBC, Python 3.4 and Linux (Ubuntu 14.04) reports error:

  ceODBC.DatabaseError: Invalid column number in DescribeCol.

PostgreSQL ODBC Usage with SQLAlchemy

List dialects available in SQLAlchemy:

python -c "import os; import sqlalchemy.dialects.postgresql as m; print('\n'.join('postgresql+'+x-3 for x in (os.listdir(os.path.dirname(m.__file__))) if (x.endswith('.py') and x != '__init__.py')))"

There are currently no ODBC dialects available for PostgreSQL.

MSSQL

Tested working with:

MS SQL Server 2014

Client libs:

MSSQL Preparation
pymssql

MSSQL Preparation - Log in as superuser

Run:

sqlcmd -H 127.0.0.1 -U sa

or use SQL Server Management Studio.

MSSQL Preparation - Create database

Run:

CREATE DATABASE demo_db;

USE demo_db;

CREATE ROLE demo_db_role;

GRANT SELECT,UPDATE,INSERT,DELETE TO demo_db_role;

GO;

MSSQL Preparation - Create user

Run:

USE demo_db;

CREATE LOGIN demo_user WITH PASSWORD = 'demo_pass';

CREATE USER demo_user FOR LOGIN demo_user;

EXEC sys.sp_addrolemember demo_db_role, demo_user;

GO;

pymssql

PyPI
Github

Tested working with:

pymssql 2.1.1
SQLAlchemy 1.0.6
Python 2.7, 3.4
Windows, Linux

Setup and Usage:

pymssql - Setup
pymssql - Verify setup
pymssql - Usage
pymssql - Usage with SQLAlchemy

pymssql - Setup

On both Windows and Linux, install using pip:

pip install pymssql

pymssql - Verify setup

Run:

python -c "import pymssql"

pymssql - Usage

Run:

aoikqueryhurry -R "aoikqueryhurry.db.mssql.pymssql.main::rfunc" -H "127.0.0.1" -P "1433" -u "demo_user" -p "demo_pass" -d "demo_db" -q "SELECT 'hello'"

The file is here.

pymssql - Usage with SQLAlchemy

Run:

aoikqueryhurry -R "aoikqueryhurry.db.mssql.pymssql.sa::rfunc" -H "127.0.0.1" -P "1433" -u "demo_user" -p "demo_pass" -d "demo_db" -q "SELECT 'hello'"

The file is here.

MSSQL ODBC

Setup and Usage:

SQL Server Native Client
FreeTDS
MSSQL ODBC Usage
MSSQL ODBC Usage with SQLAlchemy

SQL Server Native Client

Different versions of ODBC drivers are provided by different versions of MS SQL Server.

SQL Server: for SQL Server 2000.
SQL Server Native Client 10.0: for SQL Server 2005 and 2008.
SQL Server Native Client 11.0: for SQL Server 2012 and 2014.

Using an older version of ODBC driver to connect to a newer version of MS SQL Server is ok, but new funcitionalities are not available this way.

Setup and Usage:

SQL Server Native Client - Setup on Windows
SQL Server Native Client - Add driver definition on Windows
SQL Server Native Client - Query driver definition on Windows
SQL Server Native Client - Add DSN definition on Windows
SQL Server Native Client - Query DSN definition on Windows
SQL Server Native Client - Verify DSN connectivity on Windows

SQL Server Native Client - Setup on Windows

No need. It comes with MS SQL Server.

SQL Server Native Client - Add driver definition on Windows

See here.

SQL Server Native Client - Query driver definition on Windows

See here.

SQL Server Native Client - Add DSN definition on Windows

See here.

SQL Server Native Client - Query DSN definition on Windows

See here.

SQL Server Native Client - Verify DSN connectivity on Windows

See here.

Or use osql provided by MS SQL Server:

osql -D MSSQLDemoDSN -U demo_user -P demo_pass

FreeTDS

FreeTDS implements MSSQL's communication protocol Tabular Data Stream (TDS). Its package tdsodbc provides an ODBC driver.

Setup and Usage:

FreeTDS - Setup on Linux
FreeTDS - Add driver definition on Linux
FreeTDS - Query driver definition on Linux
FreeTDS - Add DSN definition on Linux
FreeTDS - Query DSN definition on Linux
FreeTDS - Verify DSN connectivity on Linux

FreeTDS - Setup on Linux

Run:

apt-get install -y freetds-dev

apt-get install -y freetds-bin

apt-get install -y tdsodbc

Package freetds-dev installs FreeTDS' libs.
Package freetds-bin installs a MSSQL client program tsql.
Package tdsodbc installs ODBC driver file libtdsodbc.so.

FreeTDS - Add driver definition on Linux

Find libtdsodbc.so:

dpkg -L tdsodbc | grep libtdsodbc.so

Result is /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so.

Find example odbcinst.ini:

dpkg -L tdsodbc | grep odbcinst.ini

Result is /usr/share/tdsodbc/odbcinst.ini.

The example odbcinst.ini is like::

[FreeTDS]
Description = TDS driver (Sybase/MS SQL)
Driver      = libtdsodbc.so
Setup       = libtdsS.so
CPTimeout   = 
CPReuse     =

Add to /etc/odbcinst.ini:

odbcinst -i -d -f /usr/share/tdsodbc/odbcinst.ini

Arguments are explained here.

FreeTDS - Query driver definition on Linux

odbcinst -q -d -n FreeTDS

Arguments are explained here.

FreeTDS - Add DSN definition on Linux

Run:

cat <<'ZZZ' | odbcinst -i -s -l -r
[MSSQLDemoDSN]
Driver       = FreeTDS
Server       = 127.0.0.1
Port         = 1433
User         =
Password     =
Database     =
ZZZ

odbcinst's arguments are explained here.

FreeTDS - Query DSN definition on Linux

Run:

odbcinst -q -s -n MSSQLDemoDSN

Arguments are explained here.

FreeTDS - Verify DSN connectivity on Linux

Run:

isql -v MSSQLDemoDSN demo_user demo_pass

Arguments are explained here.

MSSQL ODBC Usage

Default ODBC driver names:

Windows
- SQL Server: for SQL Server 2000.
- SQL Server Native Client 10.0: for SQL Server 2005 and 2008.
- SQL Server Native Client 11.0: for SQL Server 2012 and 2014.
Linux
- FreeTDS

Run:

# Windows, Linux, DSN
aoikqueryhurry -R "aoikqueryhurry.db.mssql.odbc.rfunc::rfunc" -C "aoikqueryhurry.db.odbc.ceodbc.cfunc::CFUNC" -S "MSSQLDemoDSN" -d "demo_db" -q "SELECT 'hello'"

aoikqueryhurry -R "aoikqueryhurry.db.mssql.odbc.rfunc::rfunc" -C "aoikqueryhurry.db.odbc.pyodbc.cfunc::CFUNC" -S "MSSQLDemoDSN" -d "demo_db" -q "SELECT 'hello'"

aoikqueryhurry -R "aoikqueryhurry.db.mssql.odbc.rfunc::rfunc" -C "aoikqueryhurry.db.odbc.pypyodbc.cfunc::CFUNC" -S "MSSQLDemoDSN" -d "demo_db" -q "SELECT 'hello'"

# Windows, DSN-Less
aoikqueryhurry -R "aoikqueryhurry.db.mssql.odbc.rfunc::rfunc" -C "aoikqueryhurry.db.odbc.ceodbc.cfunc::CFUNC" -O "SQL Server Native Client 11.0" -H "127.0.0.1" -P "1433" -u "demo_user" -p "demo_pass" -d "demo_db" -q "SELECT 'hello'"

aoikqueryhurry -R "aoikqueryhurry.db.mssql.odbc.rfunc::rfunc" -C "aoikqueryhurry.db.odbc.pyodbc.cfunc::CFUNC" -O "SQL Server Native Client 11.0" -H "127.0.0.1" -P "1433" -u "demo_user" -p "demo_pass" -d "demo_db" -q "SELECT 'hello'"

aoikqueryhurry -R "aoikqueryhurry.db.mssql.odbc.rfunc::rfunc" -C "aoikqueryhurry.db.odbc.pypyodbc.cfunc::CFUNC" -O "SQL Server Native Client 11.0" -H "127.0.0.1" -P "1433" -u "demo_user" -p "demo_pass" -d "demo_db" -q "SELECT 'hello'"

# Linux, DSN-Less
aoikqueryhurry -R "aoikqueryhurry.db.mssql.odbc.rfunc::rfunc" -C "aoikqueryhurry.db.odbc.ceodbc.cfunc::CFUNC" -O "FreeTDS" -H "127.0.0.1" -P "1433" -u "demo_user" -p "demo_pass" -d "demo_db" -q "SELECT 'hello'"

aoikqueryhurry -R "aoikqueryhurry.db.mssql.odbc.rfunc::rfunc" -C "aoikqueryhurry.db.odbc.pyodbc.cfunc::CFUNC" -O "FreeTDS" -H "127.0.0.1" -P "1433" -u "demo_user" -p "demo_pass" -d "demo_db" -q "SELECT 'hello'"

aoikqueryhurry -R "aoikqueryhurry.db.mssql.odbc.rfunc::rfunc" -C "aoikqueryhurry.db.odbc.pypyodbc.cfunc::CFUNC" -O "FreeTDS" -H "127.0.0.1" -P "1433" -u "demo_user" -p "demo_pass" -d "demo_db" -q "SELECT 'hello'"

Argument -R specifies the demo function to run.
The file is here.
Available deme funcs and their driver versions are
Argument -C specifies the connect function to use.

MSSQL ODBC Usage with SQLAlchemy

List dialects available in SQLAlchemy:

python -c "import os; import sqlalchemy.dialects.mssql as m; print('\n'.join('mssql+'+x-3 for x in (os.listdir(os.path.dirname(m.__file__))) if (x.endswith('.py') and x != '__init__.py')))"

mssql+mxodbc
mssql+pyodbc

Run:

# Windows, Linux, DSN
aoikqueryhurry -R "aoikqueryhurry.db.mssql.odbc.sa::rfunc" -D "mssql+pyodbc" -S "MSSQLDemoDSN" -d "demo_db" -q "SELECT 'hello'"

# Windows, DSN-Less
aoikqueryhurry -R "aoikqueryhurry.db.mssql.odbc.sa::rfunc" -D "mssql+pyodbc" -O "SQL Server Native Client 11.0" -H "127.0.0.1" -P "1433" -u "demo_user" -p "demo_pass" -d "demo_db" -q "SELECT 'hello'"

# Linux, DSN-Less
aoikqueryhurry -R "aoikqueryhurry.db.mssql.odbc.sa::rfunc" -D "mssql+pyodbc" -O "FreeTDS" -H "127.0.0.1" -P "1433" -u "demo_user" -p "demo_pass" -d "demo_db" -q "SELECT 'hello'"

Argument -R specifies runner function.
The file is here.
Argument -C specifies connect function.
Argument -S specifies data source name.
Argument -O specifies ODBC driver name.

Oracle

Tested working with:

Oracle XE 11g

Client libs:

Oracle Preparation
Instant Client
cx_Oracle

Oracle Preparation

Oracle Preparation - Log in as superuser
Oracle Preparation - Create user
Oracle Preparation - Verify user login

Oracle Preparation - Log in as superuser

Run:

sqlplus / as sysdba

Oracle Preparation - Create user

Run:

CREATE USER demo_user IDENTIFIED BY demo_pass;

GRANT CONNECT TO demo_user;

Oracle Preparation - Verify user login

Run:

sqlplus demo_user/demo_pass

Instant Client

Setup:

Instant Client - Setup on Windows
Instant Client - Setup on Linux

Instant Client - Setup on Windows

Download Instant Client for Microsoft Windows (x64) from here:

instantclient-basic-windows.x64-12.1.0.2.0.zip

Extact to:

D:\var\Oracle\InstantClient\12.1\dst

Dir dst should contain file BASIC_README.

Instant Client - Setup on Linux

Download Instant Client for Linux x86-64 from here:

instantclient-basic-linux.x64-12.1.0.2.0.zip
instantclient-sdk-linux.x64-12.1.0.2.0.zip

Extact:

mkdir -p /var/Oracle/InstantClient/12.1

unzip instantclient-basic-linux.x64-12.1.0.2.0.zip -d /var/Oracle/InstantClient/12.1
# Result dir is /var/Oracle/InstantClient/12.1/instantclient_12_1

unzip instantclient-sdk-linux.x64-12.1.0.2.0.zip -d /var/Oracle/InstantClient/12.1
# Result dir is /var/Oracle/InstantClient/12.1/instantclient_12_1/sdk

mv /var/Oracle/InstantClient/12.1/instantclient_12_1 /var/Oracle/InstantClient/12.1/dst

Dir dst should contain file BASIC_README.

Export env variables:

export ORACLE_HOME=/var/Oracle/InstantClient/12.1/dst

export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME

Find libclntsh.so's version-specific name:

ls -l "$ORACLE_HOME" | grep libclntsh.so

Result is libclntsh.so.12.1.

Create libclntsh.so's version-free symlink:

ln -s $ORACLE_HOME/libclntsh.so.12.1 $ORACLE_HOME/libclntsh.so

cx_Oracle

PyPI
SourceForge

Tested working with:

cx_Oracle 5.2
SQLAlchemy 1.0.6
Windows, Linux
Python 2.7, 3.4

Setup and Usage:

cx_Oracle - Setup on Windows
cx_Oracle - Setup on Linux
cx_Oracle - Verify setup
cx_Oracle - Usage
cx_Oracle - Usage with SQLAlchemy

cx_Oracle - Setup on Windows

Install dependency lib Instant Client.

Download installer from PyPI:

cx_Oracle-5.2-11g.win-amd64-py2.7.exe
# or
cx_Oracle-5.2-12c.win-amd64-py3.4.exe

cx_Oracle - Setup on Linux

Install dependency lib Instant Client.

Install using pip:

export ORACLE_HOME=/var/Oracle/InstantClient/12.1/dst

export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME

pip install cx_Oracle

cx_Oracle - Verify setup

Run:

# Windows
SET PATH=%PATH%;D:\var\Oracle\InstantClient\12.1\dst

# Linux
export ORACLE_HOME=/var/Oracle/InstantClient/12.1/dst

export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME

#
python -c "import cx_Oracle"

cx_Oracle - Usage

Run:

# Windows
SET PATH=%PATH%;D:\var\Oracle\InstantClient\12.1\dst

# Linux
export ORACLE_HOME=/var/Oracle/InstantClient/12.1/dst

export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME

#
aoikqueryhurry -R "aoikqueryhurry.db.oracle.cx_oracle.main::rfunc" -H "127.0.0.1" -P "1521" -I "XE" -u "demo_user" -p "demo_pass" -q "SELECT 'hello' FROM dual"

The file is here.

If you get error:

cx_Oracle.DatabaseError: ORA-21561: OID generation failed

, it might be caused by that your hostname has got no entry in /etc/hosts.

cx_Oracle - Usage with SQLAlchemy

Run:

# Windows
SET PATH=%PATH%;D:\var\Oracle\InstantClient\12.1\dst

# Linux
export ORACLE_HOME=/var/Oracle/InstantClient/12.1/dst

export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME

#
aoikqueryhurry -R "aoikqueryhurry.db.oracle.cx_oracle.sa::rfunc" -H "127.0.0.1" -P "1521" -I "XE" -u "demo_user" -p "demo_pass" -q "SELECT 'hello' FROM dual"

The file is here.

Oracle ODBC

Tested working with:

Windows, Linux
Python 2.7, 3.4

Setup and Usage:

Instant Client ODBC
Oracle ODBC Usage
Oracle ODBC Usage with SQLAlchemy

Instant Client ODBC

Doc is here.

Setup and Usage:

Instant Client ODBC - Setup on Windows
Instant Client ODBC - Setup on Linux
Instant Client ODBC - Add driver definition on Windows
Instant Client ODBC - Add driver definition on Linux
Instant Client ODBC - Query driver definition on Windows
Instant Client ODBC - Query driver definition on Linux
Instant Client ODBC - Add DSN definition on Windows
Instant Client ODBC - Add DSN definition on Linux
Instant Client ODBC - Query DSN definition on Windows
Instant Client ODBC - Query DSN definition on Linux
Instant Client ODBC - Verify DSN connectivity on Windows
Instant Client ODBC - Verify DSN connectivity on Linux

Instant Client ODBC - Setup on Windows

Download Instant Client for Microsoft Windows (x64) from here:

instantclient-odbc-windows.x64-12.1.0.1.0.zip

Extract to:

D:\var\Oracle\InstantClient\12.1\dst

Dir dst should contain file odbc_install.exe.

Run:

SET PATH=%PATH%;D:\var\Oracle\InstantClient\12.1\dst

pushd D:\var\Oracle\InstantClient\12.1\dst

odbc_install.exe

The ODBC driver name is Oracle in XE.

Instant Client ODBC - Setup on Linux

Download Instant Client for Linux x86-64 from here:

instantclient-odbc-linux.x64-12.1.0.2.0.zip

Extract to:

unzip instantclient-odbc-linux.x64-12.1.0.2.0.zip
# Result is dir "instantclient_12_1"

mv instantclient_12_1/* /var/Oracle/InstantClient/12.1/dst

Dir dst should contain file libsqora.so.12.1.

Instant Client ODBC - Add driver definition on Windows

See here.

Instant Client ODBC - Add driver definition on Linux

Run:

cat <<'ZZZ' | odbcinst -i -d -r 
[Oracle]
Description     =
Driver          = /var/Oracle/InstantClient/12.1/dst/libsqora.so.12.1
Setup           = 
FileUsage       =
CPTimeout       =
CPReuse         =
ZZZ

If you get this error later:

[01000][unixODBC][Driver Manager]Can't open lib '/var/Oracle/InstantClient/12.1/dst/libsqora.so.12.1' : file not found
[ISQL]ERROR: Could not SQLConnect

, then run:

ldd /var/Oracle/InstantClient/12.1/dst/libsqora.so.12.1 | grep "not found"

, you might see：

libodbcinst.so.2 => not found

This is because driver libsqora.so.12.1 has bound to use libodbcinst.so. On Debian and Ubuntu, however, there is only libodbcinst.so.1.0.0. So create a symlink for libodbcinst.so.2:

ln -s /usr/lib/x86_64-linux-gnu/libodbcinst.so.1.0.0 /usr/lib/x86_64-linux-gnu/libodbcinst.so.2

Instant Client ODBC - Query driver definition on Windows

See here.

Instant Client ODBC - Query driver definition on Linux

odbcinst -q -d -n Oracle

Arguments are explained here.

Instant Client ODBC - Add DSN definition on Windows

See here.

Instant Client ODBC - Add DSN definition on Linux

Run:

cat <<'ZZZ' | odbcinst -i -s -l -r
[OracleDemoDSN]
Application Attributes = T
Attributes = W
BatchAutocommitMode = IfAllSuccessful
BindAsFLOAT = F
CloseCursor = F
DisableDPM = F
DisableMTS = T
Driver = Oracle
DSN = OracleDemoDSN
EXECSchemaOpt =
EXECSyntax = T
Failover = T
FailoverDelay = 10
FailoverRetryCount = 10
FetchBufferSize = 64000
ForceWCHAR = F
Lobs = T
Longs = T
MaxLargeData = 0
MetadataIdDefault = F
QueryTimeout = T
ResultSets = T
ServerName = 127.0.0.1
Port = 1521
SQLGetData extensions = F
Translation DLL =
Translation Option = 0
DisableRULEHint = T
UserID = 
StatementCache=F
CacheBufferSize=20
UseOCIDescribeAny=F
SQLTranslateErrors=F
MaxTokenSize=8192
AggregateSQLType=FLOAT
ZZZ

Arguments are explained here.

Instant Client ODBC - Query DSN definition on Windows

See here.

Instant Client ODBC - Query DSN definition on Linux

Run:

odbcinst -q -s -n OracleDemoDSN

Arguments are explained here.

Instant Client ODBC - Verify DSN connectivity on Windows

See here.

Instant Client ODBC - Verify DSN connectivity on Linux

Run:

isql -v OracleDemoDSN demo_user demo_pass

Arguments are explained here.

Oracle ODBC Usage

Default ODBC driver names:

Windows
- Oracle in XE
Linux
- Oracle

Run:

# Windows
SET PATH=%PATH%;D:\var\Oracle\InstantClient\12.1\dst

# Linux
export ORACLE_HOME=/var/Oracle/InstantClient/12.1/dst

export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME

# Windows, Linux, DSN
aoikqueryhurry -R "aoikqueryhurry.db.oracle.odbc.rfunc::rfunc" -C "aoikqueryhurry.db.odbc.ceodbc.cfunc::CFUNC" -S "OracleDemoDSN" -q "SELECT 'hello' FROM dual"

aoikqueryhurry -R "aoikqueryhurry.db.oracle.odbc.rfunc::rfunc" -C "aoikqueryhurry.db.odbc.pyodbc.cfunc::CFUNC" -S "OracleDemoDSN" -q "SELECT 'hello' FROM dual"

aoikqueryhurry -R "aoikqueryhurry.db.oracle.odbc.rfunc::rfunc" -C "aoikqueryhurry.db.odbc.pypyodbc.cfunc::CFUNC" -S "OracleDemoDSN" -q "SELECT 'hello' FROM dual"

# Windows, DSN-Less
aoikqueryhurry -R "aoikqueryhurry.db.oracle.odbc.rfunc::rfunc" -C "aoikqueryhurry.db.odbc.ceodbc.cfunc::CFUNC" -O "Oracle in XE" -H "127.0.0.1" -P "1521" -I "XE" -u "demo_user" -p "demo_pass" -q "SELECT 'hello' FROM dual"

aoikqueryhurry -R "aoikqueryhurry.db.oracle.odbc.rfunc::rfunc" -C "aoikqueryhurry.db.odbc.pyodbc.cfunc::CFUNC" -O "Oracle in XE" -H "127.0.0.1" -P "1521" -I "XE" -u "demo_user" -p "demo_pass" -q "SELECT 'hello' FROM dual"

aoikqueryhurry -R "aoikqueryhurry.db.oracle.odbc.rfunc::rfunc" -C "aoikqueryhurry.db.odbc.pypyodbc.cfunc::CFUNC" -O "Oracle in XE" -H "127.0.0.1" -P "1521" -I "XE" -u "demo_user" -p "demo_pass" -q "SELECT 'hello' FROM dual"

# Linux, DSN-Less
aoikqueryhurry -R "aoikqueryhurry.db.oracle.odbc.rfunc::rfunc" -C "aoikqueryhurry.db.odbc.ceodbc.cfunc::CFUNC" -O "Oracle" -H "127.0.0.1" -P "1521" -I "XE" -u "demo_user" -p "demo_pass" -q "SELECT 'hello' FROM dual"

aoikqueryhurry -R "aoikqueryhurry.db.oracle.odbc.rfunc::rfunc" -C "aoikqueryhurry.db.odbc.pyodbc.cfunc::CFUNC" -O "Oracle" -H "127.0.0.1" -P "1521" -I "XE" -u "demo_user" -p "demo_pass" -q "SELECT 'hello' FROM dual"

aoikqueryhurry -R "aoikqueryhurry.db.oracle.odbc.rfunc::rfunc" -C "aoikqueryhurry.db.odbc.pypyodbc.cfunc::CFUNC" -O "Oracle" -H "127.0.0.1" -P "1521" -I "XE" -u "demo_user" -p "demo_pass" -q "SELECT 'hello' FROM dual"

Oracle ODBC Usage with SQLAlchemy

List dialects available in SQLAlchemy:

python -c "import os; import sqlalchemy.dialects.oracle as m; print('\n'.join('oracle+'+x-3 for x in (os.listdir(os.path.dirname(m.__file__))) if (x.endswith('.py') and x != '__init__.py')))"

There are currently no ODBC dialects available for Oracle.

ODBC

ODBC libs can be divided into three categories:

ODBC Drivers
ODBC Managers
ODBC Client Libs

ODBC Drivers

ODBC drivers are low-level libs.

They implement communication protocols specific to database systems, e.g.

MySQL's MySQL Client/Server Protocol
MSSQL's Tabular Data Stream (TDS) Protocol.

They expose ODBC APIs to their users (e.g. ODBC managers or client libs).

ODBC Managers

ODBC managers are intermediate-level.

They delegate ODBC implementation to ODBC drivers.

They expose ODBC APIs to their users (e.g. ODBC client libs).

The merit of having an ODBC manager in the middle instead of using ODBC drivers directly is that it has made ODBC client libs driver-independent. The driver to use can be specified on a per-connection or per-DSN basis.

ODBC Data Source Administrator
unixODBC

ODBC Data Source Administrator

ODBC Data Source Administrator is the official ODBC manager On Windows.

Doc is here.

Setup and Usage:

ODBC Data Source Administrator - Setup
ODBC Data Source Administrator - Add driver definition
ODBC Data Source Administrator - Query driver definition
ODBC Data Source Administrator - Remove driver definition
ODBC Data Source Administrator - Rename driver definition
ODBC Data Source Administrator - Add DSN definition
ODBC Data Source Administrator - Query DSN definition
ODBC Data Source Administrator - Remove DSN definition
ODBC Data Source Administrator - Rename DSN definition
ODBC Data Source Administrator - Verify DSN connectivity

ODBC Data Source Administrator - Setup

No need. It comes with Windows.

Program is C:\Windows\system32\odbcad32.exe.

ODBC Data Source Administrator - Add driver definition

An ODBC driver's installer will usually add its driver definition to Windows registry for you. You can find the driver definitions in ODBC Data Source Administrator's Drivers tab.

ODBC Data Source Administrator - Query driver definition

Use ODBC Data Source Administrator. Another method is to check Windows registry.

For a driver _DRVR_, check registry items:

HKEY_LOCAL_MACHINE\Software\ODBC\ODBCINST.INI\_DRVR_

HKEY_LOCAL_MACHINE\Software\ODBC\ODBCINST.INI\ODBC Drivers\_DRVR_

ODBC Data Source Administrator - Remove driver definition

Edit Windows registry. For a driver DRVR, remove registry items:

HKEY_LOCAL_MACHINE\Software\ODBC\ODBC.INI\_DRVR_

HKEY_LOCAL_MACHINE\Software\ODBC\ODBCINST.INI\ODBC Drivers\_DRVR_

ODBC Data Source Administrator - Rename driver definition

Edit Windows registry. For a driver DRVR, rename registry items:

HKEY_LOCAL_MACHINE\Software\ODBC\ODBC.INI\_DRVR_

HKEY_LOCAL_MACHINE\Software\ODBC\ODBCINST.INI\ODBC Drivers\_DRVR_

ODBC Data Source Administrator - Add DSN definition

On ODBC Data Source Administrator's User DSN or System DSN tab, click button Add. Then select the ODBC driver to use, and fill in other settings such as server host and port.

ODBC Data Source Administrator - Query DSN definition

Use ODBC Data Source Administrator. Another method is to check Windows registry.

For a System DSN _DSN_, check registry items:

HKEY_LOCAL_MACHINE\Software\ODBC\ODBC.INI\_DSN_

HKEY_LOCAL_MACHINE\Software\ODBC\ODBC.INI\Odbc Data sources\_DSN_

For a User DSN _DSN_, check registry items:

HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\_DSN_

HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\Odbc Data sources\_DSN_

ODBC Data Source Administrator - Remove DSN definition

Use ODBC Data Source Administrator. Another method is to check Windows registry.

For a System DSN _DSN_, remove registry items:

HKEY_LOCAL_MACHINE\Software\ODBC\ODBC.INI\_DSN_

HKEY_LOCAL_MACHINE\Software\ODBC\ODBC.INI\Odbc Data sources\_DSN_

For a User DSN _DSN_, remove registry items:

HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\_DSN_

HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\Odbc Data sources\_DSN_

ODBC Data Source Administrator - Rename DSN definition

Use ODBC Data Source Administrator. Another method is to check Windows registry.

For a System DSN _DSN_, rename registry items:

HKEY_LOCAL_MACHINE\Software\ODBC\ODBC.INI\_DSN_

HKEY_LOCAL_MACHINE\Software\ODBC\ODBC.INI\Odbc Data sources\_DSN_

For a User DSN _DSN_, rename registry items:

HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\_DSN_

HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\Odbc Data sources\_DSN_

ODBC Data Source Administrator - Verify DSN connectivity

On ODBC Data Source Administrator's User DSN or System DSN tab, select a DSN item and click button Configure.... There should be functionality for testing connectivity.

Another method is to use program odbcte32 provided by Microsoft Data Access Components (MDAC) SDK.

unixODBC

On Linux, there are choices of ODBC manager like:

Setup and Usage:

unixODBC - Setup
unixODBC - Setup - Show config paths
unixODBC - Add driver definition
unixODBC - Query driver definition
unixODBC - Remove driver definition
unixODBC - Rename driver definition
unixODBC - Add DSN definition
unixODBC - Query DSN definition
unixODBC - Remove DSN definition
unixODBC - Rename DSN definition
unixODBC - Verify DSN connectivity

unixODBC - Setup

Run:

apt-get install -y odbcinst1debian2

apt-get install -y odbcinst

apt-get install -y unixodbc

apt-get install -y unixodbc-dev

Package odbcinst1debian2 installs ODBC setup lib file libtdsS.so.
This file is used in config file /etc/odbcinst.ini's parameter Setup.
Package odbcinst installs ODBC config files /etc/odbcinst.ini and /etc/odbc.ini, and util program odbcinst used to edit these config files.
Package unixodbc installs ODBC client program isql.
Package unixodbc-dev installs unixODBC's dev libs.

unixODBC - Setup - Show config paths

Run:

odbcinst -j

Result is:

DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /root/.odbc.ini

unixODBC - Add driver definition

On Linux, ODBC managers use config file /etc/odbcinst.ini to store ODBC driver definitions. To use an ODBC driver, we need to add its driver definition to /etc/odbcinst.ini.

An ODBC driver's package usually contains an exmaple for odbcinst.ini.
Run this command to locate where the exmaple file is

dpkg -L _DRVR_PKG_ | grep odbcinst.ini

Replace _DRVR_PKG_ with proper value of yours.

Take MySQL's ODBC driver libmyodbc for example.
The example file is /usr/share/libmyodbc/odbcinst.ini.

Add driver definition to /etc/odbcinst.ini

# Method 1
odbcinst -i -d -l -f /usr/share/libmyodbc/odbcinst.ini

# Method 2
cat /usr/share/libmyodbc/odbcinst.ini | odbcinst -i -d -l -r

# Method 3
cat <<'ZZZ' | odbcinst -i -d -l -r
[MySQL]
Description = MySQL driver
Driver      = libmyodbc.so
Setup       = libodbcmyS.so
CPTimeout   =
CPReuse     =
ZZZ

MySQL in [] specifies driver definition's name.
Description gives some description.
Driver specifies ODBC driver file.
Either absolute or relative paths can be used.
Relative paths will be resolved relative to some pre-defined dirs.
Setup specifies ODBC setup file.
Argument -i means install
Argument -d -l means what to install is driver definition in system config, i.e. the definition will be added to /etc/odbcinst.ini.
Argument -f specifies driver definition is read from a file.
Argument -r specifies driver definition is read from stdin.

Using program odbcinst is recommended instead of directly appending content to /etc/odbcinst.ini, because it avoids duplication of entries if any.

unixODBC - Query driver definition

Run:

odbcinst -q -d -l -n _DRVR_

Argument -q means query.
Argument -d -l means what to query is driver definition in system config, i.e. the definition will be queried from /etc/odbcinst.ini.
Argument -n specifies driver definition's name.
Replace _DRVR_ with proper value of yours.

unixODBC - Remove driver definition

Run:

odbcinst -u -d -l -n _DRVR_

Argument -u means uninstall
Argument -d -l means what to uninstall is driver definition in system config, i.e. the definition will be removed from /etc/odbcinst.ini.
Argument -n specifies driver name.
Replace _DRVR_ with proper value of yours.

unixODBC - Rename driver definition

Run:

(
#
_OLD_=SQLite
_NEW_=SQLite3

#
echo "# Old: $_OLD_"
odbcinst -q -d -n "$_OLD_"

[ $? -ne 0 ] && { echo "Error: Not found."; exit; }

#
odbcinst -q -d -n "$_OLD_" | sed "s/^\[$_OLD_\]$/[$_NEW_]/" | odbcinst -i -d -r

[ $? -ne 0 ] && { echo "Error: Failed."; exit; }

#
echo "# New: $_NEW_" &&
odbcinst -q -d -n "$_NEW_" &&

[ $? -ne 0 ] && { echo "Error: Not found."; exit; }

#
echo "# Remove: $_OLD_"
odbcinst -u -d -n "$_OLD_"

[ $? -ne 0 ] && { echo "Error: Failed."; exit; }
)

Replace SQLite and SQLite3 with proper value of yours.

unixODBC - Add DSN definition

On Linux, ODBC managers use config file /etc/odbc.ini to store DSN definitions.

An ODBC driver's package usually contains an example file for odbc.ini.

Run this command to find the example file:

dpkg -L _DRVR_PKG_ | grep odbc.ini

Replace _DRVR_PKG_ with proper value of yours.

Take MySQL's ODBC driver libmyodbc for example.
The example file is /usr/share/doc/libmyodbc/examples/odbc.ini.

Add DSN definition to /etc/odbc.ini

# Method 1
odbcinst -i -s -l -f /usr/share/doc/libmyodbc/examples/odbc.ini

# Method 2
cat /usr/share/doc/libmyodbc/examples/odbc.ini | odbcinst -i -s -l -r

# Method 3
cat <<'ZZZ' | odbcinst -i -s -l -r
[MySQLDemoDSN]
Driver       = MySQL
Server       = 127.0.0.1
Port         = 3306
User         = 
Password     =
Database     = 
Option       = 3
ZZZ

MySQLDemoDSN in [] specifies DSN definition's name.
Driver specifies driver definition to use, as defined in /etc/odbcinst.ini.
Argument -i means install.
Argument -s -l means what to install is DSN definition in system config, i.e. the definition will be added to /etc/odbc.ini.
Argument -f specifies DSN definition is read from a file.
Argument -r specifies DSN definition is read from stdin.

Using program odbcinst is recommended instead of directly appending content to /etc/odbc.ini, because it avoids duplication of entries if any.

unixODBC - Query DSN definition

Run:

odbcinst -q -s -l -n _DSN_

Argument -q means query.
Argument -s -l means what to query is DSN in system config, i.e. the definition will be queried from /etc/odbc.ini.
Argument -n specifies DSN definition's name.
Replace _DSN_ with proper value of yours.

unixODBC - Remove DSN definition

Run:

odbcinst -u -s -l -n _DSN_

Argument -u means uninstall
Argument -s -l means what to uninstall is DSN in system config, i.e. the definition will be removed from /etc/odbc.ini.
Argument -n specifies DSN definition's name.
Replace _DSN_ with proper value of yours.

unixODBC - Rename DSN definition

Run:

(
#
_OLD_=SQLiteDemoDSN
_NEW_=SQLite3DemoDSN

#
echo "# Old: $_OLD_"
odbcinst -q -s -l -n "$_OLD_"

[ $? -ne 0 ] && { echo "Error: Not found."; exit; }

#
odbcinst -q -s -l -n "$_OLD_" | sed "s/^\[$_OLD_\]$/[$_NEW_]/" | odbcinst -i -s -l -r

[ $? -ne 0 ] && { echo "Error: Failed."; exit; }

#
echo "# New: $_NEW_" &&
odbcinst -q -s -l -n "$_NEW_" &&

[ $? -ne 0 ] && { echo "Error: Not found."; exit; }

#
echo "# Remove: $_OLD_"
odbcinst -u -s -l -n "$_OLD_"

[ $? -ne 0 ] && { echo "Error: Failed."; exit; }
)

unixODBC - Verify DSN connectivity

Run:

isql -v _DSN_ _USER_ _PASSWD_

isql is a util program provided by package unixodbc.
Replace _DSN_, _USER_ and _PASSWD_ with proper values of yours.

ODBC Client Libs

ODBC client libs are high-level libs.

They delegate ODBC implementation to ODBC drivers or managers.
(It's also possible for ODBC client libs to directly implement communication protocols, i.e. taking on the role of ODBC drivers.)

They expose ODBC APIs to specific programming langauges.

Client libs:

ceODBC
pyodbc
pypyodbc

ceODBC

SourceForge

Tested working with:

ceODBC 2.0.1
Python 2.7, 3.4
Windows, Linux

Setup:

ceODBC - Setup on Windows
ceODBC - Setup on Linux
ceODBC - Verify setup

ceODBC - Setup on Windows

Download wheel file from here.

ceODBC-2.0.1-cp27-none-win_amd64.whl
# or
ceODBC-2.0.1-cp34-none-win_amd64.whl

Install from wheel file:

pip install ceODBC-2.0.1-cp27-none-win_amd64.whl
# or
pip install ceODBC-2.0.1-cp34-none-win_amd64.whl

ceODBC - Setup on Linux

Install dependency libs:

apt-get install -y unixodbc-dev

Download source code from here.

curl -O http://ncu.dl.sourceforge.net/project/ceodbc/2.0.1/ceODBC-2.0.1.tar.gz

Install from source code:

pip install ceODBC-2.0.1.tar.gz

ceODBC - Verify setup

Run:

python -c "import ceODBC"

pyodbc

SourceForge
PyPI

Tested working with:

pyodbc 3.0.7
Python 2.7, 3.4
Windows, Linux

Setup:

pyodbc - Setup on Windows
pyodbc - Setup on Linux
pyodbc - Verify setup

pyodbc - Setup on Windows

Install using pip:

pip install pyodbc

Another method is to use installer from here:

pyodbc-3.0.7.win-amd64-py2.7.exe

Installer for Python 3.4 is not available.

pyodbc - Setup on Linux

Install dependency libs:

apt-get install -y unixodbc-dev

Download source code from here.

curl -O http://code.google.com/p/pyodbc/downloads/detail?name=pyodbc-3.0.7.zip

Install from source code:

pip install pyodbc-3.0.7.zip

pyodbc - Verify setup

Run:

python -c "import pyodbc"

pypyodbc

PyPI

Tested working with:

pypyodbc 1.3.3
Python 2.7, 3.4
Windows, Linux

Setup:

pypyodbc - Setup
pypyodbc - Verify setup

pypyodbc - Setup

On both Windows and Linux, install using pip:

pip install pypyodbc

pypyodbc - Verify setup

Run:

python -c "import pypyodbc"

Redis

Tested working with:

Redis 2.8.21

Client libs:

credis
desir
miniredis
pypredis
redis-py

credis

PyPI
Github

Tested working with:

credis 1.0.4
Python 2.7, 3.4
Linux

Setup and Usage:

credis - Setup on Linux
credis - Verify setup
credis - Usage

credis - Setup on Linux

Clone source code to local:

git clone https://github.com/yihuang/credis

The latest commit b2687a8a8c800cd2bdba2ca719354a8238b0ccad on master branch has problem compiling. So reset to an older commit (version 1.0.4):

cd credis

git reset --hard f13b199e7869d3925ce42b2821447a2421903532

Install:

pip install .

credis - Verify setup

Run:

python -c "import credis"

credis - Usage

Run:

aoikqueryhurry -R "aoikqueryhurry.db.redis.credis.main::rfunc" -H "127.0.0.1" -P "6379"

The file is here.

desir

PyPI
Github

Tested working with:

desir 0.1
Python 2.7
Python 3 is not supported.
Windows, Linux

Setup and Usage:

desir - Setup
desir - Verify setup
desir - Usage

desir - Setup

desir is not installable from PyPI.

Install from Github:

pip install git+http://github.com/aallamaa/desir

desir - Verify setup

Run:

python -c "import desir"

desir - Usage

Run:

aoikqueryhurry -R "aoikqueryhurry.db.redis.desir.main::rfunc" -H "127.0.0.1" -P "6379"

The file is here.

miniredis

PyPI
Github

Tested working with:

miniredis 0.1
Python 2.7 Python 3 is not supported.
Windows, Linux

Setup and Usage:

miniredis - Setup
miniredis - Verify setup
miniredis - Usage

miniredis - Setup

miniredis is not installable from PyPI.

Install from Github:

pip install git+https://github.com/vsergeyev/miniredis-python

miniredis - Verify setup

Run:

python -c "import miniredis"

miniredis - Usage

Run:

aoikqueryhurry -R "aoikqueryhurry.db.redis.miniredis.main::rfunc" -H "127.0.0.1" -P "6379"

The file is here.

pypredis

PyPI
Github

Tested working with:

pypredis 0.3
Linux
Python 2.7
Python 3 is not supported.

Setup and Usage:

pypredis - Setup on Linux
pypredis - Verify setup
pypredis - Usage

pypredis - Setup on Linux

Install dependency lib cffi:

apt-get install -y libffi-dev

pip install cffi

This does not work:

pip install pypredis

error: option --single-version-externally-managed not recognized

Download source code from PyPI

curl -O https://pypi.python.org/packages/source/p/pypredis/pypredis-0.3.tar.gz

Install from source code:

tar xvf pypredis-0.3.tar.gz

cd pypredis-0.3

python setup.py install

pypredis - Verify setup

Run:

python -c "import pypredis"

pypredis - Usage

Run:

aoikqueryhurry -R "aoikqueryhurry.db.redis.pypredis.main::rfunc" -H "127.0.0.1" -P "6379"

The file is here.

redis-py

PyPI
Github

Its code package is redis.

Tested working with:

redis-py 2.10.3
Python 2.7, 3.4
Windows, Linux

Setup and Usage:

redis-py - Setup
redis-py - Verify setup
redis-py - Usage

redis-py - Setup

On both Windows and Linux, install using pip:

pip install redis

redis-py - Verify setup

Run:

python -c "import redis"

redis-py - Usage

Run:

aoikqueryhurry -R "aoikqueryhurry.db.redis.redis_py.main::rfunc" -H "127.0.0.1" -P "6379"

The file is here.

MongoDB

Tested working with:

MongoDB 3.0.4

Client libs:

pymongo

pymongo

PyPI
Github

Tested working with:

pymongo 3.0.3
Python 2.7, 3.4
Windows, Linux

Setup and Usage:

pymongo - Setup on Windows
pymongo - Setup on Linux
pymongo - Verify setup
pymongo - Usage

pymongo - Setup on Windows

Download installer from PyPI:

curl -O -k https://pypi.python.org/packages/2.7/p/pymongo/pymongo-3.0.3.win32-py2.7.exe
# or
curl -O -k https://pypi.python.org/packages/3.4/p/pymongo/pymongo-3.0.3.win32-py3.4.exe

pymongo - Setup on Linux

Install using pip:

pip install pymongo

pymongo - Verify setup

Run:

python -c "import pymongo"

pymongo - Usage

Run:

aoikqueryhurry -R "aoikqueryhurry.db.mongodb.pymongo.main::rfunc" -H "127.0.0.1" -P "27017"

The file is here.

Name		Name	Last commit message	Last commit date
Latest commit History 1 Commits
src/aoikqueryhurry		src/aoikqueryhurry
.gitignore		.gitignore
README.md		README.md
README.src.md		README.src.md
readme_nto.py		readme_nto.py
setup.py		setup.py

AoiKuiyuyou/AoikQueryHurry

Folders and files

Latest commit

History

Repository files navigation

AoikQueryHurry

Table of Contents

AoikQueryHurry Setup

AoikQueryHurry Setup via git

AoikQueryHurry Setup via pip

SQLite

sqlite3

sqlite3 - Setup on Windows

sqlite3 - Setup on Linux

sqlite3 - Verify setup

sqlite3 - Usage

sqlite3 - Usage with SQLAlchemy

SQLite ODBC

sqliteodbc

sqliteodbc - Setup on Windows

sqliteodbc - Setup on Linux

sqliteodbc - Add driver definition on Windows

sqliteodbc - Add driver definition on Linux

sqliteodbc - Query driver definition on Windows

sqliteodbc - Query driver definition on Linux

sqliteodbc - Add DSN definition on Windows

sqliteodbc - Add DSN definition on Linux

sqliteodbc - Query DSN definition on Windows

sqliteodbc - Query DSN definition on Linux

sqliteodbc - Verify DSN connectivity on Windows

sqliteodbc - Verify DSN connectivity on Linux

SQLite ODBC Usage

MySQL

MySQL - Preparation

MySQL Preparation - Log in as superuser

MySQL Preparation - Create database

MySQL Preparation - Create user

MySQL Preparation - Verify user login

MySQL Preparation - Install dependency libs

cymysql

cymysql - Setup

cymysql - Verify setup

cymysql - Usage

cymysql - Usage with SQLAlchemy

MySQL Connector Python

MySQL Connector Python - Setup

MySQL Connector Python - Verify setup

MySQL Connector Python - Usage

MySQL Connector Python - Usage with SQLAlchemy

mysqlclient

mysqlclient - Setup

mysqlclient - Verify setup

mysqlclient - Usage

MySQL-Python

MySQL-Python - Setup on Windows

MySQL-Python - Setup on Linux

MySQL-Python - Verify setup

MySQL-Python - Usage

MySQL-Python - Usage with SQLAlchemy

MySQLdb

oursql

oursql - Setup on Windows

oursql - Setup on Linux

oursql - Verify setup

oursql - Usage

oursql - Usage with SQLAlchemy

pymysql

pymysql - Setup

pymysql - Verify setup

pymysql - Usage

pymysql - Usage with SQLAlchemy

umysql

umysql - Setup on Windows

umysql - Setup on Linux

umysql - Verify setup

umysql - Usage

MySQL ODBC

MySQL Connector ODBC

MySQL Connector ODBC - Setup on Windows

MySQL Connector ODBC - Setup on Linux