使用Python错误填充PostgreSQL数据库

I'm trying to populate a database, but am getting a syntax error.

我正在尝试填充数据库,但是出现语法错误。

I have multiple lists with data, with several lists that include those lists.

我有多个包含数据的列表,其中包含多个包含这些列表的列表。

injury_act_1 = ('2017-01-16 15:36:38','Injury','Unsafe Act', 'TRUE', 'FALSE', 'While lifting a 50 lb item from the floor onto their wokrstation, the employee felt a sharp pain in their lower back.','The employee ran out of room on their workstation because the takeaway conveyor was inoperable')

This repeats for 10 times. There are more lists for action items for those injuries, audits, and action items for those audits.

这重复了10次。还有对于那些伤病,审计,而对于那些审计行动项目行动项目的详细清单。

I have a function to insert these into my database.

我有一个函数将这些插入到我的数据库中。

def populate():
    params = config()
    # connect to the PostgreSQL server
    conn = psycopg2.connect("dbname = safety")
    cur = conn.cursor()

    for i in range(len(injuries)):
        incident = (
            """
            INSERT INTO incident (
                            date_time,
                            incident_type,
                            incident_cat,
                            injury,
                            property_damage,
                            description,
                            root_cause
                            )
                VALUES (
                    """+ injuries[i][0] +""",
                    """+ injuries[i][1] +""",
                    """+ injuries[i][2] +""",
                    """+ injuries[i][3] +""",
                    """+ injuries[i][4] +""",
                    """+ injuries[i][5] +""",
                    """+ injuries[i][6] +"""
            """)

        cur.execute(incident)
        print("Injury case added!")

        action_items = (
            """
            INSERT INTO action_items (
                            case_id,
                            finding,
                            corrective_action
                            )
                VALUES (
                    """+ (i+1) +""",
                    """+ injuries[i][4] +". "+ injuries[i][5] +""",
                    """+ actions[i] +""",
                    )

            """
            )

        cur.execute(action_items)
        print("Action item added!")

    for j in range(len(audits)):
        audit = (
            """
            INSERT INTO audit (
                            date_time,
                            type,
                            que_1,
                            que_2,
                            que_3,
                            ans_1,
                            ans_2,
                            ans_3,
                            )
                VALUES (
                    """+ str(audits[i][0]) +""",
                    """+ audits[i][1] +""",
                    """+ audits[i][2] +""",
                    """+ audits[i][3] +""",
                    """+ audits[i][4] +""",
                    """+ audits[i][5] +""",
                    """+ audits[i][6] +""",
                    """+ audits[i][7] +"""
            """
            )

        cur.execute(audit)
        print("Audit added!")

        action_items_a = (
            """
            INSERT INTO action_items (
                            audit_id,
                            finding,
                            corrective_action
                            )
                VALUES (
                    """+ (i+1) +""",
                    'Audit deficiency',
                    """+ actions_a[i] +""",
                    )
            """
            )

        cur.execute(action_items_a)
        print("Action item added!")

    cur.close()
    conn.commit()

populate()

I keep getting this error:

我一直收到这个错误:

Traceback (most recent call last):
    File "database_populator.py", line 204, in <module>
        populate()
    File "database_populator.py", line 137, in populate
        cur.execute(incident)
psycopg2.ProgrammingError: syntax error at or near "15"
Line 12:        2017-01-16 15:36:38,
                            ^

2 个解决方案

#1

Take a step back here and look at how you'e forming the query. Try to avoid using string concatenation for query building, and especially for user-supplied input of any kind. It's not only bug-prone (as you have found) but a security nightmare.

在这里退后一步,看看你是如何形成查询的。尽量避免使用字符串连接进行查询构建,特别是对于任何类型的用户提供的输入。它不仅容易出错(正如您所发现的那样),而且还是一场安全噩梦。

Your code should use psycopg2's bind parameter support, looking more like:

您的代码应该使用psycopg2的绑定参数支持,看起来更像:

incident = (
            """
            INSERT INTO incident (
                            date_time,
                            incident_type,
                            incident_cat,
                            injury,
                            property_damage,
                            description,
                            root_cause
                            )
                VALUES (""" + (["%s"] * 7).join(", ") + ")"
    cur.execute(incident, injuries)

so that it lets psycopg2 take care of the escaping and formatting.

所以它让psycopg2负责转义和格式化。

You can write out seven literal %ss, like %s, %s, %s, ... if you want. I just prefer the above form.

你可以写出七个字面%ss,比如%s,%s,%s,......如果你愿意的话。我只是喜欢上面的表格。

This way if someone tricks your app into accepting a string in injuries, like say ');DROP TABLE incident;--, you won't be in such trouble.

这样一来,如果有人欺骗你的应用程序接受受伤的字符串,比如说'); DROP TABLE事件; - ,你不会遇到这样的麻烦。

#2

If you still want to construct the query string yourself (which is a bad idea), enclose dates in quotation marks:

如果您仍想自己构造查询字符串(这是一个坏主意),请将日期括在引号中:

"""
    ....
    VALUES (
                '"""+ injuries[i][0] +"""',
                '"""+ injuries[i][1] +"""',
                '"""+ injuries[i][2] +"""',
                '"""+ injuries[i][3] +"""',
                '"""+ injuries[i][4] +"""',
                '"""+ injuries[i][5] +"""',
                '"""+ injuries[i][6] +"""'
        """)

Still better, construct the query with a list comprehension:

更好的是,使用列表解析构造查询:

"""
    ....     
    VALUES(""" + ",".join("'{}'".format(injury) for injury in injuries[i]) + ")"

#1