CODEWITHSUNDEEP
pythonsql
Abdul Hamid
Abdul Hamid

Reputation: 208

ValueError while inserting into a database

I'm doing an insert to my DB and I'm inserting a string with an email.

Something like this:

cur.execute("insert into client values(%s,%s,%s,%s)",(name, email, date,int(age)))

Then, when the variable email is '[email protected]', Python throws me this message

ValueError: invalid literal for int() with base 10: [email protected]

My database has the following metadata:

name = varchar
email = varchar
date = date
age = integer

Does anybody knows what is happening?

Upvotes: 0

Views: 101

Answers (2)

Larry Lustig
Larry Lustig

Reputation: 50970

Never write INSERT statements like

 INSERT INTO Client VALUES (?, ?, ?, ?)

Instead write

 INSERT INTO Client (Name, EmailAddress, StartDate, Age) VALUES (?, ?, ?, ?)

The first version is extremely brittle. Even if it works when you first implement the program it's subject to breakage if you restructure the table -- breakage that might cause the program to fail or, worse, allow it to work but insert data into the wrong columns.

Also, when applying SQL statements in this manner you use untyped ? placeholders for the parameters passed to the statement. Those parameters will then be bound to the statement in a type-sensible, SQL injection safe manner without your needing to worry about escaping internal quote marks in names (for instance).

Upvotes: 0

user2555451
user2555451

Reputation:

This error is coming from Python, not SQL.

Somehow, the name age is equal to the string '[email protected]'. So, when you do int(age), you get a ValueError:

>>> age = '[email protected]'
>>> int(age)
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
ValueError: invalid literal for int() with base 10: '[email protected]'
>>>

You need to find where you accidentally assigned age to the email address and fix that. I cannot tell exactly how you did this because the code is not posted, but it is usually because you unpacked an iterable incorrectly. Something like:

name, email, date, age = ['Joe', 20, '11/12/2014', '[email protected]']

which should be:

name, age, date, email = ['Joe', 20, '11/12/2014', '[email protected]']

Additionally, you should never use string formatting to build SQL commands. Instead, pass the arguments as a tuple and use ? to refer to them:

cur.execute("insert into client values(?,?,?,?)", (name, email, date, age))

From the docs:

Usually your SQL operations will need to use values from Python variables. You shouldn’t assemble your query using Python’s string operations because doing so is insecure; it makes your program vulnerable to an SQL injection attack (see http://xkcd.com/327/ for humorous example of what can go wrong).

Upvotes: 1

Related Questions