Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- import openpyxl
- from openpyxl import load_workbook
- import sqlite3
- con = sqlite3.connect('test14.db')
- cur = con.cursor()
- wb = load_workbook(filename = 'abc.xlsx')
- sheets = wb.sheetnames
- def get_table ():
- for sheet in sheets:
- ws = wb[sheet]
- create_table = 'CREATE TABLE ' + str(sheet) + '(ID INTEGER PRIMARY KEY AUTOINCREMENT'
- for row in next(ws.rows):
- create_table += ', ' + str (row.value) + ' TEXT'
- create_table += ')'
- cur.execute(create_table)
- """ При использование этих переменных для добавления в таблицу, добавление проходит"""
- sql_2 = "INSERT INTO name_1(Number_biulding, Date_pay, Day_fine, Summa) VALUES (?, ?, ?, ?)"
- sql_3 = (5,5,5,5)
- insert_part_1 = 'INSERT INTO ' + str(sheet) + '('
- insert_part_2 = ''
- insert_value = '('
- for col in ws.iter_rows(min_row=0, max_col=4, max_row=1):
- for cell in col:
- cell.value = str(cell.value)
- insert_part_1 += cell.value + ', '
- insert_part_2 += '?, '
- for celt in ws.iter_rows(min_row=2, max_col=4, max_row=2):
- for val in celt:
- val.value = str(val.value)
- insert_value += val.value + ', '
- insert_part_1 = insert_part_1[:-2]+ ') VALUES ('
- insert_part_2 = insert_part_2[:-2] + ')'
- insert_value = insert_value[:-2] + ')'
- insert = insert_part_1 + insert_part_2
- cur.execute(insert,insert_value)# Не добавляет
- cur.execute(sql_2,sql_3)# Добавляет
- """
- Запрос не проходит хотя при сравнении они обсолютно одинаковые.
- >>> print (insert)
- INSERT INTO name_1(Number_biulding, Date_pay, Day_fine, Summa) VALUES (?, ?, ?,?)
- >>> print (insert_value)
- (5, 5, 5, 5)
- и при
- >>> print (sql_2)
- INSERT INTO name_1(Number_biulding, Date_pay, Day_fine, Summa) VALUES (?, ?, ?, ?)
- >>> print (sql_3)
- (5, 5, 5, 5)
- """
- get_table()
- # Завершаем транзакцию
- con.commit()
- # Закрываем объект-курсор
- cur.close()
- # Закрываем соединение
- con.close()
- помогите пожайлуста исправить.
- Вот что выдает: sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 4, and there are 12 supplied.
- import pandas as pd
- import sqlite3
- fn = r'D:temptest.xlsx'
- # read & parse all excel sheets into a dictionary (keys - sheet names, values - pandas DataFrames)
- dfs = pd.read_excel(fn, sheet_name=None)
- conn = sqlite3.connect(r'D:tempoutput.sqlite')
- # save DataFrames as SQLite tables
- for tab, df in dfs.items():
- df.to_sql(tab, conn, index=False, if_exists='replace')
Add Comment
Please, Sign In to add comment