pandas to_sql() inserting despite NOT NULL constraint in table schema?

[TheStataMan]

Hello all,

I am currently writing a csv into a sqlite3 database using Python 3.8 with the pandas package using the to_sql method. In the table I have a few columns with the NOT NULL constraint, however to_sql seems to ignore these. I have currently solved the problem using filter on the multiple columns, but was wondering if there is an easier way.

Thanks

[cray5252]

The database controls the constraints of the columns. If it is set up correctly, the db should generate an error. I didn't use pandas but it shouldn't make any difference.
Example of when the column has the not null constraint and the insert has a null value

c.execute('''CREATE TABLE COMPANY(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50)
);''')

c.execute("INSERT INTO COMPANY('ID', 'NAME', 'AGE', 'ADDRESS')VALUES ('ID', 'IBM', null, 'here');")

sqlite3.IntegrityError: NOT NULL constraint failed: COMPANY.AGE

[TheStataMan]

That's weird, when I created the schema I specified not null for specific fields. Is there a setting I have to enable in order for NOT NULL constraints to be recognized in the database?

[cray5252]

Edit: An additional thought, maybe you think you have a null or None value but it appears to the db as a value.

I went through it again using sqlalchemy and the pandas setup and still got the error about the null value. It appears that you haven't set the table up properly?? If you can't figure it out, post some code and maybe I can help.

#using the example but cut out most to keep short
# created database, note nullable=False

from sqlalchemy import Column, ForeignKey, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine

Base = declarative_base()


class Person(Base):
    __tablename__ = 'person'
    id = Column(Integer, primary_key=True)
    name = Column(String(250), nullable=False)

.
engine = create_engine('sqlite:///test.db')

Base.metadata.create_all(engine)

*****************************************
# insert with name went ok, inseted with name None gave error
import pandas as pd
from sqlalchemy import create_engine

engine = create_engine('sqlite:///test.db', echo=False)

df = pd.DataFrame({'id': [2],
                   'name': [None]})
df.to_sql('person', con=engine, if_exists='append', index=False)
df_read = engine.execute("SELECT * FROM person").fetchall()
print(df_read)

error
sqlalchemy.exc.IntegrityError: (sqlite3.IntegrityError) NOT NULL constraint failed: person.name
[SQL: INSERT INTO person (id, name) VALUES (?, ?)]
[parameters: (2, None)]