Python Language
Accesso al database

Osservazioni

Accesso al database MySQL usando MySQLdb

La prima cosa che devi fare è creare una connessione al database usando il metodo connect. Dopodiché avrai bisogno di un cursore che funzionerà con quella connessione.

Utilizzare il metodo di esecuzione del cursore per interagire con il database e, una volta ogni tanto, eseguire il commit delle modifiche utilizzando il metodo di commit dell'oggetto di connessione.

Una volta che tutto è stato fatto, non dimenticare di chiudere il cursore e la connessione.

Ecco una classe Dbconnect con tutto ciò di cui hai bisogno.

import MySQLdb

class Dbconnect(object):

    def __init__(self):

        self.dbconection = MySQLdb.connect(host='host_example',
                                           port=int('port_example'),
                                           user='user_example',
                                           passwd='pass_example',
                                           db='schema_example')
        self.dbcursor = self.dbconection.cursor()

    def commit_db(self):
        self.dbconection.commit()

    def close_db(self):
        self.dbcursor.close()
        self.dbconection.close()

Interagire con il database è semplice. Dopo aver creato l'oggetto, basta usare il metodo execute.

db = Dbconnect()
db.dbcursor.execute('SELECT * FROM %s' % 'table_example')

Se si desidera chiamare una stored procedure, utilizzare la seguente sintassi. Si noti che l'elenco dei parametri è facoltativo.

db = Dbconnect()
db.callproc('stored_procedure_name', [parameters] )

Una volta completata la query, è possibile accedere ai risultati in diversi modi. L'oggetto cursore è un generatore che può recuperare tutti i risultati o essere in loop.

results = db.dbcursor.fetchall()
for individual_row in results:
    first_field = individual_row[0]

Se vuoi un ciclo usando direttamente il generatore:

for individual_row in db.dbcursor:
    first_field = individual_row[0]

Se si desidera eseguire il commit delle modifiche al database:

db.commit_db()

Se si desidera chiudere il cursore e la connessione:

db.close_db()

SQLite

SQLite è un database leggero basato su disco. Poiché non richiede un server di database separato, viene spesso utilizzato per la prototipazione o per piccole applicazioni che vengono spesso utilizzate da un singolo utente o da un utente in un dato momento.

import sqlite3

conn = sqlite3.connect("users.db")
c = conn.cursor()

c.execute("CREATE TABLE user (name text, age integer)")

c.execute("INSERT INTO user VALUES ('User A', 42)")
c.execute("INSERT INTO user VALUES ('User B', 43)")

conn.commit()

c.execute("SELECT * FROM user")
print(c.fetchall())

conn.close()

Il codice sopra si connette al database memorizzato nel file users.db , creando prima il file se non esiste già. È possibile interagire con il database tramite istruzioni SQL.

Il risultato di questo esempio dovrebbe essere:

[(u'User A', 42), (u'User B', 43)]

La sintassi SQLite: un'analisi approfondita

Iniziare

1. Importa il modulo sqlite usando

   >>> import sqlite3
   

2. Per utilizzare il modulo, è necessario innanzitutto creare un oggetto Connection che rappresenti il ​​database. Qui i dati saranno memorizzati nel file example.db:

   >>> conn = sqlite3.connect('users.db')
   

   In alternativa, è anche possibile fornire il nome speciale :memory: per creare un database temporaneo nella RAM, come segue:

   >>> conn = sqlite3.connect(':memory:')
   

3. Una volta che hai una Connection , puoi creare un oggetto Cursor e chiamare il suo metodo execute() per eseguire comandi SQL:

   c = conn.cursor()
   
   # Create table
   c.execute('''CREATE TABLE stocks
               (date text, trans text, symbol text, qty real, price real)''')
   
   # Insert a row of data
   c.execute("INSERT INTO stocks VALUES ('2006-01-05','BUY','RHAT',100,35.14)")
   
   # Save (commit) the changes
   conn.commit()
   
   # We can also close the connection if we are done with it.
   # Just be sure any changes have been committed or they will be lost.
   conn.close()
   

Attributi importanti e funzioni di Connection

1. isolation_level

   È un attributo utilizzato per ottenere o impostare il livello di isolamento corrente. Nessuno per la modalità di autocommit o uno di DEFERRED , IMMEDIATE o EXCLUSIVE .

2. cursor

   L'oggetto cursore viene utilizzato per eseguire comandi e query SQL.

3. commit()

   Commette la transazione corrente.

4. rollback()

   Annulla tutte le modifiche apportate dalla precedente chiamata a commit()

5. close()

   Chiude la connessione al database. Non chiama commit() automaticamente. Se close() viene chiamato senza prima commit() (supponendo che non ci si trovi in ​​modalità autocommit), tutte le modifiche apportate andranno perse.

6. total_changes

   Un attributo che registra il numero totale di righe modificate, eliminate o inserite da quando il database è stato aperto.

7. execute , executemany e executescript

   Queste funzioni si comportano allo stesso modo di quelle dell'oggetto cursore. Questa è una scorciatoia poiché chiamare queste funzioni tramite l'oggetto connessione determina la creazione di un oggetto cursore intermedio e chiama il metodo corrispondente dell'oggetto cursore

8. row_factory

   Puoi cambiare questo attributo in un callable che accetta il cursore e la riga originale come una tupla e restituirà la riga del risultato reale.

   def dict_factory(cursor, row):
       d = {}
       for i, col in enumerate(cursor.description):
           d[col[0]] = row[i]
       return d
   
   conn = sqlite3.connect(":memory:")
   conn.row_factory = dict_factory
   

Funzioni importanti del Cursor

1. execute(sql[, parameters])

   Esegue una singola istruzione SQL. L'istruzione SQL può essere parametrizzata (cioè segnaposto anziché letterali SQL). Il modulo sqlite3 supporta due tipi di segnaposto: punti interrogativi ? ("Stile qmark") e segnaposti con :name ("stile nominato").

   import sqlite3
   conn = sqlite3.connect(":memory:")
   cur = conn.cursor()
   cur.execute("create table people (name, age)")
   
   who = "Sophia"
   age = 37
   # This is the qmark style:
   cur.execute("insert into people values (?, ?)",
               (who, age))
   
   # And this is the named style:
   cur.execute("select * from people where name=:who and age=:age",
               {"who": who, "age": age})  # the keys correspond to the placeholders in SQL
   
   print(cur.fetchone())
   

Attenzione: non usare %s per inserire stringhe nei comandi SQL in quanto può rendere il programma vulnerabile a un attacco di SQL injection (vedere SQL Injection ).

2. executemany(sql, seq_of_parameters)

   Esegue un comando SQL su tutte le sequenze di parametri o mappature che si trovano nella sequenza sql. Il modulo sqlite3 consente inoltre di utilizzare un iteratore che fornisce parametri anziché una sequenza.

   L = [(1, 'abcd', 'dfj', 300),    # A list of tuples to be inserted into the database
        (2, 'cfgd', 'dyfj', 400),
        (3, 'sdd', 'dfjh', 300.50)]                           
   
   conn = sqlite3.connect("test1.db")
   conn.execute("create table if not exists book (id int, name text, author text, price real)")
   conn.executemany("insert into book values (?, ?, ?, ?)", L)
   
   for row in conn.execute("select * from book"):
       print(row)
   

   È anche possibile passare gli oggetti iteratore come parametro per l'esecuzione, e la funzione eseguirà l'iterazione su ciascuna tupla di valori restituita dall'iteratore. L'iteratore deve restituire una tupla di valori.

   import sqlite3
   
   class IterChars:
       def __init__(self):
           self.count = ord('a')
   
       def __iter__(self):
           return self
   
       def __next__(self):            # (use next(self) for Python 2)
           if self.count > ord('z'):
               raise StopIteration
           self.count += 1
           return (chr(self.count - 1),) 
   
   conn = sqlite3.connect("abc.db")
   cur = conn.cursor()
   cur.execute("create table characters(c)")
   
   theIter = IterChars()
   cur.executemany("insert into characters(c) values (?)", theIter)
   
   rows = cur.execute("select c from characters")
   for row in rows:
       print(row[0]),
   

3. executescript(sql_script)

   Questo è un metodo di convenienza non standard per l'esecuzione di più istruzioni SQL contemporaneamente. COMMIT prima una istruzione COMMIT , quindi esegue lo script SQL che ottiene come parametro.

   sql_script può essere un'istanza di str o bytes .

   import sqlite3
   conn = sqlite3.connect(":memory:")
   cur = conn.cursor()
   cur.executescript("""
        create table person(
            firstname,
            lastname,
            age
        );
   
        create table book(
            title,
            author,
            published
        );
   
        insert into book(title, author, published)
        values (
            'Dirk Gently''s Holistic Detective Agency',
            'Douglas Adams',
            1987
        );
        """)
   

   Il prossimo set di funzioni viene utilizzato insieme alle istruzioni SELECT in SQL. Per recuperare i dati dopo aver eseguito un'istruzione SELECT , puoi considerare il cursore come un iteratore, chiamare il metodo fetchone() del cursore per recuperare una singola riga corrispondente o chiamare fetchall() per ottenere un elenco delle righe corrispondenti.

   Esempio del modulo iteratore:

   import sqlite3
   stocks = [('2006-01-05', 'BUY', 'RHAT', 100, 35.14),
             ('2006-03-28', 'BUY', 'IBM', 1000, 45.0),
             ('2006-04-06', 'SELL', 'IBM', 500, 53.0),
             ('2006-04-05', 'BUY', 'MSFT', 1000, 72.0)]
   conn = sqlite3.connect(":memory:")
   conn.execute("create table stocks (date text, buysell text, symb text, amount int, price real)")
   conn.executemany("insert into stocks values (?, ?, ?, ?, ?)", stocks)    
   cur = conn.cursor()
   
   for row in cur.execute('SELECT * FROM stocks ORDER BY price'):
       print(row)
   
   # Output:
   # ('2006-01-05', 'BUY', 'RHAT', 100, 35.14)
   # ('2006-03-28', 'BUY', 'IBM', 1000, 45.0)
   # ('2006-04-06', 'SELL', 'IBM', 500, 53.0)
   # ('2006-04-05', 'BUY', 'MSFT', 1000, 72.0)
   

4. fetchone()

   Rileva la riga successiva di un set di risultati di query, restituendo una singola sequenza o None quando non sono disponibili altri dati.

   cur.execute('SELECT * FROM stocks ORDER BY price')
   i = cur.fetchone()
   while(i): 
       print(i)
       i = cur.fetchone()
   
   # Output:
   # ('2006-01-05', 'BUY', 'RHAT', 100, 35.14)
   # ('2006-03-28', 'BUY', 'IBM', 1000, 45.0)
   # ('2006-04-06', 'SELL', 'IBM', 500, 53.0)
   # ('2006-04-05', 'BUY', 'MSFT', 1000, 72.0)
   

5. fetchmany(size=cursor.arraysize)

   Rileva il successivo set di righe di un risultato di una query (specificato dalla dimensione), restituendo un elenco. Se la dimensione è omessa, fetchmany restituisce una singola riga. Viene restituita una lista vuota quando non sono disponibili più righe.

   cur.execute('SELECT * FROM stocks ORDER BY price')
   print(cur.fetchmany(2))
   
   # Output:    
   # [('2006-01-05', 'BUY', 'RHAT', 100, 35.14), ('2006-03-28', 'BUY', 'IBM', 1000, 45.0)]
   

6. fetchall()

   Rileva tutte le (restanti) righe di un risultato della query, restituendo un elenco.

   cur.execute('SELECT * FROM stocks ORDER BY price')
   print(cur.fetchall())
   
   # Output:
   # [('2006-01-05', 'BUY', 'RHAT', 100, 35.14), ('2006-03-28', 'BUY', 'IBM', 1000, 45.0), ('2006-04-06', 'SELL', 'IBM', 500, 53.0), ('2006-04-05', 'BUY', 'MSFT', 1000, 72.0)]
   

Tipi di dati SQLite e Python

SQLite supporta nativamente i seguenti tipi: NULL, INTEGER, REAL, TEXT, BLOB.

In questo modo vengono convertiti i tipi di dati quando si passa da SQL a Python o viceversa.

                None     <->     NULL
                int      <->     INTEGER/INT
                float    <->     REAL/FLOAT
                str      <->     TEXT/VARCHAR(n)
                bytes    <->     BLOB

Accesso al database PostgreSQL utilizzando psycopg2

psycopg2 è l'adattatore di database PostgreSQL più popolare che sia leggero ed efficiente. È l'attuale implementazione dell'adattatore PostgreSQL.

Le sue caratteristiche principali sono l'implementazione completa della specifica Python DB API 2.0 e la sicurezza del thread (diversi thread possono condividere la stessa connessione)

Stabilire una connessione al database e creare una tabella

import psycopg2

# Establish a connection to the database.
# Replace parameter values with database credentials.
conn = psycopg2.connect(database="testpython", 
                        user="postgres",
                        host="localhost",
                        password="abc123",
                        port="5432") 

# Create a cursor. The cursor allows you to execute database queries. 
cur = conn.cursor()

# Create a table. Initialise the table name, the column names and data type. 
cur.execute("""CREATE TABLE FRUITS (
                    id          INT ,
                    fruit_name  TEXT,
                    color       TEXT,
                    price       REAL
            )""")
conn.commit()
conn.close()

Inserimento di dati nella tabella:

# After creating the table as shown above, insert values into it.
cur.execute("""INSERT INTO FRUITS (id, fruit_name, color, price)
               VALUES (1, 'Apples', 'green', 1.00)""")

cur.execute("""INSERT INTO FRUITS (id, fruit_name, color, price)
               VALUES (1, 'Bananas', 'yellow', 0.80)""")

Recupero dei dati della tabella:

# Set up a query and execute it 
cur.execute("""SELECT id, fruit_name, color, price 
             FROM fruits""")

# Fetch the data 
rows = cur.fetchall()

# Do stuff with the data
for row in rows:
    print "ID = {} ".format(row[0])
    print "FRUIT NAME = {}".format(row[1])
    print("COLOR = {}".format(row[2]))
    print("PRICE = {}".format(row[3]))

L'output di quanto sopra sarebbe:

ID = 1 
NAME = Apples
COLOR = green
PRICE = 1.0

ID = 2 
NAME = Bananas
COLOR = yellow
PRICE = 0.8

E così, ecco, adesso sai la metà di tutto ciò che devi sapere su psycopg2 ! :)

Database Oracle

Pre-requisiti:

• pacchetto cx_Oracle - Vedi qui per tutte le versioni
• Client istantaneo Oracle - Per Windows x64 , Linux x64

Impostare:

• Installa il pacchetto cx_Oracle come:

  sudo rpm -i <YOUR_PACKAGE_FILENAME>

• Estrai il client istantaneo Oracle e imposta le variabili d'ambiente come:

ORACLE_HOME=<PATH_TO_INSTANTCLIENT>
PATH=$ORACLE_HOME:$PATH
LD_LIBRARY_PATH=<PATH_TO_INSTANTCLIENT>:$LD_LIBRARY_PATH

Creare una connessione:

import cx_Oracle

class OraExec(object):
    _db_connection = None
    _db_cur = None

    def __init__(self):
        self._db_connection = 
            cx_Oracle.connect('<USERNAME>/<PASSWORD>@<HOSTNAME>:<PORT>/<SERVICE_NAME>')
        self._db_cur = self._db_connection.cursor()

Ottieni la versione del database:

ver = con.version.split(".")
print ver

Esempio di output: ['12', '1', '0', '2', '0']

Esegui query: SELEZIONA

_db_cur.execute("select * from employees order by emp_id")
for result in _db_cur:
    print result

L'output sarà in tuple Python:

(10, 'SYSADMIN', 'IT-INFRA', 7)

(23, "HR ASSOCIATE", "HUMAN RESOURCES", 6)

Esegui query: INSERT

_db_cur.execute("insert into employees(emp_id, title, dept, grade) 
                values (31, 'MTS', 'ENGINEERING', 7)
_db_connection.commit()

Quando si eseguono operazioni di inserimento / aggiornamento / cancellazione in un database Oracle, le modifiche sono disponibili solo all'interno della sessione fino commit . Quando i dati aggiornati vengono impegnati nel database, sono disponibili per altri utenti e sessioni.

Esegui query: INSERT utilizzando le variabili Bind

Riferimento

Le variabili di binding consentono di rieseguire le istruzioni con nuovi valori, senza il sovraccarico di ri-analisi della dichiarazione. Le variabili di binding migliorano la riutilizzabilità del codice e possono ridurre il rischio di attacchi SQL Injection.

rows = [ (1, "First" ),
     (2, "Second" ),
     (3, "Third" ) ]
_db_cur.bindarraysize = 3
_db_cur.setinputsizes(int, 10)
_db_cur.executemany("insert into mytab(id, data) values (:1, :2)", rows)
_db_connection.commit()

Chiudi connessione:

_db_connection.close()

Il metodo close () chiude la connessione. Qualsiasi connessione non esplicitamente chiusa verrà automaticamente rilasciata al termine dello script.

Connessione

Creare una connessione

Secondo PEP 249, la connessione a un database dovrebbe essere stabilita usando un costruttore connect() , che restituisce un oggetto Connection . Gli argomenti per questo costruttore sono dipendenti dal database. Fare riferimento agli argomenti specifici del database per gli argomenti pertinenti.

import MyDBAPI

con = MyDBAPI.connect(*database_dependent_args)

Questo oggetto di connessione ha quattro metodi:

1: vicino

con.close()

Chiude la connessione all'istante. Si noti che la connessione viene automaticamente chiusa se viene chiamato il metodo Connection.__del___ . Tutte le transazioni in sospeso verranno annullate implicitamente.

2: commit

con.commit()

Commette qualsiasi transazione in sospeso al database.

3: rollback

con.rollback()

Torna all'inizio di qualsiasi transazione in sospeso. In altre parole: annulla qualsiasi transazione non impegnata nel database.

4: cursore

cur = con.cursor()

Restituisce un oggetto Cursor . Questo è usato per fare transazioni sul database.

Utilizzando sqlalchemy

Per utilizzare sqlalchemy per il database:

from sqlalchemy import create_engine
from sqlalchemy.engine.url import URL


url = URL(drivername='mysql',
          username='user',
          password='passwd',
          host='host',
          database='db')

engine = create_engine(url)  # sqlalchemy engine

Ora questo motore può essere utilizzato: ad esempio con i panda per recuperare i frame di dati direttamente da mysql

import pandas as pd

con = engine.connect()
dataframe = pd.read_sql(sql=query, con=con)