import csv into mysql using python

I have a big csv file (51 fields) and I need to import it in mysql using python.

How can I save this data respecting the type (int, date, string etc)?

I've tried this:

def import_docs(docs_csv_file):
  '''
  Imports Docs.csv into mysql 
  '''

  mydb = MySQLdb.connect(host="ip", user="user", passwd="passwd", db="db")  
  cursor = mydb.cursor()
  firstline = True
  with open(os.path.join(SCRIPT_PATH, docs_csv_file), 'r') as csvfile:
    csv_reader = csv.reader(csvfile, delimiter=';', quotechar='"', quoting=csv.QUOTE_ALL)
    for row in csv_reader:
      if firstline:
        firstline = False
        continue
      csv_ip                      = row[0]
      csv_posto                   = row[1]
      csv_doc_type                = row[2]
      csv_number                  = row[3]
      csv_nr_lines                = row[4]
      csv_vendor                  = row[5]
      csv_terminal                = row[6]
      csv_date                    = row[7]
      csv_time                    = row[8]
      csv_sys_date                = row[9]
      csv_due_date                = row[10]
      csv_user                    = row[11]
      csv_reference               = row[12]
      csv_emitted                 = row[13]
      csv_deleted                 = row[14]
      csv_target_id               = row[15]
      csv_target_name             = row[16]
      csv_doc_tax_table_0_amount  = row[17]
      csv_doc_tax_table_0_payable = row[18]
      csv_doc_tax_table_0_tax     = row[19]
      csv_doc_tax_table_1_amount  = row[20]
      csv_doc_tax_table_1_payable = row[21]
      csv_doc_tax_table_1_tax     = row[22]
      csv_doc_tax_table_2_amount  = row[23]
      csv_doc_tax_table_2_payable = row[24]
      csv_doc_tax_table_2_tax     = row[25]
      csv_doc_tax_table_3_amount  = row[26]
      csv_doc_tax_table_3_payable = row[27]
      csv_doc_tax_table_3_tax     = row[28]
      csv_target_tax_id           = row[29]
      csv_sub_total               = row[30]
      csv_discount_total          = row[31]
      csv_net_total               = row[32]
      csv_doc_total               = row[33]
      csv_doc_type_b              = row[34]
      csv_doc_number              = row[35]
      csv_rowid                   = row[36]
      csv_user_b                  = row[37]
      csv_code                    = row[38]
      csv_description             = row[39]
      csv_unit                    = row[40]
      csv_with_tax                = row[41]
      csv_price                   = row[42]
      csv_quantity                = row[43]
      csv_qtd_dec                 = row[44]
      csv_tax                     = row[45]
      csv_tax_group               = row[46]
      csv_discount1               = row[47]
      csv_tot_discount1           = row[48]
      csv_discount2               = row[49]
      csv_tot_discount2           = row[50]
      csv_total                   = row[51]

      cursor.execute('INSERT INTO docs(id, ip, posto, doc_type, number, nr_lines, vendor, terminal, date, time, sys_date, due_date, user, reference, emitted, deleted, target_id, target_name, doc_tax_table_0_amount, doc_tax_table_0_payable, doc_tax_table_0_tax, doc_tax_table_1_amount, doc_tax_table_1_payable, doc_tax_table_1_tax, doc_tax_table_2_amount, doc_tax_table_2_payable, doc_tax_table_2_tax, doc_tax_table_3_amount, doc_tax_table_3_payable, doc_tax_table_3_tax, target_tax_id, sub_total, discount_total, net_total, doc_total, doc_type_b, doc_number, rowid, user_b, code, description, unit, with_tax, price, quantity, qtd_dec, tax, tax_group, discount1, tot_discount1, discount2, tot_discount2, total) VALUES(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)', row)
      #print ('INSERT INTO docs(id, ip, posto, doc_type, number, nr_lines, vendor, terminal, date, time, sys_date, due_date, user, reference, emitted, deleted, target_id, target_name, doc_tax_table_0_amount, doc_tax_table_0_payable, doc_tax_table_0_tax, doc_tax_table_1_amount, doc_tax_table_1_payable, doc_tax_table_1_tax, doc_tax_table_2_amount, doc_tax_table_2_payable, doc_tax_table_2_tax, doc_tax_table_3_amount, doc_tax_table_3_payable, doc_tax_table_3_tax, target_tax_id, sub_total, discount_total, net_total, doc_total, doc_type_b, doc_number, rowid, user_b, code, description, unit, with_tax, price, quantity, qtd_dec, tax, tax_group, discount1, tot_discount1, discount2, tot_discount2, total) VALUES('+csv_ip+')')
      #cursor.execute('INSERT INTO docs(id, ip, posto, doc_type, number, nr_lines, vendor, terminal, date, time, sys_date, due_date, user, reference, emitted, deleted, target_id, target_name, doc_tax_table_0_amount, doc_tax_table_0_payable, doc_tax_table_0_tax, doc_tax_table_1_amount, doc_tax_table_1_payable, doc_tax_table_1_tax, doc_tax_table_2_amount, doc_tax_table_2_payable, doc_tax_table_2_tax, doc_tax_table_3_amount, doc_tax_table_3_payable, doc_tax_table_3_tax, target_tax_id, sub_total, discount_total, net_total, doc_total, doc_type_b, doc_number, rowid, user_b, code, description, unit, with_tax, price, quantity, qtd_dec, tax, tax_group, discount1, tot_discount1, discount2, tot_discount2, total) VALUES(%s, %s, %s, %s, %s, %s, %s, %s, )', row)
      #cursor.execute('INSERT INTO docs(ip, posto, doc_type, number, nr_lines, vendor, terminal, date, time, sys_date, due_date, user, reference, emitted, deleted, target_id, target_name, doc_tax_table_0_amount, doc_tax_table_0_payable, doc_tax_table_0_tax, doc_tax_table_1_amount, doc_tax_table_1_payable, doc_tax_table_1_tax, doc_tax_table_2_amount, doc_tax_table_2_payable, doc_tax_table_2_tax, doc_tax_table_3_amount, doc_tax_table_3_payable, doc_tax_table_3_tax, target_tax_id, sub_total, discount_total, net_total, doc_total, doc_type_b, doc_number, rowid, user_b, code, description, unit, with_tax, price, quantity, qtd_dec, tax, tax_group, discount1, tot_discount1, discount2, tot_discount2, total) VALUES('+csv_ip+','+csv_posto+')')
      #cursor.execute('INSERT INTO docs(ip, posto) VALUES('+csv_ip+','+csv_posto+')')
      break

Open in new window


But I get a lot of type errors...

Is there any kind of better way to get the result?

Thanks!
LVL 1
ltpittAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Swadhin Ray Commented:
This is correct :

delimiter=';'

as CSV should be delimiter=','
Swadhin Ray Commented:
Can you share the errors that you are getting while loading the file,
ltpittAuthor Commented:
I got rid of all the conversions and simply used string for everything: problem solved :)
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

ltpittAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for ltpitt's comment #a40670947

for the following reason:

It simply worked
Swadhin Ray Commented:
You can share you solution in this question, so that if any one ask or face similar kind of issues then they can refer.
ltpittAuthor Commented:
Sure!

Here's the line of code that did the job I've explained in previous comment:

cursor.execute('INSERT INTO getcash (ip, posto, cash_date, cash_time, cash_counter, cash_mpay, cash_user, cash_event, cash_value, doc_log, doc_type, doc_number, cash_obs) VALUES(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)', (csv_ip, csv_posto, csv_cash_date, csv_cash_time, csv_cash_counter, csv_cash_mpay, csv_cash_user, csv_cash_event, csv_cash_value, csv_doc_log, csv_doc_type, csv_doc_number, csv_cash_obs))

Open in new window

ZberteocCommented:
Just a comment. If you use that solution, with row to build your insert, you don't need any of those column assignments:
def import_docs(docs_csv_file):
  '''
  Imports Docs.csv into mysql 
  '''

  mydb = MySQLdb.connect(host="ip", user="user", passwd="passwd", db="db")  
  cursor = mydb.cursor()
  firstline = True
  with open(os.path.join(SCRIPT_PATH, docs_csv_file), 'r') as csvfile:
    csv_reader = csv.reader(csvfile, delimiter=';', quotechar='"', quoting=csv.QUOTE_ALL)
    for row in csv_reader:
      if firstline:
        firstline = False
        continue


      cursor.execute('INSERT INTO docs(id, ip, posto, doc_type, number, nr_lines, vendor, terminal, date, time, sys_date, due_date, user, reference, emitted, deleted, target_id, target_name, doc_tax_table_0_amount, doc_tax_table_0_payable, doc_tax_table_0_tax, doc_tax_table_1_amount, doc_tax_table_1_payable, doc_tax_table_1_tax, doc_tax_table_2_amount, doc_tax_table_2_payable, doc_tax_table_2_tax, doc_tax_table_3_amount, doc_tax_table_3_payable, doc_tax_table_3_tax, target_tax_id, sub_total, discount_total, net_total, doc_total, doc_type_b, doc_number, rowid, user_b, code, description, unit, with_tax, price, quantity, qtd_dec, tax, tax_group, discount1, tot_discount1, discount2, tot_discount2, total) VALUES(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)', row)
      #print ('INSERT INTO docs(id, ip, posto, doc_type, number, nr_lines, vendor, terminal, date, time, sys_date, due_date, user, reference, emitted, deleted, target_id, target_name, doc_tax_table_0_amount, doc_tax_table_0_payable, doc_tax_table_0_tax, doc_tax_table_1_amount, doc_tax_table_1_payable, doc_tax_table_1_tax, doc_tax_table_2_amount, doc_tax_table_2_payable, doc_tax_table_2_tax, doc_tax_table_3_amount, doc_tax_table_3_payable, doc_tax_table_3_tax, target_tax_id, sub_total, discount_total, net_total, doc_total, doc_type_b, doc_number, rowid, user_b, code, description, unit, with_tax, price, quantity, qtd_dec, tax, tax_group, discount1, tot_discount1, discount2, tot_discount2, total) VALUES('+csv_ip+')')
      #cursor.execute('INSERT INTO docs(id, ip, posto, doc_type, number, nr_lines, vendor, terminal, date, time, sys_date, due_date, user, reference, emitted, deleted, target_id, target_name, doc_tax_table_0_amount, doc_tax_table_0_payable, doc_tax_table_0_tax, doc_tax_table_1_amount, doc_tax_table_1_payable, doc_tax_table_1_tax, doc_tax_table_2_amount, doc_tax_table_2_payable, doc_tax_table_2_tax, doc_tax_table_3_amount, doc_tax_table_3_payable, doc_tax_table_3_tax, target_tax_id, sub_total, discount_total, net_total, doc_total, doc_type_b, doc_number, rowid, user_b, code, description, unit, with_tax, price, quantity, qtd_dec, tax, tax_group, discount1, tot_discount1, discount2, tot_discount2, total) VALUES(%s, %s, %s, %s, %s, %s, %s, %s, )', row)
      #cursor.execute('INSERT INTO docs(ip, posto, doc_type, number, nr_lines, vendor, terminal, date, time, sys_date, due_date, user, reference, emitted, deleted, target_id, target_name, doc_tax_table_0_amount, doc_tax_table_0_payable, doc_tax_table_0_tax, doc_tax_table_1_amount, doc_tax_table_1_payable, doc_tax_table_1_tax, doc_tax_table_2_amount, doc_tax_table_2_payable, doc_tax_table_2_tax, doc_tax_table_3_amount, doc_tax_table_3_payable, doc_tax_table_3_tax, target_tax_id, sub_total, discount_total, net_total, doc_total, doc_type_b, doc_number, rowid, user_b, code, description, unit, with_tax, price, quantity, qtd_dec, tax, tax_group, discount1, tot_discount1, discount2, tot_discount2, total) VALUES('+csv_ip+','+csv_posto+')')
      #cursor.execute('INSERT INTO docs(ip, posto) VALUES('+csv_ip+','+csv_posto+')')
      break

Open in new window


Unless, of course, you need them later in your python code. It will make your code faster.

However I would suggest to put single quotes around all your %s becayse you might have issues. Also if inside of your columns you have a quote it will cause fail.
ZberteocCommented:
This is the right way of doing it. You loop through the each column value and replace ' with '' to avoid insert failure. In any SQL syntax a string value must be enclosed with ''. I am not sure how worked in your case, unless all you columns are numbers.
def import_docs(docs_csv_file):
    '''
    Imports Docs.csv into mysql
    '''

    mydb = MySQLdb.connect(host="ip", user="user", passwd="passwd", db="db")
    cursor = mydb.cursor()
    firstline = True
    with open(os.path.join(SCRIPT_PATH, docs_csv_file), 'r') as csvfile:
        csv_reader = csv.reader(csvfile, delimiter=';', quotechar='"', quoting=csv.QUOTE_ALL)
        for row in csv_reader:
            if firstline:
                firstline = False
                continue
            # initialize the insert_sql variable
            insert_sql="INSERT INTO docs(id, ip, posto, doc_type, number, nr_lines, vendor, terminal, date, time, sys_date, due_date, user, reference, emitted, deleted, target_id, target_name, doc_tax_table_0_amount, doc_tax_table_0_payable, doc_tax_table_0_tax, doc_tax_table_1_amount, doc_tax_table_1_payable, doc_tax_table_1_tax, doc_tax_table_2_amount, doc_tax_table_2_payable, doc_tax_table_2_tax, doc_tax_table_3_amount, doc_tax_table_3_payable, doc_tax_table_3_tax, target_tax_id, sub_total, discount_total, net_total, doc_total, doc_type_b, doc_number, rowid, user_b, code, description, unit, with_tax, price, quantity, qtd_dec, tax, tax_group, discount1, tot_discount1, discount2, tot_discount2, total) VALUES("
            # go through each col and replace one ' with '' to avoid ISERT failure and add the value to the insert_sql variable
            for c in range(0,len(row)):
                insert_sql=insert_sql+"'{0}',".row(c).replace("'","'")
            # get rid of the last coma and add the bracket
            insert_sql=insert_sql[:-1]+")"

        cursor.execute(insert_sql)
        break

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ZberteocCommented:
In any SQL syntax a string value must be enclosed with single quotes: 'value'. I am not sure how worked in your case, unless all you columns are numbers but I see some date columns which should definitely fail if not enclosed with single quote. For exampel if you have:

INSERT INTO docs (..., date,...) VALUES (..., 2015-03-20,...)

would fail. The correct is:

INSERT INTO docs (..., date,...) VALUES (..., '2015-03-20',...)

But if there is a quote inside your column like O'Brien, it will fail unless you double it:

INSERT INTO docs (..., name,...) VALUES (..., 'O'Brien',...)

would fail. The correct is:

INSERT INTO docs (..., name,...) VALUES (..., 'O''Brien',...)
ltpittAuthor Commented:
Thanks for the extra mile spent on explaining and going deeper!
ZberteocCommented:
One correction, I forgot the .format method when building the insert_sql and the double single quote in the replace:
                insert_sql=insert_sql+"'{0}',".format(row(c).replace("'","''"))

Open in new window

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Python

From novice to tech pro — start learning today.