如何将python中的tuple作为sql语句中的value上传至数据库?

  • 回答 (0)
  • 关注 (0)
  • 查看 (18)

现有 txt数据如下,想通过python将数据 添加到 data数据库中的tdata表中(数据为虚拟数据不是真实存在的人)数据上百万行,这里出三个做为列举

郑留峰:27467766@qq.com:683760w:15069065432:19870568
池一卿:zaistar@163.com:tianxi51s:1810553487:19790963
卫杰:weizhonjie55@163.com:waj2771z:18798547022:1987154

python代码如下

import pymysql
# 1.建立和数据库服务器的连接
coon = pymysql.connect(host='192.168.228.130', port=3306, user='root', password='mysql', database='data', charset='utf8')
# 2.通过连接获取到一个游标
cur = coon.cursor()
# 3.使用游标执行操作
mktsql = "create table tdata(name varchar(100) not null,email VARCHAR(100) NOT NULL ,wechat VARCHAR(100) NOT NULL ,telnum VARCHAR(100) NOT NULL ,qq VARCHAR(100) NOT NULL );"
no = cur.execute(mktsql)
# 打开文件
i = 0
f = open('data.txt', "r", encoding='utf-8')
# 读取数据
data_list = f.readlines()
while i < len(data_list):
    # 得到一行的数据
    one_line = data_list[i]
    i += 1
    every_data = one_line.split(":")
    # 写入数据
    yuanzu = tuple(every_data)
    sql_two = "insert into tdata values (yuanzu)"
    no2 = cur.execute(sql_two)
# 关闭文件
f.close()
# 4.关闭游标资源
cur.close()
# 5.断开连接
coon.close()

报错如下

Traceback (most recent call last):
  File "D:/own work/workspace/write_data/Write_Data.py", line 22, in <module>
    no2 = cur.execute(sql_two)
  File "C:\Python\Python36\lib\site-packages\pymysql\cursors.py", line 170, in execute
    result = self._query(query)
  File "C:\Python\Python36\lib\site-packages\pymysql\cursors.py", line 328, in _query
    conn.query(q)
  File "C:\Python\Python36\lib\site-packages\pymysql\connections.py", line 517, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "C:\Python\Python36\lib\site-packages\pymysql\connections.py", line 732, in _read_query_result
    result.read()
  File "C:\Python\Python36\lib\site-packages\pymysql\connections.py", line 1075, in read
    first_packet = self.connection._read_packet()
  File "C:\Python\Python36\lib\site-packages\pymysql\connections.py", line 684, in _read_packet
    packet.check_error()
  File "C:\Python\Python36\lib\site-packages\pymysql\protocol.py", line 220, in check_erro
    err.raise_mysql_exception(self._data)
  File "C:\Python\Python36\lib\site-packages\pymysql\err.py", line 109, in raise_mysql_exception
    raise errorclass(errno, errval)
pymysql.err.InternalError: (1136, "Column count doesn't match value count at row 1")

求得大牛给一建议,本人初学python阶段.小弟感激涕零

用户4919623用户4919623提问于

扫码关注云+社区