Python 3 pymysql 插入空日期时间字段

Question

我有一个表，其中name为varchar(50)，ctime为datetime。如果我在 MySQL 中执行 insert into atable (name, ctime) values ('aname', NULL) 它就会起作用。

在 Python 3 中，我尝试了以下方法(全部生成错误):

conn = pymysql.connect(host='ahost', port=3306, user='auser', passwd='apasswd', db='adb')

cur = conn.cursor()

[尝试1]

cur.execute("insert into atable (name, ctime) values (%s, '%s'), ('aname', '')

pymysql.err.InternalError: (1292, "Incorrect datetime value: '' for column 'ctime' at row 1")

[尝试2]

cur.execute("insert into atable (name, ctime) values (%s, %s), ('aname', '')

pymysql.err.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 ')' at line 1")

[尝试3]

cur.execute("insert into atable (name, ctime) values (%s, '%s'), ('aname', None)

pymysql.err.InternalError: (1292, "Incorrect datetime value: 'None' for column 'ctime' at row 1")

[尝试4]

cur.execute("insert into atable (name, ctime) values (%s, %s), ('aname', None)

pymysql.err.InternalError: (1054, "Unknown column 'None' in 'field list'")

如有任何帮助，我们将不胜感激。

Answer 1

在这种情况下，您不需要传递ctime，只需传递您实际设置的值即可。

sql = "INSERT INTO `atable` (`name`) VALUES (%s)"
rows = cursor.execute(sql, ('aname2'))
print("insert:", rows)

我运行代码并按预期工作，完整代码:

import pymysql

def main():
    try:
        conn = pymysql.connect(
            host='localhost', user='@@', password='##',
            db='dn_name'
        )
        with conn.cursor() as cursor:
            sql = "INSERT INTO `atable` (`name`) VALUES (%s)"
            rows = cursor.execute(sql, ('aname2'))
            print("insert:", rows)

    except Exception as e:
        conn.rollback()
        raise e
    else:           
        conn.commit()
    finally:
        conn.close()

if __name__ == '__main__':
    main()