Testing Python to MySQL error

Question

I have created a little script that allows me to save data to MySQLdb. At first it was working fine when I was using:

cursor.execute('INSERT INTO people (name, text) VALUES ("dan", "test2")')

The above would save "dan" into the title and "test2" into the text. I wanted to test to see if I was able to define something and fill it in this way. For example if I was to scrape a site and say (dan = soup.title.string) or something like that it would be able to populate this data into the database. I have tried to have a look around but cannot seem to find anything.

import MySQLdb
import sys

try:
    db = MySQLdb.connect(
        host = 'localhost',
        user = 'root',
        passwd = '',
        db = 'python',
        )
except:
    print "db not found"

dan = "dandandan"
test2 = "testing101"

cursor = db.cursor()
cursor.execute('INSERT INTO people (name, text) VALUES (dan, test2)')
cursor.execute('SELECT * FROM people')
result = cursor.fetchall()
db.commit()
db.close()

The error I am receiving is:

C:\Users\********\Desktop>python mysqltest.py
Traceback (most recent call last):
  File "mysqltest.py", line 18, in <module>
    cursor.execute('INSERT INTO people (name) VALUES (dan)')
  File "C:\Python27\lib\site-packages\MySQLdb\cursors.py", line 174, in execute
    self.errorhandler(self, exc, value)
  File "C:\Python27\lib\site-packages\MySQLdb\connections.py", line 36, in defau
lterrorhandler
    raise errorclass, errorvalue
_mysql_exceptions.OperationalError: (1054, "Champ 'dan' inconnu dans field list"
)

Daniel Roseman · Accepted Answer · 2014-12-11 13:24:27Z

3

You need to use parameters.

cursor.execute('INSERT INTO people (name, text) VALUES (%s,%s)', (dan, test2))

answered Dec 11, 2014 at 13:24

Daniel Roseman

602k68 gold badges910 silver badges923 bronze badges

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

Comments

Riccardo · Accepted Answer · 2014-12-11 13:32:09Z

1

Use prepared statements:

cursor.execute("INSERT INTO people (name, text) VALUES (%s,%s)", (dan, test2))

From the documentation :

paramstyle

String constant stating the type of parameter marker formatting expected by the interface. Set to 'format' = ANSI C printf format codes, e.g. '...WHERE name=%s'. If a mapping object is used for conn.execute(), then the interface actually uses 'pyformat' = Python extended format codes, e.g. '...WHERE name=%(name)s'. However, the API does not presently allow the specification of more than one style in paramstyle.

Note that any literal percent signs in the query string passed to execute() must be escaped, i.e. %%.

Parameter placeholders can only be used to insert column values. They can not be used for other parts of SQL, such as table names, statements, etc.

answered Dec 11, 2014 at 13:32

Riccardo

1,5302 gold badges12 silver badges22 bronze badges

2 Comments

BubblewrapBeast Over a year ago

Thanks for that bud, sorry if this question comes across as newbie just want to make sure I am understanding this correctly. If I am to use three rather than two the line would be: cursor.execute('INSERT INTO people (name, text) VALUES (%s,%s,%s)', (dan, test2, test3))

Riccardo Over a year ago

@BubblewrapBeast yes, you can add other parameters, the style doesn't change. Obviously parameters's order is relevant for the command execution.

Collectives™ on Stack Overflow

Testing Python to MySQL error

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related