Whats wrong with this python sql statement

Question

I have A DB schema in mysql

id  int(11) PK AI
apt_id  varchar(200) 
checkin_date    date 
checkout_date   date 
price   decimal(10,0) 
deposit decimal(10,0) 
adults  int(11) 
source_id   int(11) 
confirmationCode    varchar(100) 
client_id   int(11) 
booking_date    datetime 
note    mediumtext 
Related Tables:property (apt_id → apt_id)
booking_source (source_id → id)

I am trying to insert value in DB using following query

self.start_at = datetime.strptime(self.start_at[0:10] + ' ' + self.start_at[11:19], "%Y-%m-%d %H:%M:%S")
self.end_at = datetime.strptime(self.end_at[0:10] + ' ' + self.end_at[11:19], "%Y-%m-%d %H:%M:%S")
x = db.cursor()
sql = """INSERT INTO `nycaptBS`.`booking` (`apt_id`, `checkin_date`, `checkout_date`, `price`,`deposite` `adults`, `source_id`, `confirmationCode`, `client_id`, `booking_date`) VALUES ('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s' )"""
x.execute(sql,(self.apt_id,self.start_at,self.end_at,self.final_price,self.deposit,self.adults,self.source_id,self.notes,self.client_id,self.booking_date,self.notes))

Since error itself is not clear

  File "/usr/lib/python2.7/dist-packages/MySQLdb/cursors.py", line 159, in execute
    query = query % db.literal(args)
TypeError: not enough arguments for format string

PLease help me out how can i resolve this .I worked a lot on Django ORM but for this i have to write in Mysql query .

Thanks

In x.execute you only provide four arguments instead of the 10 you have in the query — Explosion Pills
– Explosion Pills, Commented Apr 18, 2013 at 5:36
@burning, I was trying to help you make your code snippet more readable, and was wondering: are you missing a comma between deposite and adults in your SQL statement? — buruzaemon
– buruzaemon, Commented Apr 18, 2013 at 5:54

Burhan Khalid · Accepted Answer · 2013-04-18 06:00:22Z

1

This exception is raised when you have more formatting codes in your string, and not enough arguments passed in:

>>> s = "Hello %s %s %s"
>>> print(s % ('a','b'))
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
TypeError: not enough arguments for format string

Here you see I have three %s, but I am only passing in two strings ('a','b').

You have the same problem with your query, because you are missing a comma:

sql = """
    INSERT INTO `nycaptBS`.`booking` (
           `apt_id`,
           `checkin_date`,
           `checkout_date`,
           `price`,
           `deposite` `adults`, # missing comma here
           `source_id`,
           `confirmationCode`,
           `client_id`,
           `booking_date`) VALUES (
                      '%s', #1
                      '%s', #2
                      '%s', #3
                      '%s', #4
                      '%s', #5
                      '%s', #6
                      '%s', #7
                      '%s', #8
                      '%s', #9
                      '%s'  #10
             )
      """
x.execute(sql,(
self.apt_id, #1
self.start_at, #2
self.end_at, #3
self.final_price, #4
self.deposit, #5
self.adults, #6
self.source_id, #7
self.notes, #8
self.client_id, #9
self.booking_date, #10
self.notes #11 ))

edited Apr 18, 2013 at 6:00

answered Apr 18, 2013 at 5:54

Burhan Khalid

175k20 gold badges255 silver badges292 bronze badges

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

5 Comments

burning Over a year ago

Khalid you are ritgh i was missing comma thank but after that i am getting this error _mysql_exceptions.ProgrammingError: (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 '2013-04-11 16:00:00'',\n

Burhan Khalid Over a year ago

This is because your database type is DATE but you are passing in a string with date and time.

burning Over a year ago

start_at and end_at are the python date object you can check in question then y

Burhan Khalid Over a year ago

You are sending a string : '2013-04-11 16:00:00' (not a date object), and it is not in the format that a DATE column in MySQL would expect.

burning Over a year ago

@mysql accept in this format check this dev.mysql.com/doc/refman/5.1/en/datetime.html if I am wrong then please tell me the correct format so that i can modify the code Thanks

Collectives™ on Stack Overflow

Whats wrong with this python sql statement

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related