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

[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)

Autres

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