В Python 2 большинство библиотек для подключения к MySQL используют MySQLdb, но эта библиотека официально не поддерживает Python 3, поэтому рекомендуемая здесь библиотека — PyMySQL.
В этом разделе мы объясним, как использовать PyMySQL для работы с базой данных MySQL.
1. Подготовка
Перед началом убедитесь, что база данных MySQL установлена и работает правильно, а библиотека PyMySQL должна быть установлена.
2. Подключиться к базе данных
Здесь сначала попробуйте подключиться к базе данных. Предположим, что текущий MySQL работает локально, имя пользователя — root, пароль — 123456, а рабочий порт — 3306. Здесь мы используем PyMySQL, чтобы сначала подключиться к MySQL, а затем создать новую базу данных с именем пауки, Код выглядит следующим образом:
import pymysql
db = pymysql.connect(host='localhost',user='root', password='123456', port=3306)
cursor = db.cursor()
cursor.execute('SELECT VERSION()')
data = cursor.fetchone()
print('Database version:', data)
cursor.execute("CREATE DATABASE spiders DEFAULT CHARACTER SET utf8")
db.close()Результаты приведены ниже:
Database version: ('5.6.22',)здесь через PyMySQLconnect()метод объявляет объект подключения MySQLdb, вам нужно передать запущенный MySQLhost(т.е. ИП). Поскольку MySQL работает локально, входящиеlocalhost. Если MySQL работает удаленно, передайте его общедоступный IP-адрес. последующие параметрыuserто есть имя пользователя,passwordто есть пароль,portто есть порт (по умолчанию3306).
После успешного подключения необходимо снова позвонитьcursor()Метод получает курсор операции MySQL и использует его для выполнения оператора SQL. Здесь мы выполняем два оператора SQL, напрямую используяexecute()метод может быть выполнен. Первое предложение SQL используется для получения текущей версии MySQL, а затем вызываетсяfetchone()Метод получает первую часть данных, а также получает номер версии. Второе предложение SQL выполняет операцию создания базы данных, имя базы данных — пауки, а кодировка по умолчанию — UTF-8. Поскольку этот оператор не является оператором запроса, пауки базы данных успешно создаются после его непосредственного выполнения. Затем используйте эту базу данных для последующих операций.
3. Создайте таблицу
Вообще говоря, операцию создания базы данных нужно выполнить только один раз. Конечно, мы также можем создать базу данных вручную. С этого момента наши операции выполняются с базой данных пауков.
После создания базы нужно указать дополнительный параметр при подключенииdb.
Затем создайте новую таблицу данных для учащихся и выполните оператор SQL для создания таблицы. Здесь указаны три поля, и структура выглядит следующим образом.
| имя поля | значение | тип |
|---|---|---|
id |
Студенческий билет | varchar |
name |
Имя | varchar |
age |
возраст | int |
Пример кода для создания таблицы выглядит следующим образом:
import pymysql
db = pymysql.connect(host='localhost', user='root', password='123456', port=3306, db='spiders')
cursor = db.cursor()
sql = 'CREATE TABLE IF NOT EXISTS students (id VARCHAR(255) NOT NULL, name VARCHAR(255) NOT NULL, age INT NOT NULL, PRIMARY KEY (id))'
cursor.execute(sql)
db.close()После запуска мы создали таблицу данных с именем студентов.
Разумеется, для демонстрации здесь указаны только самые простые поля. Фактически, в процессе сканирования мы создадим определенные поля на основе результатов сканирования.
4. Вставьте данные
Следующим шагом является вставка данных в базу данных. Например, вот просканирована информация о студенте, номер студента 20120001, имя Боб, возраст 20, так как же вставить этот фрагмент данных в базу данных? Пример кода выглядит следующим образом:
import pymysql
id = '20120001'
user = 'Bob'
age = 20
db = pymysql.connect(host='localhost', user='root', password='123456', port=3306, db='spiders')
cursor = db.cursor()
sql = 'INSERT INTO students(id, name, age) values(%s, %s, %s)'
try:
cursor.execute(sql, (id, user, age))
db.commit()
except:
db.rollback()
db.close()Здесь сначала создается оператор SQL, которыйValueЗначения строятся без конкатенации строк, например:
sql = 'INSERT INTO students(id, name, age) values(' + id + ', ' + name + ', ' + age + ')'Этот способ написания громоздкий и неинтуитивный, поэтому мы решили использовать средство форматирования напрямую.%sреализовать. есть несколькоValueнаписать несколько%s, нам просто нужноexecute()Первый параметр метода передается в инструкцию SQL,ValueПросто передайте значение в юниформ-кортеже. Этот способ написания может не только избежать проблемы сращивания строк, но также и проблемы с конфликтующими кавычками.
После этого стоит отметить, что необходимо выполнитьdbобъектcommit()Метод может реализовать вставку данных, и этот метод является методом, который фактически отправляет запрос в базу данных для выполнения. Для операций вставки, обновления и удаления данных необходимо вызвать этот метод, чтобы он вступил в силу.
Затем мы добавили уровень обработки исключений. Если выполнение не удается, вызовитеrollback()Выполнение отката данных эквивалентно тому, что ничего не произошло.
Здесь дело в бизнесе. Механизм транзакций может обеспечить непротиворечивость данных, то есть это событие либо произошло, либо не произошло. Например, при вставке части данных не будет случая, когда вставляется половина данных, вставляются либо все данные, либо ничего, это атомарность транзакции. Кроме того, у транзакций есть 3 свойства — согласованность, изолированность и долговечность. Эти четыре свойства обычно называют характеристиками ACID, как показано в следующей таблице.
| Атрибуты | объяснять |
|---|---|
| атомарность | Транзакция — неотделимая единица работы, и операции, включенные в транзакцию, либо выполняются, либо не выполняются. |
| последовательность | Транзакция должна перевести базу данных из одного согласованного состояния в другое согласованное состояние. Непротиворечивость и атомарность тесно связаны |
| изоляция | Выполнение транзакции не может быть нарушено другими транзакциями, то есть операции и данные, используемые в транзакции, изолированы от других параллельных транзакций, а параллельные транзакции не могут мешать друг другу. |
| Долговечность | Постоянство, также известное как постоянство, означает, что после фиксации транзакции ее изменения в данных в базе данных должны быть постоянными. Другие операции или сбои, которые следуют, не должны иметь никакого влияния на него. |
Операции вставки, обновления и удаления — это все операции по изменению базы данных, а операция изменения должна быть транзакцией, поэтому стандартное описание этих операций:
try:
cursor.execute(sql)
db.commit()
except:
db.rollback()Это обеспечивает согласованность данных. здесьcommit()иrollback()Методы обеспечивают поддержку реализации транзакций.
Вышеупомянутая операция вставки данных достигается путем построения операторов SQL, но, очевидно, это имеет крайне неудобное место, например, внезапное добавление поля гендера.gender, то оператор SQL необходимо изменить на:
INSERT INTO students(id, name, age, gender) values(%s, %s, %s, %s)Соответствующий параметр кортежа необходимо изменить на:
(id, name, age, gender)Это явно не то, чего мы хотим. Во многих случаях мы хотим добиться эффекта, заключающегося в том, что метод вставки не нужно менять, и он делается общим методом, который нужно только передать в динамически изменяющемся словаре. Например, создайте такой словарь:
{
'id': '20120001',
'name': 'Bob',
'age': 20
}Затем оператор SQL будет динамически построен в соответствии со словарем, и кортеж также будет динамически построен, чтобы можно было реализовать общий метод вставки. Итак, здесь нам нужно переписать метод вставки:
data = {
'id': '20120001',
'name': 'Bob',
'age': 20
}
table = 'students'
keys = ', '.join(data.keys())
values = ', '.join(['%s'] * len(data))
sql = 'INSERT INTO {table}({keys}) VALUES ({values})'.format(table=table, keys=keys, values=values)
try:
if cursor.execute(sql, tuple(data.values())):
print('Successful')
db.commit()
except:
print('Failed')
db.rollback()
db.close()Здесь данные, которые мы передаем, представляют собой словарь и определяют его какdataПеременная. Имя таблицы также определяется как переменнаяtable. Далее вам нужно построить динамический оператор SQL.
Во-первых, вам нужно построить вставленное полеid,nameиage. Здесь необходимо толькоdataВозьмите ключевые имена и разделите их запятыми. так', '.join(data.keys())Результатid, name, age, а затем нужно построить несколько%sВ качестве заполнителя есть несколько полей для создания нескольких. Например, здесь три поля, нужно построить%s, %s, %s. Здесь сначала определяется массив длины 1['%s'], который затем расширяется путем умножения до['%s', '%s', '%s'], затем позвонитеjoin()метод, который в итоге становится%s, %s, %s. Наконец, мы повторно используем строкуformat()Метод создает имя таблицы, имя поля и заполнитель. Окончательный оператор SQL динамически создается как:
INSERT INTO students(id, name, age) VALUES (%s, %s, %s)Наконец, дляexecute()Первый параметр метода передается вsqlпеременная, второй параметр передается вdataКортеж, построенный по ключевому значению , может успешно вставить данные.
Таким образом, мы реализовали метод передачи словаря для вставки данных, и нет необходимости изменять оператор SQL и операции вставки.
5. Обновить данные
Операция обновления данных фактически выполняет операторы SQL. Самый простой способ — создать оператор SQL и выполнить его:
sql = 'UPDATE students SET age = %s WHERE name = %s'
try:
cursor.execute(sql, (25, 'Bob'))
db.commit()
except:
db.rollback()
db.close()Здесь SQL также создается в виде заполнителей, а затем выполняетсяexecute()метод, передавая параметры в виде кортежей, то же выполнениеcommit()способ выполнения действия. Если вы хотите выполнить простое обновление данных, вы можете использовать этот метод.
Однако в процессе фактического сбора данных данные необходимо вставлять в большинстве случаев, но нас волнует, будут ли дублироваться данные.Если это произойдет, мы хотим обновить данные, а не сохранять их повторно. Кроме того, как и в упомянутой выше задаче динамического построения SQL, здесь может быть реализован еще один метод дедупликации: если данные есть, данные обновляются, если данных нет, данные вставляются. Кроме того, этот подход поддерживает гибкий словарь по значению. Пример выглядит следующим образом:
data = {
'id': '20120001',
'name': 'Bob',
'age': 21
}
table = 'students'
keys = ', '.join(data.keys())
values = ', '.join(['%s'] * len(data))
sql = 'INSERT INTO {table}({keys}) VALUES ({values}) ON DUPLICATE KEY UPDATE'.format(table=table, keys=keys, values=values)
update = ','.join([" {key} = %s".format(key=key) for key in data])
sql += update
try:
if cursor.execute(sql, tuple(data.values())*2):
print('Successful')
db.commit()
except:
print('Failed')
db.rollback()
db.close()Сконструированный здесь оператор SQL на самом деле является оператором вставки, но мы добавили его позже.ON DUPLICATE KEY UPDATE. Эта строка кода означает выполнение операции обновления, если первичный ключ уже существует. Например, данные, которые мы передаемidвсе еще для20120001, но возраст изменился с 20 на 21. В это время эти данные не будут вставляться, а обновляться напрямуюidза20120001Данные. Полный SQL строится следующим образом:
INSERT INTO students(id, name, age) VALUES (%s, %s, %s) ON DUPLICATE KEY UPDATE id = %s, name = %s, age = %sЗдесь становится 6%s. так сзадиexecute()Второй кортеж параметров метода необходимо умножить на 2, чтобы он стал в 2 раза больше исходного.
Таким образом, мы можем реализовать функцию вставки данных, если первичный ключ не существует, и обновления данных, если он существует.
6. Удаление данных
Операция удаления относительно проста и может использоваться непосредственноDELETEоператор, вам просто нужно указать имя целевой таблицы для удаления и удаления условий, и вам все еще нужно использоватьdbизcommit()способ вступить в силу. Пример выглядит следующим образом:
table = 'students'
condition = 'age > 20'
sql = 'DELETE FROM {table} WHERE {condition}'.format(table=table, condition=condition)
try:
cursor.execute(sql)
db.commit()
except:
db.rollback()
db.close()Поскольку существуют различные условия удаления, операторы больше, меньше, равно,LIKEт. д., условный коннектор имеетAND,ORи т. д., так что более сложные условия суждения не строятся. Здесь условие передается непосредственно в виде строки для реализации операции удаления.
7. Запросить данные
После операций вставки, изменения и удаления остается очень важная операция — запрос. запрос будет использоватьSELECTутверждение, например:
sql = 'SELECT * FROM students WHERE age >= 20'
try:
cursor.execute(sql)
print('Count:', cursor.rowcount)
one = cursor.fetchone()
print('One:', one)
results = cursor.fetchall()
print('Results:', results)
print('Results Type:', type(results))
for row in results:
print(row)
except:
print('Error')Результаты приведены ниже:
Count: 4
One: ('20120001', 'Bob', 25)
Results: (('20120011', 'Mary', 21), ('20120012', 'Mike', 20), ('20120013', 'James', 22))
Results Type: <class 'tuple'>
('20120011', 'Mary', 21)
('20120012', 'Mike', 20)
('20120013', 'James', 22)Здесь мы создаем оператор SQL для запроса учащихся в возрасте 20 лет и старше, а затем передаем его вexecute()метод. Обратите внимание, что здесь больше не требуетсяdbизcommit()метод. Далее звонитеcursorизrowcountАтрибут получает количество результатов запроса, которое в текущем примере равно 4.
Затем мы звонимfetchone()метод, этот метод может получить первые данные результата, возвращаемый результат в виде кортежа, а порядок элементов кортежа соответствует полям один за другим, то есть первый элемент является первым полеid, второй элемент - это второе полеname, и так далее. Затем мы звонимfetchall()метод, он может получить все данные результата. Затем выведите его результат и тип, это двойной кортеж, каждый элемент является записью, мы проходим его и выводим.
Но тут есть проблема, на которую стоит обратить внимание, здесь 3 штуки данных вместо 4-х,fetchall()Разве это не способ получить все данные? Это связано с тем, что его внутренняя реализация имеет указатель смещения, указывающий на результат запроса. Первоначально указатель смещения указывает на первую часть данных. После однократной выборки указатель смещается на следующую часть данных. Если вы извлекаете его снова, вы получите результат.следующие данные. Сначала мы позвонили один разfetchone()метод, чтобы указатель смещения результата указывал на следующие данные,fetchall()Метод возвращает все данные из данных, на которые указывает указатель смещения, до конца, поэтому этим методом получается только 3 результата.
Кроме того, мы также можем использоватьwhileпетля плюсfetchone()метод для получения всех данных вместо использованияfetchall()Собери все вместе.fetchall()Все результаты будут возвращены в виде кортежей.Если объем данных большой, накладные расходы будут очень высокими. Поэтому рекомендуется использовать следующие методы для выборки данных по одному:
sql = 'SELECT * FROM students WHERE age >= 20'
try:
cursor.execute(sql)
print('Count:', cursor.rowcount)
row = cursor.fetchone()
while row:
print('Row:', row)
row = cursor.fetchone()
except:
print('Error')Таким образом, указатель будет смещаться на часть данных каждый раз, когда он зацикливается, что просто и эффективно.
В этом разделе мы рассказали, как использовать PyMySQL для работы с базой данных MySQL и создания некоторых операторов SQL.
Этот ресурс был впервые опубликован в личном блоге Цуй Цинцай Цзин Ми:Практическое руководство по разработке веб-краулера на Python3 | Цзин Ми
Если вы хотите узнать больше информации о поисковых роботах, обратите внимание на мой личный публичный аккаунт WeChat: Coder of Attack.
WeChat.QQ.com/Day/5 Это радость VE Z…(автоматическое распознавание QR-кода)