时间戳

首页 > 解决方案 > pyodbc.ProgrammingError: ('SQL 包含 2 个参数标记，但提供了 34 个参数', 'HY000')

python - pyodbc.ProgrammingError: ('SQL 包含 2 个参数标记，但提供了 34 个参数', 'HY000')

问题描述

我有 2 个错误要解决。

第一个错误：

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

第二个错误：

AttributeError：“记录器”对象没有属性“异常”

我正在使用 pytest 固定装置对数据库进行单元测试。我不明白我在哪里进行更改。

from database.db import DatabaseClient
from mykiosk_cl.kiosk_client import MyKiosk
import pytest
import pyodbc

@pytest.fixture
def setup_db():
    server = "sql-test-server-vdzbi.database.windows.net"
    database = "sql-test-database-vdzbi"
    username = "vdzbi-admin"
    password = "VD9ugvTf2"
    conn = pyodbc.connect("DRIVER={SQL Server};SERVER="+server+";DATABASE="+database+";UID="+username+";PWD="+password)
    cursor = conn.cursor()
    return cursor


def test_freq_data(setup_db):
    cursor=setup_db
    db =DatabaseClient()
    mk = MyKiosk()
    get_data = mk.get_frequency_data()
    db.write_frequency_data(get_data)
    cursor.execute("SELECT * FROM staging_db.Frequency")
    data =cursor.fetchall()
    assert data[0] == (0, 'einmalig'), "recheck"
    print("success")

这些是面临的两个错误：

/tests/test_database.py::test_freq_data Failed: [undefined]AttributeError: 'Logger' object has no attribute 'Exception'
self = <database.db.DatabaseClient object at 0x0000024A325FBF70>
data = [(0, 'einmalig'), (1, 'wöchentlich'), (2, '14-tägig'), (3, 'monatlich'), (4, '2-monatlich'), (5, 'quartalsweise'), ...]

    def write_frequency_data(self, data):
    
        stage_query = "INSERT INTO staging_db.Frequency (FreqId, Title) VALUES (?,?)"
        merge_query = """INSERT INTO mykiosk.Frequency(FreqId, Title)
            SELECT FreqId, Title
            FROM staging_db.Frequency stage
            WHERE stage.FreqId not in (Select FreqId from mykiosk.Frequency)"""
        truncate_query = "TRUNCATE TABLE staging_db.Frequency"
    
    
        try:
>           self.cursor.execute(stage_query,data)
E           pyodbc.ProgrammingError: ('The SQL contains 2 parameter markers, but 34 parameters were supplied', 'HY000')

database\db.py:29: ProgrammingError

During handling of the above exception, another exception occurred:

setup_db = <pyodbc.Cursor object at 0x0000024A32230F30>

    def test_freq_data(setup_db):
        cursor=setup_db
        db =DatabaseClient()
        mk = MyKiosk()
        get_data = mk.get_frequency_data()
>       db.write_frequency_data(get_data)

tests\test_database.py:22: 
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

self = <database.db.DatabaseClient object at 0x0000024A325FBF70>
data = [(0, 'einmalig'), (1, 'wöchentlich'), (2, '14-tägig'), (3, 'monatlich'), (4, '2-monatlich'), (5, 'quartalsweise'), ...]

    def write_frequency_data(self, data):
    
        stage_query = "INSERT INTO staging_db.Frequency (FreqId, Title) VALUES (?,?)"
        merge_query = """INSERT INTO mykiosk.Frequency(FreqId, Title)
            SELECT FreqId, Title
            FROM staging_db.Frequency stage
            WHERE stage.FreqId not in (Select FreqId from mykiosk.Frequency)"""
        truncate_query = "TRUNCATE TABLE staging_db.Frequency"
    
    
        try:
            self.cursor.execute(stage_query,data)
            self.cursor.execute(merge_query,data)
    
        except pyodbc.Error as e:
>           logger.Exception(e)
E           AttributeError: 'Logger' object has no attribute 'Exception'

标签： pythondatabaseunit-testingpytestattributeerror

解决方案

  • 对于数据，您可能希望使用循环或批量插入来插入它

    环形：

    for row in data:
    self.cursor.execute(stage_query, row)

    批量插入：

    self.cursor.executemany(stage_query, data)

  • 对于另一个问题，logger.exception(...)用小写的“e”写

推荐阅读