Herstellen einer Verbindung zu verschiedenen DBs über Python (PEP 249) und SQL Alchemy

1. Zuallererst

Der Zugriff auf die Datenbank von Python aus wird als PEP 249 - Python Database API Specification v2.0 angegeben DB implementiert gemäß dieser Spezifikation. (Diese von jeder Datenbank bereitgestellte Implementierung wird als DBAPI bezeichnet.)

Die Idee ist

 Sie können eine Verbindung zur Datenbank herstellen, SQL ausführen und das Ergebnis mit demselben Code abrufen, ohne die Datenbank des Verbindungsziels zu kennen.

Ich denke, das bedeutet es. Wie ist es eigentlich? Ich würde gerne bestätigen.

2. Informationen zur DB-API, die für jede DB vorbereitet wurde

Die typische DB-API jeder DB ist in der folgenden Liste aufgeführt.

Schauen wir uns die typische DB-API jeder DB einzeln an.

2-1.MySQL

■ Implementierungsbeispiel mit dem MySQL-Modul ("5.4 Daten mit Connector / Python abfragen" "Als)

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

■ Implementierungsbeispiel mit dem Mariadb-Modul ("So verbinden Sie Python-Programme mit MariaDB" Als)

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

■ Implementierungsbeispiel mit dem Psycopg2-Modul (aus "Grundlegende Modulverwendung")

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

■ Implementierungsbeispiel mit dem Modul psycopg2 ("Python- und Oracle-Datenbank-Tutorial: Skripterstellung für die Zukunft -für-die-Zukunft.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

■ Implementierungsbeispiel mit dem pymssql-Modul ("Schritt 3: Demonstrieren Sie das Konzept der Verbindung mit SQL über pymssql Schritt-3-Proof-of-Concept-Verbindung-zu-SQL-mit-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. Unterschiede in der DB-API jeder DB

Wenn Sie sich den obigen Beispielcode genau ansehen, können Sie dies in jeder DB-API sehen

① Erstellen Sie eine Verbindungsinstanz ② Erstellen Sie eine Cursor-Instanz ③ Führen Sie SQL mit der generierten Cursor-Instanz aus ④ Rufen Sie das Ausführungsergebnis von SQL ab

Ich denke, es ist in Form von. Es gibt einige Unterschiede beim Schreiben, aber sobald Sie gelernt haben, wie man eine DB-API schreibt, können Sie problemlos eine andere DB verwenden. In diesem Fall scheint es gut, die DB-API jeder DB direkt zu verwenden, aber leider enthält der DB-API-Standard nicht die Verbindungspoolspezifikation. Da jede DB-API möglicherweise eine eigene Implementierung hat, ist es möglich, sie in die zu entwickelnde Anwendung zu integrieren. Die Implementierung ist jedoch je nach verwendeter DB unterschiedlich.

3.SQLAlchemy SQL Alchemy, bekannt als ORM, ist wie ein Erweiterungsmodul der DB-API, das verschiedene DB-APIs verschiedener DBs enthält und den Verbindungspool implementiert.

Um ehrlich zu sein, ist die Verwendung von ORM ein Problem, und obwohl es sich um einen DB-abhängigen Schreibstil handelt, denke ich immer, dass ich SQL direkt schreiben möchte. Selbst für solch ein egoistisches Ich schreibt SQLAlchemy die Verwendung von ORM nicht vor, und ich bin sehr dankbar, dass es eine Möglichkeit bietet, SQL direkt auszuführen. Wenn Sie über die DB-API eine Verbindung zur Datenbank herstellen möchten, sollten Sie SQL Alchemy verwenden.

Da es sich nicht um eine DBAPI handelt, unterscheidet sich die Implementierungsmethode geringfügig. Sie können die DB jedoch in nahezu demselben Ablauf wie in [2-6. Unterschiede in der DBAPI jeder DB] beschrieben betreiben.

■ Implementierungsbeispiel mit dem sqlalchemy-Modul (Code-Auszug aus "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()

Wenn Sie den Verbindungspool verwenden möchten, beschreiben Sie den Teil create_engine wie folgt.

■ Aus "Verbindungspooling"

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

4. Referenz

PlaySQLAlchemy: Erste Schritte mit SQLAlchemy The Novice’s Guide to the Python 3 DB-API Einführung in SQLAlchemy, die vorerst verwendbar zu sein scheint (* ORM-Funktion wird nicht verwendet)