Différences

Ci-dessous, les différences entre deux révisions de la page.

--- tech:notes_odbc [2025/11/11 17:34] – Jean-Baptiste
+++ tech:notes_odbc [2026/06/07 19:12] (Version actuelle) – modification externe 127.0.0.1
@@ Ligne 1: / Ligne 1: @@
+<!DOCTYPE markdown>
+{{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
+~~~bash
+apt-get install unixodbc odbcinst
+~~~
+~~~bash
+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`
+~~~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`
+~~~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
+~~~bash
+iusql tracdb
+~~~
+~~~bash
+iusql mydb1 root P@ssw0rd
+~~~
+Connaître le schéma de la base
+Equivalent à `show tables`
+~~~bash
+$ 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
+~~~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
+~~~