2023年1月 – ページ 3 – mowareのブログ

[PHP] 03 MySQL検索

[M1 Mac, Big Sur 11.7.2, PHP 8.2.1, MySQL 8.0.31]

MySQLサーバに接続して検索し、結果をChromeに表示させました。

PHP7からMySQL関連の関数が刷新されていました。PHP5以前の情報もネットに多く残っており、非常に紛らわしいです。

少なくとも記事の公開日とPHPのバージョンを明記して欲しいです。

<?php

require_once ('../../composer/vendor/autoload.php');
use Facebook\WebDriver\Chrome\ChromeDriver;

# カレントディレクトリ変更
$cmd_cd_fmt = 'cd %s';
$cmd_cd = sprintf($cmd_cd_fmt, "/code/PHP/projects/mysql03");
exec($cmd_cd);

# MySQLサーバ起動判定
exec('mysqladmin ping', $out, $ret);
echo $out[0];
echo "\n";

if(strpos($out[0],'alive') === false){
    exec('mysql.server start');
}else{
    print 'MySQLは起動しています';
    echo "\n";
}

# Webサーバ起動
exec('php -S localhost:8888 -t "/code/PHP/projects/mysql03" > /dev/null &');

# chromedriverのパス設定
$driverPath = realpath("/opt/homebrew/Caskroom/chromedriver/107.0.5304.62/chromedriver");
putenv("webdriver.chrome.driver=" . $driverPath);

# Chromeを起動しphpスクリプトを実行
$driver = ChromeDriver::start();
$driver->get('http://localhost:8888/mysql_html.php');

exec('mysql.server stop');

?>

<html>
<head>
<title>MySQL TOOL</title>
</head>
<body>
<?php

# MySQLにログイン
$conn = new mysqli('localhost', 'root');
if (!$conn){
    die("MySQL接続に失敗しました");
} else {
    print 'MySQL接続に成功しました';
}

# データベース選択
$select_db = 'use data';
$select = $conn->query($select_db);

# 検索実行
$query = "SELECT * FROM `TableA` WHERE `Name` LIKE '%suzuki%'";
$result = $conn->query($query);
$rows = $result->fetch_all(MYSQLI_ASSOC);
foreach ($rows as $row) {
    printf("%s %s %s %d %d\n", $row["A"], $row["B"], $row["C"], $row["D"], $row["E"]);
}

$conn->close();

?>
</body>
</html>

[PHP] 02 MySQLサーバ起動

[M1 Mac, Big Sur 11.7.2, PHP 8.2.1, MySQL 8.0.31]

PHPスクリプトでMySQLを起動し、Chromeに起動の有無を表示させました。

MySQL検索機能をWebアプリ化できないか検討を進めていきます。

JavaScriptでは何度も挫折しましたが、PHPを足掛かりにWebアプリ製作に関するスキルを身につけていきたいです。

1.chromedriverのダウンロード
  方法は省略

2.Composerのインストール
brew install composer

3.php-webdriverのインストール
  composerディレクトリを作成しカレントディレクトリにしてから以下コマンドを実行
curl -sS https://getcomposer.org/installer | php
php composer.phar require facebook/webdriver

<?php

require_once ('../../composer/vendor/autoload.php');
use Facebook\WebDriver\Chrome\ChromeDriver;

# MySQLサーバ起動
exec('mysql.server start');

# 簡易Webサーバ起動
exec('php -S localhost:8000 -t /code/PHP/projects/mysql > /dev/null &');

# chromedriverのパス設定
$driverPath = realpath("/opt/homebrew/Caskroom/chromedriver/107.0.5304.62/chromedriver");
putenv("webdriver.chrome.driver=" . $driverPath);

# Chromeを起動しphpスクリプトを実行
$driver = ChromeDriver::start();
$driver->get('http://localhost:8000/230110_mysql_html.php');

?>

<html>
<head>
<title>MySQL TOOL</title>
</head>
<body>
<?php

exec('mysqladmin ping', $out, $ret);
echo $out[0];
echo "\n";

?>
</body>
</html>

[Python] 344 MySQL自動検索全テーブル対象

[M1 Mac, Big Sur 11.7.2, Python 3.10.4, MySQL 8.0.31]

前の記事は1つのテーブルを対象とする自動検索でしたが、今回は全テーブルを対象にしました。

これまでネット情報を見渡してきて、データベース技術者の方々には案外プログラミングが苦手な方が多いのではないかと感じました。修得すべきスキルが広範なためプログラミングまで手が回らないのでしょうか。

import time,datetime,subprocess,os
import MySQLdb

# MySQLの起動確認
proc = subprocess.run("mysqladmin ping", shell=True, stdout= subprocess.PIPE, stderr = subprocess.PIPE)
ping_result = proc.stdout.decode('UTF-8')
print(ping_result)

# 起動していなければ起動
detect = "alive" in ping_result
if detect == False:
    os.system('mysql.server start')
else:
    print("MySQLは起動しています")

# MySQLに接続
conn = MySQLdb.connect(user='root')
cur = conn.cursor()

# 処理時間測定開始
start = time.time()

# データベースdataを選択
select_db = f"use data"
cur.execute(select_db)

# 全テーブルを取得
search_tables = f"show tables"
cur.execute(search_tables)
tables = cur.fetchall()
# print(f"tables : {tables}")

# 検索
for table in tables:
    print(f"table : {table}")
    table2 = str(table).replace('(','').replace(')','').replace(",",'').replace("'",'`')
    search = f"SELECT * FROM {table2} WHERE `Name` LIKE '%suzuki%'"
    cur.execute(search)
    rows = cur.fetchall()
    for row in rows:
        print(row)

    # 処理時間算出(秒)
    process_time = time.time() - start
    td = datetime.timedelta(seconds = process_time).total_seconds()

    # 小数点第２位まで表示
    td_2f = f'{td:.2f}'
    print(td_2f)

conn.close()
os.system('mysql.server stop')

[Python] 343 MySQL自動検索

[M1 Mac, Big Sur 11.7.2, Python 3.10.4, MySQL 8.0.31]

MySQLを起動、ログイン、データベース選択、検索まで自動的に行うスクリプトを作成しました。

処理時間はphpMyAdminよりも24%長く掛かってしまいました。

データベースを作ってしまえばあとは検索するだけなので、開発環境Aの方が総合的に優れていると取りあえず判断しました。

ターミナルでのMySQL管理に期待したのですが、なんとも残念な結果です。

ただ全テーブル内検索の場合は対象テーブルが400以上あってハードにかなり負荷をかけるため、単純に処理時間が比例するとも限りません。

もう少し検討が必要かもしれません。

import time,datetime,subprocess,os
import MySQLdb

# MySQLの起動確認
proc = subprocess.run("mysqladmin ping", shell=True, stdout= subprocess.PIPE, stderr = subprocess.PIPE)
ping_result = proc.stdout.decode('UTF-8')
print(ping_result)

# 起動していなければ起動
detect = "alive" in ping_result
if detect == False:
    os.system('mysql.server start')
else:
    print("MySQLは起動しています")

# MySQLに接続
conn = MySQLdb.connect(user='root')
cur = conn.cursor()

# 処理時間測定開始
start = time.time()

# データベースtestを選択
select_db = f"use test"
cur.execute(select_db)

# 検索
search = f"SELECT * FROM `TableA` WHERE `Address` LIKE '%東京都%'"
cur.execute(search)
rows = cur.fetchall()
for row in rows:
    print(row)

# 処理時間算出(秒)
process_time = time.time() - start
td = datetime.timedelta(seconds = process_time).total_seconds()

# 小数点第２位まで表示
td_2f = f'{td:.2f}'
print(td_2f)

conn.close()
os.system('mysql.server stop')

[Python] 342 MySQL自動起動ターミナルコマンド

[M1 Mac, Big Sur 11.7.2, Python 3.10.4, MySQL 8.0.31]

HomebrewからインストールしたMySQLを自動起動させるスクリプトを書きました。パスワード入力は無効にしています。

Pythonはデータ型を意識しなくてもいいのでメモ感覚で楽に書けますね。

import time,datetime,subprocess,os

start = time.time()

# MySQLの起動確認
proc = subprocess.run("mysqladmin ping", shell=True, stdout= subprocess.PIPE, stderr = subprocess.PIPE)
ping_result = proc.stdout.decode('UTF-8')
print(ping_result)

# 起動していなければ起動
detect = "alive" in ping_result
if detect == False:
    os.system('mysql.server start')
else:
    print("MySQLは起動しています")

# 処理時間算出(秒)
process_time = time.time() - start
td = datetime.timedelta(seconds = process_time).total_seconds()

# 小数点第２位まで表示
td_2f = f'{td:.2f}'
print(td_2f)

[Python] 341 MySQL高速化非Webサーバ環境

[M1 Mac, Big Sur 11.7.2, Python 3.10.4, MySQL 8.0.31]

MySQL高速化のため非Webサーバ環境でデータベースを作成してみました。

その結果、テーブルの作成時間を３割削減できました。

クエリやテーブルの最適化など他の方法についても試していきます。

import MySQLdb
import pandas as pd
import time,datetime,glob,csv

start = time.time()

num = 1
for file in glob.glob(f'/*.csv'):
    # CSVファイル名を抽出しtable名にする
    tablename = file[-12:-4]
    # print(tablename)

    column_title = ['A', 'B', 'C', 'D', 'E']
    column_type = ['varchar(256)','varchar(512)','varchar(512)','int(9)','int(8)']

    # SQL文に使う列名&データ型の文字列を作成する
    column_l = []
    for ti,ty in zip(column_title,column_type):
        column = ti + ' ' + str(ty)
        column_l.append(column)

    # print(column_l)

    # SQL仕様に整形
    column_l_str = str(column_l).replace('[','(').replace(']',')').replace("'",'')

    # print(column_l_str)

    # MySQLに接続
    conn = MySQLdb.connect(user='root')
    cur = conn.cursor()

    # データベースdataにtableを作成する
    sql = f"create table test.{tablename} {column_l_str} CHARACTER SET utf8mb4"
    cur.execute(sql)
    cur.execute('begin')

    # CSVファイルを読み込み、各行をtableに挿入する
    with open(file, 'rt', encoding='UTF-8') as f:
        reader = csv.reader(f)
        for i,row in enumerate(reader):
            # print(f'row {row}')
            if i != 0:
                row_str = str(row).replace('[','(').replace(']',')')
                # print(f'{i+1}:{row_str}')
                sql = f'insert into test.{tablename} values {row_str}'
                cur.execute(sql)

    cur.execute('commit')
    conn.close()

    # 処理時間算出(秒)
    process_time = time.time() - start
    td = datetime.timedelta(seconds = process_time).total_seconds()

    # 小数点第２位まで表示
    td_2f = f'{td:.2f}'
    print(f'{num}:{td_2f}')

    num = num + 1

[Python] 340 MySQLによるDB作成 Apple Silicon, MAMP

[M1 Mac, Big Sur 11.7.2, Python 3.10.4, MAMP 6.7, MySQL 5.7.39]

数千万件の巨大データベースを作成するため久しぶりにMySQLをいじっています。MAMPの設定は2021年4月以来なので1年9ヶ月ぶりになります。

Intel Macでは問題なかったMAMPのポート設定(Apache:80, MySQL:3306)では下図のエラーになるためデフォルト設定(Apache:8888, MySQL:8889)で使っています。

phpMyAdminで管理していますが、データが大きすぎるからか動作が重いように感じます。オラクルが開発しているMySQL Workbenchを試してみたいです。

# /etc/apache2/httpd.confに以下を追記

ServerName localhost:80

~ $ sudo httpd -k restart
~ $ sudo lsof -i:80
COMMAND   PID USER   FD   TYPE             DEVICE SIZE/OFF NODE NAME
httpd   37303 root    4u  IPv6 0xf22e6e946f960a71      0t0  TCP *:http (LISTEN)
httpd   41597 _www    4u  IPv6 0xf22e6e946f960a71      0t0  TCP *:http (LISTEN)

# httpdは正常に動作しているがMAMPはエラー。MAMPの不具合？

import csv, mysql.connector
import pandas as pd
import time,datetime,glob

# 処理時間を測定
start = time.time()

config = {
    'user': 'root',
    'password': 'root',
    'host': 'localhost',
    'port': 8889,
    'raise_on_warnings': True
}

num = 1
for file in glob.glob(f'/*.csv'):
    # CSVファイル名を抽出しtable名にする
    tablename = file[-12:-4]
    # print(tablename)

    column_title = ['A', 'B', 'C', 'D', 'E']
    column_type = ['varchar(256)','varchar(512)','varchar(512)','int(9)','int(8)']

    # SQL文に使う列名&データ型の文字列を作成する
    column_l = []
    for ti,ty in zip(column_title,column_type):
        column = ti + ' ' + str(ty)
        column_l.append(column)

    # print(column_l)

    # SQL仕様に整形
    column_l_str = str(column_l).replace('[','(').replace(']',')').replace("'",'')

    # print(column_l_str)

    # MySQLに接続
    conn = mysql.connector.connect(**config)
    cur = conn.cursor()

    # データベース名dataにtableを作成する(4バイト文字対応)
    sql = f"create table data.{tablename} {column_l_str} CHARACTER SET utf8mb4"
    cur.execute(sql)
    cur.execute('begin')

    # CSVファイルを読み込み、各行をtableに挿入する
    with open(file, 'rt', encoding='UTF-8') as f:
        reader = csv.reader(f)
        for i,row in enumerate(reader):
            # print(f'row {row}')
            if i != 0:
                row_str = str(row).replace('[','(').replace(']',')')
                # print(f'{i+1}:{row_str}')
                sql = f'insert into data.{tablename} values {row_str}'
                cur.execute(sql)

    cur.execute('commit')
    conn.close()

    # 処理時間算出(秒)
    process_time = time.time() - start
    td = datetime.timedelta(seconds = process_time).total_seconds()

    # 小数点第２位まで表示
    td_2f = f'{td:.2f}'
    print(f'{num}:{td_2f}')

    num = num + 1

日	月	火	水	木	金	土
1	2	3	4	5	6	7
8	9	10	11	12	13	14
15	16	17	18	19	20	21
22	23	24	25	26	27	28
29	30	31