end0tknr's kipple - web写経開発

太宰府天満宮の狛犬って、妙にカワイイ

csvを含むzipをjszip+papaparse for javascriptで読み sql.js で sql select

sql.js によるブラウザでの sqlite3 操作 - end0tknr's kipple - web写経開発

先日の上記entryでは sql.js で sqlite3のバイナリファイルを扱いましたが、 今回は、csvを含むzipをjszip+papaparse for javascriptで読み、sql.js で sql select

<!DOCTYPE html>
<html lang="ja">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<script src="https://cdnjs.cloudflare.com/ajax/libs/sql.js/1.10.2/sql-wasm.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/jszip/3.10.1/jszip.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/PapaParse/5.4.1/papaparse.min.js"></script>
<script src="./csv_db.js"></script>
</head>
<body>
  <button onclick="csv_db.load_csv_zip()">CSV-ZIP選択</button>
  <button onclick="csv_db.select_tbl()">SQL SELECT * FROM $TABLE</button>
  <div id="sql_result"></div>
  
  <script>let csv_db = new CsvDb();</script>
</body>
</html>
'use strict';

class CsvDb {
    constructor() {}
    
    async load_csv_zip() {
        // windowsのコモン・ダイアログ?で zipを開く
        let zip_contents = await this.open_file_picker()
        // jpzipで zipを解凍
        let zip = new JSZip()
        await zip.loadAsync( zip_contents.arrayBuffer() )

        const SQL = await initSqlJs({
            locateFile:file=>
            `https://cdnjs.cloudflare.com/ajax/libs/sql.js/1.10.2/${file}`
        })
        this.db = new SQL.Database()
        this.table_names = []
        
        let csv_datas = []
        // zip に含まれる csv or tsv毎に処理
        for (const filename in zip.files) {
            const zip_content = zip.files[filename]
            if( zip_content.dir ) continue
            
            let parsed_filename = this.parse_csv_filename( filename )
            let tbl_name = parsed_filename[0]
            let csv_rows_str = await zip_content.async('text')
            // papa parseで csv or tsvをパース
            // https://www.papaparse.com/docs
            let tbl_rows   = Papa.parse(csv_rows_str.trim(),{delimiter:"\t"}).data
            let tbl_header = tbl_rows.shift()

            if(tbl_name=="planed_zumens") continue
            if(tbl_rows.length==0) continue
            // sql.jsに対し csv or tsv毎に create table, insert
            let sql_create =
                `CREATE TABLE  ${tbl_name}(${tbl_header.map(h=>`${h} TEXT`).join(',')});`
            let sql_insert =
                `INSERT INTO ${tbl_name}(${tbl_header.join(',')})
VALUES ${tbl_rows.map(r=>`(${r.map(r=>`'${r}'`).join(',')})`).join(',')};`
            this.db.run(sql_create).run(sql_insert)
            this.table_names.push(tbl_name)
        }

        document.querySelector("#sql_result").innerHTML =
            "読み込み完了. " + this.table_names.join(" ")

    }

    parse_csv_filename( org_filename ){
        let re_patern = /([^\./]+)\.(csv|tsv)$/i
        let re_result = re_patern.exec(org_filename)
        if(! re_result) return []

        let basename = re_result[1]
        let ext      = re_result[2]
        return [basename, ext]
    }
    
    // https://qiita.com/lumis/items/dd209d52c9bc7ce39db6
    async open_file_picker() {
        const pick_opts = {
            types      : [{description: "CSVを含むZIP",
                           accept:{'application/zip': ['.zip']} } ],
            multiple   : false,
            excludeAcceptAllOption: true }
        //「excludeAcceptAllOption: true 」は
        //「すべてのファイル (*.*)」の選択を無効にし
        // types に指定したファイルのみ選択okにする
        
        const [fh] = await showOpenFilePicker(pick_opts)
        const file = await fh.getFile()
        return file
    }

    select_tbl(){
        let tbl_name = this.table_names[1]
        let sql = `
SELECT * from ${tbl_name}
limit 10`;
        const stmt = this.db.prepare(sql);
        stmt.bind();
        let sql_result = "";
        while( stmt.step() ) {
            let row = stmt.getAsObject();
            sql_result += JSON.stringify(row);
        }
        document.querySelector("#sql_result").innerHTML = sql_result;
    }
}

pythonにおける csv ファイルのメール添付送信

code snippet ですが、少々の修正で、動作すると思います

#!python
# -*- coding: utf-8 -*-
from email.mime.text        import MIMEText
from email.mime.multipart   import MIMEMultipart
from email.mime.application import MIMEApplication
import csv
import io
import smtplib
import socket

CONF={
    "mail": {"smtp_server" : "xxx.xmile.sexy.co.jp",
             "port"    : 587,
             "user_id" : "ないしょ",
             "user_pw" : "ないしょ",
             "from"    : "ないしょ@example.com",
             "to"      : ["ないしょ@example.com"],
             "cc"      : [],
             },
}


def main():
    csv_for_espis = <省略>
    
    # 実fileに書き込まず、CSV添付する為、StringIO()を利用
    dummy_csv_file = io.StringIO()
    
    csv_headers = [
        "UserD","氏名","所属部所コード","日付","曜日",
        "入室時刻","退室時刻","最新入室時刻",
        "所属部所名","入室回数","退室回数" ]
    writer = csv.DictWriter(dummy_csv_file,
                            fieldnames=csv_headers)
    writer.writeheader()
    writer.writerows(csv_for_espis)
    csv_data = dummy_csv_file.getvalue()
    io.StringIO().close()

    # windows用csvの為、文字コードは cp932
    csv_data = csv_data.encode("cp932","ignore")

    # メール添付で送信
    notify_by_email( csv_data )
    
    logger.info("DONE "+func_name)


def notify_by_email(start_end_csv_contents):

    message = MIMEMultipart()
    message["Subject"] = "連携CSVデータ"
    message["From"]    = CONF["mail"]["from"]
    
    msg_body_tmpl = """e-SPIS の ご担当者様へ

※このメールは {hostname} {ip} にある
   RPA ({script}) から自動送信しています

連携用CSVを添付致しますので、ご確認をお願いします。

--
ないしょの署名
"""
    msg_body = msg_body_tmpl.format(
        hostname = socket.gethostname(),
        ip       = socket.gethostbyname(socket.gethostname()),
        script   = __file__ )

    message.attach( MIMEText(msg_body,"plain", "utf-8") )

    attachment = MIMEApplication(start_end_csv_contents)
    attachment.add_header("Content-Disposition", "attachment",
                          filename="E_SPIS_WORK.CSV" )
    message.attach( attachment )

    tos = []
    for mailto in CONF["mail"]["to"]:
        tos.append(mailto)

    if len(tos) == 0 :
        return

    to_addrs = tos
    
    message["To"]   = ",".join(tos)
    message["Cc"]   = ",".join( CONF["mail"]["cc"] )
    to_addrs += CONF["mail"]["cc"]
        
    try:
        server = smtplib.SMTP(CONF["mail"]["smtp_server"], CONF["mail"]["port"])
        #server.set_debuglevel(True)
        server.login( CONF["mail"]["user_id"], CONF["mail"]["user_pw"])
        server.send_message( message, to_addrs=to_addrs )
    except Exception as e:
        print(e)
    server.quit()
    


if __name__ == '__main__':
    main()

sql.js によるブラウザでの sqlite3 操作

web assemblyのおかげでしょうか、 最近は javascriptで sqlite3 のデータベースファイルを そのまま扱えるらしい。

参考url

install sqlite3

DOS> wsl

$ cat /etc/redhat-release
Red Hat Enterprise Linux release 8.7 (Ootpa)

$ sudo yum install sqlite

$ /usr/bin/sqlite3 --version
3.26.0 2018-12-01 12:34:55 bf8c1b2b7a5960c282e<略>38alt1

create ddl

-- 教師
DROP TABLE IF EXISTS teachers;
CREATE TABLE teachers ( teacher_id   BIGINT PRIMARY KEY,
                        teacher_name VARCHAR(64) );
INSERT INTO teachers VALUES (101,'先生一郎');
INSERT INTO teachers VALUES (102,'先生二郎');
INSERT INTO teachers VALUES (103,'先生三郎');

-- 学生
DROP TABLE IF EXISTS students;
CREATE TABLE students ( student_id BIGINT PRIMARY KEY,
                        student_name VARCHAR(64) );
INSERT INTO students VALUES (301,'生徒一子');
INSERT INTO students VALUES (302,'生徒二子');
INSERT INTO students VALUES (303,'生徒三子');
INSERT INTO students VALUES (304,'生徒四子');
INSERT INTO students VALUES (305,'生徒五子');
INSERT INTO students VALUES (306,'生徒六子');
INSERT INTO students VALUES (307,'生徒七子');
INSERT INTO students VALUES (308,'生徒八子');
INSERT INTO students VALUES (309,'生徒九子');

-- 講座
DROP TABLE IF EXISTS courses;
CREATE TABLE courses (
  course_id   VARCHAR(16)  PRIMARY KEY,
  course_name VARCHAR(128) NOT NULL,
  teacher_id  BIGINT,
  FOREIGN KEY (teacher_id) REFERENCES teachers(teacher_id) );
INSERT INTO courses VALUES (1,'国語',101);
INSERT INTO courses VALUES (2,'算数',102);
INSERT INTO courses VALUES (3,'理科',103);

-- 受講
DROP TABLE IF EXISTS student_courses;
CREATE TABLE student_courses (
  course_id  VARCHAR(16),
  student_id BIGINT,
  PRIMARY KEY (course_id, student_id),
  FOREIGN KEY (course_id)  REFERENCES courses(course_id),
  FOREIGN KEY (student_id) REFERENCES students(student_id));

INSERT INTO student_courses VALUES (1,301);
INSERT INTO student_courses VALUES (1,302);
INSERT INTO student_courses VALUES (1,303);
INSERT INTO student_courses VALUES (1,306);
INSERT INTO student_courses VALUES (1,307);
INSERT INTO student_courses VALUES (1,308);
INSERT INTO student_courses VALUES (1,310);
INSERT INTO student_courses VALUES (2,301);
INSERT INTO student_courses VALUES (2,309);
INSERT INTO student_courses VALUES (3,310);

create database

$ /usr/bin/sqlite3 test.sqlite3 < test_ddl.sql

test sql.js

<meta charset="utf8" />
<html>
<script src="https://cdnjs.cloudflare.com/ajax/libs/sql.js/1.10.2/sql-wasm.js">
</script>
<script>
  async function init_sqljs() {
      const sqlPromise = initSqlJs({
          locateFile:file=>`https://cdnjs.cloudflare.com/ajax/libs/sql.js/1.10.2/${file}`
      });
      
      const dataPromise = fetch("test.sqlite3").then(res => res.arrayBuffer());
      const [SQL, buf] = await Promise.all([sqlPromise, dataPromise])
      const db = new SQL.Database(new Uint8Array(buf));

      let sql = `
SELECT c.course_name, t.teacher_name, s.student_name
FROM student_courses sc
JOIN students s ON(sc.student_id=s.student_id)
JOIN courses  c ON(sc.course_id=c.course_id)
JOIN teachers t ON(c.teacher_id=t.teacher_id)
WHERE s.student_name=?
ORDER BY c.course_id, s.student_id`;
      const stmt = db.prepare(sql);
      stmt.bind(["生徒一子"]);
      //console.log( stmt );
      
      let sql_result = "";
      while( stmt.step() ) {
          let row = stmt.getAsObject();
          sql_result += JSON.stringify(row);
      }
      document.querySelector("#sql_result").innerHTML = sql_result;
      
      stmt.free();
      //const dbBinary = db.export();
      db.close();
  }
  
  init_sqljs();
</script>
<body>
  https://sql.js.org
  https://github.com/sql-js/sql.js
  Output is here.
  <div id="sql_result"></div>d
</body>
</html>

sybase dbにある各テーブルの行数やファイルサイズの表示

mysqlのinformation_schema.tablesによるテーブルサイズの表示 - 改 - end0tknr's kipple - web写経開発

以前の上記entryにある通り、mysqlでは information_schema.tables に対しての select sqlで取得できますが、 sybase db の場合

select
     convert(varchar(30),o.name)  AS table_name
    ,row_count(db_id(), o.id)     AS row_count
    ,data_pages(db_id(), o.id, 0) * (@@maxpagesize/1024) AS size_kb
from sysobjects o
where type = 'U'
order by table_name

go

または isqlで接続し、以下

sp_spaceused <table>

go

認知的焦点化理論 - 利己的な人は長い目で見ると損

先程のentryにある「ダニングクルーガー効果」に関連し 「認知的焦点化理論」の存在を思い出したので、メモ

例えば、以下のurlが分かりやすい

利己的な人は長い目で見ると損をする!? 認知的焦点化理論など、運に関する3つの研究を紹介! | データで越境者に寄り添うメディア データのじかん

Highcharts の オレオレ テンプレート

久しぶりに www.highcharts.com を触ったら、随分と忘れていたので、メモ

https://www.highcharts.com/docs/index

↓こう書くと↑こう表示されます

<!DOCTYPE HTML>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
</head>
<body>
  <a href="https://www.highcharts.com/docs/index">Highcharts documents</a>
  <!-- high chartのrendering先 -->
  <div id="chart_container" style="width:800px; height:400px;"></div>
  
  <script src="https://code.highcharts.com/highcharts.js"></script>
  <script src="my_highcharts.js"></script>
  <script>
    let my_charts = new MyHighCharts();
    my_charts.init_chart("chart_container");
  </script>
</body>
</html>
'use strict';

let chart_tmpl = {
    chart   :{renderTo: 'html_id', // 表示先となるhtml element id
              zoomType: 'xy',
              defaultSeriesType:'column'},
    title   :{text: 'end0tknrのHighchartsテスト',
              floating:true,
              style:{fontWeight:"medium",fontSize:"small"}},
    subtitle:{text: '' },
    xAxis: {categories :[], // X軸(横軸)の各値
            startOnTick:false, endOnTick:false},
    yAxis: [ // Y軸(縦軸)は、複数表示可能
        {title :{text:'温度', style:{color:'#89A54E'}},
         labels:{formatter:()=>{return this.value+'℃'},style:{color:'#89A54E'}},
         opposite: true  //trueの場合,グラフ右側に表示
        },
        {title :{text:'降水量', style:{color:'#4572A7'}},
         labels:{formatter:()=>{return this.value +'mm'},style:{color:'#4572A7'}},
         gridLineWidth: 0},
        {title :{text:'気圧', style:{color:'#AA4643'}},
         labels:{formatter:()=>{return this.value +'mb'},style:{color:'#AA4643'}},
         gridLineWidth: 0,
         opposite: true}
    ],
    series:[
        {name:'降水1',color:'#4572A7',type:'column',yAxis:1,stack:'grp1',data:[]},
        {name:'降水2',color:'#BAD1E6',type:'column',yAxis:1,stack:'grp1',data:[]},
        {name:'降水3',color:'#F5F5F5',type:'column',yAxis:1,stack:'grp2',data:[]},
        {name:'気温', color:'#89A54E',type:'spline',/*spline=曲線*/      data:[]},
        {name:'気圧', color:'#AA4643',type:'',/*折れ線*/ yAxis:2,        data:[],
         marker:{enabled: false},     dashStyle:'shortdot'} ],
    tooltip: {formatter:()=>{var unit = {'降水量1': 'mm',
                                         '降水量2': 'mm',
                                         '降水量3': 'mm',
                                         '気温': '°C',
                                         '気圧': 'mb'}[this.series.name];
                             return ''+ this.x +': '+ this.y +' '+ unit; } },
    plotOptions:{ column: { stacking: 'normal' } },
    legend: {layout:'vertical', verticalAlign:'top',align:'left',
             x:120, y:80,       floating:true,      backgroundColor:'#FFF'},
    accessibility:{enabled:false}, //実行時に何やらwarningが表示される為
    credits      :{enabled:false}, //グラフ領域のhigh chartsクレジット非表示
};

class MyHighCharts {
    constructor() {}

    init_chart=(render_html_elm_id)=>{
        // グラフtemplateのdeep copy
        let chart_src =
            Object.assign({},JSON.parse(JSON.stringify(chart_tmpl)));
        // rendering先となるhtml element idの設定
        chart_src.chart.renderTo   = render_html_elm_id;
        // X軸の値
        chart_src.xAxis.categories = ['Jan','Feb','Mar','Apr','May','Jun',
                                      'Jul','Aug','Sep','Oct','Nov','Dec'];
        // X軸の値
        chart_src.series[0].data=
            [49.9,71.5,106.4,129.2,144.0,176.0,135.6,148.5,216.4,194.1,95.6,54.4];
        chart_src.series[1].data=
            [19.9,41.5,76.4,99.2,114.0,146.0,105.6,118.5,186.4,164.1,65.6,24.4];
        chart_src.series[2].data=
            [39.9,61.5,96.4,119.2,134.0,166.0,125.6,138.5,206.4,184.1,85.6,44.4];
        chart_src.series[3].data=
            [7.0,6.9,9.5,14.5,18.2,21.5,25.2,26.5,23.3,18.3,13.9,9.6];
        chart_src.series[4].data=
            [1016,  1016,  1015.9,1015.5,1012.3,1009.5,
             1009.6,1010.2,1013.1,1016.9,1018.2,1016.7];
        // グラフの実体化
        let new_chart = new Highcharts.Chart( chart_src );
    }
}

Sybase (SAP)では WHERE IN句に複数条件はNG

sqlのwhere in って、複数条件(カラム)を指定できるんですね - end0tknr's kipple - web写経開発

上記entryにある通り、mysqlでは WHERE IN句に複数条件を指定できましたが Sybase (SAP)ではNGらしい

なので、SQLの実行速度は遅いでしょうが

SELECT pla.plan_numw, pla.addition_num, pla.revision
FROM plan_attr pla
WHERE (pla.plan_num+pla.addition_num)
   in ('XXXA5001201','XXXA5002101')
go

のように複数カラムを文字列連結し、 疑似的?に複数条件を指定できるようにしてみます

openpyxl.utils for python による xcelのA1形式座標←→R1C1形式座標の変換

Excel::Writer::XLSX::Utility for perlで excelのA1形式座標←→R1C1形式座標の変換 - end0tknr's kipple - web写経開発

上記entryのようにperlでは Excel::Writer::XLSX::Utility を使用しますが、 pythonの場合、openpyxl.utils.column_index_from_string() や openpyxl.utils.openpyxl.utils.cell.get_column_letter() を使用するようです。

https://openpyxl.readthedocs.io/en/stable/api/openpyxl.utils.cell.html

openpyxl for python の load_workbook()で UserWarning: wmf image format is not supported so the image is being dropped

openpyxl for python で、とある xlsx を load_workbook() したところ、以下のエラー

C:\Users\end0t\python310\lib\site-packages\openpyxl\reader\drawings.py:63:
  UserWarning: wmf image format is not supported so the image is being dropped

wmf は、windows標準のvector画像ですが「not supported」であれば、しょうがいない

perl v.5.38から出力される Locale 'ja_JP.eucJP' is unsupported, and may crash the interpreter.

euc-jpで作成されたコンテンツを扱うサーバのperlをver.5.28→5.38へ更新。

すると、以下の「may crash」のような強め?の警告メッセージが表示。

$ export LANG=ja_JP.eucJP
$ /path/to/bin/perl sample-test.pl
Locale 'ja_JP.eucJP' is unsupported, and may crash the interpreter.

以下のurlによれば、perl 5.38からの仕様変更による表示らしい。

https://perldoc.jp/docs/perl/5.38.0/perl5380delta.pod

次に ver.5.28と ver.5.38 の locale.c を見ると、以下の通りで、 versionにより少々の差はありますが、以前から 「We only handle single-byte locales」らしい。

perl 5.28のlocale.c 抜粋

#  ifdef MB_CUR_MAX

        /* We only handle single-byte locales (outside of UTF-8 ones; so if
         * this locale requires more than one byte, there are going to be
         * problems. */
        DEBUG_Lv(PerlIO_printf(Perl_debug_log,
                 "%s:%d: check_for_problems=%d, MB_CUR_MAX=%d\n",
                 __FILE__, __LINE__, check_for_problems, (int) MB_CUR_MAX));

        if (   check_for_problems && MB_CUR_MAX > 1
            && ! PL_in_utf8_CTYPE_locale

               /* Some platforms return MB_CUR_MAX > 1 for even the "C"
                * locale.  Just assume that the implementation for them (plus
                * for POSIX) is correct and the > 1 value is spurious.  (Since
                * these are specially handled to never be considered UTF-8
                * locales, as long as this is the only problem, everything
                * should work fine */
            && strNE(newctype, "C") && strNE(newctype, "POSIX"))
        {
            multi_byte_locale = TRUE;
        }

#  endif

perl 5.38のlocale.c 抜粋

#    ifdef MB_CUR_MAX
    /* We only handle single-byte locales (outside of UTF-8 ones); so if this
     * locale requires more than one byte, there are going to be BIG problems.
     * */
    if (MB_CUR_MAX > 1 && ! PL_in_utf8_CTYPE_locale
            /* Some platforms return MB_CUR_MAX > 1 for even the "C" locale.
             * Just assume that the implementation for them (plus for POSIX) is
             * correct and the > 1 value is spurious.  (Since these are
             * specially handled to never be considered UTF-8 locales, as long
             * as this is the only problem, everything should work fine */
        && ! isNAME_C_OR_POSIX(newctype))
    {
        DEBUG_L(PerlIO_printf(Perl_debug_log,
                              "Unsupported, MB_CUR_MAX=%d\n", (int) MB_CUR_MAX));
        Perl_ck_warner_d(aTHX_ packWARN(WARN_LOCALE),
                         "Locale '%s' is unsupported, and may crash the"
                         " interpreter.\n",
                         newctype);
    }
#    endif

暫定的に「env LANG=en_US.utf8」を加えて、実行することで、 この警告表示を回避できますが、 eucは随分、マイナーになりつつありますので 根本的には、徐々にでも utf8に移行した方がよさそう。

$ env LANG=en_US.utf8 /path/to/bin/perl sample-test.pl