0

I am currently working on a python script that pulls data from a table on databaseA, and puts all that data into databaseB, which is not located near databaseA. when calling an insert into databaseB I get an error from the mysqldb module 1064:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near \'1">&reg;</font></sup>)", "somewords", "", "somewords", "some more words", "00000XXXXX\' at line 1')

I'm uncertain if its from the string containing quotes without escapes, or the html<> characters.

Is there any way to make python completely ignore the contents of a string when doing an sql insert?

I've tried ' and " and ''' and """ around the sql command, and while one situation fixes one problem, later down the rows it runs into the other kind of quote, and errors out the same way.

both databases are the same using utf8, i mimiced databaseA, here's the sql command sql2 = 

'''insert into %s (orders_id, customers_id, customers_name, customers_company, customers_street_address, customers_suburb, customers_city, customers_postcode, customers_state, customers_country,
 customers_telephone, customers_email_address, customers_address_format_id, delivery_name, delivery_company, delivery_street_address, delivery_suburb, delivery_city, delivery_postcode, delivery_state,
 delivery_country, delivery_address_format_id, billing_name, billing_company, billing_street_address, billing_suburb, billing_city, billing_postcode, billing_state, billing_country, billing_address_format_id,
 payment_method, payment_module_code, shipping_method, shipping_module_code, coupon_code, cc_type, cc_owner, cc_number, cc_expires, cc_cvv, last_modified, date_purchased, orders_status, orders_date_finished,
 currency, currency_value, order_total, order_tax, paypal_ipn_id, ip_address) 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")''' %
(bush, r1, r2, r3, r4, r5, r6, r7, r8, r9, r10, r11, r12, r13, r14, r15, r16, r17, r18, r19, r20, r21, r22, r23, r24, r25, r26, r27, r28, r29, r30, r31, r32, r33, r34, r35, r36, r37, r38, r39, r40, r41, r42, r43,
 r44, r45, r46, r47, r48, r49, r50, r51)

bush, is a raw_input asking the table name.

joaquin
  • 82,968
  • 29
  • 138
  • 152
helmet648
  • 53
  • 2
  • 11
  • Can you post your insert statement? Do the character encodings of the two databases differ? (i.e., is one UTF-8 and the other Latin?) – imm Oct 17 '11 at 03:36
  • It's from the fact that you're executing the query incorrectly. – Ignacio Vazquez-Abrams Oct 17 '11 at 03:41
  • You are probably building an INSERT statement using string formatting, instead using parameter substitution. Show us the cursor.execute() call that you are making, and if the first arg is composed earlier, show us this code as well. – John Machin Oct 17 '11 at 03:46
  • told me: cursor has no attribute "select". however using execute where select is, and doing everything else you've suggested i do has worked. thank you Very much. – helmet648 Oct 17 '11 at 04:34
  • @helmet648: Yes, "select" was a braino; I've edited the answer to fix that. Please consider accepting my answer (click on the big "tick" at the side of the answer). – John Machin Oct 17 '11 at 05:04
  • @johnMachin: done and done :) tyvm – helmet648 Oct 17 '11 at 05:33

1 Answers1

1

Instead of ("%s","%s", etc etc) for the insert values, just do (%s,%s, etc etc)

You will however need to use string formatting to get the table name in the SQL.

Here's an example, with 2 columns instead of 40:

sql2 = "insert into" + bush + "(orders_id, customers_id) Values (%s, %s)"
cursor.execute(sql2, (r1, r2))

All that (r1,r2, etc etc,r39,r40) looks extremely tedious ... I suggest that you consider getting the results of your select into a sequence called r. Then instead of the second line above, use cursor.execute(sql2, r)

John Machin
  • 81,303
  • 11
  • 141
  • 189