mysql - CSV每行插入MySQL python

Question

20170325_225012,ctcc01,voice,639128342574,639464810386,cap_timeout,6004,639180007006,2,0,null
20170325_235012,ctcc01,voice,639128342554,639464520384,cap_timeout,6004,639180007006,2,0,null
20170325_245012,ctcc01,voice,639128342174,639464820327,cap_timeout,6004,639180007006,2,0,null

上面的示例文本 data.csv 文件:

需要完成的步骤:

1. 处理 csv 文件
2. 每一行都应插入 MySQL 列。列 1、列 2、列 3 ... 列 11

这是我到目前为止的代码。

import csv
import re


f = open('data.csv')
csv_f = csv.reader(f)
writer = csv.writer(f)

cdr = []

for row in csv_f:
  cdr.append("Some auto increment id")
  cdr.append(re.sub(r'_.*$', "", row[0]))
  cdr.append(row[1])
  cdr.append(row[2])
  cdr.append(row[3])
  cdr.append(row[4])
  cdr.append(row[5])
  cdr.append(row[6])
  cdr.append(row[7])
  cdr.append(row[8])
  cdr.append(row[9])
  cdr.append(row[10])

print cdr

with open('output.csv', 'wb') as f:
    writer = csv.writer(f)
    writer.writerows(cdr)

我能够按照我想要的方式在终端上输出它，但它确实将其放入一个列表中:)。不知何故，我不知道如何将其拆分并插入到 mysql 上。

['Some auto increment id', '20170325', 'ctcc01', 'voice', '639128342574', '639464820387', 'cap_timeout', '6004', '639180007006', '2', '0', 'null', 'Some auto increment id', '20170325', 'ctcc01', 'voice', '639128342574', '639464820387', 'no_subs', '6004', '639180007006', '2', '0', 'null', 'Some auto increment id', '20170325', 'ctcc01', 'voice', '639128342574', '639464820387', 'cap_timeout', '6004', '639180007006', '2', '0', 'null']

Answer 1

没有。你和你需要使用MySql.db.connect，并插入和提交。
基本上，您可以在 a similar question here 中找到答案。

代码应该是:

# open file, and define a csv reader and writer - you've done that correctly
import csv
import re

f = open('data.csv')
csv_f = csv.reader(f)
writer = csv.writer(f)

vals = [] 

# open and connect to database
dbname = 'mydb'        # or whatever your database is named
tablename = 'mytable'  # or whatever table you wish to insert into
hostname = 'localhost' # or whatever your mysql db hostname is
username = 'root'      # or whatever your username for mysql db is
pw = ''                # or whatever your password is for that user
mydb = MySQLdb.connect(host=hostname, user=username, passwd=pw, db=dbname)
cursor = mydb.cursor()

# for each row create an 'INSERT INTO' execution-string
auto = 0 # auto-incrementing
exec_string = ""
rowid = ""
for row in csv_f:
    # INSERT INTO mytable(Column, Column1,Column2, ... Column12)
    #              VALUES(auto,   rowid,  row(1), row(2)...

    # execstr header: 
    exec_string = "INSERT INTO " + tablename + "(Column, "
    for i in range(1,11): # columns
        exec_string += "Column" + i + (", " if (i<11))
    # ...it may be a mistake in the question and you need Column0
    # ...in which case the end of the exec_string line should read + "("
    # ...and the for should be in range(0,11): ... 

    # execstr values: 
    exec_string += ") Values("

    for _ in range(12):
        exec_string += "%S"
    exec_string += ")" # close values function

    vals = []
    auto += 1
    rowid = re.sub(r'_.*$', "", row[0])
    vals.append(auto)
    vals.append(rowid)
    for i in range(2,12) # count to 12 starting with 2
        vals.append(row[i])

 # and execute it! 
 cursor.execute(exec_string, vals)

# commit and close the connection to the database.
mydb.commit()
cursor.close()