解析CSV文件，循环并将行插入PostGreSQL数据库

Question

我使用Python psycopg2模块将csv文件(用户列表(的内容复制到PostGreSQL数据库中
因此，我开始使用Python pandas模块解析CSV。然后使用for循环，我尝试在SQL查询中插入数据
我有两个问题：

a( 当我执行角色查询(查询2-见下文(以在数据库中创建新角色时，我得到的是"用户">，而不是用户。如何使用正确的语法插入角色
b(查询3和4(见下文(给出以下错误：

TypeError: not all arguments converted during string formatting 

这个问题究竟是什么？如何解决？

这是完整的代码：

import csv, psycopg2
import pandas as pd
conn = psycopg2.connect("host=localhost dbname=vmap user=postgres password=postgres port=5432")
c = conn.cursor()
# Import_CSV
data = pd.read_csv (r'users.csv', sep=';')   
df = pd.DataFrame(data, columns= ['id','login','mdp','mail','date'])
print(df)
for row in df.itertuples():
print (row.login)
c.execute("INSERT INTO users (user_id, login, email) VALUES(%s, %s, %s);", (row.id, row.login, row.mail))
# query2
c.execute('create role "%s" with encrypted password %s',(row.login, row.mdp))
# query3 
c.execute('grant vitis_user, vmap_user to "%s"',(row.login))    
# query4
c.execute('grant connect on vmap to "%s"',(row.login))

我想要解析的DataFrame(CSV文件的内容(是这样的：

id              login  ...                             mail        date
0  10  ldeschamps-diallo  ...  [e-mail]  2022-01-31
1  11            pmarion  ...            [e-mail]  2022-01-31
2  12             cleroy  ...             [e-mail]  2022-01-31
3  13          lcourtois  ...          [e-mail]  2022-01-31
4  14      rpaul-monnier  ...      [e-mail]  2022-01-31

Answer 1

读取CSV

首先，这里可能不需要pandas，因为您只需要打开csv文件并解析其内容。使用内置的csv模块就足够了。

你可以这样读取文件：

import csv
with open("users.csv", "r", encoding="utf-8", newline="") as fid:
reader = csv.reader(fid, delimiter=";")

正在加载

正如Adrian Klaver所建议的，您可以使用.copy_from方法加载数据，但这只会帮助您解决问题的第一部分，即插入用户。您将从查询号2开始处理您现在面临的相同问题。

解释

另一件事是，psycopg2为您提供了参数化查询，以保护您免受SQL注入的影响，但它被设计为在转义"值"时工作，而您正试图将其与"标识符"一起使用。当您第一次将用户插入到表中时，用户名是一个要插入的值，一切都按预期进行。在第二个查询中，您引用的是数据库中的实际用户(请注意，您用"而不是'转义用户名(。因为psycopg2意味着用户名是一个值，所以它用额外的'来转义它，您最终会得到这样的查询：

create role "'cleroy'" with encrypted password 'password'

如果你想传递用户名，你可能需要使用字符串格式：

c.execute(f'create role "{row.login}" with encrypted password %s',(row.mdp,))

但是通过这种方式，您对sql注入持开放态度。想象一下拥有一个用户名tom"; DROP DATABASE mysuperdatabase;。这样，您将得到查询：create role "tom"; DROP DATABASE mysuperdatabase;，您将面临丢失宝贵数据的潜在风险。

安全的方式

因此，首先需要做的是确保正确地转义了标识符。您可以使用psycopg2.sql模块来执行此操作。

from psycopg2 import sql
login = sql.Identifier(row.login)
query = sql.SQL(f"create role {login} with encrypted password %s")
c.execute(query, (row.mdp,))