E
E
EreDe2021-12-07 15:20:43
Python
EreDe, 2021-12-07 15:20:43

When you try to write data to the database, receiving via the API, Null is written to the cells. How to fix?

I get data from an API request and I want to write it to the database, everything is written correctly except for 2 parameters, they are written as Null, but I put nullable=False in models and I get an error.

If I just print what I get via the API, then everything is there and there are no Nulls

[{'artists_id': '5NMwoStnfHT4LdETlJSwDT', 'name': 'Big Baby Tape', 'genres': ['russian hip hop', 'russian trap'], 'artists_url': 'https://open.spotify.com/artist/5NMwoStnfHT4LdETlJSwDT', 'artists_img_url': 'https://i.scdn.co/image/ab6761610000e5eb2cd70eda99a0714718e67229'}


But when I write to the database, the columns name and artists_img_url are written as Null
Here is my models.py
class Artists(db.Model):
    """ Artists model """

    __tablename__ = 'artists'

    id = db.Column(db.Integer, nullable=False, primary_key=True)
    artist_id = db.Column(db.String(128), name='artist_id', nullable=False, unique=True)
    artist_name = db.Column(db.String(128), nullable=False, unique=True, name='artist_name')
    genres = db.Column(db.String(128), name='genres', nullable=False)
    artist_url = db.Column(db.String(128), name='artist_url', nullable=False)
    artist_img_url = db.Column(db.String(128), name='img_url', nullable=False)

    def __init__(self, artist_id, artist_name, genres, artist_url, artist_img_url):
        self.artist_id = artist_id
        self.name = artist_name
        self.genres = genres
        self.artist_url = artist_url
        self.img_url = artist_img_url

    def __repr__(self):
        return f'{self.artist_id}, {self.name}'


After I receive a response from the API, I sort it and save it to a dictionary, and from the dictionary through for I save it to the database:
for obj in items:

    a = Artists(artist_id=obj['artists_id'],
                artist_name=obj['name'],
                genres=obj['genres'],
                artist_url=obj['artists_url'],
                artist_img_url=obj['artists_img_url'])

    db.session.add(a)


db.session.commit()


Well, I get an error:
Traceback (most recent call last):
  File "/home/anton/PycharmProjects/Project_Epam/venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1782, in _execute_context
    self.dialect.do_executemany(
  File "/home/anton/PycharmProjects/Project_Epam/venv/lib/python3.8/site-packages/sqlalchemy/dialects/postgresql/psycopg2.py", line 951, in do_executemany
    context._psycopg2_fetched_rows = xtras.execute_values(
  File "/home/anton/PycharmProjects/Project_Epam/venv/lib/python3.8/site-packages/psycopg2/extras.py", line 1270, in execute_values
    cur.execute(b''.join(parts))
psycopg2.errors.NotNullViolation: null value in column "artist_name" violates not-null constraint
DETAIL:  Failing row contains (3, 5NMwoStnfHT4LdETlJSwDT, null, {"russian hip hop","russian trap"}, https://open.spotify.com/artist/5NMwoStnfHT4LdETlJSwDT, null).


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

Traceback (most recent call last):
  File "/home/anton/PycharmProjects/Project_Epam/app.py", line 1, in <module>
    from Flask_project import app, db, Users_Top_Item
  File "/home/anton/PycharmProjects/Project_Epam/Flask_project/Users_Top_Item.py", line 83, in <module>
    db.session.commit()
  File "<string>", line 2, in commit
  File "/home/anton/PycharmProjects/Project_Epam/venv/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 1428, in commit
    self._transaction.commit(_to_root=self.future)
  File "/home/anton/PycharmProjects/Project_Epam/venv/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 829, in commit
    self._prepare_impl()
  File "/home/anton/PycharmProjects/Project_Epam/venv/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 808, in _prepare_impl
    self.session.flush()
  File "/home/anton/PycharmProjects/Project_Epam/venv/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 3345, in flush
    self._flush(objects)
  File "/home/anton/PycharmProjects/Project_Epam/venv/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 3485, in _flush
    transaction.rollback(_capture_exception=True)
  File "/home/anton/PycharmProjects/Project_Epam/venv/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
    compat.raise_(
  File "/home/anton/PycharmProjects/Project_Epam/venv/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 207, in raise_
    raise exception
  File "/home/anton/PycharmProjects/Project_Epam/venv/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 3445, in _flush
    flush_context.execute()
  File "/home/anton/PycharmProjects/Project_Epam/venv/lib/python3.8/site-packages/sqlalchemy/orm/unitofwork.py", line 456, in execute
    rec.execute(self)
  File "/home/anton/PycharmProjects/Project_Epam/venv/lib/python3.8/site-packages/sqlalchemy/orm/unitofwork.py", line 630, in execute
    util.preloaded.orm_persistence.save_obj(
  File "/home/anton/PycharmProjects/Project_Epam/venv/lib/python3.8/site-packages/sqlalchemy/orm/persistence.py", line 244, in save_obj
    _emit_insert_statements(
  File "/home/anton/PycharmProjects/Project_Epam/venv/lib/python3.8/site-packages/sqlalchemy/orm/persistence.py", line 1155, in _emit_insert_statements
    c = connection._execute_20(
  File "/home/anton/PycharmProjects/Project_Epam/venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1614, in _execute_20
    return meth(self, args_10style, kwargs_10style, execution_options)
  File "/home/anton/PycharmProjects/Project_Epam/venv/lib/python3.8/site-packages/sqlalchemy/sql/elements.py", line 325, in _execute_on_connection
    return connection._execute_clauseelement(
  File "/home/anton/PycharmProjects/Project_Epam/venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1481, in _execute_clauseelement
    ret = self._execute_context(
  File "/home/anton/PycharmProjects/Project_Epam/venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1845, in _execute_context
    self._handle_dbapi_exception(
  File "/home/anton/PycharmProjects/Project_Epam/venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 2026, in _handle_dbapi_exception
    util.raise_(
  File "/home/anton/PycharmProjects/Project_Epam/venv/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 207, in raise_
    raise exception
  File "/home/anton/PycharmProjects/Project_Epam/venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1782, in _execute_context
    self.dialect.do_executemany(
  File "/home/anton/PycharmProjects/Project_Epam/venv/lib/python3.8/site-packages/sqlalchemy/dialects/postgresql/psycopg2.py", line 951, in do_executemany
    context._psycopg2_fetched_rows = xtras.execute_values(
  File "/home/anton/PycharmProjects/Project_Epam/venv/lib/python3.8/site-packages/psycopg2/extras.py", line 1270, in execute_values
    cur.execute(b''.join(parts))
sqlalchemy.exc.IntegrityError: (psycopg2.errors.NotNullViolation) null value in column "artist_name" violates not-null constraint
DETAIL:  Failing row contains (3, 5NMwoStnfHT4LdETlJSwDT, null, {"russian hip hop","russian trap"}, https://open.spotify.com/artist/5NMwoStnfHT4LdETlJSwDT, null).

[SQL: INSERT INTO artists (artist_id, artist_name, genres, artist_url, img_url) VALUES (%(artist_id)s, %(artist_name)s, %(genres)s, %(artist_url)s, %(img_url)s) RETURNING artists.id]
[parameters: ({'artist_id': '5NMwoStnfHT4LdETlJSwDT', 'artist_name': None, 'genres': ['russian hip hop', 'russian trap'], 'artist_url': 'https://open.spotify.com/artist/5NMwoStnfHT4LdETlJSwDT', 'img_url': None}, {'artist_id': '5NipqMGsY4AUeb7kGT8aVz', 'artist_name': None, 'genres': ['russian emo rap', 'russian hip hop', 'russian trap'], 'artist_url': 'https://open.spotify.com/artist/5NipqMGsY4AUeb7kGT8aVz', 'img_url': None}

(Background on this error at: https://sqlalche.me/e/14/gkpj)

Answer the question

In order to leave comments, you need to log in

1 answer(s)
P
pcdesign, 2021-12-07
@EreDe

for obj in items:
    print(obj)
    a = Artists(artist_id=obj['artists_id'],
                artist_name=obj['name'],
                genres=obj['genres'],
                artist_url=obj['artists_url'],
                artist_img_url=obj['artists_img_url'])

    db.session.add(a)

Do print(obj).
Make sure it's not actually empty.

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question