回答此问题可获得 20 贡献值,回答如果被采纳可获得 50 分。
<p>在python中,我试图将一些数据插入到我的表中</p>
<pre><code> try:
cursor.execute("INSERT INTO covid_testtable (pid, age, state, city, notes, backnotes, type, nationality, status, announced, changed) " +
"VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, STR_TO_DATE(%s, '%%d/%%m/%%Y'), STR_TO_DATE(%s, '%%d/%%m/%%Y'))",
(pid, age, item["detectedstate"], item["detectedcity"], item["notes"], item["backupnotes"], item["typeoftransmission"],
item["nationality"], item["currentstatus"], dateannounced, statuschanged));
except ValueError as verr:
print(item["dateannounced"], verr);
break;
</code></pre>
<p>这会一直工作到其中一个日期列的条目为空为止</p>
<pre><code>pymysql.err.InternalError: (1411, "Incorrect datetime value: '' for function str_to_date"
</code></pre>
<p>我试图将空日期的值更改为“<strong>00/00/0000</strong>”,但问题似乎出在<strong>str_to_date</strong>()函数上。
我可以插入<strong>NULL</strong>值,但是无法插入</p>
<p>成功插入的有效值的格式为“<strong>30/01/2020</strong>”->;“<strong>%d/%m/%Y</strong>”</p>
<p>我的表格架构如下:</p>
<pre><code>create table covid_testtable ( pid int NOT NULL, age int, state VARCHAR(255),
city VARCHAR(255), notes VARCHAR(255), backnotes VARCHAR(255), type VARCHAR(255),
nationality VARCHAR(255), status VARCHAR(255), announced DATE default NULL,
changed DATE default NULL, PRIMARY KEY (pid));
</code></pre>
<p><strong>编辑2:选定答案解决了此问题:</strong></p>
<pre><code>def validate_date(val):
try:
return datetime.strptime(val, '%d/%m/%Y').strftime('%Y-%m-%d');
except ValueError as verr:
return None;
dateannounced = validate_date(item["dateannounced"]);
statuschanged = validate_date(item["statuschangedate"]);
try:
cursor.execute("INSERT INTO covid_testtable (pid, age, state, city, notes, backnotes, type, nationality, status, announced, changed) " +
"VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",
(pid, age, item["detectedstate"], item["detectedcity"],
item["notes"], item["backupnotes"], item["typeoftransmission"],
item["nationality"], item["currentstatus"], dateannounced,
statuschanged));
</code></pre>