python - 将 CSV 数据插入 AWS RDS MySQL DB 时出现错误消息 1054“‘字段列表’中的未知列。请告诉我如何解决此错误
问题描述
这是 AWS Lambda 函数lambda_function.py代码,我正在使用该代码从 AWS S3 存储桶读取 CSV 数据并将其插入 AWS RDS MySQL 数据库。
import json
import boto3
import sys
import logging
import rds_config
import pymysql
import csv
s3_client = boto3.client("s3")
#rds settings
rds_host = "myrdsdatabase-xxxxx-amazonaws.com"
name = rds_config.db_username
password = rds_config.db_password
db_name = rds_config.db_name
logger = logging.getLogger()
logger.setLevel(logging.INFO)
try:
conn = pymysql.connect(rds_host, user=name, passwd=password, db=db_name, connect_timeout=5)
except pymysql.MySQLError as e:
logger.error("ERROR: Unexpected error: Could not connect to MySQL instance.")
logger.error(e)
sys.exit()
logger.info("SUCCESS: Connection to RDS MySQL instance succeeded")
def lambda_handler(event, context):
bucket_name = event['Records'][0]['s3']['bucket']['name']
s3_file_name = event['Records'][0]['s3']['object']['key']
csv_file = s3_client.get_object(Bucket=bucket_name,Key=s3_file_name)
record_list = csv_file['Body'].read().decode("utf-8").split('\n')
print('RAW CSV Data:')
print(record_list)
csv_reader = csv.reader(record_list, delimiter=',', quotechar='"')
colheader, *rows = csv_reader
print('Data to be inserted into RDS MySQL:')
for r in rows:
empID = r[0]
empName = r[1]
print(empID+' '+empName)
"""
This function fetches content from MySQL RDS instance
"""
result = []
with conn.cursor() as cur:
#cur.execute("create table Employee ( EmpID int NOT NULL, EmpName varchar(255) NOT NULL, PRIMARY KEY (EmpID))")
#cur.execute("Drop table Employee")
#cur.execute("Delete from Employee")
#cur.execute('insert into Employee (EmpID, EmpName) values(0,"Test")')
#cur.execute("select * from Employee")
#conn.commit()
#cur.close()
for r in rows:
empID = r[0]
empName = r[1]
cur.execute('insert into Employee (EmpID, EmpName) values('+empID+','+empName+')')
cur.execute("select * from Employee")
conn.commit()
cur.close()
for row in cur:
result.append(list(row))
print ("Data from AWS RDS MySQL...")
print (result)
输出:-
Response:
{
"errorMessage": "(1054, \"Unknown column 'Tom' in 'field list'\")",
"errorType": "InternalError",
"stackTrace": [
" File \"/var/task/lambda_function.py\", line 62, in lambda_handler\n cur.execute('insert into Employee (EmpID, EmpName) values('+empID+','+empName+')')\n",
" File \"/var/task/pymysql/cursors.py\", line 165, in execute\n result = self._query(query)\n",
" File \"/var/task/pymysql/cursors.py\", line 321, in _query\n conn.query(q)\n",
" File \"/var/task/pymysql/connections.py\", line 860, in query\n self._affected_rows = self._read_query_result(unbuffered=unbuffered)\n",
" File \"/var/task/pymysql/connections.py\", line 1061, in _read_query_result\n result.read()\n",
" File \"/var/task/pymysql/connections.py\", line 1349, in read\n first_packet = self.connection._read_packet()\n",
" File \"/var/task/pymysql/connections.py\", line 1018, in _read_packet\n packet.check_error()\n",
" File \"/var/task/pymysql/connections.py\", line 384, in check_error\n err.raise_mysql_exception(self._data)\n",
" File \"/var/task/pymysql/err.py\", line 107, in raise_mysql_exception\n raise errorclass(errno, errval)\n"
]
}
Request ID:
"6b8f70cd-dc9a-458c-9024-c4ecdb9a913e"
Function logs:
START RequestId: 6b8f70cd-dc9a-458c-9024-c4ecdb9a913e Version: $LATEST
RAW CSV Data:
['EmpID,EmpName\r', '1,Tom\r', '2,Jack\r', '3,Ross\r', '4,William']
Data to be inserted into RDS MySQL:
1 Tom
2 Jack
3 Ross
4 William
[ERROR] InternalError: (1054, "Unknown column 'Tom' in 'field list'")
Traceback (most recent call last):
File "/var/task/lambda_function.py", line 62, in lambda_handler
cur.execute('insert into Employee (EmpID, EmpName) values('+empID+','+empName+')')
File "/var/task/pymysql/cursors.py", line 165, in execute
result = self._query(query)
File "/var/task/pymysql/cursors.py", line 321, in _query
conn.query(q)
File "/var/task/pymysql/connections.py", line 860, in query
self._affected_rows = self._read_query_result(unbuffered=unbuffered)
File "/var/task/pymysql/connections.py", line 1061, in _read_query_result
result.read()
File "/var/task/pymysql/connections.py", line 1349, in read
first_packet = self.connection._read_packet()
File "/var/task/pymysql/connections.py", line 1018, in _read_packet
packet.check_error()
File "/var/task/pymysql/connections.py", line 384, in check_error
err.raise_mysql_exception(self._data)
File "/var/task/pymysql/err.py", line 107, in raise_mysql_exception
raise errorclass(errno, errval)
END RequestId: 6b8f70cd-dc9a-458c-9024-c4ecdb9a913e
REPORT RequestId: 6b8f70cd-dc9a-458c-9024-c4ecdb9a913e Duration: 223.12 ms Billed Duration: 300 ms Memory Size: 128 MB Max Memory Used: 80 MB Init Duration: 488.20 ms
注意:- 我能够将硬代码值插入 RDS MySQL 数据库,但是当尝试插入 CSV 文件值时,只会收到此错误。
如果需要任何有助于调试此错误并找到根本原因的进一步信息,请告诉我。
提前致谢!!!
解决方案
cur.execute 调用中存在语法问题。现在更改语法后,我可以成功地将 CSV 数据插入 RDS MySQL 数据库。
更正:
cur.execute("insert into Employee (EmpID, EmpName) values(%s,%s)", (empID,empName))
推荐阅读
- python - 从 Python 脚本调用时,如何获取 .exe 文件以识别同一文件夹中的文件?
- reactjs - Reactjs Antd 库和谷歌地图 Places
- c++ - 如何在 makefile 中包含字符串 fstream 和 iostream 库
- css - 未定义的混合
- python - Python Regex 仅用于第一个匹配项并忽略其他匹配项
- python - requirements.txt:以 python 版本为条件
- angular - 自动将前端应用程序的标头附加到外部http请求?
- vb.net - 创建命令以搜索现有用户的 Excel 数据库(在带有 Windows 窗体的 Visual Basic 上使用 OleDb)
- grep - 如何从字符串中捕获(grep / awk / sed)子字符串shell中的值
- api - 如何解决:解包的值太多