mysql - Python 和 Mysql 查询问题 - 结果错误

Question

我尝试在 python 代码中使用 mysql，但是当我在代码中运行选择查询时，得到错误的结果。

这是我的 app.py 代码:

import web
import pymysql
import createdb

render = web.template.render('templates/')

urls = (
    '/', 'index'
)

class index:
    def GET(self):
        createdb.createTables()
        result = createdb.select()
        return render.index(result)


if __name__ == "__main__":
    app = web.application(urls, globals())
    app.run()   

这是我的createdb.py代码:

import pymysql

# Connect to the database
connection = pymysql.connect(host='localhost',
                             port=3306,
                             user='root',
                             password='xxxx',
                             db='mysql',
                             charset='utf8',
                             cursorclass=pymysql.cursors.DictCursor)

def createTables():
    cursor = connection.cursor()
    cursor.execute("CREATE DATABASE mydatabase")
    cursor.execute("CREATE TABLE words(id INT, name VARCHAR(255))")
    cursor.execute("INSERT INTO words(id, name) VALUES(1, 'look')")
    cursor.execute("INSERT INTO words(id, name) VALUES(2, 'book')")
    cursor.execute("INSERT INTO words(id, name) VALUES(3, 'try')")
    cursor.execute("INSERT INTO words(id, name) VALUES(4, 'read')")


def select():
    cursor = connection.cursor()
    selectResult = cursor.execute("SELECT name FROM words WHERE id = 3")
    return selectResult

if __name__ == "__main__":
    createTables()

这是我的index.html代码:

$def with (name)

$if name:
    I just wanted to say <em>hello</em> to $name.
$else:
    <em>Hello</em>, world!

就我而言，我期待这样的结果；

I just wanted to say hello to try.

但是，我得到以下结果；

I just wanted to say hello to 1.

运行应用程序后，我检查了 mysql 工作台，发现数据库已创建为mydatabase，但未创建表。但是，在我停止并重新运行应用程序后，我收到一条错误消息，指出数据库已存在。没关系，然后我在创建数据库的那一行前面添加了注释。重新运行应用程序后，出现另一个错误，提示 words 表已存在。正如我之前所说，当我检查mysql workbench时，没有这个表。也许这个错误的查询结果与这个问题有关。这就是为什么我想详细解释一下。

你能告诉我这里出了什么问题吗？预先感谢您。

Answer 1

更改 createdb.py 代码后，我解决了问题。这是它的修改版本；

import pymysql

# Connect to the database
connection = pymysql.connect(host='localhost',
                             port=3306,
                             user='root',
                             password='xxxxx',
                             db='mydatabase',
                             charset='utf8',
                             cursorclass=pymysql.cursors.DictCursor)

def createTables():
    cursor = connection.cursor()
    cursor.execute("CREATE TABLE words(id INT, name VARCHAR(255))")
    cursor.execute("INSERT INTO words(id, name) VALUES(1, 'look')")
    cursor.execute("INSERT INTO words(id, name) VALUES(2, 'book')")
    cursor.execute("INSERT INTO words(id, name) VALUES(3, 'try')")
    cursor.execute("INSERT INTO words(id, name) VALUES(4, 'read')")
    connection.commit()

def select():
    cursor = connection.cursor()
    cursor.execute("SELECT name FROM words WHERE id = 3")
    selectResult = cursor.fetchone()
    value = "%s" % (selectResult["name"])
    return value

if __name__ == "__main__":
    createTables()
    connection.close()

不，我得到了我想要的结果:

I just wanted to say hello to try.