时间戳

首页 > 解决方案 > 三引号中的 Django 原始 sql 插入查询：Django 将 ajax 请求数据中的空值解释为 None 列

django - 三引号中的 Django 原始 sql 插入查询：Django 将 ajax 请求数据中的空值解释为 None 列

问题描述

我正在做一个 Django/React 项目。使用DRF，我抛出了一个 API 路由，用于在我的PostgreSQL数据库中执行 SQL 查询。但是我当前的代码设置存在问题。

我INSERT在 API 中设置了我的查询，raw queries (using cursor)包含在三引号多行字符串"""INSERT..."""中，然后使用字符串格式格式化我的值%s。在我的 API 中，我将请求正文中的每个数据捕获到一个变量中。如果所有请求数据都已填写，一切正常。但是，如果是null，Django 显然会分配None给变量。

现在回到我的 sql 查询，Django 会将 null%s视为None和 atable column而不是正确的 null 值，从而抛出一个ProgrammingError column "none" does not exist.

以下是示例代码：

反应前端

const [lastName, setLastName] = useState('')
const [firstName, setFirstName] = useState('')
const [middleName, setMiddleName] = useState('')
const [nameExtn, setNameExtn] = useState('')
const [sex, setSex] = useState('')
const [civilStatus, setCivilStatus] = useState('')
const [bloodType, setBloodType] = useState('')
const [height, setHeight] = useState('')
const [weight, setWeight] = useState('')

const newPersonalInfo = (token, data) => {
    let endpoint = "/jobnet/api/profile/pds/basic/personal/"
    let lookupOptions = {
        method: "POST",
        headers: {
          'Content-Type': 'application/json',
            'Authorization': `Token ${token}`
        },
        body: JSON.stringify(data),
        credentials: 'include'
      }
    fetch(endpoint, lookupOptions)
    .then(res=>res.json())
    .then(function(info){
     console.log(info)
    })
    .catch(err=>console.log(err));
}

const handleNewPersonalInfo = () => {
    newPersonalInfo(props.user.token, {
        firstname: firstName,
        lastname: lastName,
        middlename: middleName,
        extension: nameExtn,
        birthdate: selectedDate,
        sex: sex,
        civilstatus: civilStatus,
        bloodtype: bloodType,
        height: height,
        weight: weight,
    })
}

...

return(
    <Button
        variant="contained"
        color="primary"
        onClick={handleNewPersonalInfo}
    >
        SAVE
    </Button>
)

Django API (DRF)

class APIListCreate__PersonalInfo(generics.ListCreateAPIView):
    try:
        serializer_class = PDSBasicPersonalInfoSerializer
        permission_classes  = (jobnet_permissions.IsAuthenticated,)
        authentication_classes = (knox_TokenAuthentication,)
    except Exception as e:
        traceback.print_exc()

    def get_queryset(self):
        user = self.request.user
        if user and user.is_authenticated:
            query = ("""
                SELECT
                    bsinfo.firstname,
                    bsinfo.middlename,
                    bsinfo.surname,
                    bsinfo.birthdate,
                    bsinfo.sex,
                    bsinfo.extention,
                    bsinfo.civilstatus,
                    bsinfo.height_m,
                    bsinfo.weight_kg,
                    bsinfo.bloodtype,
                    bsinfo.photo_path
                FROM jobnet_app.basicinfo bsinfo
                WHERE
                id=%s
            """ % user.id)
            return raw_sql_select(query, "default")
        else:
            return None

    def get(self, request):
        data = [
            {
                "first_name": col.firstname,
                "middle_name": col.middlename,
                "last_name": col.surname,
                "name_extension": col.extention,
                "birthdate": col.birthdate,
                "sex": col.sex,
                "civil_status": col.civilstatus,
                "height": col.height_m,
                "weight": col.weight_kg,
                "blood_type": col.bloodtype,
                "photo_path": col.photo_path
            } for col in self.get_queryset()[1]
        ]
        return Response(data[0])

    def post(self, request):
        try:
            user = request.user.id
            firstname = request.data.get('firstname') or ''
            middlename = request.data.get('middlename') or ''
            lastname = request.data.get('lastname') or ''
            birthdate = request.data.get('birthdate') or ''
            sex = request.data.get('sex') or ''
            extension = request.data.get('extension') or ''
            civilstatus = request.data.get('civilstatus') or ''
            height_m = request.data.get('height') or 0
            weight_kg = request.data.get('weight') or 0
            bloodtype = request.data.get('bloodtype') or ''
            query = ("""
                START TRANSACTION;
                    INSERT INTO jobnet_app.basicinfo (
                        id,
                        firstname,
                        middlename,
                        surname,
                        birthdate,
                        sex,
                        extention,
                        civilstatus,
                        height_m,
                        bloodtype,
                        weight_kg
                    )
                    VALUES (%s,'%s','%s','%s','%s','%s','%s','%s',%s,'%s',%s);
            """ % (
                    user,
                    firstname,
                    middlename,
                    lastname,
                    birthdate,
                    sex,
                    extension,
                    civilstatus,
                    height_m,
                    bloodtype,
                    weight_kg
                )
            )
            unformatted_query_result = raw_sql_insert(query, "default")
            if unformatted_query_result:
                raw_sql_commit("default")
                return Response({
                    "success": True,
                    "message": "Your basic personal information has been updated successfully."
                }, status=status.HTTP_201_CREATED)
            else:
                raw_sql_rollback("default")
                return Response({
                    "success": False,
                    "message": "There was a problem updating your personal information."
                }, status=status.HTTP_400_BAD_REQUEST)
        except Exception as e:
            traceback.print_exc()
            return Response({
                "success": False,
                "message":"Internal System Error: " + str(e)
            }, status=status.HTTP_500_INTERNAL_SERVER_ERROR)

使用上述设置，我得到这样的错误：

Traceback (most recent call last):
  File "C:\Users\Acer\Envs\adnwebsite-react\lib\site-packages\django\db\backends\utils.py", line 83, in _execute
    return self.cursor.execute(sql)
psycopg2.ProgrammingError: column "none" does not exist
LINE 4: ...on','2002-03-18T06:18:45.284Z','Male','','Single',None,'B+',...
                                                             ^


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "D:\PGADN\Projects\pgadn-v2-website\adnwebsite\reactify\utils.py", line 17, in raw_sql_insert
    cn.execute(query)
  File "C:\Users\Acer\Envs\adnwebsite-react\lib\site-packages\django\db\backends\utils.py", line 100, in execute
    return super().execute(sql, params)
  File "C:\Users\Acer\Envs\adnwebsite-react\lib\site-packages\django\db\backends\utils.py", line 68, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "C:\Users\Acer\Envs\adnwebsite-react\lib\site-packages\django\db\backends\utils.py", line 77, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "C:\Users\Acer\Envs\adnwebsite-react\lib\site-packages\django\db\backends\utils.py", line 85, in _execute
    return self.cursor.execute(sql, params)
  File "C:\Users\Acer\Envs\adnwebsite-react\lib\site-packages\django\db\utils.py", line 89, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "C:\Users\Acer\Envs\adnwebsite-react\lib\site-packages\django\db\backends\utils.py", line 83, in _execute
    return self.cursor.execute(sql)
django.db.utils.ProgrammingError: column "none" does not exist
LINE 4: ...on','2002-03-18T06:18:45.284Z','Male','','Single',None,'B+',...

我在用：

  1. Django 2.0.6
  2. Django 休息框架 3.10.3
  3. PostgreSQL 11.3（在我的开发机器上）和 PostgreSQL 12.1（在生产服务器上）（虽然上面的错误还没有被复制到服务器上）

注意： raw_sql_select , raw_sql_insert, raw_sql_commit,raw_sql_rollback只是定制的帮助函数，它们在后台处理实际的光标执行。

标签： djangoreactjspostgresqldjango-rest-framework

解决方案

使用SQL 参数而不是%创建插入脚本。

例如-

# **WRONG** 
>>> cur.execute("INSERT INTO numbers VALUES (%s, %s)" % (10, 20)) 

# **correct**
>>> cur.execute("INSERT INTO numbers VALUES (%s, %s)", (10, 20))

您必须cusrsor.execute在您的辅助函数中使用。cusrsor.execute如上所示，将值作为列表作为第二个参数传递。

点击此链接阅读更多内容。

推荐阅读