如何将数据插入mysql表而不与python复制?

Question

如果输入数据和已经插入数据库的数据是相同的，我如何插入数据而不重复?最好是更新已经存在的。但我不知道该怎么做。

sql = "INSERT INTO ftp(a,b,c,d) VALUES ( %s, %s, %s, %s)"

Answer 1

查看这篇文章

简而言之:您必须检查这些值是否存在于您的数据库中，然后插入。

Answer 2

可以按如下方式插入数据

import mysql.connector

def main(config):
output = []
cnx = mysql.connector.connect(**config)
cur = cnx.cursor()
# Drop table if exists, and create it new
stmt_drop = "DROP TABLE IF EXISTS names"
cur.execute(stmt_drop)
stmt_create = (
"CREATE TABLE names ("
"    id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT, "
"    name VARCHAR(30) DEFAULT '' NOT NULL, "
"    info TEXT DEFAULT '', "
"    age TINYINT UNSIGNED DEFAULT '30', "
"PRIMARY KEY (id))"
)
cur.execute(stmt_create)
info = "abc" * 10000
# Insert 3 records
names = (('Geert', info, 30), ('Jan', info, 31), ('Michel', info, 32))
stmt_insert = "INSERT INTO names (name, info, age) VALUES (%s, %s, %s)"
cur.executemany(stmt_insert, names)
cnx.commit()
# Read the names again and print them
stmt_select = "SELECT id, name, info, age FROM names ORDER BY id"
cur.execute(stmt_select)
for row in cur.fetchall():
output.append("%d | %s | %dnInfo: %s..n" % (
row[0], row[1], row[3], row[2][:20]))
# Cleaning up, dropping the table again
cur.execute(stmt_drop)
cur.close()
cnx.close()
return output

if __name__ == '__main__':
config = {
'host': 'localhost',
'port': 3306,
'database': 'test',
'user': 'root',
'password': '',
'charset': 'utf8',
'use_unicode': True,
'get_warnings': True,
}
out = main(config)
print('n'.join(out))

问候!