pythonでmysqlを操作

$ pip3 install mysql-connector-python

mysql側の操作

create database reddit;
use reddit
create table polygon (
	id int auto_increment primary key,
	title varchar(255),
	score int
);

insert into polygon (title, score) values
("Daily Polygon Discussion Thread", 25);

db.py

import mysql.connector

conn = mysql.connector.connect(
user='root',
passwd='hogehoge',
host='localhost',
db='reddit'
)

cur = conn.cursor()

sql = "select * from polygon"
cur.execute(sql)

rows = cur.fetchall()

for row in rows:
	print(row)

cur.close

conn.close

$ python3 db.py
(1, ‘Daily Polygon Discussion Thread’, 25)

### insertの場合

sql1 = "truncate table polygon"
cur.execute(sql1)

sql2 = "INSERT INTO polygon (title, score) VALUES('Daily Polygon Discussion Thread', 4)"
cur.execute(sql2)
conn.commit()

mysql> select * from polygon;
+—-+———————————+——-+
| id | title | score |
+—-+———————————+——-+
| 1 | Daily Polygon Discussion Thread | 4 |
+—-+———————————+——-+
1 row in set (0.00 sec)

OK
これに翻訳を入れたい

from googletrans import Translator
translator = Translator()

text = "Daily Polygon Discussion Thread"
 
translation = translator.translate(text, dest='ja', src='en')
print(translation.text)

$ python3 tran.py
毎日のポリゴンディスカッションスレッド

OK
これを全部つなげる

mysql> select * from polygon;
+—-+———————————+———————————————————–+——-+———————+
| id | title | title_ja | score | date |
+—-+———————————+———————————————————–+——-+———————+
| 1 | Daily Polygon Discussion Thread | 毎日のポリゴンディスカッションスレッド | 4 | 2022-01-08 05:14:42 |
+—-+———————————+———————————————————–+——-+———————+
1 row in set (0.00 sec)

ubuntu20.04にmysql8.0をインストール

$ sudo apt update
$ sudo apt install mysql-server
$ sudo mysql –defaults-file=/etc/mysql/debian.cnf
mysql> SET GLOBAL validate_password.length=6;
mysql> SET GLOBAL validate_password.policy=LOW;
mysql> ALTER USER ‘root’@’localhost’ IDENTIFIED WITH mysql_native_password BY ‘${new password}’;
$ mysql -u root -p

create database test;
use test;
create table master_zip (
zip_code char(7) primary key,
pref_name char(5),
city_name char(10),
town_name char(20),
area_name char(20)
);
insert into master_zip values
(“1690075″,”東京都”,”新宿区”,””,”高田馬場”),
(“2130001″,”神奈川県”,”川崎市”,”高津区”,”溝の口”),
(“2470056″,”神奈川県”,”鎌倉市”,””,”大船”),
(“2500408″,”神奈川県”,”足柄下郡”,”箱根町”,”強羅”)
;
mysql> select * from master_zip;
+———-+————–+————–+———–+————–+
| zip_code | pref_name | city_name | town_name | area_name |
+———-+————–+————–+———–+————–+
| 1690075 | 東京都 | 新宿区 | | 高田馬場 |
| 2130001 | 神奈川県 | 川崎市 | 高津区 | 溝の口 |
| 2470056 | 神奈川県 | 鎌倉市 | | 大船 |
| 2500408 | 神奈川県 | 足柄下郡 | 箱根町 | 強羅 |
+———-+————–+————–+———–+————–+
4 rows in set (0.00 sec)

$ php -v
PHP 7.4.3 (cli) (built: Jul 5 2021 15:13:35) ( NTS )
$ sudo apt install php7.4-mysql
$ php -m
$ sudo vi /etc/php/7.4/cli/php.ini
$ php -S 192.168.34.10:8000

<body>
  <label>郵便番号</label>
  <input type="text" id="code">
  <div id="mes">住所:<span id="place"></span></div>

  <script
  src="https://code.jquery.com/jquery-3.6.0.min.js"
  integrity="sha256-/xUj+3OJU5yExlq6GSYGSHk7tPXikynS7ogEvDej/m4="
  crossorigin="anonymous"></script>
    <script>
      $(function(){
        let area;
        $('#code').on("mouseleave input",function(){
            code = $(this).val();
            $.post({
               url: 'ajax_getDBData.php',
               data: {
                  'code': code
               },
               dataType: 'json',
            }).done(function(data){
              console.log(data);
              $.each(data, function(key, item){
                area = isValue(item.pref_name) + isValue(item.city_name) + isValue(item.town_name) + isValue(item.area_name);
              })
              $("#place").text(area);
            }).fail(function(XMLHttpRequest, textStatus, errorThrown){
              alert(errorThrown);
            })
        })
        isValue = function(val){
          if(val == undefined){
            val = "";
          }
          return val;
        }
      })
    </script>
</body>

php

header("Content-Type: application/json; charset=UTF-8");
$row = array();
$data = array();
$code = func_escape(filter_input(INPUT_POST, "code"));

$dsn = "mysql:dbname=test;host=localhost";
$user = "root";
$password = "hogehoge";
try {
    $dbh = new PDO($dsn, $user, $password);
    // echo "接続";
} catch (PDOException $e){
    print('connection failed:'.$e->getMessage());
}
$sql = "select zip_code, pref_name, city_name, town_name, area_name from master_zip where zip_code = ?";
$sth = $dbh->prepare($sql);
$sth->bindValue(1,$code,PDO::PARAM_STR);
$sth->execute();
$data = $sth->fetchAll(PDO::FETCH_ASSOC);
echo json_encode($data);
exit;

function func_escape($word){
	return htmlspecialchars($word, ENT_QUOTES);
}

パスワードの変更の箇所が躓きやすいかも

[MySQLデータ移行]エクスポートとインポート

$ uname -a
Linux localhost 4.14.214-160.339.amzn2.x86_64 #1 SMP Sun Jan 10 05:53:05 UTC 2021 x86_64 x86_64 x86_64 GNU/Linu
$ mysql –version
mysql Ver 8.0.23 for Linux on x86_64 (MySQL Community Server – GPL)

$ mysql -u root -p
mysql> show databases;
mysql> use nonemail
mysql> show tables;
+————————+
| Tables_in_nonemail |
+————————+
| failed_jobs |
| migrations |
| password_resets |
| personal_access_tokens |
| sessions |
| users |
+————————+
mysql> select * from users;
-> usersテーブルに4件データが入っています。

### mysqlのデータエクスポート
$ mysqldump -u root -p –opt nonemail > database_name.sql
$ ls
database_name.sql

dumpファイルの中身
-> sql文が入っている

### mysqlのdumpファイルからデータインポート
$ ls
database_name.sql
$ mysql -u root -p newdatabase < database_name.sql Enter password: ERROR 1049 (42000): Unknown database 'newdatabase' -> ん? dbを作ってからでないとダメ??

mysql> create database newdatabase;
Query OK, 1 row affected (0.01 sec)
$ mysql -u root -p newdatabase < database_name.sql Enter password: mysql> use newdatabase;
mysql> show tables;
+————————+
| Tables_in_newdatabase |
+————————+
| failed_jobs |
| migrations |
| password_resets |
| personal_access_tokens |
| sessions |
| users |
+————————+
6 rows in set (0.00 sec)
mysql> select * from users;

ちゃんとデータも入ってます^^

なるほど、mysqlのデータ移行の方法は理解した。

[MySQL8.0.22] 機種依存文字の保存

update users set name=”アイウエオ、カキクケコ” where id=3;

| 3 | NULL | アイウエオ、カキクケコ |

update users set name=”①②③④⑤⑥⑦⑧⑨⑩⑪⑫⑬⑭⑮⑯⑰⑱⑲⑳:㍉㍍㌔㌘㌧㌦㍑㌫㌢:㍻㍼㍽㍾:ⅠⅡⅢⅣⅤⅥⅦⅧⅨⅩ” where id=3;
| 3 | NULL | ①②③④⑤⑥⑦⑧⑨⑩⑪⑫⑬⑭⑮⑯⑰⑱⑲⑳:㍉㍍㌔㌘㌧㌦㍑㌫㌢:㍻㍼㍽㍾:

update users set name=”〃 仝 ゝ ゞ 々 〆 ヾ ― ‐ / 〇 ヽ _  ̄ ¨ ` ´ ゜ ゛ \ § ^ ≫ ¬ ⇒ ⇔ ∀ ∃ ∠ ⊥ ⌒ ∂ ∇ ≡ ∨ ≪ † √ ∽ ∝ ∵ ∫ ∬ Å ‰ ♯ ♭ ♪ ‡ ~ ′ ≒ × ∥ ∧ | … ± ÷ ≠ ≦ ≧ ∞ ∴ ♂ ♀ ∪ ‥ ° ⊃ ⊂ ⊇ ∩ ⊆ ∋ ∈ 〓 〒 ※ ″” where id=3;
| 3 | NULL | 〃 仝 ゝ ゞ 々 〆 ヾ ― ‐ / 〇 ヽ _  ̄ ¨ ` ´ ゜ ゛ \ § ^ ≫ ¬ ⇒ ⇔ ∀ ∃ ∠ ⊥ ⌒ ∂ ∇ ≡ ∨ ≪ † √ ∽ ∝ ∵ ∫ ∬ Å ‰ ♯ ♭ ♪ ‡ ~ ′ ≒ × ∥ ∧ | … ± ÷ ≠ ≦ ≧ ∞ ∴ ♂ ♀ ∪ ‥ ° ⊃ ⊂ ⊇ ∩ ⊆ ∋ ∈ 〓 〒 ※ ″

全部いけるんか。すげえ。
続いて、PDO

$name = "〃 仝 ゝ ゞ 々 〆 ヾ ― ‐ / 〇 ヽ _  ̄ ¨ ` ´ ゜ ゛ \ § ^ ≫ ¬ ⇒ ⇔ ∀ ∃ ∠ ⊥ ⌒ ∂ ∇ ≡ ∨ ≪ † √ ∽ ∝ ∵ ∫ ∬ Å ‰ ♯ ♭ ♪ ‡ ~ ′ ≒ × ∥ ∧ | … ± ÷ ≠ ≦ ≧ ∞ ∴ ♂ ♀ ∪ ‥ ° ⊃ ⊂ ⊇ ∩ ⊆ ∋ ∈ 〓 〒 ※ ″";
$name = mb_convert_encoding($name, "UTF-8", "utf-8");
$stmt = $pdo->prepare("INSERT INTO test1(name) VALUES(:name)");
$stmt->bindParam(':name', $name,PDO::PARAM_STR);
$stmt->execute();

mysql> select * from test1;
+—-+————————————————————————————————————————————————————————————————————————————————————————————————————————-+
| id | name |
+—-+————————————————————————————————————————————————————————————————————————————————————————————————————————-+
| 1 | test |
| 2 | アイウエオ、カキクケコ |
| 3 | ①②③④⑤⑥⑦⑧⑨⑩⑪⑫⑬⑭⑮⑯⑰⑱⑲⑳:㍉㍍㌔㌘㌧㌦㍑㌫㌢:㍻㍼㍽㍾:ⅠⅡⅢⅣⅤⅥⅦⅧⅨⅩ |
| 4 | 〃 仝 ゝ ゞ 々 〆 ヾ ― ‐ / 〇 ヽ _  ̄ ¨ ` ´ ゜ ゛ \ § ^ ≫ ¬ ⇒ ⇔ ∀ ∃ ∠ ⊥ ⌒ ∂ ∇ ≡ ∨ ≪ † √ ∽ ∝ ∵ ∫ ∬ Å ‰ ♯ ♭ ♪ ‡ ~ ′ ≒ × ∥ ∧ | … ± ÷ ≠ ≦ ≧ ∞ ∴ ♂ ♀ ∪ ‥ ° ⊃ ⊂ ⊇ ∩ ⊆ ∋ ∈ 〓 〒 ※ ″ |
+—-+————————————————————————————————————————————————————————————————————————————————————————————————————————-+

ちょっと整理する必要があるな。

[MySQL8.0.22] Oracleのデータ型(NUMBERとDATE)を挿入する

OracleのNUMBER(16,4)とDATEをMySQLに挿入したい。

取り敢えずOracle側のテストデータとして以下を用意する。
2021/01/07 10:00:00
1500.0000

mysqlのCreate table

create table oracle_test(
	id int unsigned auto_increment primary key,
	price4 decimal(10,4),
	price2 decimal(10,2),
	datetime_at datetime,
	timestamp_at timestamp
);

mysql> describe oracle_test;
+————–+—————+——+—–+———+—————-+
| Field | Type | Null | Key | Default | Extra |
+————–+—————+——+—–+———+—————-+
| id | int unsigned | NO | PRI | NULL | auto_increment |
| price4 | decimal(10,4) | YES | | NULL | |
| price2 | decimal(10,2) | YES | | NULL | |
| datetime_at | datetime | YES | | NULL | |
| timestamp_at | timestamp | YES | | NULL | |
+————–+—————+——+—–+———+—————-+
5 rows in set (0.00 sec)

insert into oracle_test(price4) value("1500.0000");
insert into oracle_test(price2) value("1500.0000");
insert into oracle_test(datetime_at) value("2021/01/07 10:00:00");
insert into oracle_test(timestamp_at) value("2021/01/07 10:00:00");

mysql> select * from oracle_test;
+—-+———–+———+———————+———————+
| id | price4 | price2 | datetime_at | timestamp_at |
+—-+———–+———+———————+———————+
| 1 | 1500.0000 | NULL | NULL | NULL |
| 2 | NULL | 1500.00 | NULL | NULL |
| 3 | NULL | NULL | 2021-01-07 10:00:00 | NULL |
| 4 | NULL | NULL | NULL | 2021-01-07 10:00:00 |
+—-+———–+———+———————+———————+
4 rows in set (0.00 sec)

insert into oracle_test(price2) value(“1500.0050”);
| 5 | NULL | 1500.01 | NULL | NULL |

なるほど、mysql側で勝手に変換してくれんのか。
凄いけど、型づけ言語の考え方からいくと、型が完全に一致してないのに入るのは気持ち悪いな。
まあ、取り敢えず良しとしよう。

[MySQL8.0.22] updateでTruncated incorrect DOUBLE value

値を慌ててupdateしたい。

update users set name=’営業所’ and tel=’03-1234-5678′ where id=1;
-> ERROR 1292 (22007): Truncated incorrect DOUBLE value: ‘営業所’

なんやと!!!

update users set name=’営業所’, tel=’03-1234-5678′ where id=1;

andではなく、カンマ(,)で区切るのが正解なんだと。。
ビックリするぜ。

[MySQL8.0.22]改行テキストをCSV・PDOでinsertしたい

mysql> select version();
+———–+
| version() |
+———–+
| 8.0.22 |
+———–+
1 row in set (0.00 sec)

まず適当にテーブルを作ります。

create table news(
	id int auto_increment PRIMARY KEY, 
	name varchar(20),
	body text
);

mysql> describe news;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int         | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | YES  |     | NULL    |                |
| body  | text        | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

insert intoでは\nで改行になる。

INSERT INTO news(name, body) VALUE ('NHK', '公務員に冬のボーナス 国家公務員平均約65万円 3年連続の減少');

INSERT INTO news(name, body) VALUE ('NHK', '公務員に冬のボーナス\n国家公務員平均約65万円 3年連続の減少');

### pdo
pdoで入れる際は、\r\nをつける。

$sql = 'INSERT INTO news (name, body) VALUE (:name, :body)';
$prepare = $pdo->prepare($sql);

$text = "公務員に冬のボーナス\r\n国家公務員平均約65万円 3年連続の減少";

$prepare->bindValue(':name', 'NHK', PDO::PARAM_STR);
$prepare->bindValue(':body', $text, PDO::PARAM_STR);
$prepare->execute();

echo "insert done";

### csv
L insertする前に、str_replace(‘\r\n’,”\r\n”,$csv);でエスケープする必要がある

$fp = fopen("test.csv", "r");

while(! feof($fp)){
	$csv = fgets($fp);
	$csv = trim($csv,'"');
	$csv = mb_convert_encoding($csv, "UTF-8", "utf-8");
	$csv = str_replace('"','',$csv);
	$csv = str_replace('\r\n',"\r\n",$csv);
	$csv_array = explode(",",$csv);
	

	$stmt = $pdo->prepare("INSERT INTO news (name, body) VALUE (:name, :body)");

	$stmt->bindValue(":name", $csv_array[0], PDO::PARAM_STR);
	$stmt->bindValue(":body", $csv_array[1], PDO::PARAM_STR);
	// $stmt->bindValue(":body", str_replace(["\\r","\\n"], ["\r", "\n"], $csv_array[1]), PDO::PARAM_STR);
	$stmt->execute();
	
}

fgetcsv
L fgetcsvだと、わざわざ\r\nを入れなくても、改行データをそのままmysqlに入れてくれる。

$fp = fopen("hoge.csv", "r");
while(($data[]=fgetcsv($fp))!==FALSE){
}
fclose($fp);

var_dump($data);

foreach($data as $result){
	$stmt = $pdo->prepare("INSERT INTO news (name, body) VALUE (:name, :body)");

	$stmt->bindValue(":name", $result[0], PDO::PARAM_STR);
	$stmt->bindValue(":body", $result[1], PDO::PARAM_STR);
	$stmt->execute();
}
echo "done";

素晴らしい。

[mysql8.0.22] 2000件レコードでテーブルのカラム数28、48、116のselect文の実行時間の差

### レコード数: 2001件
mysql> select count(id) from orders2;
+———–+
| count(id) |
+———–+
| 2001 |
+———–+
1 row in set (0.04 sec)

### sql文
mysql.sql

select * from orders2 where supplier_num = 1;

### カラム数28
$ time (cat mysql.sql | mysql -u root -p test > /dev/null)
Enter password:

real 0m3.099s // プログラムの呼び出しから終了までにかかった実時間
user 0m0.009s // プログラム自体の処理時間(秒)(ユーザCPU時間)
sys 0m0.012s // プログラムを処理するために、OSが処理をした時間

### カラム数48
$ time (cat mysql.sql | mysql -u root -p test > /dev/null)
Enter password:

real 0m3.162s
user 0m0.013s
sys 0m0.009s

### カラム数116
$ time (cat mysql.sql | mysql -u root -p test > /dev/null)
Enter password:

real 0m3.504s
user 0m0.011s
sys 0m0.011s

確かにちょっと遅くなってるけど、カラム数50ぐらいだと、本当に誤差だな
商用環境ではテーブル結合により絞り込み検索を行うので検索スピードは変わってくるのが、レコード数が数千件程度では、カラム数が100を超えていてもそこまで影響はなさそうだな。

[mysql8.0.22]実行速度(ユーザCPU時間)の確認方法

mysql> select version();
+———–+
| version() |
+———–+
| 8.0.22 |
+———–+
1 row in set (0.00 sec)

mysql> use test;
mysql> select * from users;
mysql> SELECT BENCHMARK(100,(SELECT id FROM users m0 WHERE (m0.id = 1)));
+————————————————————+
| BENCHMARK(100,(SELECT id FROM users m0 WHERE (m0.id = 1))) |
+————————————————————+
| 0 |
+————————————————————+
1 row in set (0.00 sec)

ん? 0秒ってこと?

$ touch mysql.sql

select * from users where id = 1;
$ mysql -u root -p "password" -D test <mysql.sql
$ cat mysql.sql | mysql -u root -p -vvv test | tail -n 3
Enter password: 

1 row in set (0.00 sec)

Bye

$ time (cat mysql.sql | mysql -u root -p test > /dev/null)
Enter password: 

real 0m2.544s // プログラムの呼び出しから終了までにかかった実時間(秒)
user 0m0.013s // プログラム自体の処理時間(秒)(ユーザCPU時間)
sys 0m0.007s // プログラムを処理するために、OSが処理をした時間(秒)(システム時間)

0.013s秒ってことか。

これをレコード4000件で、テーブルのカラム数10, 30, 100でテストしてみるか。
結構大変だな。

[PHP 7.4.11] MySQLにCSVインポートする

### dbとテーブルの作成
mysql> create database myapp;
mysql> use myapp;
mysql> create table users(
-> id int primary key auto_increment,
-> name varchar(255),
-> sports varchar(255)
-> );

nameとsportsを入れたcsvファイル(users.csv)を作成します。

### pdoの確認
$ php -i|grep PDO
PDO
PDO support => enabled
PDO drivers => mysql, sqlite
PDO Driver for MySQL => enabled
PDO Driver for SQLite 3.x => enabled

### csv.php

ini_set('display_errors', 1);

try {
		$pdo = new PDO('mysql:host=localhost;dbname=myapp;charaset=utf8','root','****',
				array(PDO::ATTR_EMULATE_PREPARES => false));
	} catch (PDOException $e){
		exit('データベース接続失敗.'.$e->getMessage());
	}

$fp = fopen("users.csv", "r");

while(! feof($fp)){
	$csv = fgets($fp);
	$csv = trim($csv,'"');
	$csv = mb_convert_encoding($csv, "UTF-8", "utf-8");
	$csv = str_replace('"','',$csv);
	$csv_array = explode(",",$csv);

	$stmt = $pdo->prepare("INSERT INTO users(name, sports) VALUES(:name, :sports)");

	$stmt->bindParam(':name', $csv_array[0],PDO::PARAM_STR);
	$stmt->bindParam(':sports', $csv_array[1],PDO::PARAM_STR);
	$stmt->execute();
}

echo "insert done";

$ php csv.php
insert done

mysql> select * from users;
+—-+—————–+—————-+
| id | name | sports |
+—-+—————–+—————-+
| 1 | name | sports
|
| 2 | Efa Trujillo | Gynastics
|
| 3 | Erica Werner | Rowing
|
| 4 | Amin Guerrero | Wiffleball
|
| 5 | Abbie Bender | Rugby
|
| 6 | Kamron Mcneill | Surfing
|
| 7 | Arabella Sutton | Basketball
|
| 8 | Emily Kerr | Curling
|
| 9 | Connar Small | Ice skating
|
| 10 | Haidar Fletcher | Figure skating |
+—-+—————–+—————-+
10 rows in set (0.00 sec)

1行目からinsertされてしまうので、1行目は抜かす必要がありますね。