CODEWITHSUNDEEP
pythonmysql
adarshram
adarshram

Reputation: 181

python cannot insert string in table

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)

Upvotes: 1

Views: 9611

Answers (2)

WKPlus
WKPlus

Reputation: 7255

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.

Upvotes: 2

sundar nataraj
sundar nataraj

Reputation: 8692

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 ?

Upvotes: 6

Related Questions