Pythonスクリプト【MySQLにリモート接続しよう⑦～平均値】

2021年3月4日

前回

PythonスクリプトからMySQLのDBのデータの平均値を求めよう

今回は新しくテーブルを作成して、そのデータの平均値を取得してみましょう。
テーブル名を「tb2」として、以下のようなカラムを持たせます。

カラム名	タイプ	内容	備考
name	VARCHAR(14)	氏名	NOT NULL
lang	INT(3)	国語の点数
math	INT(3)	数学の点数
engl	INT(3)	英語の点数

テーブルは、第2回の記事で使ったスクリプトの11行目を以下のように修正して作成します。

### 11行目
sql = "CREATE TABLE tb2 (name VARCHAR(14) NOT NULL, lang INT(3), math INT(3), engl INT(3))"

1 2	### 11行目 sql = "CREATE TABLE tb2 (name VARCHAR(14) NOT NULL, lang INT(3), math INT(3), engl INT(3))"

データは、第3回の記事で使ったスクリプトの11行目と14行目を以下のように修正して挿入します。

### 11行目
members = [('山田太郎',100,80,100),('鈴木新之助',50,40,60),('佐藤明',30,80,20),('John Tanaka',18,85,99)]

### 14行目
sql = "INSERT INTO tb2 (name, lang, math, engl) VALUES (%s, %s, %s, %s)"

### 11行目

members = [('山田太郎',100,80,100),('鈴木新之助',50,40,60),('佐藤明',30,80,20),('John Tanaka',18,85,99)]

### 14行目

sql = "INSERT INTO tb2 (name, lang, math, engl) VALUES (%s, %s, %s, %s)"

平均値を求める関数

SQLで平均値を求めるには、AVG()関数を使います。

AVG([DISTINCT] 値)

DISTINCTオプションを使用すると、重複を除いた値で平均を出します。

値をまるめる関数

SQLで値をまるめるには、ROUND()関数を使います。

ROUND(値, 小数点以下の桁数)

平均値を求めるPythonスクリプト

平均値を求めるPythonスクリプトは、以下のようになります。
先にデータの一覧を出力して、そのあと各教科の平均値を出力します。
なお、Pythonでは全角も半角も1文字と計算されてしまい、全角と半角が混ざっていると表示する位置がずれてしまいます。
そのため、29～31行目で文字が何バイトあるかを計算して、表示桁数を揃えています。
全角に関する詳しい内容は、こちらの記事を参照してください。
また、平均値は1行しかないため、カーソル値の取得に fetchone()関数を使っています。

### インポート
import unicodedata
import mysql.connector

### DB接続
cnx = mysql.connector.connect(host='192.168.11.10', user='user1', password='password1', database='db1')

### カーソル作成
cursor = cnx.cursor()

### SELECT文作成
sql1 = "SELECT * FROM tb2"
sql2 = "SELECT ROUND(AVG(lang),1), ROUND(AVG(math),1), ROUND(AVG(engl),1) FROM tb2"

### データ取得実行
cursor.execute(sql1)

### ヘッダ出力
print("{} {} {} {}".format("氏名          ", "国語", "数学", "英語"))
print("{} {} {} {}".format("--------------", "----", "----", "----"))

### データ出力
for col1, col2, col3, col4 in cursor:

    ### カウンター初期化
    count = 0

    ### キャラクター数取得
    for chara in col1:
        if unicodedata.east_asian_width(chara) in "FWA":
            count += 1

    ### データ出力
    print("{:{width}s} {:4d} {:4d} {:4d}".format(col1, col2, col3, col4, width=14-count))

### 改行
print()

### データ平均値取得実行
cursor.execute(sql2)

### ヘッダ出力
print("{} {} {}".format("国語", "数学", "英語"))
print("{} {} {}".format("----", "----", "----"))

### データ出力
col1, col2, col3 = cursor.fetchone()
print("{:4} {:4} {:4}".format(col1, col2, col3))

### カーソルクローズ
cursor.close()

### DB切断
cnx.close()

### インポート

import unicodedata

import mysql.connector

### DB接続

cnx = mysql.connector.connect(host='192.168.11.10', user='user1', password='password1', database='db1')

### カーソル作成

cursor = cnx.cursor()

### SELECT文作成

sql1 = "SELECT * FROM tb2"

sql2 = "SELECT ROUND(AVG(lang),1), ROUND(AVG(math),1), ROUND(AVG(engl),1) FROM tb2"

### データ取得実行

cursor.execute(sql1)

### ヘッダ出力

print("{} {} {} {}".format("氏名 ", "国語", "数学", "英語"))

print("{} {} {} {}".format("--------------", "----", "----", "----"))

### データ出力

for col1, col2, col3, col4 in cursor:

### カウンター初期化

count = 0

### キャラクター数取得

for chara in col1:

if unicodedata.east_asian_width(chara) in "FWA":

count += 1

### データ出力

print("{:{width}s} {:4d} {:4d} {:4d}".format(col1, col2, col3, col4, width=14-count))

### 改行

print()

### データ平均値取得実行

cursor.execute(sql2)

### ヘッダ出力

print("{} {} {}".format("国語", "数学", "英語"))

print("{} {} {}".format("----", "----", "----"))

### データ出力

col1, col2, col3 = cursor.fetchone()

print("{:4} {:4} {:4}".format(col1, col2, col3))

### カーソルクローズ

cursor.close()

### DB切断

cnx.close()

プログラムを実行すると、以下のように結果が出力されます。

mysql7-1

次回

Pythonスクリプト【MySQLにリモート接続しよう⑧～合計値】

-PROGRAM, PYTHON
-mysql

comment コメントをキャンセル

: JavaScriptで三角関数を使ってアナログ時計(時字付)を作成しよう【初心者向け】

前回 JavaScriptで三角関数を使ってアナログ時計(目盛り付)を作成しよう【初心者向け】アナログ時計に時字を表示しよう前回はアナログ時計に1分単位の目盛りを追加しました。今回は前回のアナログ ...

JAVASCRIPT PROGRAM

: Pygame超入門【第13回写真複数表示】

写真を複数枚表示しよう今回はひとつの画面上に写真を複数枚表示させてみましょう。写真を複数枚表示するプログラム以下のプログラムは、4枚の写真を画面の中央を ...

PROGRAM PYTHON

bash実践シェルスクリプト【ログバックアップ】

ログをバックアップするシェルスクリプトです。このシェルスクリプトの仕様は、以下のようになります。

#!/bin/bash

### 定数
readonly LIST=backup.lst        # バックアップ対象リスト
readonly LOG=backup.log         # バックアップ結果ログ

### 変数
INF_COUNT=0                     # バックアップ成功数
ERR_COUNT=0                     # バックアップ失敗数
YMDHMS=$(date +"%Y%m%d%H%M%S")  # 現在年月日時分秒

### リストファイル確認
if [[ ! -r "$LIST" ]]
then
  echo "ERR [$LIST] リストファイルが存在しない" >> $LOG 2>&1
  exit 1
fi

### リストファイル読み込み
while read target pattern backup option dummy
do
  ### コメントまたは空白の場合は読み飛ばす
  if [[ "$target" == \#* ]] || [[ -z "$target" ]]
  then
    continue
  fi

  ### パラメーター不正
  if [[ "$pattern" == \#* ]] || [[ -z "$pattern" ]] || \
     [[ "$backup"  == \#* ]] || [[ -z "$backup"  ]] || \
     [[ "$option"  == \#* ]] || [[ -z "$option"  ]]
  then
    (( ERR_COUNT++ ))
    echo "ERR [$target][$pattern][$backup][$option] パラメーター不正" >> $LOG 2>&1
    continue
  fi

  ### バックアップ先ディレクトリ確認
  if [[ ! -d "$backup" ]]
  then
    (( ERR_COUNT++ ))
    echo "ERR [$backup] ディレクトリーなし" >> $LOG 2>&1
    continue
  fi

  ### バックアップ対象ファイル取得
  files=$(ls -1 $target/$pattern 2>/dev/null)

  ### 該当ファイルチェック
  if [[ -z "$files" ]]
  then
    echo "WAR [$target/$pattern] 該当ファイルなし" >> $LOG 2>&1
    continue
  fi

  ### オプション毎のコマンドを作成
  for buf in $files
  do
    case "$option" in
      c)  # コピーのみ
        cmd_cpy="/bin/cp -p $buf $backup/$(basename $buf).$YMDHMS"
        cmd_del=""
        ;;
      d)  # コピー後に削除
        cmd_cpy="/bin/cp -p $buf $backup/$(basename $buf).$YMDHMS"
        cmd_del="/bin/rm -f $buf"
        ;;
      *)  # オプション不正
        (( ERR_COUNT++ ))
        echo "ERR [$target/$pattern][$option] オプション不正" >> $LOG 2>&1
        break
        ;;
    esac

    ### バックアップコマンド実行
    $cmd_cpy > /dev/null 2>&1
    if (( $? == 0 ))
    then
      (( INF_COUNT++ ))
      echo "INF CPY $buf" >> $LOG 2>&1

      ### 削除コマンド確認
      if [[ -z "$cmd_del" ]]
      then
        continue
      fi

      ### 削除コマンド実行
      $cmd_del > /dev/null 2>&1
      if (( $? == 0 ))
      then
        echo "INF DEL $buf" >> $LOG 2>&1
      else
        (( ERR_COUNT++ ))
        echo "ERR DEL $buf" >> $LOG 2>&1
      fi
    else
      (( ERR_COUNT++ ))
      echo "ERR CPY $buf" >> $LOG 2>&1
    fi
  done
done < "$LIST"

### 総合エラーチェック
echo "INF バックアップ数=[$INF_COUNT] バックアップエラー数=[$ERR_COUNT]" >> $LOG 2>&1

### 終了処理
if (( ERR_COUNT == 0 ))
then
  exit 0
else
  exit 1
fi

100

101

102

103

104

105

106

107

108

109

110

111

112

113

#!/bin/bash

### 定数

readonly LIST=backup.lst # バックアップ対象リスト

readonly LOG=backup.log # バックアップ結果ログ

### 変数

INF_COUNT=0 # バックアップ成功数

ERR_COUNT=0 # バックアップ失敗数

YMDHMS=$(date +"%Y%m%d%H%M%S") # 現在年月日時分秒

### リストファイル確認

if [[ ! -r "$LIST" ]]

then

echo "ERR [$LIST] リストファイルが存在しない" >> $LOG 2>&1

exit 1

### リストファイル読み込み

while read target pattern backup option dummy

### コメントまたは空白の場合は読み飛ばす

if [[ "$target" == \#* ]] || [[ -z "$target" ]]

then

continue

### パラメーター不正

if [[ "$pattern" == \#* ]] || [[ -z "$pattern" ]] || \

[[ "$backup" == \#* ]] || [[ -z "$backup" ]] || \

[[ "$option" == \#* ]] || [[ -z "$option" ]]

then

(( ERR_COUNT++ ))

echo "ERR [$target][$pattern][$backup][$option] パラメーター不正" >> $LOG 2>&1

continue

### バックアップ先ディレクトリ確認

if [[ ! -d "$backup" ]]

then

(( ERR_COUNT++ ))

echo "ERR [$backup] ディレクトリーなし" >> $LOG 2>&1

continue

### バックアップ対象ファイル取得

files=$(ls -1 $target/$pattern 2>/dev/null)

### 該当ファイルチェック

if [[ -z "$files" ]]

then

echo "WAR [$target/$pattern] 該当ファイルなし" >> $LOG 2>&1

continue

### オプション毎のコマンドを作成

for buf in $files

case "$option" in

c) # コピーのみ

cmd_cpy="/bin/cp -p $buf $backup/$(basename $buf).$YMDHMS"

cmd_del=""

;;

d) # コピー後に削除

cmd_cpy="/bin/cp -p $buf $backup/$(basename $buf).$YMDHMS"

cmd_del="/bin/rm -f $buf"

;;

*) # オプション不正

(( ERR_COUNT++ ))

echo "ERR [$target/$pattern][$option] オプション不正" >> $LOG 2>&1

break

;;

esac

### バックアップコマンド実行

$cmd_cpy > /dev/null 2>&1

if (( $? == 0 ))

then

(( INF_COUNT++ ))

echo "INF CPY $buf" >> $LOG 2>&1

### 削除コマンド確認

if [[ -z "$cmd_del" ]]

then

continue

### 削除コマンド実行

$cmd_del > /dev/null 2>&1

if (( $? == 0 ))

then

echo "INF DEL $buf" >> $LOG 2>&1

else

(( ERR_COUNT++ ))

echo "ERR DEL $buf" >> $LOG 2>&1

else

(( ERR_COUNT++ ))

echo "ERR CPY $buf" >> $LOG 2>&1

done

done < "$LIST"

### 総合エラーチェック

echo "INF バックアップ数=[$INF_COUNT] バックアップエラー数=[$ERR_COUNT]" >> $LOG 2>&1

### 終了処理

if (( ERR_COUNT == 0 ))

then

exit 0

else

exit 1

メモ 20行目のdu ...

PROGRAM SHELL

: tkinter超入門【第13回チェックボタン②】

チェックボタンの状態を表示しよう前回はCheckbuttonウィジェットを使ってチェックボタンを表示させました。今回はチェックボタンの状態を表示してみましょう。 &nb ...

PROGRAM PYTHON

: Pygame超入門【第17回写真モザイク処理】

写真にモザイクをかけよう今回は写真にモザイクをかけてみましょう。モザイクは一定の範囲ごとにピクセルの色を平均して、その範囲を平均値の色で塗りつぶします。 ...

PROGRAM PYTHON

Pythonスクリプト【MySQLにリモート接続しよう⑥～データ削除】

Pythonスクリプト【MySQLにリモート接続しよう⑧～合計値】