如何从 MySQL 数据库中获取 Python 中的数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25479710/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How To Fetch data in Python from MySQL database
提问by Ricky
I am new to python and I am facing a problem while fetching data from MySQL DB while I am passing parameters in MySQL query I think my MySQL syntax is incorrect.
我是 python 的新手，当我在 MySQL 查询中传递参数时，我在从 MySQL DB 获取数据时遇到了一个问题，我认为我的 MySQL 语法不正确。
Here is the Error displayed on Screen Like this.
这是屏幕上显示的错误，如下所示。
Internal Server Error
The server encountered an internal error or misconfiguration and was unable to complete your request.
Please contact the server administrator at webmaster@localhost to inform them of the time this error occurred, and the actions you performed just before this error.
More information about this error may be available in the server error log. Apache/2.4.6 (Ubuntu) Server at localhost Port 80
内部服务器错误
服务器遇到内部错误或配置错误，无法完成您的请求。
请通过 webmaster@localhost 联系服务器管理员，告知他们此错误发生的时间，以及您在此错误之前执行的操作。
服务器错误日志中可能提供有关此错误的更多信息。本地主机端口 80 上的 Apache/2.4.6 (Ubuntu) 服务器
Here Is My Code For Select query in that I want to fetch data from get parameter of Url.
这是我的 Select 查询代码，因为我想从 Url 的 get 参数中获取数据。
#!/usr/bin/python2.7
import cgi
import cgitb
cgitb.enable()
print "Content-type: text/html\n\n"
print "<h1>Hello Python</h1>"
#!/usr/bin/python
import MySQLdb
# Create instance of FieldStorage
form = cgi.FieldStorage()
# Get data from fields
first_name = form.getvalue('first_name')
last_name = form.getvalue('last_name')
# Open database connection
db = MySQLdb.connect("localhost","root","123456789","testdrive" )
# prepare a cursor object using cursor() method
cursor = db.cursor()
# Prepare SQL query to INSERT a record into the database
sqlstmt = "SELECT * FROM EMPLOYEE WHERE FIRST_NAME = %(first_name)s AND LAST_NAME = %(last_name)s"
try:
# Execute the SQL command
cursor.execute(sqlstmt, {'first_name': first_name, 'last_name': last_name})
# Fetch all the rows in a list of lists.
results = cursor.fetchall()
for row in results:
fname = row[0]
lname = row[1]
age = row[2]
sex = row[3]
income = row[4]
# Now print fetched result
print "fname=%s,lname=%s,age=%d,sex=%s,income=%d" % \
(fname, lname, age, sex, income )
except:
print "Error: unable to fecth data"
# disconnect from server
db.close()
回答by metatoaster
First off, you should try abstracting all that into a single function you can call outside of CGI, but that's a whole other exercise now. Anyway, if you had done that you can get the stacktrace much easier to see what you did wrong, however, I can see you have a syntax error in the code you helpfully included
首先，您应该尝试将所有这些抽象为可以在 CGI 之外调用的单个函数，但现在这是另一个练习。无论如何，如果你这样做了，你可以更容易地通过堆栈跟踪来查看你做错了什么，但是，我可以看到你在帮助包含的代码中存在语法错误
sql = "SELECT * FROM EMPLOYEE WHERE FIRST_NAME = '".first_name."' AND LAST_NAME = '".last_name."'"
Python string concatenation uses the +operator, not .like it is in PHP.
Python 字符串连接使用+运算符，.不像在 PHP 中那样。
Second, this code is not secure. See http://xkcd.com/327/
其次，此代码不安全。见http://xkcd.com/327/
To fix this, the cursor.executemethod provides a second argument to fill out the tokens, this is what you should do
为了解决这个问题，该cursor.execute方法提供了第二个参数来填写令牌，这是你应该做的
sqlstmt = "SELECT * FROM EMPLOYEE WHERE FIRST_NAME = %(first_name)s AND LAST_NAME = %(last_name)s"
try:
cursor.execute(sqlstmt, {'first_name': first_name, 'last_name': last_name})
...
回答by kecer
You have an error in this line
你在这一行有错误
sql = "SELECT * FROM EMPLOYEE WHERE FIRST_NAME = '".first_name."' AND LAST_NAME = '".last_name."'"
This isn't PHP, meaning you can't concat strings and variables like this. I assume you wan't to make prepared statements. In that case, you should read following reply.
这不是 PHP，这意味着您不能像这样连接字符串和变量。我假设你不想做准备好的陈述。在这种情况下，您应该阅读以下回复。
Relevant part of your code would look like this:
代码的相关部分如下所示：
cursor.execute("SELECT * FROM EMPLOYEE WHERE FIRST_NAME = %s AND LAST_NAME = %s", [first_name, last_name])
回答by lenhhoxung
I think we don't need fetchall()here, which maybe legacy from sqlite code. Just do something like:
我认为我们不需要fetchall()这里，这可能是来自 sqlite 代码的遗留问题。只需执行以下操作：
for row in cursor:
x = row[0]
y = row[1]
...
回答by SMshrimant
Try this code
试试这个代码
import mysql.connector
from mysql.connector import Error
try:
mySQLconnection = mysql.connector.connect(host='localhost',
database='python_database',
user='username',
password='passw0rd')
sql_select_Query = "select * from tablename"
cursor = mySQLconnection .cursor()
cursor.execute(sql_select_Query)
records = cursor.fetchall()
for row in records:
print("Sr No = ", row[0], )
print("Name = ", row[1])
print("Age = ", row[2])
print("Gender = ", row[3], "\n")
cursor.close()
except Error as e :
print ("Error connecting MySQL", e)
finally:
#closing database connection.
if(mySQLconnection .is_connected()):
connection.close()
print("MySQL connection is closed Now"
