Entry in DATABASE using python

Question

I have a database named user and a table named Employee. Now I want a python program that can append the details of a user in the table(employee).

for linux the code is:

 mysql -u root -proot
 |>use user;
 |>show tables;
 |>insert into Employee(name,age,salary)
 ->values("jack","25","10000");

i am now using python:

n='jack'
a='25'
sal='10000'
import MySQLdb
x=MySQLdb.connect('localhost','root','root')
y=x.cursor()
y.execute('use user;')
sql='''insert into employee(name,age,salary)
 values('''+n+''','''+a+''','''+sal+''');'''
y.execute(sql)
x.commit()

Using the above python code i am unable to append the details of the user inside the table employee. Please help! THANK YOU.

Answer 1

You need to put placeholders into the query and then parameterize it properly:

sql = """
 INSERT INTO 
 employee
 (name, age, salary)
 VALUES
 (%s, %s, %s)
"""
y.execute(sql, (n, a, sal))
x.commit()

Also note how we take the advantage of a multi-line string to make the query readable.

• rishabh jain: Hi, can you please explain to me that why are we suppose to pass the variables in the execute module?
• alecxe: @rishabhjain sure. First of all, execute() is not a module, it is a method. By parameterizing the query we are protected against SQL injection attacks letting the database driver properly escape the variable values passed into the query. Additionally, this way we don't need to worry about python-to-database type conversions - the driver would handle that for us.
• rishabh jain: Thank You Alecxe! This really helped me a lot. :D