CSV Insert per line to 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

Sample text data.csv file above:

Steps needed to complete:

Process the csv file
Each line should be inserted to MySQL Column. Column1, Column2, Column3 ... Column11

This is my Code so far.

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)

I was able to output it on the terminal the way i want it but it did make it in one list :). Somehow i don't know how can i split and insert it on the 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']

score 0 · Accepted Answer · edited May 23 '17 at 12:10

No. You and you need to use MySql.db.connect, and insert and commit.
Basically, you'll find your answer in a similar question here

The code should be:

# 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()

CSV Insert per line to MySQL python

1 Answers1