Python Language
Dostęp do bazy danych

Uwagi

Dostęp do bazy danych MySQL za pomocą MySQLdb

Pierwszą rzeczą, którą musisz zrobić, to utworzyć połączenie z bazą danych za pomocą metody connect. Następnie będziesz potrzebować kursora, który będzie działał z tym połączeniem.

Użyj metody wykonania kursora, aby wchodzić w interakcję z bazą danych i co jakiś czas zatwierdzaj zmiany za pomocą metody zatwierdzania obiektu połączenia.

Gdy wszystko zostanie zrobione, nie zapomnij zamknąć kursora i połączenia.

Oto klasa Dbconnect ze wszystkim, czego potrzebujesz.

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

Interakcja z bazą danych jest prosta. Po utworzeniu obiektu wystarczy użyć metody execute.

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

Jeśli chcesz wywołać procedurę składowaną, użyj następującej składni. Pamiętaj, że lista parametrów jest opcjonalna.

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

Po zakończeniu zapytania możesz uzyskać dostęp do wyników na wiele sposobów. Obiekt kursora jest generatorem, który może pobrać wszystkie wyniki lub zostać zapętlony.

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

Jeśli chcesz użyć pętli bezpośrednio z generatora:

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

Jeśli chcesz zatwierdzić zmiany w bazie danych:

db.commit_db()

Jeśli chcesz zamknąć kursor i połączenie:

db.close_db()

SQLite

SQLite to lekka, dyskowa baza danych. Ponieważ nie wymaga oddzielnego serwera bazy danych, jest często używany do prototypowania lub do małych aplikacji, które są często używane przez jednego użytkownika lub przez jednego użytkownika w danym momencie.

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

Powyższy kod łączy się z bazą danych przechowywaną w pliku o nazwie users.db , tworząc najpierw plik, jeśli jeszcze nie istnieje. Z bazą danych można wchodzić w interakcje za pomocą instrukcji SQL.

Wynikiem tego przykładu powinno być:

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

Składnia SQLite: dogłębna analiza

Pierwsze kroki

1. Zaimportuj moduł sqlite za pomocą

   >>> import sqlite3
   

2. Aby użyć modułu, musisz najpierw utworzyć obiekt Connection reprezentujący bazę danych. Tutaj dane będą przechowywane w pliku example.db:

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

   Możesz również podać specjalną nazwę :memory: aby utworzyć tymczasową bazę danych w pamięci RAM, w następujący sposób:

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

3. Po utworzeniu Connection można utworzyć Cursor obiekt i wywołać jej execute() metody do wykonywania poleceń 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()
   

Ważne atrybuty i funkcje Connection

1. isolation_level

   Jest to atrybut używany do uzyskania lub ustawienia bieżącego poziomu izolacji. Brak dla trybu automatycznego zatwierdzania lub jednego z DEFERRED , IMMEDIATE lub EXCLUSIVE .

2. cursor

   Obiekt kursora służy do wykonywania poleceń i zapytań SQL.

3. commit()

   Zatwierdza bieżącą transakcję.

4. rollback()

   Cofa wszelkie zmiany wprowadzone od czasu poprzedniego wywołania commit()

5. close()

   Zamyka połączenie z bazą danych. Nie wywołuje automatycznie commit() . Jeśli wywołanie close() zostanie wywołane bez pierwszego wywołania commit() (zakładając, że nie jesteś w trybie automatycznego zatwierdzania), wszystkie wprowadzone zmiany zostaną utracone.

6. total_changes

   Atrybut rejestrujący całkowitą liczbę wierszy zmodyfikowanych, usuniętych lub wstawionych od czasu otwarcia bazy danych.

7. execute , executemany i executescript

   Funkcje te działają w taki sam sposób, jak w obiekcie kursora. Jest to skrót, ponieważ wywołanie tych funkcji przez obiekt połączenia powoduje utworzenie pośredniego obiektu kursora i wywołuje odpowiednią metodę obiektu kursora

8. row_factory

   Możesz zmienić ten atrybut na wywoływalny, który akceptuje kursor i oryginalny wiersz jako krotkę i zwróci wiersz wyniku rzeczywistego.

   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
   

Ważne funkcje Cursor

1. execute(sql[, parameters])

   Wykonuje pojedynczą instrukcję SQL. Instrukcja SQL może być sparametryzowana (tj. Symbole zastępcze zamiast literałów SQL). Moduł sqlite3 obsługuje dwa rodzaje symboli zastępczych: znaki zapytania ? („Styl qmark”) i nazwane symbole zastępcze :name („styl nazwany”).

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

Uwaga: nie używaj %s do wstawiania ciągów do poleceń SQL, ponieważ może to narazić twój program na atak wstrzyknięcia SQL (patrz Wstrzykiwanie SQL ).

2. executemany(sql, seq_of_parameters)

   Wykonuje polecenie SQL dla wszystkich sekwencji parametrów lub odwzorowań znalezionych w sekwencji sql. Moduł sqlite3 pozwala również na użycie iteratora podającego parametry zamiast sekwencji.

   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)
   

   Możesz również przekazać obiekty iteratora jako parametr do wykonania wiele, a funkcja będzie iterować po każdej krotce wartości zwracanych przez iterator. Iterator musi zwrócić krotkę wartości.

   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)

   Jest to niestandardowa wygodna metoda wykonywania wielu instrukcji SQL jednocześnie. Najpierw wydaje instrukcję COMMIT , a następnie wykonuje skrypt SQL, który otrzymuje jako parametr.

   sql_script może być instancją str lub 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
        );
        """)
   

   Następny zestaw funkcji jest używany w połączeniu z instrukcjami SELECT w SQL. Aby pobrać dane po wykonaniu instrukcji SELECT , możesz traktować kursor jako iterator, wywołać metodę fetchone() kursora, aby pobrać pojedynczy pasujący wiersz, lub wywołać fetchall() aby uzyskać listę pasujących wierszy.

   Przykład formularza iteratora:

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

   Pobiera następny wiersz zestawu wyników zapytania, zwracając pojedynczą sekwencję lub Brak, gdy nie ma już dostępnych danych.

   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)

   Pobiera następny zestaw wierszy wyniku zapytania (określonego przez rozmiar), zwracając listę. Jeśli pominięto rozmiar, fetchmany zwraca pojedynczy wiersz. Pusta lista jest zwracana, gdy nie ma już dostępnych wierszy.

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

   Pobiera wszystkie (pozostałe) wiersze wyniku zapytania, zwracając listę.

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

Typy danych SQLite i Python

SQLite natywnie obsługuje następujące typy: NULL, INTEGER, REAL, TEXT, BLOB.

W ten sposób typy danych są konwertowane podczas przechodzenia z SQL do Pythona i odwrotnie.

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

Dostęp do bazy danych PostgreSQL przy użyciu psycopg2

psycopg2 to najpopularniejszy adapter bazy danych PostgreSQL, który jest zarówno lekki, jak i wydajny. Jest to bieżąca implementacja adaptera PostgreSQL.

Jego główne cechy to pełna implementacja specyfikacji Python DB API 2.0 i bezpieczeństwo wątków (kilka wątków może współdzielić to samo połączenie)

Nawiązanie połączenia z bazą danych i utworzenie tabeli

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

Wstawianie danych do tabeli:

# 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)""")

Pobieranie danych tabeli:

# 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]))

Wynikiem powyższego byłoby:

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

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

A więc proszę bardzo, teraz wiesz już połowę wszystkiego, co musisz wiedzieć o psycopg2 ! :)

Baza danych Oracle

Warunki wstępne:

• Pakiet cx_Oracle - zobacz tutaj dla wszystkich wersji
• Klient błyskawiczny Oracle - dla Windows x64 , Linux x64

Ustawiać:

• Zainstaluj pakiet cx_Oracle jako:

  sudo rpm -i <YOUR_PACKAGE_FILENAME>

• Wyodrębnij klienta błyskawicznego Oracle i ustaw zmienne środowiskowe jako:

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

Tworzenie połączenia:

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

Pobierz wersję bazy danych:

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

Przykładowe dane wyjściowe: [„12”, „1”, „0”, „2”, „0”]

Wykonaj zapytanie: SELECT

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

Dane wyjściowe będą w krotkach Python:

(10, „SYSADMIN”, „IT-INFRA”, 7)

(23, „HR ASSOCIATE”, „LUDZKIE ZASOBY”, 6)

Wykonaj zapytanie: INSERT

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

Podczas wykonywania operacji wstawiania / aktualizacji / usuwania w bazie danych Oracle zmiany są dostępne tylko w sesji, dopóki nie zostanie wydane commit . Gdy zaktualizowane dane są zatwierdzane w bazie danych, są one następnie dostępne dla innych użytkowników i sesji.

Wykonaj zapytanie: INSERT przy użyciu zmiennych Bind

Odniesienie

Zmienne powiązania umożliwiają ponowne wykonanie instrukcji z nowymi wartościami bez konieczności ponownego analizowania instrukcji. Zmienne wiązania poprawiają ponowne użycie kodu i mogą zmniejszyć ryzyko ataków 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()

Zamknij połączenie:

_db_connection.close()

Metoda close () zamyka połączenie. Wszelkie połączenia, które nie zostaną jawnie zamknięte, zostaną automatycznie zwolnione po zakończeniu skryptu.

Połączenie

Tworzenie połączenia

Zgodnie z PEP 249 połączenie z bazą danych powinno być ustanowione za pomocą konstruktora connect() , który zwraca obiekt Connection . Argumenty tego konstruktora zależą od bazy danych. Odpowiednie argumenty znajdują się w tematach dotyczących bazy danych.

import MyDBAPI

con = MyDBAPI.connect(*database_dependent_args)

Ten obiekt połączenia ma cztery metody:

1: zamknij

con.close()

Natychmiast zamyka połączenie. Zauważ, że połączenie zostanie automatycznie zamknięte, jeśli Connection.__del___ zostanie metoda Connection.__del___ . Wszelkie oczekujące transakcje zostaną domyślnie wycofane.

2: zatwierdzenie

con.commit()

Zatwierdza każdą oczekującą transakcję do bazy danych.

3: wycofanie

con.rollback()

Cofa do początku każdej oczekującej transakcji. Innymi słowy: anuluje to wszelkie niezaangażowane transakcje w bazie danych.

4: kursor

cur = con.cursor()

Zwraca obiekt Cursor . Służy do wykonywania transakcji w bazie danych.

Korzystanie z sqlalchemy

Aby użyć narzędzia sqlalchemy do bazy danych:

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

Teraz ten silnik może być używany: np. Z pandami do pobierania ramek danych bezpośrednio z mysql

import pandas as pd

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