Aditya_softwaredeveloper Aditya_softwaredeveloper - 1 year ago 158
Python Question

Failed to insert the data using python.mysql connector

I'm new to using MySQL connector for python and I have the below-given data to be inserted into a table called "region_based_billings". I tried all possible ways to insert this data and it seems I missing a syntax not sure exactly where and for which data type? .

date = '2016-06-29'
region = 'Asia Pacific'
account_type = 'PRODUCTION ACCOUNT'
product_type = 'AmazonEC2'
total= 10.58383305
count = 21

cursor = self.testdb.cursor()
cursor.execute('INSERT INTO iaas.region_based_billing (date, region, account type, product type, total amount, no of products) VALUES (%s, %s, %s, %s, %s, %s)',(date, region, account_type,product_type,total,count) )


My table objects are :

desc iaas.region_based_billing;

date date NO PRI
region varchar(50) NO
account type varchar(65) NO
product type varchar(65) NO
total amount float NO
no of products int(255) NO

I know it's pretty basic, not sure what exactly causing the problem. I tried keeping single quotes for date type '%s', but still it didn't work.

Also here is the error

mysql.connector.errors.ProgrammingError: 1064 (42000): 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 'type, product type, total amount, no of products) VALUES ('2016-06-29', 'Asia Pa' at line 1

Answer Source

Remove space between column names. column name should be a single word or words seprated by _. In your case column name have gap. So these column should be:-

account type = account_type
product type = product_type

and so on...

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download