Python Language => Доступ к базе данных

замечания

Python может обрабатывать множество различных типов баз данных. Для каждого из этих типов существует другой API. Поэтому поощряем сходство между этими различными API, PEP 249.

Этот API был определен для поощрения сходства между модулями Python, которые используются для доступа к базам данных. Делая это, мы надеемся достичь согласованности, ведущей к более понятным модулям, коду, который, как правило, более переносим по базам данных и более широкому доступу к подключению к базе данных от Python. PEP-249

Доступ к базе данных MySQL с использованием MySQLdb

Первое, что вам нужно сделать, это создать соединение с базой данных с помощью метода connect. После этого вам понадобится курсор, который будет работать с этим соединением.

Используйте метод execute курсора для взаимодействия с базой данных, и каждый раз в то время фиксируйте изменения, используя метод commit объекта соединения.

Как только все будет сделано, не забудьте закрыть курсор и соединение.

Вот класс Dbconnect со всем, что вам нужно.

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

Взаимодействие с базой данных простое. После создания объекта просто используйте метод execute.

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

Если вы хотите вызвать хранимую процедуру, используйте следующий синтаксис. Обратите внимание, что список параметров не является обязательным.

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

По завершении запроса вы можете получить доступ к результатам несколькими способами. Объект cursor - это генератор, который может извлекать все результаты или зацикливаться.

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

Если вы хотите, чтобы цикл использовал непосредственно генератор:

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

Если вы хотите зафиксировать изменения в базе данных:

db.commit_db()

Если вы хотите закрыть курсор и соединение:

db.close_db()

SQLite

SQLite - это легкая база данных на основе дисков. Поскольку для него не требуется отдельный сервер базы данных, он часто используется для прототипирования или для небольших приложений, которые часто используются одним пользователем или одним пользователем в данный момент времени.

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

Приведенный выше код подключается к базе данных, хранящейся в файле с именем users.db , users.db создавая файл, если он еще не существует. Вы можете взаимодействовать с базой данных с помощью операторов SQL.

Результатом этого примера должно быть:

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

Синтаксис SQLite: углубленный анализ

Начиная

Импортируйте модуль sqlite, используя
```
>>> import sqlite3
```
Чтобы использовать модуль, вы должны сначала создать объект Connection, который представляет базу данных. Здесь данные будут сохранены в файле example.db:
```
>>> conn = sqlite3.connect('users.db')
```
Кроме того, вы также можете указать специальное имя :memory: создать временную базу данных в ОЗУ следующим образом:
```
>>> conn = sqlite3.connect(':memory:')
```

После того, как у вас есть Connection , вы можете создать объект Cursor и вызвать его метод execute() для выполнения команд 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()

Важные атрибуты и функции `Connection`

isolation_level

Это атрибут, используемый для получения или установки текущего уровня изоляции. Нет для режима автосохранения или один из DEFERRED , IMMEDIATE или EXCLUSIVE .

cursor

Объект cursor используется для выполнения SQL-команд и запросов.

commit()

Выполняет текущую транзакцию.

rollback()

Отбрасывает любые изменения, сделанные с предыдущего вызова commit()

close()

Закрывает соединение с базой данных. Он не вызывает commit() автоматически. Если close() вызывается без первого вызова commit() (при условии, что вы не находитесь в режиме autocommit), все сделанные изменения будут потеряны.

total_changes

Атрибут, который регистрирует общее количество строк, измененных, удаленных или вставленных с момента открытия базы данных.
execute , executemany и executescript
Эти функции выполняются так же, как и объекты курсора. Это ярлык, поскольку вызов этих функций через объект соединения приводит к созданию промежуточного объекта курсора и вызывает соответствующий метод объекта курсора

row_factory

Вы можете изменить этот атрибут на вызываемый, который принимает курсор и исходную строку как кортеж и вернет строку реального результата.
```
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
```

Важные функции `Cursor`

execute(sql[, parameters])

Выполняет один оператор SQL. Оператор SQL может быть параметризован (т. Е. Заполнители вместо SQL-литералов). Модуль sqlite3 поддерживает два типа заполнителей: вопросительные знаки ? («Стиль qmark») и названные заполнители :name («named style»).

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

Остерегайтесь: не используйте %s для вставки строк в команды SQL, так как это может сделать вашу программу уязвимой для атаки SQL-инъекции (см. SQL Injection ).

executemany(sql, seq_of_parameters)

Выполняет команду SQL для всех последовательностей параметров или сопоставлений, найденных в последовательности sql. Модуль sqlite3 также позволяет использовать итератор, приводящий параметры вместо последовательности.

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)

Вы также можете передавать объекты-итераторы в качестве параметра в executeemany, и функция будет перебирать каждый кортеж значений, возвращаемых итератором. Итератор должен возвращать кортеж значений.

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

executescript(sql_script)

Это нестандартный метод удобства для выполнения сразу нескольких операторов SQL. Сначала он выдает инструкцию COMMIT , затем выполняет SQL-скрипт, который он получает в качестве параметра.

sql_script может быть экземпляром str или 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
     );
     """)

Следующий набор функций используется вместе с SELECT в SQL. Чтобы получить данные после выполнения SELECT , вы можете обрабатывать курсор как итератор, вызывать метод fetchone() курсора для извлечения одной подходящей строки или вызвать fetchall() чтобы получить список соответствующих строк.

Пример формы итератора:

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)

fetchone()

Выбирает следующую строку набора результатов запроса, возвращает одну последовательность или None, когда больше нет данных.

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)

fetchmany(size=cursor.arraysize)

Выбирает следующий набор строк результата запроса (задается по размеру), возвращая список. Если размер опущен, fetchmany возвращает одну строку. Пустой список возвращается, когда больше строк не доступно.
```
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)]
```

fetchall()

Выбирает все (остальные) строки результата запроса, возвращая список.

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

Типы данных SQLite и Python

SQLite поддерживает следующие типы: NULL, INTEGER, REAL, TEXT, BLOB.

Так преобразуются типы данных при переходе с SQL на Python или наоборот.

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

Доступ к базе данных PostgreSQL с помощью psycopg2

psycopg2 - самый популярный адаптер базы данных PostgreSQL, который является легким и эффективным. Это текущая реализация адаптера PostgreSQL.

Его основными функциями являются полная реализация спецификации API Python DB API 2.0 и безопасности потоков (несколько потоков могут использовать одно и то же соединение)

Установление соединения с базой данных и создание таблицы

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

Вставка данных в таблицу:

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

Получение данных таблицы:

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

Результатом вышесказанного будет:

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

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

Итак, вот вы, вы теперь знаете половину всего, что вам нужно знать о psycopg2 ! :)

База данных Oracle

Предпосылки:

пакет cx_Oracle - см. здесь для всех версий
Мгновенный клиент Oracle - для Windows x64 , Linux x64

Настроить:

Установите пакет cx_Oracle как:
sudo rpm -i <YOUR_PACKAGE_FILENAME>
Извлеките мгновенный клиент Oracle и установите переменные среды следующим образом:

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

Создание соединения:

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

Получить версию базы данных:

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

Пример: ['12', '1', '0', '2', '0']

Выполнить запрос: SELECT

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

Выход будет в кортежах Python:

(10, «SYSADMIN», «IT-INFRA», 7)

(23, «HR ASSOCIATE», «ЛЮДСКИЕ РЕСУРСЫ», 6)

Выполнить запрос: INSERT

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

Когда вы выполняете операции вставки / обновления / удаления в базе данных Oracle, изменения доступны только в пределах вашего сеанса до тех пор, пока не будет выпущена commit . Когда обновленные данные привязаны к базе данных, они затем доступны для других пользователей и сеансов.

Выполнить запрос: INSERT с использованием переменных Bind

Ссылка

Переменные Bind позволяют повторно выполнять операторы с новыми значениями без накладных расходов на повторный анализ оператора. Переменные привязки улучшают перепрограммирование кода и могут снизить риск атаки 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()

Закрыть соединение:

_db_connection.close()

Метод close () закрывает соединение. Любые соединения, явно не закрытые, будут автоматически освобождены при завершении сценария.

соединение

Создание соединения

Согласно PEP 249, соединение с базой данных должно быть установлено с помощью конструктора connect() , который возвращает объект Connection . Аргументы для этого конструктора зависят от базы данных. Для соответствующих аргументов обратитесь к конкретным темам базы данных.

import MyDBAPI

con = MyDBAPI.connect(*database_dependent_args)

Этот объект соединения имеет четыре метода:

1: закрыть

con.close()

Немедленно закрывает соединение. Обратите внимание, что соединение автоматически закрывается, если Connection.__del___ метод Connection.__del___ . Любые незавершенные транзакции будут скрытно отклонены.

2: фиксация

con.commit()

Записывает любую ожидающую транзакцию в базу данных.

3: откат

con.rollback()

Возврат к началу любой ожидающей транзакции. Другими словами: это отменяет любую транзакцию без привязки к базе данных.

4: курсор

cur = con.cursor()

Возвращает объект Cursor . Это используется для транзакций в базе данных.

Использование sqlalchemy

Использовать sqlalchemy для базы данных:

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

Теперь этот движок можно использовать: например, с помощью pandas для извлечения данных из базы данных непосредственно из mysql

import pandas as pd

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

Modified text is an extract of the original Stack Overflow Documentation

Лицензировано согласно CC BY-SA 3.0

Не связан с Stack Overflow

Python Language
Доступ к базе данных

Поиск…

замечания

Доступ к базе данных MySQL с использованием MySQLdb

SQLite

Синтаксис SQLite: углубленный анализ

Начиная

Важные атрибуты и функции `Connection`

Важные функции `Cursor`

Типы данных SQLite и Python

Доступ к базе данных PostgreSQL с помощью psycopg2

Установление соединения с базой данных и создание таблицы

Вставка данных в таблицу:

Получение данных таблицы:

База данных Oracle

соединение

Использование sqlalchemy

замечания

Доступ к базе данных MySQL с использованием MySQLdb

SQLite

Синтаксис SQLite: углубленный анализ

Начиная

Важные атрибуты и функции Connection

Важные функции Cursor

Типы данных SQLite и Python

Доступ к базе данных PostgreSQL с помощью psycopg2

Установление соединения с базой данных и создание таблицы

Вставка данных в таблицу:

Получение данных таблицы:

База данных Oracle

соединение

Использование sqlalchemy

Важные атрибуты и функции `Connection`

Важные функции `Cursor`