データベースを使ったサイトを作りたい⑧ PythonでMariaDBへデータ更新｜更新編

投稿日: 2020年9月8日 作成者: dalomo

MariaDB Connector/Python

ぶっちゃけ自分の用途だとINSERTしか使わないような気がするんですが、それでもいきなりやるのは怖いので前回作ったtestデータベースを使って練習したい。

練習

cursor.execute("CREATE TABLE mytest(id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,"
               "first_name VARCHAR(100), last_name VARCHAR(100), qdate DATE)")

cursor.execute("INSERT INTO mytest(first_name, last_name, qdate) VALUES (?,?,?)",
               ("山田", "太郎","2020-9-8"))

cursor.execute("SELECT id, first_name, last_name, qdate FROM mytest")

前回のBasic usageに日付追加して、日本語にしただけ。

$ python3 /home/dalomo/shogi/dbtest.py
Traceback (most recent call last):
  File "/home/dalomo/shogi/dbtest.py", line 13, in 
    cursor.execute("INSERT INTO mytest(first_name, last_name, qdate) VALUES (?,?,?)",
mariadb.OperationalError: Incorrect string value: '\xE5\xB1\xB1\xE7\x94\xB0' for column `test`.`mytest`.`first_name` at row 1

あれ？あーまた文字コード忘れた。一回データベース消して

create database test character set utf8mb4;

でもっかい作る。

$ python3 /home/dalomo/shogi/dbtest.py
1       山田    太郎    2020-09-08

おー、もういけんじゃね？他に

data = [
('山田', '太郎', '2020-9-8'),
('鈴木', '花子', '2020-9-8'),
('佐藤', '次郎', '2020-9-8')
]

cursor.executemany("INSERT INTO mytest(first_name, last_name, qdate) VALUES (?,?,?)", data)

cursor.execute("SELECT id, first_name, last_name, qdate FROM mytest")

row= cursor.fetchone()
print("---fetchone---")
print(*row, sep='\t')
pprint(row)
print(type(row))

cursor.execute("SELECT id, first_name, last_name, qdate FROM mytest")

row= cursor.fetchall()
print("---fetchall---")
print(*row, sep='\t')
pprint(row)
print(type(row))

cursor.execute("SELECT id, first_name, last_name, qdate FROM mytest")

row= cursor.fetchmany()
print("---fetchmany---")
print(*row, sep='\t')
pprint(row)
print(type(row))

てやると

$ python3 /home/dalomo/shogi/dbtest.py
---fetchone---
1       山田    太郎    2020-09-08
(1, '山田', '太郎', datetime.date(2020, 9, 8))
<class 'tuple'>
---fetchall---
(1, '山田', '太郎', datetime.date(2020, 9, 8))  (2, '鈴木', '花子', datetime.date(2020, 9, 8))  (3, '佐藤', '次郎', datetime.date(2020, 9, 8))
[(1, '山田', '太郎', datetime.date(2020, 9, 8)),
 (2, '鈴木', '花子', datetime.date(2020, 9, 8)),
 (3, '佐藤', '次郎', datetime.date(2020, 9, 8))]
<class 'list'>
---fetchmany---
(1, '山田', '太郎', datetime.date(2020, 9, 8))
[(1, '山田', '太郎', datetime.date(2020, 9, 8))]
<class 'list'>

こんなんとか。まぁSELECT使う予定ないけど。これはもういけるんじゃないか？書いてみよ。

本番

スクレイピングのコードの下に

conn= mariadb.connect(user="*", password="*", database="shogi", host="127.0.0.1")
cur= conn.cursor()

cur.execute("INSERT INTO data(date, title, maker, steps, url) VALUES (?,?,?,?,?)", 
            (q_date, title, maker, steps, url))

cur.close()
conn.close()

こんな感じで書いた、けど、エラーも出ずDBも更新されず何も起こらなかった…。エラーでないときの原因調査ってどうやるんだ。色々調べるとcommitが怪しいらしい！DBのオートコミット設定を調べてみると

MariaDB [(none)]> SELECT @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+
1 row in set (0.000 sec)

あれー？なんで？ONになってるよね…。じゃあログを出してみようということで

https://mariadb.com/kb/en/general-query-log/

MariaDB [(none)]> SET GLOBAL general_log = 1;
Query OK, 0 rows affected (0.002 sec) MariaDB [(none)]> SET GLOBAL general_log_file='/var/log/mariadb/query.log'; Query OK, 0 rows affected (0.002 sec)

こう。クエリログを出力するようにしてみた。で、プログラム実行してみて、ログを見てみる。

200909 21:30:38	  5951 Connect	@localhost on shogi using TCP/IP
		  5951 Query	SET autocommit=0
		  5951 Prepare	INSERT INTO data(date, title, maker, steps, url) VALUES (?,?,?,?,?)
		  5951 Execute	INSERT INTO data(date, title, maker, steps, url) VALUES ('2020-09-09','2020年9月9日の詰将棋（杉本和陽作、9手詰）','杉本和陽','9','https://www.shogi.or.jp/tsume_shogi/everyday/2020999.html')
		  5951 Close stmt	
		  5951 Quit

おおおおっとー！autocommit=0やんけ！これかな？原因は。どういうこっちゃと思ってドキュメントよく読んで見ると

https://mariadb-corporation.github.io/mariadb-connector-python/module.html#mariadb.connect

New in version 1.0.1.

  • autocommit (bool or None): Specifies the autocommit settings: None will use the server default. True will enable autocommit, False will disable it (default).

うーん？だとするとBasic usageのヤツはいけてなんで俺のはいけないんだろ。もっかい調べてみて

MariaDB [(none)]> use test
MariaDB [test]> SELECT @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+
1 row in set (0.000 sec)

MariaDB [test]> use shogi
MariaDB [shogi]> SELECT @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+
1 row in set (0.000 sec)

んー？んー？分かんない！けどログだとオートコミットがオフになってるからオンになるように書いてみよう！

conn= mariadb.connect(
  user="*", 
  password="*", 
  database="shogi", 
  host="127.0.0.1", 
  autocommit=True
  )

DOKIDOKI

MariaDB [shogi]> select * from data where id between 1255 and 1300;
+------+------------+-------------------------------------------------------------+--------------+-------+-------------------------------------------------------------+
| id   | date       | title                                                       | maker        | steps | url                                                         |
+------+------------+-------------------------------------------------------------+--------------+-------+-------------------------------------------------------------+
| 1255 | 2020-09-06 | 2020年9月6日の詰将棋（中田章道作、11手詰）                  | 中田章道     |    11 | https://www.shogi.or.jp/tsume_shogi/everyday/20209611 |tml
| 1256 | 2020-09-07 | 2020年9月7日の詰将棋（3手詰）                               |              |     3 | https://www.shogi.or.jp/tsume_shogi/everyday/2020973.  |l
| 1269 | 2020-09-09 | 2020年9月9日の詰将棋（杉本和陽作、9手詰）                   | 杉本和陽     |     9 | https://www.shogi.or.jp/tsume_shogi/everyday/2020999.html   |
+------+------------+-------------------------------------------------------------+--------------+-------+-------------------------------------------------------------+
3 rows in set (0.001 sec)

いったーーーーーーーーーーーーー！！！！！！！！けどid飛びまくっとるし9/8分更新するの忘れてた！まぁいいや！いやっふ～！！

で、そのままcronで1日1回まわせばいっかーとか思ってたんだけど、それだとダブったり抜けたりする可能性はあるよなぁと思ったので、日付チェック入れることにする。でも

if row == t_date:
  print("日付一緒だよ")
  sys.exit()

って書いたけど、またもや何も起こらない。とりま

print(row)
print(t_date)
print(t_date.date)

したら

(datetime.date(2020, 9, 9),)
2020-09-09 00:00:00
<built-in method date of datetime.datetime object at 0x7f5babff0f90>

あれれー？なんだこれは、こんなつもりじゃないのに！

if row[0] == t_date.date():
  print("日付一緒だよ")
  sys.exit()

こうだと動いた。[]()忘れるよねー。多分これで事足りると思うんだけどな、どうだろう。ということで最終的にはこうなった

完成

import requests
from bs4 import BeautifulSoup
import mariadb
from datetime import datetime as dt
import sys

#scrape
target_url = 'https://www.shogi.or.jp/tsume_shogi/everyday/'
res = requests.get(target_url)
res.encoding = 'utf-8'

soup = BeautifulSoup(res.text, 'lxml')

contents = soup.find(id="contents")

url = contents.find('a').get('href')
title = contents.find('p').text
t_date = dt.strptime(title[0:title.find('日')].replace('年','-').replace('月','-'),'%Y-%m-%d')
q_date = t_date.date()
q_date = q_date.strftime('%Y-%m-%d')

maker = ''
steps = 0
if '、' in title:
  maker = title[title.find('（')+1:title.find('、')-1]
  steps = title[title.find('、')+1:title.find('手詰')]
else:
  steps = title[title.find('（')+1:title.find('手詰')]

#update
conn= mariadb.connect(
  user="*", 
  password="*", 
  database="shogi", 
  host="127.0.0.1", 
  autocommit=True
  )
cur= conn.cursor()

cur.execute("SELECT MAX(date) FROM data")
row = cur.fetchone()

if row[0] == t_date.date():
  cur.close()
  conn.close()
  sys.exit()

cur.execute("INSERT INTO data(date, title, maker, steps, url) VALUES (?,?,?,?,?)", 
            (q_date, title, maker, steps, url))

cur.close()
conn.close()

そういえば結局SELECT使ったな！あとはcronに登録しておいてちゃんと動いてるか確認しよ。

0 */3 * * * /usr/local/bin/python3 /home/dalomo/shogi/checkandupdate.py

こんな。3時間毎にチェックしにいく感じにした。さて結果どうなったかなとまずはMariaDBのログを見てみると

200910 12:00:02	  7465 Connect	@localhost on shogi using TCP/IP
		  7465 Query	SET autocommit=1
		  7465 Query	SELECT MAX(date) FROM data
		  7465 Prepare	INSERT INTO data(date, title, maker, steps, url) VALUES (?,?,?,?,?)
		  7465 Execute	INSERT INTO data(date, title, maker, steps, url) VALUES ('2020-09-10','2020年9月10日の詰将棋（3手詰）','','3','https://www.shogi.or.jp/tsume_shogi/everyday/20209103.html')
		  7465 Close stmt	
		  7465 Quit

おっ！おーっ！できてるかも！検索サイトで検索してみると

やったああああああああああ！！！！！！！！できてる！ちゃんと更新されてる！嬉しい～、いやー感激だわぁ～よかったー。次は、自動更新ができるようになったことで、ちょこちょこ改善できそうなところがあるので、それをやってこー。

カテゴリー: したい タグ: , , パーマリンク

コメントを残す

メールアドレスが公開されることはありません。 ※ が付いている欄は必須項目です