Comment se connecter à diverses bases de données à partir de Python (PEP 249) et SQL Alchemy

1.Tout d'abord

L'accès à la base de données à partir de python est spécifié comme PEP 249 - Spécification de l'API de base de données Python v2.0, et chaque DB met en œuvre selon cette spécification. (Cette implémentation fournie par chaque DB s'appellera DBAPI.)

L'idée est

 Vous pouvez vous connecter à la base de données, exécuter SQL et obtenir le résultat avec le même code sans connaître la base de données de la destination de connexion.

Je pense que c'est ce que cela signifie. Alors, comment est-ce réellement? J'aimerais confirmer.

2. À propos de l'API DB préparée pour chaque DB

L'API DB typique de chaque DB est indiquée dans la liste ci-dessous.

Examinons l'API DB typique de chaque DB une par une.

2-1.MySQL

■ Exemple d'implémentation utilisant le module mysql ("5.4 Interroger des données à l'aide de Connector / Python" "Que)

import datetime
import mysql.connector

cnx = mysql.connector.connect(user='scott', database='employees')
cursor = cnx.cursor()

query = ("SELECT first_name, last_name, hire_date FROM employees "
         "WHERE hire_date BETWEEN %s AND %s")

hire_start = datetime.date(1999, 1, 1)
hire_end = datetime.date(1999, 12, 31)

cursor.execute(query, (hire_start, hire_end))

for (first_name, last_name, hire_date) in cursor:
  print("{}, {} was hired on {:%d %b %Y}".format(
    last_name, first_name, hire_date))

cursor.close()
cnx.close()

2-2.MariaDB

■ Exemple d'implémentation utilisant le module mariadb ("Comment connecter des programmes Python à MariaDB" Que)

import mariadb 

conn = mariadb.connect(
    user="db_user",
    password="db_user_passwd",
    host="localhost",
    database="employees")
cur = conn.cursor() 

retrieving information 
some_name = "Georgi" 
cur.execute("SELECT first_name,last_name FROM employees WHERE first_name=?", (some_name,)) 

for first_name, last_name in cur: 
    print(f"First name: {first_name}, Last name: {last_name}")
    
insert information 
try: 
    cur.execute("INSERT INTO employees (first_name,last_name) VALUES (?, ?)", ("Maria","DB")) 
except mariadb.Error as e: 
    print(f"Error: {e}")

conn.commit() 
print(f"Last Inserted ID: {cur.lastrowid}")
    
conn.close()

2-3.PostgreSQL

■ Exemple d'implémentation utilisant le module psycopg2 (extrait de "Basic module usage")

>>> import psycopg2

 Connect to an existing database
>>> conn = psycopg2.connect("dbname=test user=postgres")

 Open a cursor to perform database operations
>>> cur = conn.cursor()

 Execute a command: this creates a new table
>>> cur.execute("CREATE TABLE test (id serial PRIMARY KEY, num integer, data varchar);")

 Pass data to fill a query placeholders and let Psycopg perform
 the correct conversion (no more SQL injections!)
>>> cur.execute("INSERT INTO test (num, data) VALUES (%s, %s)",
...      (100, "abc'def"))

 Query the database and obtain data as Python objects
>>> cur.execute("SELECT * FROM test;")
>>> cur.fetchone()
(1, 100, "abc'def")

 Make the changes to the database persistent
>>> conn.commit()

 Close communication with the database
>>> cur.close()
>>> conn.close()

2-4.Oracle

■ Exemple d'implémentation utilisant le module psycopg2 ("[Python and Oracle Database Tutorial: Scripting for the Future](https://oracle.github.io/python-cx_Oracle/samples/tutorial/Python-and-Oracle-Database-Scripting]" -for-the-Future.html) ")

import cx_Oracle
import db_config

con = cx_Oracle.connect(db_config.user, db_config.pw, db_config.dsn)

cur = con.cursor()
cur.execute("select * from dept order by deptno")
res = cur.fetchall()
for row in res:
    print(row)

cur.close()
con.close()

2-5.Microsoft SQL Server

■ Exemple d'implémentation utilisant le module pymssql ("[Étape 3: Démontrez le concept de connexion à SQL en utilisant pymssql](https://docs.microsoft.com/ja-jp/sql/connect/python/pymssql/" étape 3-preuve-de-concept-connexion-à-sql-en-utilisant-pymssql? view = sql-server-ver15) ")

import pymssql  
conn = pymssql.connect(server='yourserver.database.windows.net', user='yourusername@yourserver', password='yourpassword', database='AdventureWorks')  
cursor = conn.cursor()  
cursor.execute('SELECT c.CustomerID, c.CompanyName,COUNT(soh.SalesOrderID) AS OrderCount FROM SalesLT.Customer AS c LEFT OUTER JOIN SalesLT.SalesOrderHeader AS soh ON c.CustomerID = soh.CustomerID GROUP BY c.CustomerID, c.CompanyName ORDER BY OrderCount DESC;')  
row = cursor.fetchone()  
while row:  
    print str(row[0]) + " " + str(row[1]) + " " + str(row[2])     
    row = cursor.fetchone()  

2-6. Différences dans l'API DB de chaque DB

Si vous regardez attentivement l'exemple de code ci-dessus, vous pouvez le voir dans n'importe quelle API DB

① Créer une instance de connexion ② Créer une instance Cursor ③ Exécuter SQL à l'aide de l'instance Cursor générée ④ Récupérez le résultat de l'exécution de SQL

Je pense que c'est sous la forme de. Il existe quelques différences d'écriture, mais une fois que vous avez appris à écrire une API DB, vous pouvez utiliser une autre DB sans aucune difficulté. Dans ce cas, il semble bon d'utiliser directement l'API DB de chaque DB, mais malheureusement, la norme DB API n'inclut pas la spécification Connection Pool. Comme chaque DB API peut avoir sa propre implémentation, il est possible de l'intégrer dans l'application à développer, mais l'implémentation sera différente selon la DB utilisée.

3.SQLAlchemy SQL Alchemy, connu sous le nom d'ORM, est comme un module d'extension de l'API DB qui a diverses API DB de différentes bases de données et implémente Connction Pool.

Pour être honnête, utiliser ORM est un problème, et bien que cela devienne un style d'écriture dépendant de DB, je pense toujours que je veux écrire SQL directement. Même pour un moi aussi égoïste, SQLAlchemy n'impose pas l'utilisation de l'ORM, et je suis très reconnaissant qu'il fournisse un moyen d'exécuter SQL directement. Si vous envisagez de vous connecter à la base de données à l'aide de l'API DB, pensez à utiliser SQL Alchemy.

Puisqu'il ne s'agit pas d'un DBAPI, la méthode d'implémentation est légèrement différente, mais vous pouvez faire fonctionner le DB dans presque le même flux comme décrit dans [2-6. Différences dans DBAPI de chaque DB].

■ Exemple d'implémentation utilisant le module sqlalchemy (extrait de code du "SQL Expression Language Tutorial")

>>> from sqlalchemy import create_engine
>>> engine = create_engine('sqlite:///:memory:', echo=True)
>>> conn = engine.connect()
>>> from sqlalchemy.sql import text
>>> s = text(
 ...     "SELECT users.fullname || ', ' || addresses.email_address AS title "
...         "FROM users, addresses "
...         "WHERE users.id = addresses.user_id "
...         "AND users.name BETWEEN :x AND :y "
...         "AND (addresses.email_address LIKE :e1 "
...             "OR addresses.email_address LIKE :e2)")
>>> conn.execute(s, x='m', y='z', e1='%@aol.com', e2='%@msn.com').fetchall()

Si vous souhaitez utiliser le pool de connexions, décrivez la partie create_engine comme suit.

■ Depuis «[Connection Pooling](https://docs.sqlalchemy.org/en/13/core/ Covoiturage.html)»

engine = create_engine('postgresql://me@localhost/mydb',pool_size=20, max_overflow=0)

4. Référence

PlaySQLAlchemy: Premiers pas avec SQLAlchemy The Novice’s Guide to the Python 3 DB-API Introduction à SQLAlchemy qui semble être utilisable pour le moment (* La fonction ORM n'est pas utilisée)