Add data in MySQL using python

Question

I want to add the data in MySQL database using python 2.7 on CentOS.

Here's my code,

# Sample data
clusterList =['a', 'w', 'e', 'r']
versionList = [1,2,3,4]
portalDomain = ['aa', 'bb', 'cc', 'dd']
portalDomains = ['p1', 'p2', 'p3', 'p4']

db = mysql.connect(host="mydb.com", user="nod", passwd="", db="noc")
cur = db.cursor()

for i in range(len(portalDomains)):
print("Adding data...")
query = "INSERT INTO test_score (portalCluster,portalVersion,portalDomain) values 
    ('%s', %s, '%s')" 
    % (clusterList[i], versionList[i], portalDomains[i])

cur.execute(query)
db.commit()

I am getting the error below,

_mysql_exceptions.ProgrammingError: (1064, "You have an error in your 
SQL syntax; check the manual that corresponds to your MySQL server version for 
the right syntax to use near 'release-20180822-16, 'portal.xyv.com')' at line 1")

I am able to the data manually in db using,

 cur.execute ("INSERT INTO test_release_score 
(portalCluster,portalVersion,portalDomain) values ('%s', %s, '%s')" 
% ("1122q", 7, "2wq2w"))

But, when I change it to something like below

# change 7 to 'qa22'

cur.execute ("INSERT INTO test_release_score 
(portalCluster,portalVersion,portalDomain) values ('%s', %s, '%s')" % 
("1122q", "qa12", "2wq2w"))

I get the error:

_mysql_exceptions.OperationalError: (1054, "Unknown column 'qa12' in 'field list'")

Any help would be appreciated.

can you display the query in your question? After the values are substituted. — Mohit Motwani
– Mohit Motwani, Commented Aug 28, 2018 at 11:22
query is a string right? But then you substitute '%s' with some values. Can you show the query after the substitution?Just give the output of the print(query). Because your error displays a syntax error. Plus is possible also show schema of your database table for more clarity. — Mohit Motwani
– Mohit Motwani, Commented Aug 28, 2018 at 11:59
Here it is, ('QUERY ==>', "INSERT INTO test_release_score (portalCluster,portalVersion,portalDomain) values ('ClA', Ve22-16, 'pcom')") — PRK
– PRK, Commented Aug 28, 2018 at 12:28

Eugene Yarmash · Accepted Answer · 2018-08-28 11:25:13Z

2

Instead of interpolating the values into the query with the % operator, use placeholders and query parameters:

query = ("INSERT INTO test_score (portalCluster,portalVersion,portalDomain) "
         "VALUES (%s, %s, %s)") 
cur.execute(query, (clusterList[i], versionList[i], portalDomains[i]))

This way the database driver will do the necessary escaping/quoting for you, which also prevents SQL-injection vulnerabilities.

edited Aug 28, 2018 at 11:25

answered Aug 28, 2018 at 11:20

Eugene Yarmash

152k44 gold badges346 silver badges391 bronze badges

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

2 Comments

PRK Over a year ago

Thank a lot Eugene, it worked. Can you please tell what was wrong with my approach?

Eugene Yarmash Over a year ago

In your original query the second value is not quoted: ('%s', %s, '%s'); therefore it will be treated as a column name when it's a string.

Collectives™ on Stack Overflow

Add data in MySQL using python

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related