Я новичок в python django. И я пытаюсь создать сайт с несколькими вариантами тестирования.
Я вьетнамец, поэтому мои вопросы на вьетнамском языке.
У меня возникла проблема, когда я пытаюсь вставить объект Answers в базу данных.
Моя ошибка:
django.db.utils.DataError: malformed array literal: "{"A": "Cu01a1m", "B": "chu00e1o"}"
LINE 1: ...answer", "level") VALUES ('Hôm nay ăn gì', '', 1, '{"A": "C...
^
DETAIL: Unexpected array element.
Моя POST просьба:
{
"title": "Hôm năy ăn gì",
"category": 3,
"choices": {
"A": "Cơm",
"B": "cháo"
},
"answer": "A"
}
Вот мой код в models.py:
from django.db import models
from django.contrib.postgres.fields import ArrayField
# Create your models here.
class Category(models.Model):
title = models.TextField(null=False, blank=False)
description = models.TextField(null=False, blank=False)
class Question(models.Model):
title = models.TextField(null=False, blank=False)
description = models.TextField(null=False, blank=True)
category = models.ForeignKey(Category, on_delete=models.CASCADE)
# choices = ArrayField(ArrayField(models.TextField(blank=True)))
choices = models.JSONField(null=False, blank=False)
answer = models.TextField(null=False,blank=False)
level = models.IntegerField(null=True, blank=True)
В views.py:
from django.db.models import query
from django.shortcuts import render
from rest_framework.views import APIView
from rest_framework.response import Response
from rest_framework import status
from rest_framework import generics
from rest_framework import viewsets
from app.models import User, Category, Question
from app.serializers import UserSerializer, QuestionSerializer, CategorySerializer
from django.shortcuts import get_object_or_404
# Create your views here.
class CategoryViewSet(viewsets.ModelViewSet):
serializer_class = CategorySerializer
queryset = Category.objects.all()
class QuestionViewSet(viewsets.ModelViewSet):
serializer_class = QuestionSerializer
queryset = Question.objects.all()
В serializers.py:
from django.contrib.postgres import fields
from rest_framework import serializers
from app.models import User, Question, Category
class CategorySerializer(serializers.HyperlinkedModelSerializer):
class Meta:
model = Category
fields = ['id', 'title', 'description']
class QuestionSerializer(serializers.ModelSerializer):
# category = CategorySerializer(read_only=False)
class Meta:
model = Question
fields = ['id', 'description', 'category', 'choices', 'answer', 'level']
Есть ли решение для меня, чтобы вставить вьетнамский JSON объект в базу данных. Или могу ли я использовать ArrayField вместо JSONField? Если да, то можете ли вы привести мне пример?
Большое спасибо.
Мне кажется, что поле Question модели choices не было изменено с array на json в базе данных. Поэтому то, что вы видите, это:
select '{"A": "Cu01a1m", "B": "chu00e1o"}'::varchar[];
ERROR: malformed array literal: "{"A": "Cu01a1m", "B": "chu00e1o"}"
LINE 1: select '{"A": "Cu01a1m", "B": "chu00e1o"}'::varchar[];
вместо:
select '{"A": "Cu01a1m", "B": "chu00e1o"}'::jsonb;
jsonb
---------------------------
{"A": "Cơm", "B": "cháo"}
В psql посмотрите на таблицу вопросов и узнайте, что на самом деле представляет собой поле?
попробуйте это
from django.contrib.postgres.fields import JSONField
class Question(models.Model):
choices = JSONField()
Вернуться на верх
#python #json #django #postgresql #django-rest-framework
Вопрос:
Я новичок в python django. И я пытаюсь создать тестовый сайт с несколькими вариантами ответов. Я вьетнамец, поэтому мои вопросы на вьетнамском языке.
У меня возникли проблемы, когда я пытаюсь вставить ответы на объекты в базу данных.
Моя ошибка:
django.db.utils.DataError: malformed array literal: "{"A": "Cu01a1m", "B": "chu00e1o"}"
LINE 1: ...answer", "level") VALUES ('Hôm nay ăn gì', '', 1, '{"A": "C...
^
DETAIL: Unexpected array element.
Моя POST просьба:
{
"title": "Hôm năy ăn gì",
"category": 3,
"choices": {
"A": "Cơm",
"B": "cháo"
},
"answer": "A"
}
Вот мой код в models.py :
from django.db import models
from django.contrib.postgres.fields import ArrayField
# Create your models here.
class Category(models.Model):
title = models.TextField(null=False, blank=False)
description = models.TextField(null=False, blank=False)
class Question(models.Model):
title = models.TextField(null=False, blank=False)
description = models.TextField(null=False, blank=True)
category = models.ForeignKey(Category, on_delete=models.CASCADE)
# choices = ArrayField(ArrayField(models.TextField(blank=True)))
choices = models.JSONField(null=False, blank=False)
answer = models.TextField(null=False,blank=False)
level = models.IntegerField(null=True, blank=True)
В views.py :
from django.db.models import query
from django.shortcuts import render
from rest_framework.views import APIView
from rest_framework.response import Response
from rest_framework import status
from rest_framework import generics
from rest_framework import viewsets
from app.models import User, Category, Question
from app.serializers import UserSerializer, QuestionSerializer, CategorySerializer
from django.shortcuts import get_object_or_404
# Create your views here.
class CategoryViewSet(viewsets.ModelViewSet):
serializer_class = CategorySerializer
queryset = Category.objects.all()
class QuestionViewSet(viewsets.ModelViewSet):
serializer_class = QuestionSerializer
queryset = Question.objects.all()
В serializers.py :
from django.contrib.postgres import fields
from rest_framework import serializers
from app.models import User, Question, Category
class CategorySerializer(serializers.HyperlinkedModelSerializer):
class Meta:
model = Category
fields = ['id', 'title', 'description']
class QuestionSerializer(serializers.ModelSerializer):
# category = CategorySerializer(read_only=False)
class Meta:
model = Question
fields = ['id', 'description', 'category', 'choices', 'answer', 'level']
Любое решение для меня, чтобы вставить вьетнамский объект JSON в базу данных. Или я могу использовать arrayField вместо JSONField? Если да, можете ли вы привести мне пример?
Большое спасибо.
Ответ №1:
Мне кажется Question choices , что поле модели не было изменено с array поля на json поле в базе данных. Итак, то, что вы видите, это:
select '{"A": "Cu01a1m", "B": "chu00e1o"}'::varchar[];
ERROR: malformed array literal: "{"A": "Cu01a1m", "B": "chu00e1o"}"
LINE 1: select '{"A": "Cu01a1m", "B": "chu00e1o"}'::varchar[];
вместо:
select '{"A": "Cu01a1m", "B": "chu00e1o"}'::jsonb;
jsonb
---------------------------
{"A": "Cơm", "B": "cháo"}
В psql посмотрите на таблицу вопросов и посмотрите, что на самом деле представляет собой поле?
Комментарии:
1. Как я могу это исправить? Можете ли вы дать мне решение?
2. Вы проверили, какой тип столбца находится в базе данных, и если да, то что это такое? Бежать
migrate?. Трудно сказать без дополнительной информации о том, как вы изменили значение сArrayFieldнаJSONFieldи есть ли в существующем столбце данные?3. Я думаю, что мой тип столбца-jsonb. После того, как я побежал
migrate, у меня возникла эта проблемаdjango.db.utils.ProgrammingError: cannot cast type text[] to jsonb LINE 1: ...on" ALTER COLUMN "choices" TYPE jsonb USING "choices"::jsonb.4. Ошибка говорит о том, что текущим типом столбца в базе данных является текстовый массив(
text[]). В модели есть столбец,JSONFieldкоторый переводитсяjsonbв базу данных. Очевидноmigrate, что он не справится с этим переходом. Я никогда не работалmigrateс Django, поэтому я не собираюсь там чем-то помогать. Я бы создал новый вопрос о том, как перенестиtext[]поле вjsonbодно.
Ответ №2:
попробуйте это
from django.contrib.postgres.fields import JSONField
class Question(models.Model):
choices = JSONField()
New in version 2.8.
Changed in version 2.8.4: added errors introduced in PostgreSQL 12
Changed in version 2.8.6: added errors introduced in PostgreSQL 13
Changed in version 2.9.2: added errors introduced in PostgreSQL 14
Changed in version 2.9.4: added errors introduced in PostgreSQL 15
This module exposes the classes psycopg raises upon receiving an error from
the database with a SQLSTATE value attached (available in the
pgcode attribute). The content of the module is generated
from the PostgreSQL source code and includes classes for every error defined
by PostgreSQL in versions between 9.1 and 15.
Every class in the module is named after what referred as “condition name” in
the documentation, converted to CamelCase: e.g. the error 22012,
division_by_zero is exposed by this module as the class DivisionByZero.
Every exception class is a subclass of one of the standard DB-API
exception and expose the Error interface.
Each class’ superclass is what used to be raised by psycopg in versions before
the introduction of this module, so everything should be compatible with
previously written code catching one the DB-API class: if your code used to
catch IntegrityError to detect a duplicate entry, it will keep on working
even if a more specialised subclass such as UniqueViolation is raised.
The new classes allow a more idiomatic way to check and process a specific
error among the many the database may return. For instance, in order to check
that a table is locked, the following code could have been used previously:
try: cur.execute("LOCK TABLE mytable IN ACCESS EXCLUSIVE MODE NOWAIT") except psycopg2.OperationalError as e: if e.pgcode == psycopg2.errorcodes.LOCK_NOT_AVAILABLE: locked = True else: raise
While this method is still available, the specialised class allows for a more
idiomatic error handler:
try: cur.execute("LOCK TABLE mytable IN ACCESS EXCLUSIVE MODE NOWAIT") except psycopg2.errors.LockNotAvailable: locked = True
- psycopg2.errors.lookup(code)¶
-
Lookup an error code and return its exception class.
Raise
KeyErrorif the code is not found.try: cur.execute("LOCK TABLE mytable IN ACCESS EXCLUSIVE MODE NOWAIT") except psycopg2.errors.lookup("55P03"): locked = True
SQLSTATE exception classes¶
The following table contains the list of all the SQLSTATE classes exposed by
the module.
Note that, for completeness, the module also exposes all the
DB-API-defined exceptions and a few
psycopg-specific ones exposed by the extensions
module, which are not listed here.
|
SQLSTATE |
Exception |
Base exception |
|---|---|---|
|
Class 02: No Data (this is also a warning class per the SQL standard) |
||
|
|
|
|
|
|
|
|
|
Class 03: SQL Statement Not Yet Complete |
||
|
|
|
|
|
Class 08: Connection Exception |
||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Class 09: Triggered Action Exception |
||
|
|
|
|
|
Class 0A: Feature Not Supported |
||
|
|
|
|
|
Class 0B: Invalid Transaction Initiation |
||
|
|
|
|
|
Class 0F: Locator Exception |
||
|
|
|
|
|
|
|
|
|
Class 0L: Invalid Grantor |
||
|
|
|
|
|
|
|
|
|
Class 0P: Invalid Role Specification |
||
|
|
|
|
|
Class 0Z: Diagnostics Exception |
||
|
|
|
|
|
|
|
|
|
Class 20: Case Not Found |
||
|
|
|
|
|
Class 21: Cardinality Violation |
||
|
|
|
|
|
Class 22: Data Exception |
||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Class 23: Integrity Constraint Violation |
||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Class 24: Invalid Cursor State |
||
|
|
|
|
|
Class 25: Invalid Transaction State |
||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Class 26: Invalid SQL Statement Name |
||
|
|
|
|
|
Class 27: Triggered Data Change Violation |
||
|
|
|
|
|
Class 28: Invalid Authorization Specification |
||
|
|
|
|
|
|
|
|
|
Class 2B: Dependent Privilege Descriptors Still Exist |
||
|
|
|
|
|
|
|
|
|
Class 2D: Invalid Transaction Termination |
||
|
|
|
|
|
Class 2F: SQL Routine Exception |
||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Class 34: Invalid Cursor Name |
||
|
|
|
|
|
Class 38: External Routine Exception |
||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Class 39: External Routine Invocation Exception |
||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Class 3B: Savepoint Exception |
||
|
|
|
|
|
|
|
|
|
Class 3D: Invalid Catalog Name |
||
|
|
|
|
|
Class 3F: Invalid Schema Name |
||
|
|
|
|
|
Class 40: Transaction Rollback |
||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Class 42: Syntax Error or Access Rule Violation |
||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Class 44: WITH CHECK OPTION Violation |
||
|
|
|
|
|
Class 53: Insufficient Resources |
||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Class 54: Program Limit Exceeded |
||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Class 55: Object Not In Prerequisite State |
||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Class 57: Operator Intervention |
||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Class 58: System Error (errors external to PostgreSQL itself) |
||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Class 72: Snapshot Failure |
||
|
|
|
|
|
Class F0: Configuration File Error |
||
|
|
|
|
|
|
|
|
|
Class HV: Foreign Data Wrapper Error (SQL/MED) |
||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Class P0: PL/pgSQL Error |
||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Class XX: Internal Error |
||
|
|
|
|
|
|
|
|
|
|
|
|
Issue
I am tryng to login the admin on my flask app using ID, when i signup it is taking inputs perfectly, but while getting values from database(postgresql) it showing «DataError» I have taken input as alphanumeric in HTML form
here are my code:
in model.py i have written code for databese to make columns
model.py
class Admin(UserMixin, db.Model):
id = db.Column(db.String(1000), primary_key=True )
name = db.Column(db.String(1000))
email = db.Column(db.String(100), unique=True)
password = db.Column(db.String(100))
timestamp = db.Column(db.DateTime)
in this auth.py I writen code for POST methods for signup and login
auth.py
@auth.route('/admin')
def admin():
return render_template('admin.html')
@auth.route('/admin', methods=['POST'])
def admin_post():
id = request.form.get('id')
password = request.form.get('password')
remember = True if request.form.get('remember') else False
admins = Admin.query.filter_by(id=id).first()
# check if the user actually exists
# take the user-supplied password, hash it, and compare it to the hashed password in the database
if not admins or not check_password_hash(admins.password, password):
flash('Please check your login details and try again.')
# if the user doesn't exist or password is wrong, reload the page
return redirect(url_for('auth.admin'))
# if the above check passes, then we know the user has the right credentials
login_user(admins, remember=remember)
return redirect(url_for('main.adminOP'))
@auth.route('/admin-signup')
def adminsignup():
return render_template('admin-signup.html')
@auth.route('/admin-signup', methods=['POST'])
def adminsignup_post():
id = request.form.get('id')
email = request.form.get('email')
name = request.form.get('name')
password = request.form.get('password')
timestamp = datetime.now()
# if this returns a admin, then the email already exists in database
admins = Admin.query.filter_by(email=email).first()
if admins: # if a user is found, we want to redirect back to signup page so user can try again
flash('Email address already exists')
return redirect(url_for('auth.adminsignup'))
# create a new user with the form data. Hash the password so the plaintext version isn't saved.
new_admin = Admin(id=id, email=email, name=name,
password=generate_password_hash(password, method='sha256'), timestamp=timestamp)
# add the new user to the database
db.session.add(new_admin)
db.session.commit()
return redirect(url_for('auth.admin'))
The error caught in trace back is:
sqlalchemy.exc.DataError: (psycopg2.errors.InvalidTextRepresentation) invalid input syntax for type
integer: "B1"
LINE 3: WHERE person.id = 'B1'
^
[SQL: SELECT person.id AS person_id, person.email AS person_email, person.password AS person_password,
person.name AS person_name, person.timestamp AS person_timestamp
FROM person
WHERE person.id = %(pk_1)s]
[parameters: {'pk_1': 'B1'}]
(Background on this error at: http://sqlalche.me/e/14/9h9h)
Here B1 is id
admin-signup.html
As I said earlier I have used alhapanumeric in input field
{% extends "base.html" %}
{% block content %}
<div class="column is-4 is-offset-4">
<h3 class="title has-text-info">Sign Up</h3>
<div class="box">
{% with messages = get_flashed_messages() %}
{% if messages %}
<div class="notification is-danger">
{{ messages[0] }}. Go to <a href="{{ url_for('auth.admin') }}">login page</a>.
</div>
{% endif %}
{% endwith %}
<form method="POST" action="/admin-signup">
<div class="field">
<div class="control has-icons-left has-icons-right">
<input class="input is-large" type="text" name="id" placeholder="Your ID" autofocus="" pattern="[a-zA-Z0-9]+"><span
class="icon is-small is-left">
🆔
</span>
<small> Remember: ID is you employee number </small>
</div>
</div>
<div class="field">
<div class="control has-icons-left has-icons-right">
<input class="input is-large" type="email" aria-required="true" name="email" placeholder="Email"
autofocus=""><span class="icon is-small is-left">
📧
</span>
</div>
</div>
<div class="field">
<div class="control has-icons-left has-icons-right">
<input class="input is-large" type="text" aria-required="true" name="name" placeholder="Name"
autofocus=""><span class="icon is-small is-left">
👱
</span>
</div>
</div>
<div class="field">
<div class="control has-icons-left has-icons-right">
<input class="input is-large" type="password" aria-required="true" name="password"
placeholder="Password"><span class="icon is-small is-left">
🔑
</span>
</div>
</div>
<button class="button is-block is-info is-large is-fullwidth">Sign Up</button>
</form>
</div>
</div>
{% endblock %}admin.html
{% extends "base.html" %}
{% block content %}
<div class="column is-4 is-offset-4">
<h3 class="title has-text-info">Admin Login</h3>
<div class="box">
{% with messages = get_flashed_messages() %}
{% if messages %}
<div class="notification is-danger">
{{ messages[0] }}
</div>
{% endif %}
{% endwith %}
<form method="POST" action="/admin">
<div class="field">
<div class="control has-icons-left has-icons-right">
<input class="input is-large" type="text" name="id" placeholder="Your ID" autofocus="" required pattern="[a-zA-Z0-9]+"><span
class="icon is-small is-left">
🆔
</span>
<small> HINT: ID is you employee number </small>
</div>
</div>
<div class="field">
<div class="control has-icons-left has-icons-right">
<input class="input is-large" type="password" name="password" placeholder="Your Password"><span
class="icon is-small is-left">
🔑
</span>
</div>
</div>
<div class="field">
<label class="checkbox">
<input type="checkbox">
Remember me
</label>
</div>
<button class="button is-block is-info is-large is-fullwidth">Login</button>
</form>
</div>
</div>
{% endblock %}After editing code i got this same error caught in debugging
this is person model
class person(UserMixin, db.Model):
# primary keys are required by SQLAlchemy
id = db.Column(db.Integer, primary_key=True)
email = db.Column(db.String(100), unique=True)
password = db.Column(db.String(100))
name = db.Column(db.String(1000))
timestamp = db.Column(db.DateTime)Solution
The issue is that the column id from your person model is an Integer, not a string. Your load_user function queries the person model providing a key B1, which can not be converted to Integer.
If you intend to query your Admin model and also query your person model depending on who is logged, you should add an identifier in your user session during login and retrieve it in the load_user function. Check out this question for more details.
But if you need to treat your users differently (User#1 is an admin but User#2 is not), you should really consider using a boolean flag in your model, something like this:
class User(UserMixin, db.Model):
id = db.Column(db.String(1000), primary_key=True )
name = db.Column(db.String(1000))
# ...
is_admin = db.Column(db.Boolean)
You should really avoid having two user’s models with different types of keys, it can get really messy over time.
If your Flask app tend to grow and have multiple kind of users, you should consider adopt the concept of ‘user roles’. Check out Flask-Principal for this.
Old Answer
The issue is probably because your model and your database aren’t synchronized. In some point the development processes you probably had id = db.Column(db.Integer, primary_key=True) then you ran db.create_all() and then altered the id to id = db.Column(db.String(1000), primary_key=True).
In simple words, Flask believes that id is a string, but PostgreSQL is sure that it is an int. Just to be sure, you could check the DDL of the table person with:
pg_dump -U your_user your_database -t person --schema-only
Note that flask/flask-sqlalchemy does not do automatic migrations, after changing your model you should recreate the database or apply the changes manually (the choice is up to you).
Answered By – Magnun Leno
This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0