ltpitt
asked on
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:
But I get a lot of type errors...
Is there any kind of better way to get the result?
Thanks!
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+')')
breakBut I get a lot of type errors...
Is there any kind of better way to get the result?
Thanks!
Can you share the errors that you are getting while loading the file,
ASKER
I got rid of all the conversions and simply used string for everything: problem solved :)
ASKER
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
Accepted answer: 0 points for ltpitt's comment #a40670947
for the following reason:
It simply worked
You can share you solution in this question, so that if any one ask or face similar kind of issues then they can refer.
ASKER
Sure!
Here's the line of code that did the job I've explained in previous comment:
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))
Just a comment. If you use that solution, with row to build your insert, you don't need any of those column assignments:
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.
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+')')
breakUnless, 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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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',...)
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',...)
ASKER
Thanks for the extra mile spent on explaining and going deeper!
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("'","''"))
delimiter=';'
as CSV should be delimiter=','