python - 将csv中的行插入mysql数据库
问题描述
我正在提取给定文件夹中的所有 csv,并尝试将它们逐行插入到 mysql 数据库中。这是我的代码:
def upload_to_db(table, folder):
print('Uploading...', end='')
files = grab_files(folder)
# Connect to dfeventlogger database
connection = pymysql.connect(**eventlogger_config, charset='utf8mb4', cursorclass=pymysql.cursors.DictCursor)
try:
with connection.cursor() as cursor:
# Open each csv in the files list
# and ignore column headers
for file in files:
csv_file = open_csv(file)
csv_headers = csv_file[0]
#csv_headers = tuple(csv_file[0]) #tried this too with no luck
csv_data = csv_file[1:]
# Insert each row of each csv into eventlogger table
for row in csv_data:
placeholders = ', '.join(['%s'] * len(row))
sql = "INSERT INTO %s ( %s ) VALUES ( %s )" % (table, csv_headers, placeholders)
print(csv_headers, '\n')
print(sql, '\n')
print(row)
cursor.execute(sql, row)
# Connection is not autocommit by default.
# So you must commit to save your changes.
connection.commit()
finally:
connection.close()
print('Finished')
upload_to_db('gsSearchAnalyticsTest', 'some_folder')
这是输出:
Uploading...['date', 'site_id', 'site', 'landing_page', 'keyword', 'source', 'impressions', 'clicks', 'position']
INSERT INTO gsSearchAnalyticsTest ( ['date', 'site_id', 'site', 'landing_page', 'keyword', 'source', 'impressions', 'clicks', 'position'] ) VALUES ( %s, %s, %s, %s, %s, %s, %s, %s, %s )
['2018-01-01', '2', 'something.co.uk', 'something.co.uk/somewhere_in_the_world', 'somewhere in the world', 'uk', '1', '1', '2.000000000000']
ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '['date', 'site_id', 'site', 'landing_page', 'keyword', 'source', 'impressions', ' at line 1")
这是我正在尝试做的事情:
- 从源数据库中提取数据
- 导出为 csv 并保存在某个文件夹中
- 拾取并循环浏览文件夹中的所有 csv 文件并插入目标数据库
问题是,我的代码哪里出错了?我该如何纠正它?
解决方案
从查询“INSERT INTO gsSearchAnalyticsTest (['date',...]) VALUES (...)”中删除方括号
推荐阅读
- django - 如何在 django rest 序列化程序上删除多余的空格
- sql-server - Azure Sql 列级加密错误 - 无法使用密钥存储提供程序解密列加密密钥:“AZURE_KEY_VAULT”
- javascript - 常量值不更新
- python - 如何在 Python pandas 数据框中的任何行具有 NaN 值后删除列
- docker - 无法将 chrome 节点与集线器绑定
- react-bootstrap - React-bootstrap (BS v3) - 不能使用来自输入的 ref
- javascript - 反应过滤器更改数据中的ID
- reactjs - CORS 策略已阻止从源“http://localhost:3000”访问“http://localhost:8000/api/puppies”获取
- sql - 在 SQL 阶段对数据仓库进行 QA... 测试表组合的想法?
- xslt-1.0 - XSLT 分组和转换