import openpyxl
import sqlite3
name_xlsx = 'C:/Users/shienkikou11/Desktop/＊＊＊＊＊.xlsx'
workbook = openpyxl.load_workbook(name_xlsx)
# print("----- opened xlsx")
name_table = "column_setter-02_CSV"
worksheet = workbook[name_table]
min_row = worksheet.min_row
max_row = worksheet.max_row + 1
min_col = worksheet.min_column
max_col = worksheet.max_column
# print(max_row,max_col)
# setting location of columns_tables
loc_directories = 0
loc_databases = 1
loc_name_table = 2
loc_cord_table = 3
loc_num_blk01 = 5
loc_num_blk02 = 6
loc_num_col_res = 7
loc_blk01_01 = 8
loc_blk02_01 = 13
loc_col_res_01 = 18
# get dbfile-pass and columns-of-tables for generating SQL_select
pass_dbs =[]
sql_select_corps = []
name_tables = []
STRlikes = "Like ?"
loc_row = 1
for loc_row in range(2,max_row):
# print(loc_row,end=",")
row_data = worksheet[loc_row]
directory_db = row_data[loc_directories].value
name_db = row_data[loc_databases].value
pass_db = directory_db + name_db
pass_dbs.append(pass_db)
name_table = row_data[loc_name_table].value
cord_table = row_data[loc_cord_table].value
num_blk01 = row_data[loc_num_blk01].value
num_blk02 = row_data[loc_num_blk02].value
num_col_res = row_data[loc_num_col_res].value
# get blk1 columns
inc_loc = 0
columns_blk01 = ""
for inc_loc in range(0,num_blk01):
blk01 = row_data[loc_blk01_01+inc_loc].value
columns_blk01 = columns_blk01 + blk01
if inc_loc < num_blk01-1 :
columns_blk01 = columns_blk01 + ","
# get blk2 columns
inc_loc = 0
columns_blk02 = ""
if num_blk02 != 0:
for inc_loc in range(0,num_blk02):
blk02 = row_data[loc_blk02_01+inc_loc].value
columns_blk02.append(blk02)
if inc_loc < num_blk02-1 :
columns_blk02 = columns_blk02 + ","
# get columns_res
inc_loc = 0
res =" "
columns_res = " "
for inc_loc in range(0,num_col_res):
res = row_data[loc_col_res_01 + inc_loc].value
columns_res = columns_res + res
if inc_loc < num_col_res-1 :
columns_res = columns_res + ","
# generate SQL(select)
sql_select_corp = "SELECT "+ columns_blk01+","+columns_res+" FROM "+ name_table + " WHERE " + columns_blk01 +" "+ STRlikes
sql_select_corps.append(sql_select_corp)
name_tables.append(name_table)
print(sql_select_corp)
workbook.close()
print("----- closed xlsx")
inc_row_SQL = 1
corpname = "株式会社＊＊＊＊＊"
# max_row_SQL = 1
max_row_SQL = max_row-2
for inc_row_SQL in range(0,max_row_SQL):
# print("--------------",inc_row_SQL)
# print(pass_dbs[inc_row_SQL])
# print(sql_select_corps[inc_row_SQL])
# setting DB-pass
dbname = pass_dbs[inc_row_SQL]
#1.データベースに接続
conn = sqlite3.connect(dbname)
# 2.sqliteを操作するカーソルオブジェクトを作成
cur = conn.cursor()
# SQL execution
cur.execute(sql_select_corps[inc_row_SQL],[corpname])
# 取得したデータを出力
print("■",inc_row_SQL,"■ ",name_tables[inc_row_SQL]," --------------------------------------------------")
for row in cur:
print(row)
print(" ")
# 4.データベースの接続を切断
cur.close()
conn.close()