python - pyodbc.ProgrammingError : ('The SQL contains 0 parameter markers, but 3 parameters were supplied' , 'HY000' )

Question

我正在使用 Python 3.6、flask、pyodbc，并连接到 SQL Server。

我正在尝试连接到数据库，然后创建带参数的查询。

代码如下:

应用.py

from flask import Flask, render_template, flash, redirect, url_for, session, request, logging
from wtforms import Form, StringField, TextAreaField, PasswordField, validators
from passlib.hash import sha256_crypt
from functools import wraps
import pyodbc
import os

app = Flask(__name__)

# Config SQL Server
conn = pyodbc.connect("DRIVER={ODBC Driver 11 for SQL Server};"
                      "SERVER=(local);"
                      "DATABASE=Restaurant;"
                      "UID=sa;"
                      "PWD=1tc0r3;"
                      "Trusted_Connection=yes;")

# ----------------------------------------------------

@app.route("/")
def hello():
    return render_template('accounts/login.html', clients=clients)

# ----------------------------------------------------

# Register Form Class
class RegisterForm(Form):
    name = StringField('Fullname', [validators.Length(min=1, max=50)])
    email = StringField('EmailAddress', [validators.Length(min=6, max=50)])
    password = PasswordField('Password', [
        validators.DataRequired(),
        validators.EqualTo('confirm', message='Passwords do not match')
    ])
    confirm = PasswordField('Confirm Password')

# ----------------------------------------------------

# User Register
@app.route('/register', methods=['GET', 'POST'])
def register():
    form = RegisterForm(request.form)
    if request.method == 'POST' and form.validate():
        name = form.name.data
        email = form.email.data
        password = sha256_crypt.encrypt(str(form.password.data))

        # Create cursor
        cur = conn.cursor()

        # Execute query
        cur.execute("INSERT INTO Users(Fullname, EmailAddress, Password) VALUES(%s, %s, %s)", (name, email, password))

        # Commit to DB
        conn.commit()

        # Close connection
        cur.close()

        flash('You are now registered and can log in', 'success')

        return redirect(url_for('hello'))
    return render_template('accounts/register.html', form=form)

if __name__ == '__main__':
    app.secret_key='1234'
    app.debug = True
    host = os.environ.get('IP', '127.0.0.1')
    port = int(os.environ.get('PORT', 5050))
    app.run(host=host, port=port)

注册.html

{% extends 'layouts/account_base.html'%}

{% block body %}
    <div class="register-box-body">
        <p class="login-box-msg">Register a new membership</p>
        {% from "includes/_formhelpers.html" import render_field %}
        <form  class="site-form" method="POST" action="">
            <div class="form-group">
              {{render_field(form.name, class_="form-control")}}
            </div>
            <div class="form-group">
              {{render_field(form.email, class_="form-control")}}
            </div>
            <div class="form-group">
              {{render_field(form.password, class_="form-control")}}
            </div>
            <div class="form-group">
              {{render_field(form.confirm, class_="form-control")}}
            </div>
            <p><input type="submit" class="btn btn-primary" value="Submit"></p>
        </form>
    </div>
{% endblock %}

当我运行这个程序时，我得到了这个错误:

pyodbc.ProgrammingError: ('SQL 包含 0 个参数标记，但提供了 3 个参数', 'HY000')

Answer 1

pyodbc 使用?，而不是%s，作为参数占位符。