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.

alecxe · Accepted Answer · 2016-07-17 14:58:51Z

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.

answered Jul 17, 2016 at 14:58

alecxe

476k127 gold badges1.1k silver badges1.2k bronze badges

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

3 Comments

rishabh jain Over a year ago

Hi, can you please explain to me that why are we suppose to pass the variables in the execute module?

alecxe Over a year ago

@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 Over a year ago

Thank You Alecxe! This really helped me a lot. :D

Collectives™ on Stack Overflow

Entry in DATABASE using python

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related