Guest User

Untitled

a guest
May 20th, 2018
143
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 2.75 KB | None | 0 0
  1. import openpyxl
  2. from openpyxl import load_workbook
  3. import sqlite3
  4.  
  5. con = sqlite3.connect('test14.db')
  6. cur = con.cursor()
  7. wb = load_workbook(filename = 'abc.xlsx')
  8. sheets = wb.sheetnames
  9.  
  10. def get_table ():
  11. for sheet in sheets:
  12. ws = wb[sheet]
  13. create_table = 'CREATE TABLE ' + str(sheet) + '(ID INTEGER PRIMARY KEY AUTOINCREMENT'
  14. for row in next(ws.rows):
  15. create_table += ', ' + str (row.value) + ' TEXT'
  16. create_table += ')'
  17. cur.execute(create_table)
  18.  
  19.  
  20. """ При использование этих переменных для добавления в таблицу, добавление проходит"""
  21. sql_2 = "INSERT INTO name_1(Number_biulding, Date_pay, Day_fine, Summa) VALUES (?, ?, ?, ?)"
  22. sql_3 = (5,5,5,5)
  23.  
  24. insert_part_1 = 'INSERT INTO ' + str(sheet) + '('
  25. insert_part_2 = ''
  26. insert_value = '('
  27.  
  28. for col in ws.iter_rows(min_row=0, max_col=4, max_row=1):
  29. for cell in col:
  30. cell.value = str(cell.value)
  31. insert_part_1 += cell.value + ', '
  32. insert_part_2 += '?, '
  33.  
  34. for celt in ws.iter_rows(min_row=2, max_col=4, max_row=2):
  35. for val in celt:
  36. val.value = str(val.value)
  37. insert_value += val.value + ', '
  38.  
  39. insert_part_1 = insert_part_1[:-2]+ ') VALUES ('
  40. insert_part_2 = insert_part_2[:-2] + ')'
  41. insert_value = insert_value[:-2] + ')'
  42. insert = insert_part_1 + insert_part_2
  43.  
  44. cur.execute(insert,insert_value)# Не добавляет
  45. cur.execute(sql_2,sql_3)# Добавляет
  46.  
  47. """
  48. Запрос не проходит хотя при сравнении они обсолютно одинаковые.
  49. >>> print (insert)
  50. INSERT INTO name_1(Number_biulding, Date_pay, Day_fine, Summa) VALUES (?, ?, ?,?)
  51. >>> print (insert_value)
  52. (5, 5, 5, 5)
  53. и при
  54. >>> print (sql_2)
  55. INSERT INTO name_1(Number_biulding, Date_pay, Day_fine, Summa) VALUES (?, ?, ?, ?)
  56. >>> print (sql_3)
  57. (5, 5, 5, 5)
  58. """
  59.  
  60.  
  61.  
  62. get_table()
  63.  
  64.  
  65. # Завершаем транзакцию
  66. con.commit()
  67. # Закрываем объект-курсор
  68. cur.close()
  69. # Закрываем соединение
  70. con.close()
  71.  
  72. помогите пожайлуста исправить.
  73. Вот что выдает: sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 4, and there are 12 supplied.
  74.  
  75. import pandas as pd
  76. import sqlite3
  77.  
  78. fn = r'D:temptest.xlsx'
  79.  
  80. # read & parse all excel sheets into a dictionary (keys - sheet names, values - pandas DataFrames)
  81. dfs = pd.read_excel(fn, sheet_name=None)
  82.  
  83. conn = sqlite3.connect(r'D:tempoutput.sqlite')
  84.  
  85. # save DataFrames as SQLite tables
  86. for tab, df in dfs.items():
  87. df.to_sql(tab, conn, index=False, if_exists='replace')
Add Comment
Please, Sign In to add comment