CODEWITHSUNDEEP
pythonsqlitesqlalchemyorm
fabiankuenzer
fabiankuenzer

Reputation: 148

SQLALchemy: Where does the attribute error after data import come from?

I wrote an application using SQLAlchemy's object relational mapper to store and access data from an SQLite3 database.

What am I doing wrong?

Here's a simple version of the application:

orm_test.py

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

def orm_setup():
    Base = declarative_base()    
    engine = create_engine('sqlite:///:main:', echo=True)
    Base.metadata.create_all(bind=engine)
    Session = sessionmaker(bind=engine)
    session = Session()
    return Base, engine, session

orm_test_class.py

from sqlalchemy import Column, Integer, String
from orm_test import orm_setup

Base = orm_setup()[0]
engine = orm_setup()[1]

class User(Base):
    __tablename__ = 'person'
    id = Column('id', Integer, primary_key=True)
    username = Column('username', String, unique=True)

Base.metadata.create_all(bind=engine)

orm_test_functions.py

from orm_test_class import User
from orm_test import orm_setup

session = orm_setup()[2]

def add_user(name):
    u = User()
    user_name = str(name)
    u.username = user_name
    session.add(u)
    session.commit()

def get_users():
    users = session.query(User).all()
    for user in users:
        print(user.id, user.username)
    session.close()

main.py

import fire
from orm_test_functions import add_user, get_users

if __name__ == '__main__':
    fire.Fire()

data_import.py

import fire
import pandas as pd
from orm_test import orm_setup

# import engine from orm
engine = orm_setup()[1]

def data_import():
    file = 'Data.xlsx'
    df_user = pd.read_excel(file, sheet_name = 'User')
    df_user.to_sql('person', engine, if_exists='replace', index=False)


# Command line interface
if __name__ == '__main__':
    fire.Fire()

Upvotes: 1

Views: 818

Answers (2)

snakecharmerb
snakecharmerb

Reputation: 55669

The problem is that df_to_sql drops the original table, which has a primary key defined, and replaces it with a table that does not define a primary key.

From the dataframe_to_sql docs

replace: Drop the table before inserting new values.

You can get around this by setting if_exists='append' instead of if_exists='replace'.

df_user.to_sql('person', engine, if_exists='append', index=False)

If necessary you can emulate the "replace" behaviour by deleting any existing records from the table before importing the data.

This is the code I used to reproduce and resolve:

import io
import sqlalchemy as sa
from sqlalchemy import orm
import pandas as pd

Base = orm.declarative_base()


class User(Base):
    __tablename__ = 'person'

    id = sa.Column('id', sa.Integer, primary_key=True)
    username = sa.Column('username', sa.String, unique=True)


engine = sa.create_engine('sqlite://', echo=True, future=False)
# Drop all is redundant for in-memory db
Base.metadata.drop_all(engine)
Base.metadata.create_all(engine)
sessionmaker = orm.sessionmaker(engine)


def add_user(name):
    session = sessionmaker()
    u = User()
    user_name = str(name)
    u.username = user_name
    session.add(u)
    session.commit()


def get_users():
    session = sessionmaker()
    users = session.query(User).all()
    for user in users:
        print(user.id, user.username)
    print()
    session.close()


DATA = """\
id,username
1,Alice
2,Bob
"""

buf = io.StringIO(DATA)
df_user = pd.read_csv(buf)
df_user.to_sql('person', engine, if_exists='append', index=False)


users = get_users()
add_user('Carol')
users = get_users()

engine.dispose()

Upvotes: 2

Tom Chen
Tom Chen

Reputation: 265

You should set the column id with AUTOINCREMENT keyword, see https://docs.sqlalchemy.org/en/14/dialects/sqlite.html#using-the-autoincrement-keyword

Upvotes: 0

Related Questions