Sqlalchemy python примеры mysql

Python sqlalchemy to manage MySQL database

We will use mysqlclient ( recomended for Python 3 ) here to manage mysql database. Read here how to install and connect to MySQL database using sqlalchemy.

Here is the connection string again , after successful connection we will use the variable my_conn in our examples below.

from sqlalchemy import create_engine my_conn = create_engine("mysql+mysqldb://userid:pw@localhost/my_db")

usrid : Your MySql login user id
pw : Your MySql login password
localhost : Address of MySQL server, IP address or localhost
my_db : Database name of MySQL where our table is available.

Once above code works without any error ( use your own userid , password and database name ) we will go for collecting records.
In our MySQL database there is a student table with sample data. You can use the link at the end of this tutorial to get SQL dump to create your own table.

Based on the data you are handling you can add the type of charset to your connection.

engine = create_engine("mysql+mysqldb://userid:pw@localhost/db?charset=utf8mb4")

List of column names of the table in query

query="SELECT * FROM student LIMIT 0,10" my_data=my_conn.execute(query) print(my_data.keys())

fetchmany() : Returning 5 rows of data

query="SELECT * FROM student LIMIT 0,5" my_data=my_conn.execute(query) # SQLAlchemy my_conn result #my_data=my_data.fetchmany(size=2) # collect 2 rows of data for row in my_data: print(row) 

my_data=my_conn.execute(query) # SQLAlchemy my_conn result my_list = [r for r, in my_data] # create a list 

What happens if our SQL query ask for 10 records by using LIMIT and we try to print 15 records by using fetchmany() ?

fetchone()

query="SELECT * FROM student LIMIT 0,5" my_data=my_conn.execute(query) my_row=my_data.fetchone() #my_data=my_data.fetchmany(size=2) print(my_row[0] , my_row[1]) # 1 John Deo #my_data.next() my_row=my_data.fetchone() print(my_row[0],my_row[1]) # 2 Max Ruin

fetchall()

from sqlalchemy.exc import SQLAlchemyError q="SELECT * FROM student LIMIT 0,10" try: my_cursor=my_conn.execute(q) my_data=my_cursor.fetchall() for row in my_data: print(row) except SQLAlchemyError as e: error=str(e.__dict__['orig']) print(error) except Exception as e: print(e) else: print("Total Number of rows : ",my_cursor.rowcount)

first()

from sqlalchemy.exc import SQLAlchemyError q="SELECT * FROM student LIMIT 0,10" try: my_cursor=my_conn.execute(q) my_data=my_cursor.first() print(type(my_data)) print(my_data['name']) for row in my_data: print(row) except SQLAlchemyError as e: error=str(e.__dict__['orig']) print(error) except Exception as e: print(e) else: print("Total Number of rows : ",my_cursor.rowcount)

 my_data=my_cursor.first() print(my_data['name']) my_data=my_cursor.fetchone()

This result object is closed.

WHERE Condition

MySQL SELECT Query in Python with WHERE to get matching records by user input & parameterized query

from sqlalchemy import create_engine my_conn = create_engine("mysql+mysqldb://userid:password@localhost/my_database") rs=my_conn.execute("SELECT * FROM student WHERE ") my_data= rs.fetchall() # a list print(my_data)

[(2, 'Max Ruin', 'Three', 85, 'male'), (3, 'Arnold', 'Three', 55, 'male'), (27, 'Big Nose', 'Three', 81, 'female')]

We used here SQL WHERE to collect records of , similarly we can use different SQL queries to get data from MySQL table.

Using parameters in queries to get data from table

sql rs=my_conn.execute(sql, 'Four',20) print("Rows collected INSERT INTO student (`id` ,`name` ,`class` ,`mark` ,`sex`) \ VALUES ('36', 'King', 'Five', '45', 'male')") print("Rows Added UPDATE student SET Where ") print("Rows updated mysql+mysqldb://root:test@localhost/my_tutorial") sql rs=my_conn.execute(sql, 'Four',20) print("Rows Deleted INSERT INTO student (`name` ,`class` ,`mark` ,`sex`) \ VALUES ( 'King2', 'Five', '45', 'male')") print("Last insert Rows Added INSERT INTO `my_tutorial`.`student` (`name` ,`class` ,`mark` ,`sex`) \ VALUES(%s,%s,%s,%s)" my_data=('King','Five',45,'male') rs=my_conn.execute(query,my_data) print("Last insert Rows Added mysql+mysqldb://userid:pw@localhost/my_db") try: my_conn.connect() print("Successfully connected") except SQLAlchemyError as e: error=str(e.__dict__['orig']) print(error)

from sqlalchemy.exc import SQLAlchemyError q="SELECT * FROM student LIMIT 0,10" try: rs=my_conn.execute(q) except SQLAlchemyError as e: error=str(e.__dict__['orig']) print(error) else: print("Total Number of rows : ",rs.rowcount)

Exercise
In above code you have seen how to use SQLAlchemy library and manage MySQL database.
Here is a list of Queries you can execute using the above learning and display the outcome.

Источник

Установка SQLAlchemy и подключение к базе данных

SQLAlchemy можно использовать с Python 2.7 и более поздними версиями. В этом руководстве будет использоваться Python 3.8, но вы можете выбрать любую версию Python 3.6+.

Установка SQLAlchemy

Для установки SQLAlchemy введите следующее:

Чтобы проверить успешность установки введите следующее в командной строке:

 
 
>>> import sqlalchemy >>> sqlalchemy.__version__ '1.4.8'
 
Установка DBAPI
 
По умолчанию SQLAlchemy работает только с базой данных SQLite без дополнительных драйверов. Для работы с другими базами данных необходимо установить DBAPI-совместимый драйвер в соответствии с базой данных.
 
Что такое DBAPI?
DBAPI — это стандарт, который поощряет один и тот же API для работы с большим количеством баз данных. В следующей таблице перечислены все DBAPI-совместимые драйверы:
 
 
 
База данных
 
DBAPI драйвер
 
  
 
 
MySQL
 
PyMySQL, MySQL-Connector, CyMySQL, MySQL-Python (по умолчанию)
 
 
 
PostgreSQL
 
psycopg2 (по умолчанию), pg8000,
 
 
 
Microsoft SQL Server
 
PyODBC (по умолчанию), pymssql
 
 
 
Oracle
 
cx-Oracle (по умолчанию)
 
 
 
Firebird
 
fdb (по умолчанию), kinterbasdb
 
 
 
 
Все примеры в этом руководстве протестированы в PostgreSQL, но вы можете выбрать базу данных по вкусу. Для установки DBAPI psycopg2 для PostgreSQL введите следующую команду:
 
Подготовка к подключению
 
Первый шаг для подключения к базе данных — создания объекта Engine . Именно он отвечает за взаимодействие с базой данных. Состоит из двух элементов: диалекта и пула соединений.
 
Диалект SQLAlchemy
 
SQL — это стандартный язык для работы с базами данных. Однако и он отличается от базы к базе. Производители баз данных редко придерживаются одной и той же версии и предпочитают добавлять свои особенности. Например, если вы используете Firebird , то для получения id и name для первых 5 строк из таблицы employees нужна следующая команда:
 
select first 10 id, name from employees
 
А вот как получить тот же результат для MySQL:
 
select id, name from employees limit 10 
 
Чтобы обрабатывать эти различия нужен диалект. Диалект определяет поведение базы данных. Другими словами он отвечает за обработку SQL-инструкций, выполнение, обработку результатов и так далее. После установки соответствующего драйвера диалект обрабатывает все отличия, что позволяет сосредоточиться на создании самого приложения.
 
Пул соединений SQLAlchemy
 
Пул соединений — это стандартный способ кэширования соединений в памяти, что позволяет использовать их повторно. Создавать соединение каждый раз при необходимости связаться с базой данных — затратно. А пул соединений обеспечивает неплохой прирост производительности.
 
При таком подходе приложение при необходимости обратиться к базе данных вытягивает соединение из пула. После выполнения запросов подключение освобождается и возвращается в пул. Новое создается только в том случае, если все остальные связаны.
 
Для создания движка (объекта Engine ) используется функция create_engine() из пакета sqlalchemy . В базовом виде она принимает только строку подключения. Последняя включает информацию об источнике данных. Обычно это приблизительно следующий формат:
 
dialect+driver://username:password@host:port/database
 
 
dialect — это имя базы данных (mysql, postgresql, mssql, oracle и так далее).
 
driver — используемый DBAPI. Этот параметр является необязательным. Если его не указать будет использоваться драйвер по умолчанию (если он установлен).
 
username и password — данные для получения доступа к базе данных.
 
host — расположение сервера базы данных.
 
port — порт для подключения.
 
database — название базы данных.
 
 
Вот код для создания движка некоторых популярных баз данных:
 
 
 
from sqlalchemy import create_engine # Подключение к серверу MySQL на localhost с помощью PyMySQL DBAPI. engine = create_engine("mysql+pymysql://root:pass@localhost/mydb") # Подключение к серверу MySQL по ip 23.92.23.113 с использованием mysql-python DBAPI. engine = create_engine("mysql+mysqldb://root:pass@23.92.23.113/mydb") # Подключение к серверу PostgreSQL на localhost с помощью psycopg2 DBAPI engine = create_engine("postgresql+psycopg2://root:pass@localhost/mydb") # Подключение к серверу Oracle на локальном хосте с помощью cx-Oracle DBAPI. engine = create_engine("oracle+cx_oracle://root:pass@localhost/mydb")) # Подключение к MSSQL серверу на localhost с помощью PyODBC DBAPI. engine = create_engine("oracle+pyodbc://root:pass@localhost/mydb")
 
Формат строки подключения для базы данных SQLite немного отличается. Поскольку это файловая база данных, для нее не нужны имя пользователя, пароль, порт и хост. Вот как создать движок для базы данных SQLite:
 
 
 
from sqlalchemy import create_engine engine = create_engine('sqlite:///sqlite3.db') # используя относительный путь engine = create_engine('sqlite:////path/to/sqlite3.db') # абсолютный путь
 
Подключение к базе данных
 
Но создание движка — это еще не подключение к базе данных. Для получения соединения нужно использовать метод connect() объекта Engine , который возвращает объект типа Connection .
 
 
 
from sqlalchemy import create_engine # 1111 это мой пароль для пользователя postgres engine = create_engine("postgresql+psycopg2://postgres:1111@localhost/sqlalchemy_tuts") engine.connect() print(engine)
 
Но если запустить его, то будет следующая ошибка:
 
sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) (Background on this error at: http://sqlalche.me/e/14/e3q8)
 
Проблема в том, что предпринимается попытка подключиться к несуществующей базе данных. Для создания базы данных PostgreSQL нужно выполнить следующий код:
 
 
 
import psycopg2 from psycopg2.extensions import ISOLATION_LEVEL_AUTOCOMMIT # Устанавливаем соединение с postgres connection = psycopg2.connect(user="postgres", password="1111") connection.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT) # Создаем курсор для выполнения операций с базой данных cursor = connection.cursor() sql_create_database = # Создаем базу данных cursor.execute('create database sqlalchemy_tuts') # Закрываем соединение cursor.close() connection.close()
 
Запустите скрипт еще раз, чтобы получить нужный вывод:
 
Engine(postgresql+psycopg2://postgres:***@localhost/sqlalchemy_tuts)
 
Дополнительные аргументы
 
В следующей таблице перечислены дополнительные аргументы-ключевые слова, которые можно передать в функцию create_engine() .
 
 
 
 
Аргумент
 
Описание
 
 
 
echo
 
Булево значение. Если задать True, то движок будет сохранять логи SQL в стандартный вывод. По умолчанию значение равно False
 
 
 
pool_size
 
Определяет количество соединений для пула. По умолчанию — 5
 
 
 
max_overflow
 
Определяет количество соединений вне значения pool_size . По умолчанию — 10
 
 
 
encoding
 
Определяет кодировку SQLAlchemy. По умолчанию — UTF-8. Однако этот параметр не влияет на кодировку всей базы данных
 
 
 
isolation_level
 
Уровень изоляции. Эта настройка контролирует степень изоляции одной транзакции. Разные базы данных поддерживают разные уровни. Для этого лучше ознакомиться с документацией конкретной базы данных
 
 
 
 
Вот скрипт, в котором использованы дополнительные аргументы-ключевые слова при создании движка:
 
Источник