Notes ODBC

Voir :

• odbc_avec_freetds Exemple d'ODBC avec microsoft SQL Server
• https://www.mssqltips.com/sqlservertip/7293/pyodbc-open-source-access-odbc-databases/
• https://blog.dbi-services.com/installing-the-odbc-driver-manager-with-sqlite-on-linux/
• https://datawookie.dev/blog/2015/09/setting-up-odbc-for-sqlite-on-ubuntu/
• https://www.devart.com/odbc/mongodb/docs/linux.htm
• DB API 2.0
• https://gambaswiki.org/wiki/doc/odbc

apt-get install unixodbc odbcinst 

apt-get install odbc-postgresql libsqliteodbc libmyodbc 

Sur Debian à ne pas éditer directement, c'est en installant odbc-postgresql, libsqliteodbc, libmyodbc que ce fichier sera mis à jour

/etc/odbcinst.ini

[SQLite]
Description=SQLite ODBC Driver
Driver=/usr/lib/odbc/libsqliteodbc.so
Setup=/usr/lib/odbc/libsqliteodbc.so
UsageCount=1
 
[SQLite3]
Description=SQLite3 ODBC Driver
Driver=/usr/lib/odbc/libsqlite3odbc.so
Setup=/usr/lib/odbc/libsqlite3odbc.so
UsageCount=1
 
[MySQL]
Description		= ODBC Driver for MySQL
Driver		= libmyodbc.so
Setup		= libodbcmyS.so
FileUsage		= 1
CPTimeout		= 
CPReuse		= 
 
[PostgreSQL ANSI]
Description		= PostgreSQL ODBC driver (ANSI version)
Driver		= psqlodbca.so
Setup		= libodbcpsqlS.so
Debug		= 0
CommLog		= 1
UsageCount		= 2
 
[PostgreSQL Unicode]
Description		= PostgreSQL ODBC driver (Unicode version)
Driver		= psqlodbcw.so
Setup		= libodbcpsqlS.so
Debug		= 0
CommLog		= 1
UsageCount		= 2

~/.odbc.ini ou /etc/odbc.ini

[mydb1]
Description     = MySQL MyDB1
Driver          = MySQL
Server          = localhost
Database        = mydb1
Port            = 3306
Socket          =
Option          =
Stmt            =
 
[mydb2]
Description     = MySQL MyDB2
Driver          = MySQL
Server          = localhost
Database        = mydb2
Port            = 3306
Socket          =
Option          =
Stmt            =
 
[tracdb]
Description=SQLite3 Trac
Driver=SQLite3
Database=/home/jean/trac.db
# optional lock timeout in milliseconds
#Timeout=2000

Vérifier la connexion avec iusql

iusql tracdb

iusql mydb1 root P@ssw0rd

Connaître le schéma de la base

Equivalent à show tables

$ iusql -d, -b SQLiteODBC <<< "help"
,,contacts,TABLE,

Équivalent à describe table

$ iusql -d, -b SQLiteODBC <<< "help contacts"
,,contacts,contact_id,4,INTEGER,9,10,10,0,1,,NULL,4,,16384,1,YES
,,contacts,first_name,-1,TEXT,0,65536,10,0,0,,NULL,-1,,16384,2,NO
,,contacts,last_name,-1,TEXT,0,65536,10,0,0,,NULL,-1,,16384,3,NO
,,contacts,email,-1,TEXT,0,65536,10,0,0,,NULL,-1,,16384,4,NO
,,contacts,phone,-1,TEXT,0,65536,10,0,0,,NULL,-1,,16384,5,NO

Exemple en Python

import sqlalchemy
from sqlalchemy.exc import  DBAPIError
 
engine = sqlalchemy.create_engine('mysql+pyodbc://tracdb', encoding='utf-8')
 
try:
	engine.connect()
except DBAPIError:
	pass
 
for row in engine.execute('select * from client'):
    print (row)

odbcinst -j
unixODBC 2.3.11
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /root/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8