python - MySQLdb._exceptions.ProgrammingError:格式字符串的参数不足
问题描述
我正在尝试获取一个字典列表,该列表是从工作的板 API 返回的,并将该数据插入到 SQL 表中以供我的一个队友访问。我遇到的问题是,当我运行脚本一次读取一个字典并将它们插入到 MySQLDb 表中时,我在遇到有关格式字符串参数不足的错误之前经历了一些运行. 这对我来说没有多大意义,既是因为我缺乏经验,也是因为我根据 len(dict) 设置了参数的数量。有什么想法让我偏离了轨道吗?
我的代码:
#!/usr/bin/env python3
def refresh_remote(jobs=""):
import MySQLdb
dB = MySQLdb.connect("localhost","******", "******", "BoomTown")
cursor = dB.cursor()
cursor.execute("TRUNCATE TABLE remote_jobs");
for job in jobs:
placeholders = ', '.join(['%s'] * len(job))
columns = ', '.join(job.keys())
sql = "INSERT INTO remote_jobs ( %s ) VALUES ( %s )" % (columns, placeholders)
vls = []
for v in job.values():
try:
str(v).encode(encoding='utf-8').decode('ascii')
if str(v) == "":
v = 'NULL'
else:
v = str(v)
vls.append(v)
except UnicodeDecodeError:
pass
print(vls)
print(sql)
cursor.execute(sql, vls)
dB.close()
print("All done with remote jobs table")
if __name__ == '__main__':
"""
This will be our main guard which will import
gaurd our methods for dropping and refreshing the
tables in the SQL DB
"""
from getRemoteJobs import remote_jobs_get
"""
First we'll need to get remote jobs via the API
call to remotework, then we will send the data
as a list of dicts to refresh_remote to be put
into a SQL table
"""
remote_jobs = remote_jobs_get()
refresh_remote(remote_jobs)
错误:
Traceback (most recent call last):
File "/home/tulsaboomtown/.local/lib/python3.5/site-packages/MySQLdb/cursors.py", line 201, in execute
query = query % args
TypeError: not enough arguments for format string
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
File "./refresh_jobs_db.py", line 49, in <module>
refresh_remote(remote_jobs)
File "./refresh_jobs_db.py", line 28, in refresh_remote
cursor.execute(sql, vls)
File "/home/tulsaboomtown/.local/lib/python3.5/site-packages/MySQLdb/cursors.py", line 203, in execute
raise ProgrammingError(str(m))
MySQLdb._exceptions.ProgrammingError: not enough arguments for format string
我从我拥有的那些打印语句中看到了我认为很好的输出:
['Business Development Representative (BDR)', 'Brightback', 'full_time', '2021-03-09T21:42:29', 'USA Only', 'https://remotive.io/remote-jobs/business/business-development-representative-bdr-483643', 'NULL']
INSERT INTO remote_jobs ( title, company_name, job_type, publication_date, candidate_required_location, url, salary ) VALUES ( %s, %s, %s, %s, %s, %s, %s )
['Customer Success Champion', 'Geckoboard', 'NULL', '2021-03-09T19:05:49', 'Pacific Timezone', 'https://remotive.io/remote-jobs/customer-support/customer-success-champion-516576', 'full_time']
INSERT INTO remote_jobs ( title, company_name, salary, publication_date, candidate_required_location, url, job_type ) VALUES ( %s, %s, %s, %s, %s, %s, %s )
['Translations Clinical Quality Auditors (Italian/English)', 'International SOS', 'full_time', '2021-03-09T13:40:12', 'Italy only', 'https://remotive.io/remote-jobs/all-others/translations-clinical-quality-auditors-italian-english-487628', 'NULL']
INSERT INTO remote_jobs ( title, company_name, job_type, publication_date, candidate_required_location, url, salary ) VALUES ( %s, %s, %s, %s, %s, %s, %s )
['Managing Editor', 'Testlio', 'full_time', '2021-03-09T13:40:07', 'USA Only', 'https://remotive.io/remote-jobs/writing/managing-editor-512640', 'NULL']
INSERT INTO remote_jobs ( title, company_name, job_type, publication_date, candidate_required_location, url, salary ) VALUES ( %s, %s, %s, %s, %s, %s, %s )
['Copy Editor', 'Raspberry Pi Foundation', 'full_time', '2021-03-09T13:40:06', 'UK Only', 'https://remotive.io/remote-jobs/writing/copy-editor-515941', 'NULL']
INSERT INTO remote_jobs ( title, company_name, job_type, publication_date, candidate_required_location, url, salary ) VALUES ( %s, %s, %s, %s, %s, %s, %s )
['Lead Software QA Engineer', 'Swapcard', '$75K - $95K', '2021-03-09T09:14:27', 'CET Time Zone', 'https://remotive.io/remote-jobs/qa/lead-software-qa-engineer-515914', 'full_time']
INSERT INTO remote_jobs ( title, company_name, salary, publication_date, candidate_required_location, url, job_type ) VALUES ( %s, %s, %s, %s, %s, %s, %s )
['Senior C++ Developer', 'Flightradar24', 'NULL', '2021-03-09T05:58:10', 'https://remotive.io/remote-jobs/software-dev/senior-c-developer-515106', 'full_time']
INSERT INTO remote_jobs ( title, company_name, salary, publication_date, candidate_required_location, url, job_type ) VALUES ( %s, %s, %s, %s, %s, %s, %s )
但是当我在运行后检查 sqldb 中的 remote_jobs 时,表中没有任何内容(空集)。
解决方案
看一下您的输出中的一个示例:
INSERT INTO remote_jobs ( title, company_name, salary, publication_date, candidate_required_location, url, job_type ) VALUES ( %s, %s, %s, %s, %s, %s, %s )
['Senior C++ Developer', 'Flightradar24', 'NULL', '2021-03-09T05:58:10', 'https://remotive.io/remote-jobs/software-dev/senior-c-developer-515106', 'full_time']
您的 INSERT 期望插入 7 个值,但在这种情况下您只提供 6 个。看起来您缺少“candidate_required_location”的值
推荐阅读
- android - 如何在 esp32 和颤振应用程序之间颤振中从 BLE 接收数据
- sql-server - SQL Server 2019 路径包含空格时找不到文件夹,加上引号不能解决问题
- keycloak - Keycloak 在模板中使用 IdP 显示名称而不是别名
- android - CoroutineDispatcher 注入时 Hilt 构建失败
- python - 使用 pydot 绘制图形时出现断言错误
- php - 使用 ACF 字段更改 WooCommerce 产品简短描述
- wordpress - 强制浏览器查看/下载最新版本的 PDF?
- c# - 如何在文本框中显示 xmlnodes
- r - s3 r 中的自定义设置器
- java - 如何从密钥为 Tomcat 上的 HTTPS 生成 .p12 证书