Python sql: Как подружить Python и базы данных SQL. Подробное руководство

Python 3 — доступ к базе данных MySQL

Стандарт Python для интерфейсов базы данных — это DB-API Python. Большинство интерфейсов баз данных Python соответствуют этому стандарту.

Вы можете выбрать правильную базу данных для вашего приложения. Python Database API поддерживает широкий спектр серверов баз данных, таких как —

• овод
• Msql
• MySQL
• PostgreSQL
• Microsoft SQL Server 2000
• Informix
• Interbase
• оракул
• Sybase
• SQLite

Вот список доступных интерфейсов базы данных Python — интерфейсы базы данных Python и API . Вы должны загрузить отдельный модуль API БД для каждой базы данных, к которой вам нужен доступ. Например, если вам нужен доступ к базе данных Oracle, а также к базе данных MySQL, вы должны загрузить модули базы данных Oracle и MySQL.

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

• Импорт модуля API.
• Получение соединения с базой данных.
• Выдача операторов SQL и хранимых процедур.
• Закрытие соединения

Python имеет встроенную поддержку SQLite. В этом разделе мы изучим все концепции, используя MySQL. Модуль MySQLdb, популярный интерфейс с MySQL, не совместим с Python 3. Вместо этого мы будем использовать модуль PyMySQL .

Что такое PyMySQL?

PyMySQL — это интерфейс для подключения к серверу баз данных MySQL из Python. Он реализует API базы данных Python v2.0 и содержит чистую клиентскую библиотеку MySQL Python. Целью PyMySQL является быстрая замена MySQLdb.

Как установить PyMySQL?

Прежде чем продолжить, убедитесь, что на вашем компьютере установлен PyMySQL. Просто введите следующее в вашем скрипте Python и выполните его —

#!/usr/bin/python3

import pymysql

Если он дает следующий результат, то это означает, что модуль MySQLdb не установлен —

Traceback (most recent call last):
   File "test.py", line 3, in <module>
      Import pymysql
ImportError: No module named pymysql

Последний стабильный выпуск доступен на PyPI и может быть установлен с помощью pip —

pip install pymysql

В качестве альтернативы (например, если pip недоступен), tarball можно загрузить с GitHub и установить вместе с Setuptools следующим образом:

$ # X.X is the desired pymysql version (e.g. 0.5 or 0.6).
$ curl -L https://github.com/PyMySQL/PyMySQL/tarball/pymysql-X.X | tar xz
$ cd PyMySQL*
$ python setup.py install
$ # The folder PyMySQL* can be safely removed now.

Примечание. Убедитесь, что у вас есть права суперпользователя для установки вышеуказанного модуля.

Подключение к базе данных

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

• Вы создали базу данных TESTDB.

• Вы создали таблицу EMPLOYEE в TESTDB.

• В этой таблице есть поля FIRST_NAME, LAST_NAME, AGE, SEX и INCOME.

• Идентификатор пользователя «testuser» и пароль «test123» установлены для доступа к TESTDB.

• Модуль Python PyMySQL правильно установлен на вашем компьютере.

• Вы прошли учебник по MySQL, чтобы понять основы MySQL.

Вы создали базу данных TESTDB.

Вы создали таблицу EMPLOYEE в TESTDB.

В этой таблице есть поля FIRST_NAME, LAST_NAME, AGE, SEX и INCOME.

Идентификатор пользователя «testuser» и пароль «test123» установлены для доступа к TESTDB.

Модуль Python PyMySQL правильно установлен на вашем компьютере.

Вы прошли учебник по MySQL, чтобы понять основы MySQL.

пример

Ниже приведен пример соединения с базой данных MySQL «TESTDB» —

#!/usr/bin/python3

import pymysql

# Open database connection
db = pymysql.connect("localhost","testuser","test123","TESTDB" )

# prepare a cursor object using cursor() method
cursor = db.cursor()

# execute SQL query using execute() method.
cursor.execute("SELECT VERSION()")

# Fetch a single row using fetchone() method.
data = cursor.fetchone()
print ("Database version : %s " % data)

# disconnect from server
db.close()

При запуске этого скрипта он выдает следующий результат.

Database version : 5.5.20-log

Если соединение с источником данных установлено, то объект соединения возвращается и сохраняется в БД для дальнейшего использования, в противном случае для БД установлено значение Нет. Затем объект db используется для создания объекта курсора , который, в свою очередь, используется для выполнения запросов SQL. Наконец, перед выходом он гарантирует, что соединение с базой данных закрыто и ресурсы освобождены.

Создание таблицы базы данных

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

пример

Давайте создадим таблицу базы данных EMPLOYEE —

#!/usr/bin/python3

import pymysql

# Open database connection
db = pymysql.connect("localhost","testuser","test123","TESTDB" )

# prepare a cursor object using cursor() method
cursor = db.cursor()

# Drop table if it already exist using execute() method.
cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")

# Create table as per requirement
sql = """CREATE TABLE EMPLOYEE (
   FIRST_NAME  CHAR(20) NOT NULL,
   LAST_NAME  CHAR(20),
   AGE INT,  
   SEX CHAR(1),
   INCOME FLOAT )"""

cursor.execute(sql)

# disconnect from server
db.close()

ВСТАВИТЬ Операция

Операция INSERT требуется, когда вы хотите создать свои записи в таблице базы данных.

пример

В следующем примере выполняется инструкция SQL INSERT для создания записи в таблице EMPLOYEE —

#!/usr/bin/python3

import pymysql

# Open database connection
db = pymysql.connect("localhost","testuser","test123","TESTDB" )

# prepare a cursor object using cursor() method
cursor = db.cursor()

# Prepare SQL query to INSERT a record into the database.
sql = """INSERT INTO EMPLOYEE(FIRST_NAME,
   LAST_NAME, AGE, SEX, INCOME)
   VALUES ('Mac', 'Mohan', 20, 'M', 2000)"""
try:
   # Execute the SQL command
   cursor.execute(sql)
   # Commit your changes in the database
   db.commit()
except:
   # Rollback in case there is any error
   db.rollback()

# disconnect from server
db.close()

Приведенный выше пример может быть написан следующим образом для динамического создания запросов SQL:

#!/usr/bin/python3

import pymysql

# Open database connection
db = pymysql.connect("localhost","testuser","test123","TESTDB" )

# prepare a cursor object using cursor() method
cursor = db.cursor()

# Prepare SQL query to INSERT a record into the database.
sql = "INSERT INTO EMPLOYEE(FIRST_NAME, \
   LAST_NAME, AGE, SEX, INCOME) \
   VALUES ('%s', '%s', '%d', '%c', '%d' )" % \
   ('Mac', 'Mohan', 20, 'M', 2000)
try:
   # Execute the SQL command
   cursor.execute(sql)
   # Commit your changes in the database
   db.commit()
except:
   # Rollback in case there is any error
   db.rollback()

# disconnect from server
db.close()

пример

Следующий фрагмент кода является еще одной формой выполнения, где вы можете передавать параметры напрямую —

..................................
user_id = "test123"
password = "password"

con.execute('insert into Login values("%s", "%s")' % \
             (user_id, password))
..................................

ЧИТАЙТЕ Операцию

Чтение Операция с любой базой данных означает получение некоторой полезной информации из базы данных.

Как только соединение с базой данных установлено, вы готовы сделать запрос в эту базу данных. Вы можете использовать либо метод fetchone () для извлечения одной записи, либо метод fetchall () для извлечения нескольких значений из таблицы базы данных.

• fetchone () — извлекает следующую строку из набора результатов запроса. Результирующий набор — это объект, который возвращается, когда объект-курсор используется для запроса таблицы.

• fetchall () — извлекает все строки в наборе результатов. Если некоторые строки уже были извлечены из результирующего набора, то он извлекает оставшиеся строки из результирующего набора.

• rowcount — это атрибут только для чтения, который возвращает количество строк, на которые воздействовал метод execute ().

fetchone () — извлекает следующую строку из набора результатов запроса. Результирующий набор — это объект, который возвращается, когда объект-курсор используется для запроса таблицы.

fetchall () — извлекает все строки в наборе результатов. Если некоторые строки уже были извлечены из результирующего набора, то он извлекает оставшиеся строки из результирующего набора.

rowcount — это атрибут только для чтения, который возвращает количество строк, на которые воздействовал метод execute ().

пример

Следующая процедура запрашивает все записи из таблицы EMPLOYEE с зарплатой более 1000 —

#!/usr/bin/python3

import pymysql

# Open database connection
db = pymysql.connect("localhost","testuser","test123","TESTDB" )

# prepare a cursor object using cursor() method
cursor = db.cursor()

# Prepare SQL query to INSERT a record into the database.
sql = "SELECT * FROM EMPLOYEE \
      WHERE INCOME > '%d'" % (1000)
try:
   # Execute the SQL command
   cursor.execute(sql)
   # Fetch all the rows in a list of lists.
   results = cursor.fetchall()
   for row in results:
      fname = row[0]
      lname = row[1]
      age = row[2]
      sex = row[3]
      income = row[4]
      # Now print fetched result
      print ("fname = %s,lname = %s,age = %d,sex = %s,income = %d" % \
         (fname, lname, age, sex, income ))
except:
   print ("Error: unable to fetch data")

# disconnect from server
db.close()

Выход

Это даст следующий результат —

fname = Mac, lname = Mohan, age = 20, sex = M, income = 2000

Операция обновления

ОБНОВЛЕНИЕ Операция в любой базе данных означает обновление одной или нескольких записей, которые уже доступны в базе данных.

Следующая процедура обновляет все записи, имеющие SEX как «M» . Здесь мы увеличиваем ВОЗРАСТ всех мужчин на один год.

пример

#!/usr/bin/python3

import pymysql

# Open database connection
db = pymysql.connect("localhost","testuser","test123","TESTDB" )

# prepare a cursor object using cursor() method
cursor = db.cursor()

# Prepare SQL query to UPDATE required records
sql = "UPDATE EMPLOYEE SET AGE = AGE + 1
                          WHERE SEX = '%c'" % ('M')
try:
   # Execute the SQL command
   cursor.execute(sql)
   # Commit your changes in the database
   db.commit()
except:
   # Rollback in case there is any error
   db.rollback()

# disconnect from server
db.close()

УДАЛЕНИЕ Операция

Операция DELETE требуется, когда вы хотите удалить некоторые записи из вашей базы данных. Ниже описана процедура удаления всех записей из РАБОТНИКА, где ВОЗРАСТ больше 20 —

пример

#!/usr/bin/python3

import pymysql

# Open database connection
db = pymysql.connect("localhost","testuser","test123","TESTDB" )

# prepare a cursor object using cursor() method
cursor = db.cursor()

# Prepare SQL query to DELETE required records
sql = "DELETE FROM EMPLOYEE WHERE AGE > '%d'" % (20)
try:
   # Execute the SQL command
   cursor.execute(sql)
   # Commit your changes in the database
   db.commit()
except:
   # Rollback in case there is any error
   db.rollback()

# disconnect from server
db.close()

Выполнение транзакций

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

• Атомарность — либо транзакция завершена, либо ничего не происходит вообще.

• Согласованность — транзакция должна начинаться в согласованном состоянии и оставлять систему в согласованном состоянии.

• Изоляция — промежуточные результаты транзакции не видны за пределами текущей транзакции.

• Долговечность — после совершения транзакции последствия сохраняются даже после сбоя системы.

Атомарность — либо транзакция завершена, либо ничего не происходит вообще.

Согласованность — транзакция должна начинаться в согласованном состоянии и оставлять систему в согласованном состоянии.

Изоляция — промежуточные результаты транзакции не видны за пределами текущей транзакции.

Долговечность — после совершения транзакции последствия сохраняются даже после сбоя системы.

Python DB API 2.0 предоставляет два метода для фиксации или отката транзакции.

пример

Вы уже знаете, как осуществлять транзакции. Вот похожий пример —

# Prepare SQL query to DELETE required records
sql = "DELETE FROM EMPLOYEE WHERE AGE > '%d'" % (20)
try:
   # Execute the SQL command
   cursor.execute(sql)
   # Commit your changes in the database
   db.commit()
except:
   # Rollback in case there is any error
   db.rollback()

Операция COMMIT

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

Вот простой пример вызова метода commit .

db.commit()

ROLLBACK Операция

Если вас не устраивает одно или несколько изменений, и вы хотите полностью отменить эти изменения, используйте метод rollback () .

Вот простой пример вызова метода rollback () .

db.rollback()

Отключение базы данных

Чтобы отключить соединение с базой данных, используйте метод close ().

db.close()

Если соединение с базой данных закрывается пользователем методом close (), все незавершенные транзакции откатываются БД. Однако вместо того, чтобы зависеть от какой-либо детали реализации нижнего уровня БД, вашему приложению было бы лучше явно не вызывать commit или rollback.

Обработка ошибок

Есть много источников ошибок. Вот несколько примеров: синтаксическая ошибка в выполненном операторе SQL, сбой соединения или вызов метода выборки для уже отмененного или законченного дескриптора оператора.

DB API определяет количество ошибок, которые должны существовать в каждом модуле базы данных. В следующей таблице перечислены эти исключения.

Предупреждение

Используется для нефатальных проблем. Должен подкласс StandardError.

ошибка

Базовый класс для ошибок. Должен подкласс StandardError.

InterfaceError

Используется для ошибок в модуле базы данных, а не в самой базе данных. Ошибка подкласса.

Ошибка базы данных

Используется для ошибок в базе данных. Ошибка подкласса.

DataError

Подкласс DatabaseError, который ссылается на ошибки в данных.

OperationalError

Подкласс DatabaseError, который ссылается на ошибки, такие как потеря соединения с базой данных. Эти ошибки обычно находятся вне контроля скрипта Python.

IntegrityError

Подкласс DatabaseError для ситуаций, которые могут повредить целостности отношений, таких как ограничения уникальности или внешние ключи.

Внутренняя ошибка

Подкласс DatabaseError, который ссылается на ошибки, внутренние для модуля базы данных, такие как курсор, который больше не активен.

ProgrammingError

Подкласс DatabaseError, который ссылается на ошибки, такие как неверное имя таблицы и другие вещи, которые можно смело обвинять в вас.

NotSupportedError

Подкласс DatabaseError, который относится к попытке вызвать неподдерживаемую функциональность.

Ваши скрипты Python должны обрабатывать эти ошибки, но перед использованием любого из вышеперечисленных исключений убедитесь, что ваш MySQLdb поддерживает это исключение. Вы можете получить больше информации о них, прочитав спецификацию DB API 2.0.

Простой подход к шаблонным SQL-запросам в Python

Существует множество ситуаций, когда нужно вставить параметры в запрос SQL, и существует множество способов реализации шаблонных запросов SQL в python. Не вдаваясь в сравнение различных подходов, этот пост объясняет простой и эффективный метод параметризации SQL с использованием JinjaSql. Помимо множества мощных функций Jinja2, таких как условные операторы и циклы, JinjaSql предлагает чистый и простой способ параметризации не только значений подставляемых в where и in, но и многое другое, подробнее ниже в статье.

Подстановка основных параметров

Предположим, у нас есть таблица transactions с записями о финансовых транзакциях. Столбцы в этой таблице могут быть такие transaction_id, user_id, transaction_date и amount. Чтобы вычислить количество транзакций и общую сумму для данного пользователя в заданный день, запрос непосредственно в базу данных может выглядеть примерно так

select
   user_id
   , count(*) as num_transactions
   , sum(amount) as total_amount
from
   transactions
where
   user_id = 1234
   and transaction_date = '2019-03-02'

Здесь мы предполагаем, что база данных автоматически преобразует формат строкового представления даты YYYY-MM-DD в правильный тип даты.

Если мы хотим выполнить запрос выше для любого пользователя и даты, мы должны параметризовать user_id и значение transaction_date. В JinjaSql соответствующий шаблон будет выглядеть так:

select
   user_id
   , count(*) as num_transactions
   , sum(amount) as total_amount
from
   transactions
where
   user_id = {{ uid }}
   and transaction_date = {{ tdate }}

Здесь значения были заменены заполнителями с именами переменных Python, заключенными в двойные фигурные скобки {{ }}. Обратите внимание , что имена переменных uid и tdate были выбраны только чтобы показать, что они являются именами переменных и не имеют ничего общего с самими названиями столбцов. Более читаемая версия того же шаблона хранится в переменной Python

user_transaction_template = '''
select
   user_id
   , count(*) as num_transactions
   , sum(amount) as total_amount
from
   transactions
where
   user_id = {{ user_id }}
   and transaction_date = {{ transaction_date }}
'''

Далее нам нужно установить параметры для запроса.

params = {
   'user_id': 1234,
   'transaction_date': '2019-03-02',
}

Теперь сгенерировать SQL-запрос из этого шаблона следующим образом.

from jinjasql import JinjaSql
j = JinjaSql(param_style='pyformat')
query, bind_params = j.prepare_query(user_transaction_template, params)

Если мы напечатаем query и bind_params, мы обнаружим, что первая является параметризованной строкой, а вторая является одним обьектом OrderedDict:

>>> print(query)
select
   user_id
   , count(*) as num_transactions
   , sum(amount) as total_amount
from
   transactions
where
   user_id = %(user_id)s
   and transaction_date = %(transaction_date)s
>>> print(bind_params)
OrderedDict([('user_id', 1234), ('transaction_date', '2018-03-01')])

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

Многие соединения с базой данных имеют возможность передавать в качестве аргумента bind_params метод, выполняющий SQL-запрос для установки соединения. Для исследователя данных может быть естественным получить результаты запроса во фрейме данных Pandas. Когда у нас есть соединение conn, это так же просто, как запустить read_sql:

import pandas as pd
frm = pd.read_sql(query, conn, params=bind_params)

Посмотрите документы JinjaSql для других примеров.

От шаблона к окончательному запросу SQL

Часто желательно полностью развернуть запрос со всеми параметрами перед его выполнением. Например, регистрация полного запроса неоценима для отладки пакетных процессов, поскольку можно скопировать и вставить запрос из журналов непосредственно в интерактивный интерфейс SQL. Заманчиво подставить в bind_params строку query. Однако мы быстро обнаруживаем, что строковые параметры должны быть заключены в кавычки, чтобы получить правильный SQL. Например, в приведенном выше шаблоне значение даты должно быть заключено в одинарные кавычки.

>>> print(query % bind_params)

select
   user_id
   , count(*) as num_transactions
   , sum(amount) as total_amount
from
   transactions
where
   user_id = 1234
   and transaction_date = 2018-03-01

Чтобы справиться с этим, нам нужна вспомогательная функция для правильного цитирования параметров, которые являются строками. Мы определяем, является ли параметр строкой, вызывая

from six import string_types
isinstance(value, string_types)

Это работает как для Python 3, так и для 2.7. Параметры строки преобразуются в тип str, одинарные кавычки в именах экранируются другой одинарной кавычкой, и, наконец, все значение заключается в одинарные кавычки.

from six import string_types

def quote_sql_string(value):
   '''
   Если `value` является строковым типом, экранирует одинарные кавычки в
   строке и возвращает строку, заключенную в одинарные кавычки
   '''
   if isinstance(value, string_types):
       new_value = str(value)
       new_value = new_value.replace("'", "''")
       return "'{}'".format(new_value)
   return value

Наконец, чтобы преобразовать шаблон в правильный SQL, мы в цикле перебрали bind_params, заключили в кавычки строки, а затем выполнили подстановку строк.

from copy import deepcopy

def get_sql_from_template(query, bind_params):
   if not bind_params:
       return query
   params = deepcopy(bind_params)
   for key, val in params.items():
       params[key] = quote_sql_string(val)
   return query % params

Теперь мы можем легко получить окончательный запрос, который мы можем отправить или запустить в интерактивном режиме:

>>> print(get_sql_from_template(query, bind_params))

select
   user_id
   , count(*) as num_transactions
   , sum(amount) as total_amount
from
   transactions
where
   user_id = 1234
   and transaction_date = '2018-03-01'

Собрав все это вместе, другая вспомогательная функция оборачивает вызовы JinjaSql и просто берет template и объект parameters и возвращает полный SQL.

from jinjasql import JinjaSql

def apply_sql_template(template, parameters):
   '''
   Apply a JinjaSql template (string) substituting parameters (dict) and return
   the final SQL.
   '''
   j = JinjaSql(param_style='pyformat')
   query, bind_params = j.prepare_query(template, parameters)
   return get_sql_from_template(query, bind_params)

Вычислить статистику по столбцу

Вычисление статистики по значениям, хранящимся в конкретном столбце базы данных, удобно как при первом исследовании данных, так и для проверки данных в продакшене. Поскольку для простоты мы хотим продемонстрировать только некоторые особенности шаблонов, давайте просто поработаем со столбцами integer, такими как столбец user_id в таблице transactions. Для целочисленных столбцов нас интересует количество уникальных значений, минимальных и максимальных значений и количество нулей. Некоторые столбцы могут иметь, скажем, значение по умолчанию, -1 недостатки которого выходят за рамки этой публикации, однако мы хотим зафиксировать это, сообщив количество значений по умолчанию.

Рассмотрим следующий шаблон и функцию. Функция принимает имя таблицы, имя столбца и значение по умолчанию в качестве аргументов и возвращает SQL для вычисления статистики.

COLUMN_STATS_TEMPLATE = '''
select
   {{ column_name | sqlsafe }} as column_name
   , count(*) as num_rows
   , count(distinct {{ column_name | sqlsafe }}) as num_unique
   , sum(case when {{ column_name | sqlsafe }} is null then 1 else 0 end) as num_nulls
   {% if default_value %}
   , sum(case when {{ column_name | sqlsafe }} = {{ default_value }} then 1 else 0 end) as num_default
   {% else %}
   , 0 as num_default
   {% endif %}
   , min({{ column_name | sqlsafe }}) as min_value
   , max({{ column_name | sqlsafe }}) as max_value
from
   {{ table_name | sqlsafe }}
'''


def get_column_stats_sql(table_name, column_name, default_value):
   '''
   Возвращает SQL для вычисления статистики столбца. Передача None для default_value 
   приводит к нулевому выводу числа значений по умолчанию
   '''
   # Note that a string default needs to be quoted first.
   params = {
       'table_name': table_name,
       'column_name': column_name,
       'default_value': quote_sql_string(default_value),
   }
   return apply_sql_template(COLUMN_STATS_TEMPLATE, params)

Эта функция проста и очень мощна, потому что она применяется к любому столбцу в любой таблице. Обратите внимание на синтаксис {% if default_value %} в шаблоне. Если значение по умолчанию, которое передается в функцию None, SQL возвращает ноль в поле num_default.

Приведенная выше функция и шаблон также будут работать со строками, датами и другими типами данных, если для параметра default_value установлено значение None. Однако для более разумной обработки различных типов данных необходимо расширить функцию, чтобы также принимать тип данных в качестве аргумента и строить логику, характерную для различных типов данных. Например, может потребоваться узнать минимальное и максимальное значения длины строки вместо минимального и максимального значения самого значения.

Давайте посмотрим на вывод для столбца transactions.user_id.

>>> print(get_column_stats_sql('transactions', 'user_id', None))

select
   user_id as column_name
   , count(*) as num_rows
   , count(distinct user_id) as num_unique
   , sum(case when user_id is null then 1 else 0 end) as num_nulls

   , 0 as num_default

   , min(user_id) as min_value
   , max(user_id) as max_value
from
   transactions

Обратите внимание, что вместо {% %} появляются пустые строки, которые можно удалить.

Резюме

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

Одним из важных предостережений является риск внедрения кода. Для пакетных процессов это не должно быть проблемой, но использование конструкции sqlsafe в веб-приложениях может быть опасным. Ключевое слово sqlsafe указывает на то, что пользователь (вы) уверен, что никакой инъекции кода невозможно, и берет на себя ответственность за это.

С другой стороны, возможность помещать произвольную строку в запрос позволяет передавать целые блоки кода в шаблон. Например, вместо передачи table_name='transactions', можно передавать '(select * from transactions where transaction_date = 2018-03-01) t', и запрос все равно будет работать.

Как изучить SQLite для новичков, зная только Python

Базы данных являются неотъемлемой частью Data Science, поскольку часто приходится работать с хранящимися данными: получать, добавлять, удалять, обновлять, а также анализировать. Сегодня мы расскажем о встроенной в Python СУБД – SQLite: узнаем, как подключаться к базам данных, как выполнять запросы и как обезопасить их от злоумышленников.

CУБД SQlite

SQLite – это легковесная, быстрая, надежная и встраиваемая система управления базами данных (СУБД). В отличие от клиент-серверных СУБД, таких как MySQL, PostgresSQL, Oracle и др., SQLite ориентирована на экономию, надежность и простоту. Используется эта СУБД в следующих случаях:

• Встраиваемые устройства и Интернет вещей (Internet of things). Поскольку она не требует администрирования и специализированной поддержки, SQLite может быть встроена в бытовые приборы, автомобили, самолеты, датчики, роботов.
• Standalone-приложения, которые используются на персональных компьютерах, например, система контроля версия, финансовые инструменты, хранение заметок и т.д.
• Веб-сайты с низким или средним трафиком.
• Анализ данных (data analysis) для хранения датасетов.
• Кэширование данных из корпоративной СУБД.

А вот не рекомендуется использовать SQLite там, где есть:

• Большие данные (Big data),
• Высокий трафик со множеством серверов,
• Множество распределенных потоков,
• Данные отделены от приложения, то есть не находятся на том же физическом устройстве, где установлено само приложение.

В Python по умолчанию встроена SQLite в качестве библиотеки sqlite3, с которой мы сейчас ознакомимся.

Подключение локальное и через оперативную память

Для работы с SQLite не нужно ничего устанавливать, достаточно импортировать Python-библиотеку:

import sqlite3

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

Чтобы установить соединение с базой данных на компьютере, нужно указать путь к не. Если Python не найдет базу, то создаст ее сам. Чтобы создать базу в оперативной памяти, следует указать :memory:. Ниже показаны примеры.

# con = sqlite3.connect(":memory:") # в оперативной памяти
con = sqlite3.connect("mydb.db")

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

Совершение транзакций и закрытие соединения

После каждого выполнения запроса, требуется совершить транзакцию. В Python это реализуется через метод commit:

con.execute("ВАШ ЗАПРОС")
con.commit()

Можно избежать постоянного использования commit с помощью контекстного менеджера with:

with con:
    con.execute("ВАШ ЗАПРОС")

Выполнив все необходимые запросы, соединение следует закрыть напрямую:

con.close()

Cursor для совершения запросов и получения данных

Для совершения запросов можно использовать инициализированное соединение, как это написано выше через метод con.execute, либо с помощью объекта Cursor:

cur = con.cursor()
сur.execute("ВАШ ЗАПРОС")

Преимуществом использования объекта Cursor является возможность получить данные без включения дополнительного кода на Python.

Безопасные запросы

В качестве значений при добавлении данных используется знак ? или :value. Не следует писать запрос с использованием строковых операций (format или f-stirng), так это небезопасно и может быть подвергнута атакам c внедрением SQL-кода (SQL-injection). Ниже показаны примеры безопасного и небезопасного запроса на Python.

# НЕ делайте этого:
name = 'Vova'
cur.execute("SELECT * FROM user WHERE name = '%s'" % name)

# Так лучше:
name = ('Vova',)
cur.execute("SELECT * FROM user WHERE name=?", name)

# Так тоже хорошо:
name = {'n': 'Vova'}
cur.execute("SELECT * FROM user WHERE name=:n", name)

Обратите внимание, что знак ? требует в качестве значения кортеж (tuple), а :value – словарь (dict).

В следующей статье мы рассмотрим на конкретном примере создание таблиц БД и выполнение запросов к ним. А получить необходимый минимум для работы с базами данных в реальных проектах Data Science, вы можете на наших курсах по Python в лицензированном учебном центре обучения и повышения квалификации IT-специалистов в Москве.

Источники

1. https://docs.python.org/3/library/sqlite3.html

Работа с MySQL в Python

30 Март 2019, Python,
48716 просмотров

Ранее я уже писал статью про работу с PostgreSQL из Python. Сегодняшний пост будет посвящен другой популярной базе данных MySQL. Мой путь в веб-программирование был классическим: PHP, MySQL и Apache. Среди php-разработчиков MySQL пользуется большей популярностью чем PostgreSQL, хотя последняя предоставляет функционал намного богаче. MySQL до сих пор остаётся лидером среди реляционных open source баз данных, поэтому давайте узнаем как с ней работать через Python.

Установка

В статье речь пойдёт про пакет PyMySQL, это реализация mysql-клиента на чистом Python, поэтому никакие дополнительный Си-библиотеки ставить не придётся. Пакет поддерживает работу с Python 2.7 и Python >= 3.5.

Для установки библиотеки выполняем стандартную команду:

pip install PyMySQL

Начало работы

Соединиться с базой можно вот так:

import pymysql
from pymysql.cursors import DictCursor
connection = pymysql.connect(
    host='localhost',
    user='user',
    password='password',
    db='iata',
    charset='utf8mb4',
    cursorclass=DictCursor
)
...
connection.close()

Обратите внимание, что я импортировал класс DictCursor и передал его в вызов функции connect, это нужно для того, чтобы получить результат в виде словаря, где ключами будут названия колонок. Если вам удобно работать с tuple, то ничего не передавайте в cursorclass

Рекомендуется всегда явно закрывать открытое с базой MySQL соединение, путём вызова метода close у объекта connection. Чаще всего разработчики прибегают к конструкции try/finally, но я использую closing из contextlib. Вот как это выглядит:

from contextlib import closing
import pymysql
from pymysql.cursors import DictCursor
with closing(pymysql.connect(...)) as connection:
    ...

В этом случае при выходе из контекстного блока автоматически будет вызван метод close. Чтобы начать взаимодействие с MySQL, необходимо создать курсор:

with closing(pymysql.connect(...)) as connection:
    with connection.cursor() as cursor:
        query = """
        SELECT
            airport_code
        FROM
            airports
        ORDER BY 
            airport_code DESC
        LIMIT 5
        """
        cursor.execute(query)
        for row in cursor:
            print(row)
{'airport_code': 'ZZW'}
{'airport_code': 'ZZU'}
{'airport_code': 'ZZO'}
{'airport_code': 'ZZG'}
{'airport_code': 'ZYR'}

Пишем в базу

А вот как выглядит INSERT в простенькую таблицу с одной колонкой. Описание схемы таблицы следующее:

CREATE TABLE IF NOT EXISTS tweets (
    tweet VARCHAR(254)
)  ENGINE=INNODB;

А вот код вставки и чтения результата операции

with closing(pymysql.connect(...)) as conn:
    with conn.cursor() as cursor:
        tweets = [
            'Hello world!',
            'I love Python & MySQL',
            'Let\'s start programming ASAP',
            'Python is the coolest programming language'
        ]
        query = 'INSERT INTO tweets (tweet) VALUES (%s)'
        cursor.executemany(query, tweets)
        # необходимо, т.к. по-умолчанию commit происходит только после выхода
        # из контекстного менеджера иначе мы бы не увидели твиттов
        conn.commit()
    with conn.cursor() as cursor:
        query = 'SELECT tweet FROM tweets'
        cursor.execute(query)
        for row in cursor:
            print(row['tweet'])

В выполнения кода получим следующий результат

Hello world!
I love Python & MySQL
Let's start programming ASAP
Python is the coolest programming language

чтение внешнего sql скрипта в python

Переполнение стека

1. Около

2. Продукты

3. Для команд

1. Переполнение стека
   Общественные вопросы и ответы

2. Переполнение стека для команд
   Где разработчики и технологи делятся частными знаниями с коллегами

3. Вакансии
   Программирование и связанные с ним технические возможности карьерного роста

4. Талант
   Нанимайте технических специалистов и создавайте свой бренд работодателя

5. Реклама
   Обратитесь к разработчикам и технологам со всего мира

6. О компании

Загрузка…

2. Авторизоваться
   зарегистрироваться

3. текущее сообщество
   

.

11,13. sqlite3 — интерфейс DB-API 2.0 для баз данных SQLite — документация Python 2.7.18

SQLite — это библиотека C, которая предоставляет легкую дисковую базу данных, которая
не требует отдельного серверного процесса и позволяет получить доступ к базе данных
с использованием нестандартного варианта языка запросов SQL. Некоторые приложения могут использовать
SQLite для внутреннего хранения данных. Также возможно создать прототип
приложение с использованием SQLite, а затем перенесите код в большую базу данных, такую ​​как
PostgreSQL или Oracle.

Модуль sqlite3 был написан Герхардом Херингом. Он предоставляет интерфейс SQL
соответствует спецификации DB-API 2.0, описанной в PEP 249 .

Для использования модуля необходимо сначала создать объект Connection , который
представляет базу данных. Здесь данные будут храниться в
example.db файл:

 импорт sqlite3
conn = sqlite3.connect ('example.db')
 

Вы также можете указать специальное имя : memory: для создания базы данных в RAM.

Если у вас есть Connection , вы можете создать объект Cursor
и вызовите его метод execute () для выполнения команд SQL:

 c = conn.cursor ()

# Создать таблицу
c.execute ('' 'СОЗДАТЬ ТАБЛИЦУ акции
             (текст даты, транс-текст, текст символа, реальное кол-во, реальная цена) '' ')

# Вставляем строку данных
c.execute ("ВСТАВИТЬ ЗНАЧЕНИЯ акций ('2006-01-05', 'КУПИТЬ', 'RHAT', 100,35,14)")

# Сохранить (зафиксировать) изменения
conn.commit ()

# Мы также можем закрыть соединение, если мы закончили с ним.# Просто убедитесь, что все изменения были зафиксированы, иначе они будут потеряны.
conn.close ()
 

Сохраненные данные являются постоянными и доступны в последующих сеансах:

 импорт sqlite3
conn = sqlite3.connect ('example.db')
c = conn.cursor ()
 

Обычно ваши SQL-операции должны использовать значения из переменных Python. Вы
не следует собирать ваш запрос с использованием строковых операций Python, потому что при этом
небезопасен; это делает вашу программу уязвимой для атаки SQL-инъекции
(см. https: // xkcd.com / 327 / для юмористического примера того, что может пойти не так).

Вместо этого используйте подстановку параметров DB-API. Ставить ? как заполнитель
везде, где вы хотите использовать значение, а затем укажите кортеж значений в качестве
второй аргумент метода курсора execute () . (Другая база данных
модули могут использовать другой заполнитель, например % s или : 1 .)
пример:

 # Никогда не делайте этого - небезопасно!
символ = 'RHAT'
c.execute ("ВЫБРАТЬ * ИЗ акций WHERE symbol = '% s'"% symbol)

# Сделайте это вместо
t = ('RHAT',)
c.execute ('ВЫБРАТЬ * ИЗ акций ГДЕ символ =?', t)
напечатать c.fetchone ()

# Более крупный пример, который вставляет много записей за раз
Purchases = [('2006-03-28', 'КУПИТЬ', 'IBM', 1000, 45.00),
             ('2006-04-05', 'ПОКУПАТЬ', 'MSFT', 1000, 72,00),
             ('2006-04-06', 'ПРОДАВАТЬ', 'IBM', 500, 53.00),
            ]
c.executemany ('INSERT INTO Stocks VALUES (?,?,?,?,?)', покупки)
 

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

В этом примере используется форма итератора:

 >>> для строки в c.execute ('ВЫБРАТЬ * ИЗ запасов ЗАКАЗАТЬ ПО цене'):
        строка печати

(u'2006-01-05 ', u'BUY', u'RHAT ', 100, 35,14)
(u'2006-03-28 ', u'BUY', u'IBM ', 1000, 45.0)
(u'2006-04-06 ', u'SELL', u 

.