python cannot insert string in table

Question

I did a coding for dynamic updating table. it gave me output,but i can only insert Integers not strings it gives me "operational error" if i enter strings,I tried altering the table field datatype, but still it accepts integers only,I think it needs a change within the program.Please help:

Here's my code:

import MySQLdb
class data:
    def __init__(self):

        self.file123 = raw_input("Enter film: ")
        self.title_ = raw_input("Enter title: ")
        self.year = raw_input("Enter year: ")
        self.director = raw_input("Enter director: ")

a=data()

db = MySQLdb.connect(host="localhost", # your host, usually localhost
                     user="root", # your username
                      passwd="mysql", # your password
                      db="sakila") # name of the data base

cursor = db.cursor()

query = "INSERT INTO films (file123, title_, year, director) VALUES (%s, %s, %s, %s)" % (a.file123, a.title_, a.year, a.director)

cursor.execute(query)
db.commit()
db.close()

what should i change so that it accepts both integers and strings as input?please help

error :

Enter film: 123
Enter title: adarsh
Enter year: 1234
Enter director: 132

**error**
    Traceback (most recent call last):
      File "C:\Python27\maybe1.py", line 22, in <module>
        cursor.execute(query)
      File "C:\Python27\lib\site-packages\MySQLdb\cursors.py", line 202, in execute
        self.errorhandler(self, exc, value)
      File "C:\Python27\lib\site-packages\MySQLdb\connections.py", line 36, in defaulterrorhandler
        raise errorclass, errorvalue
    OperationalError: (1054, "Unknown column 'adarsh' in 'field list'")

Datatypes: file123 int(11),title_ varchar(50),year int(11),director varchar(12)

It appears the problem is not with your code but with the definition of the table. Give us the table structure, and possibly also the error you're getting. — tktk
– tktk, Commented Jun 25, 2014 at 10:19

sundar nataraj · Accepted Answer · 2014-06-25 10:36:53Z

6

i think you need to add '%s' for the string and %s to the integers

query = "INSERT INTO films (file123, title_, year, director) VALUES ('%s', '%s', %s, '%s')" % (a.file123, a.title_, a.year, a.director)

or

query = "INSERT INTO films (file123, title_, year, director) VALUES (?,?,?,?)"

curs.excute(query,[a.file123, a.title_, a.year, a.director])

Explanation what wrong with your code:

    self.file123 = raw_input("Enter film: ")
    self.title_ = raw_input("Enter title: ")
    self.year = raw_input("Enter year: ")
    self.director = raw_input("Enter director: ")

raw_input("Enter film: ") always a string . so you need to convert each variable to appropriate type eg :file123 to int; year to int

now

query = "INSERT INTO films (file123, title_, year, director) VALUES (%s, %s, %s, %s)" % (a.file123, a.title_, a.year, a.director)
print query

it gives

INSERT INTO films (file123, title_, year, director) VALUES (123, adars, 200, sundar)

but right format should be

INSERT INTO films (file123, title_, year, director) VALUES (123, 'adars', 200, 'sundar')

this happens due to %s directly put values as string without quotes so instead of %s use ?

edited Jun 25, 2014 at 10:36

answered Jun 25, 2014 at 10:24

sundar nataraj

8,7102 gold badges36 silver badges48 bronze badges

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

2 Comments

tktk Over a year ago

or better yet - let cursor.execute handle the variables formatting

adarshram Over a year ago

file123 int(11),title_ varchar(50),year int(11),director varchar(12),these are my datatypes.

WKPlus · Accepted Answer · 2014-06-25 12:42:10Z

2

I think this is better:

cursor.execute("INSERT INTO films (file123, title_, year, director) "
               "VALUES (%s, %s, %s, %s)", 
               (a.file123, a.title_, a.year, a.director))

Just let MySQLdb do the variables formatting job for you, you do not need to add quotes yourself and it's more safe.

Here are examples.

answered Jun 25, 2014 at 12:42

WKPlus

7,3152 gold badges38 silver badges55 bronze badges

Collectives™ on Stack Overflow

python cannot insert string in table

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related