Failed to insert the data using python.mysql connector

Question

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

    self.testdb.commit()
    self.testdb.close()

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

Rakesh Kumar · Accepted Answer · 2016-07-04 05:29:44Z

1

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...

answered Jul 4, 2016 at 5:29

Rakesh Kumar

4,4522 gold badges19 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Aditya_softwaredeveloper Over a year ago

Ahh thanks pal , that worked its my white space that's causing the whole problem

Aditya_softwaredeveloper · Accepted Answer · 2016-07-04 05:50:17Z

0

Removed the white spaces and it resolved the problem. Thanks.

Serial_no       int(11) NO  PRI     auto_increment
date            date    NO          
region          varchar(50) NO          
account_type    varchar(65) NO          
product_type    varchar(65) NO          
total_amount    float   YES         
no_of_products  int(255)    NO

answered Jul 4, 2016 at 5:50

Aditya_softwaredeveloper

8310 bronze badges

Comments

kvk30 · Accepted Answer · 2016-07-14 16:22:21Z

0

Here no of products has a datatype of int. But, you are using it has a string. For integer, you need to use %d.

cursor.execute('INSERT INTO iaas.region_based_billing (date, region, account type, product type, total amount, no of products) VALUES (%s, %s, %s, %s, %f, %d)',(date, region, count_type,product_type,total,count) )

You can check this example.

edited Jul 14, 2016 at 16:22

answered Jul 4, 2016 at 5:55

kvk30

1,2031 gold badge15 silver badges34 bronze badges

1 Comment

Aditya_softwaredeveloper Over a year ago

Actually you could %s for all the data types using MySQL connector . %d is not needed dev.mysql.com/doc/connector-python/en/…

Collectives™ on Stack Overflow

Failed to insert the data using python.mysql connector

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest