めもぶろぐ

お勉強したこと、趣味なんかを適当に書いてます。。。

pythonでExcelからデータをsqlite3のDBに格納する方法

pythonexcelデータをあつかいたくて
そのやり方をメモしておきます。

環境はwindows10のbash環境で実行しています。
excelのデータからテキストやDB等を生成することを目的としています。

ちなみに非プログラマなサーバSEなので、一般的な処理方法かどうかは全く気にしていません。

インポートするライブラリ

sqlite3

import sqlite3

openpyxl

from openpyxl import load_workbook

contextlib

from contextlib import closing

Excelシートの取り込み

infile='/mnt/d/foo/bar/iplist/iplist.xlsx'
wb = load_workbook(filename = infile)

#シート名を指定する
ws = wb['Sheet1]

sqlite3のDBにデータ格納

配列とタプルでSQL実行時のデータを生成

r_lst = []
for r in range(1, ws.max_row + 1):
        r_tpl = ()
        for c in range(1, ws.max_column + 1):
                #print(ws.cell(r,c).value, end='')
                r_tpl += (ws.cell(r,c).value, )

        r_lst.append(r_tpl)

# ws.max_rowでシート内の最終行を取得
# ws.max_columnでシート内の最終列を取得
# セルの値はcell.valueで取得
# cell(row=1, column=1).value とかでもよし

sqlite3 のDBにテーブルを作成(メモリテーブル)

#with を使ってconnectすることでこのブロック処理が完了時にcolseしてくれる
with closing(sqlite3.connect(':memory:')) as conn:
    cur = conn.cursor()

    sql_tbl = '''create table iplist (
                                    oct1 varchar(3),
                                    c1 char(1),
                                    oct2 varchar(3),
                                    c2 char(1),
                                    oct3 varchar(3),
                                    c3 char(1),
                                    oct4 varchar(3),
                                    pos  varchar(10),
                                    name varchar(12),
                                    alias varchar(15),
                                    descript varchar(255),
                                    alias2 varchar(15),
                                    alias3 varchar(15))'''

    cur.execute(sql_tbl)

テーブルにデータをインサート

# cur.execute('insert into iplist values(hogehoge)') でもよい
# 今回はr_lstというリストに1レコード1タプルで複数レコードを格納しているためexecutemanyにて実行
# r_lst = [ (a,b,c), (aa,bb,cc), (aaa,bbb,ccc) ] というような感じの構造
    cur.executemany("insert into iplist values(?,?,?,?,?,?,?,?,?,?,?,?,?)", r_lst)
    conn.commit()

テーブルからデータを抽出(全部)

#sqlの実行結果を1レコードずつ処理
    for row in cur.execute('select * from iplist'):
        print(row)

    for i in range(80): print("=", end='')
    print()

テーブルから特定行の抽出

# sqlの実行結果を1レコードずつ処理
# rowの中身はタプルになっているのでlist関数で配列化して、joinしながらprint
    for row in cur.execute('select * from iplist where pos="開発"'):
        data = list(row)
        print('.'.join(data[0:7:2]), end=' ')
        print(' '.join(data[7:]))
        print()
# coding: utf-8

import sqlite3
from openpyxl import load_workbook
from contextlib import closing

infile='/mnt/d/foo/bar/iplist/iplist.xlsx'
wb = load_workbook(filename = infile)
ws = wb['Sheet1']

r_lst = []
for r in range(1, ws.max_row + 1):
        r_tpl = ()
        for c in range(1, ws.max_column + 1):
                #print(ws.cell(r,c).value, end='')
                r_tpl += (ws.cell(r,c).value, )

        r_lst.append(r_tpl)

with closing(sqlite3.connect(':memory:')) as conn:
    cur = conn.cursor()

    sql_tbl = '''create table iplist (
                                    oct1 varchar(3),
                                    c1 char(1),
                                    oct2 varchar(3),
                                    c2 char(1),
                                    oct3 varchar(3),
                                    c3 char(1),
                                    oct4 varchar(3),
                                    pos  varchar(10),
                                    name varchar(12),
                                    alias varchar(15),
                                    descript varchar(255),
                                    alias2 varchar(15),
                                    alias3 varchar(15))'''

    cur.execute(sql_tbl)

    cur.executemany("insert into iplist values(?,?,?,?,?,?,?,?,?,?,?,?,?)", r_lst)
    conn.commit()

    outfile='/mnt/d/foo/bar/iplist/file.txt'
    sql = '''select oct1, c1, oct2, c2, oct3, c3, oct4,
                    case when name is null
                        then ""
                        else name
                    end,
                    case when alias is null
                        then ""
                        else alias
                    end,
                    case when alias2 is null
                        then ""
                        else alias2
                    end,
                    case when alias3 is null
                        then ""
                        else alias3
                    end,
                    case when descript is null
                        then "#            "
                        else "#" || descript
                    end
                    from iplist where pos="開発"
    '''

    max_col = 0
    for row in cur.execute(sql):
        data = list(row)
        if len(' '.join(data[:11])) > max_col:
            max_col = len(' '.join(data[:11]))

    with open(outfile, 'wt') as ofile:
        for row in cur.execute(sql):
            data = list(row)
            hosts_line = '.'.join(data[0:7:2]).ljust(16)

            host = ""
            for alias in data[7:-2]:
                if alias != "":
                    host += alias + " "
            hosts_line += host.ljust(max_col - 16)
            hosts_line += data[-1]

            print(hosts_line, file=ofile)
広告を非表示にする