{{tag>DB Python}}

= Notes ODBC

Voir :
* odbc_avec_freetds [[connexion_a_une_base_proprietaire_-_python_-_jdbc_-_odbc|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

<code bash>
apt-get install unixodbc odbcinst 
</code>

<code bash>
apt-get install odbc-postgresql libsqliteodbc libmyodbc 
</code>

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

''/etc/odbcinst.ini''
<code 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
</code>

''~/.odbc.ini'' ou ''/etc/odbc.ini''
<code 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
</code>

Vérifier la connexion avec iusql
<code bash>
iusql tracdb
</code>

<code bash>
iusql mydb1 root P@ssw0rd
</code>

Connaître le schéma de la base

Equivalent à ''show tables''
<code bash>
$ iusql -d, -b SQLiteODBC <<< "help"
,,contacts,TABLE,
</code>

Équivalent à ''describe table''
<code ->
$ 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
</code>


Exemple en Python

<code 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)
</code>


== Autres

<code ->
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
</code>