CODEWITHSUNDEEP
pythonsqlalchemy
Chris
Chris

Reputation: 301

'Essential SQLAlchemy' example question causing Duplicate entry IntegrityError

I'm learning SQLAlchemy with the help of the book Essential SQLAlchemy.

One of the examples is pivotal to what I want to learn - adding related objects. And I just can't get it to work. And I'm hoping this community can help identify whether I'm missing something, or whether there's a flaw in the example. Obviously the former is the most probable. But, regardless, I'm hoping this might be helpful for others. For me, the book has felt straight-forward up until this critical point.

The examples given are from Chapter 7, and the troublesome "orders" code is Example 7-25. Full code examples are at https://github.com/jasonamyers/essential-sqlalchemy-2e

I start with the following setup:

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

engine = create_engine('mysql+pymysql://root:password@localhost/Alberta', pool_recycle=3600)

Session = sessionmaker(bind=engine)

session = Session()

from datetime import datetime
from sqlalchemy import (Table, Column, Integer, Numeric, String, DateTime, ForeignKey, Boolean, desc, func)
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship, backref

Base = declarative_base()

class Cookie(Base):
    __tablename__ = 'cookies'
    cookie_id = Column(Integer, primary_key=True)
    cookie_name = Column(String(50), index=True)
    cookie_recipe_url = Column(String(255))
    cookie_sku = Column(String(55))
    quantity = Column(Integer())
    unit_cost = Column(Numeric(12, 2))

    def __repr__(self):
        return "Cookie(cookie_name='{self.cookie_name}', " \
                   "cookie_recipe_url='{self.cookie_recipe_url}', " \
                   "cookie_sku='{self.cookie_sku}', " \
                   "quantity={self.quantity}, " \
                   "unit_cost={self.unit_cost})".format(self=self)


class User(Base):
    __tablename__ = 'users'
    user_id = Column(Integer(), primary_key=True)
    username = Column(String(15), nullable=False, unique=True)
    email_address = Column(String(255), nullable=False)
    phone = Column(String(20), nullable=False)
    password = Column(String(25), nullable=False)
    created_on = Column(DateTime(), default=datetime.now)
    updated_on = Column(DateTime(), default=datetime.now, onupdate=datetime.now)

def __repr__(self):
    return "User(username='{self.username}', " \
                 "email_address='{self.email_address}', " \
                 "phone='{self.phone}', " \
                 "password='{self.password}')".format(self=self)

class Order(Base):
    __tablename__ = 'orders'
    order_id = Column(Integer(), primary_key=True)
    user_id = Column(Integer(), ForeignKey('users.user_id'))
    shipped = Column(Boolean(), default=False)

     user =  relationship("User", backref=backref('orders', order_by=order_id))

    def __repr__(self):
        return "Order(user_id={self.user_id}, " \
                  "shipped={self.shipped})".format(self=self)


class LineItem(Base):
    __tablename__ = 'line_items'
    line_item_id = Column(Integer(), primary_key=True)
    order_id = Column(Integer(), ForeignKey('orders.order_id'))
    cookie_id = Column(Integer(), ForeignKey('cookies.cookie_id'))
    quantity = Column(Integer())
    extended_cost = Column(Numeric(12, 2))

    order = relationship("Order", backref=backref('line_items', order_by=line_item_id))
    cookie = relationship("Cookie", uselist=False)

    def __repr__(self):
        return "LineItems(order_id={self.order_id}, " \
                      "cookie_id={self.cookie_id}, " \
                      "quantity={self.quantity}, " \
                      "extended_cost={self.extended_cost})".format(
                self=self)

Base.metadata.create_all(engine)

I successfully run the following example to populate the database with a users with username cookiemon:

cookiemon = User(username='cookiemon',
    email_address='[email protected]',
    phone='416-555-1212',
    password='password'
    )

session.add(cookiemon)
session.commit()

Now in the next example in the book, I'm told that the following code will work. 

o1 = Order()
o1.user = cookiemon
session.add(o1)

cc = session.query(Cookie).filter(Cookie.cookie_name == "chocolate chip").one()
line1 = LineItem(cookie=cc, quantity=2, extended_cost=1.00)

pb = session.query(Cookie).filter(Cookie.cookie_name == 
                              "peanut butter").one()
line2 = LineItem(quantity=12, extended_cost=3.00)
line2.cookie = pb 
line2.order = o1

o1.line_items.append(line1)
o1.line_items.append(line2)
session.commit()

However, because user cookiemon already exists in the database, I get the following error:

sqlalchemy.exc.IntegrityError: (raised as a result of Query-invoked autoflush; consider using a session.no_autoflush block if this flush is occurring prematurely) (pymysql.err.IntegrityError) (1062, u"Duplicate entry 'cookiemon' for key'username'") [SQL: u'INSERT INTO users (username, email_address, phone, password, created_on, updated_on) VALUES (%(username)s, %(email_address)s, %(phone)s, %(password)s, %(created_on)s, %(updated_on)s)'] [parameters: {'username': 'cookiemon', 'phone': '416-555-1212', 'created_on': datetime.datetime(2018, 9, 14, 13, 7, 29, 353673), 'updated_on': datetime.datetime(2018, 9, 14, 13, 7, 29, 353701), 'password': 'passwordEW', 'email_address': '[email protected]'}]

The order code above works like a charm if I delete the cookiemon user first (no surprise there). But that's hardly going to work well, in the real world.

And I can see that there's a solution with no_autoflush. But is that necessary? Or am I just missing something simple but important here?

Here's the Traceback:

cd "/Users/chris/Desktop/Banff Experiments" ; env "PYTHONIOENCODING=UTF-8" "PYTHONUNBUFFERED=1" "PYTHONPATH=/Users/chris/.vscode/extensions/ms-python.python-2018.8.0/pythonFiles/experimental/ptvsd" "/Users/chris/Desktop/Banff Experiments/banff/bin/python" -m ptvsd --host localhost --port 59094 "/Users/chris/Desktop/Banff Experiments/workspace4.py"
Traceback (most recent call last):
File "/System/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/runpy.py", line 162, in _run_module_as_main
"__main__", fname, loader, pkg_name)
File "/System/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/runpy.py", line 72, in _run_code
exec code in run_globals
 File "/Users/chris/.vscode/extensions/ms-python.python-2018.8.0/pythonFiles/experimental/ptvsd/ptvsd/__main__.py", line 211, in <module>
singlesession=args.single_session)
 File "/Users/chris/.vscode/extensions/ms-python.python-2018.8.0/pythonFiles/experimental/ptvsd/ptvsd/__main__.py", line 205, in main
debug_main(addr, name, kind, *extra, **kwargs)
 File "/Users/chris/.vscode/extensions/ms-python.python-2018.8.0/pythonFiles/experimental/ptvsd/ptvsd/_local.py", line 30, in debug_main
run_file(address, name, *extra, **kwargs)
 File "/Users/chris/.vscode/extensions/ms-python.python-2018.8.0/pythonFiles/experimental/ptvsd/ptvsd/_local.py", line 64, in run_file
run(argv, addr, **kwargs)
 File "/Users/chris/.vscode/extensions/ms-python.python-2018.8.0/pythonFiles/experimental/ptvsd/ptvsd/_local.py", line 125, in _run
_pydevd.main()
 File "/Users/chris/.vscode/extensions/ms-python.python-2018.8.0/pythonFiles/experimental/ptvsd/ptvsd/_vendored/pydevd/pydevd.py", line 1743, in main
debugger.connect(host, port)
 File "/Users/chris/.vscode/extensions/ms-python.python-2018.8.0/pythonFiles/experimental/ptvsd/ptvsd/_vendored/pydevd/pydevd.py", line 1099, in run
return self._exec(is_module, entry_point_fn, module_name, file, globals, locals)
 File "/Users/chris/.vscode/extensions/ms-python.python-2018.8.0/pythonFiles/experimental/ptvsd/ptvsd/_vendored/pydevd/pydevd.py", line 1106, in _exec
pydev_imports.execfile(file, globals, locals)  # execute the script
 File "/Users/chris/Desktop/Banff Experiments/workspace4.py", line 94, in <module>
session.commit()
 File "/Users/chris/Desktop/Banff Experiments/banff/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 943, in commit
self.transaction.commit()
 File "/Users/chris/Desktop/Banff Experiments/banff/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 467, in commit
self._prepare_impl()
 File "/Users/chris/Desktop/Banff Experiments/banff/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 447, in _prepare_impl
self.session.flush()
 File "/Users/chris/Desktop/Banff Experiments/banff/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 2254, in flush
self._flush(objects)
 File "/Users/chris/Desktop/Banff Experiments/banff/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 2380, in _flush
transaction.rollback(_capture_exception=True)
 File "/Users/chris/Desktop/Banff Experiments/banff/lib/python2.7/site-packages/sqlalchemy/util/langhelpers.py", line 66, in __exit__
compat.reraise(exc_type, exc_value, exc_tb)
 File "/Users/chris/Desktop/Banff Experiments/banff/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 2344, in _flush
flush_context.execute()
 File "/Users/chris/Desktop/Banff Experiments/banff/lib/python2.7/site-packages/sqlalchemy/orm/unitofwork.py", line 391, in execute
rec.execute(self)
 File "/Users/chris/Desktop/Banff Experiments/banff/lib/python2.7/site-packages/sqlalchemy/orm/unitofwork.py", line 556, in execute
uow
 File "/Users/chris/Desktop/Banff Experiments/banff/lib/python2.7/site-packages/sqlalchemy/orm/persistence.py", line 181, in save_obj
mapper, table, insert)
 File "/Users/chris/Desktop/Banff Experiments/banff/lib/python2.7/site-packages/sqlalchemy/orm/persistence.py", line 866, in _emit_insert_statements
execute(statement, params)
File "/Users/chris/Desktop/Banff Experiments/banff/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 948, in execute
return meth(self, multiparams, params)
 File "/Users/chris/Desktop/Banff Experiments/banff/lib/python2.7/site-packages/sqlalchemy/sql/elements.py", line 269, in _execute_on_connection
return connection._execute_clauseelement(self, multiparams, params)
 File "/Users/chris/Desktop/Banff Experiments/banff/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1060, in _execute_clauseelement
compiled_sql, distilled_params
 File "/Users/chris/Desktop/Banff Experiments/banff/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1200, in _execute_context
context)
 File "/Users/chris/Desktop/Banff Experiments/banff/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1413, in _handle_dbapi_exception
exc_info
 File "/Users/chris/Desktop/Banff Experiments/banff/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 265, in raise_from_cause
reraise(type(exception), exception, tb=exc_tb, cause=cause)
 File "/Users/chris/Desktop/Banff Experiments/banff/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1193, in _execute_context
context)
 File "/Users/chris/Desktop/Banff Experiments/banff/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 509, in do_execute
cursor.execute(statement, parameters)
 File "/Users/chris/Desktop/Banff Experiments/banff/lib/python2.7/site-packages/pymysql/cursors.py", line 170, in execute
result = self._query(query)
 File "/Users/chris/Desktop/Banff Experiments/banff/lib/python2.7/site-packages/pymysql/cursors.py", line 328, in _query
conn.query(q)
 File "/Users/chris/Desktop/Banff Experiments/banff/lib/python2.7/site-packages/pymysql/connections.py", line 516, in query
self._affected_rows = self._read_query_result(unbuffered=unbuffered)
 File "/Users/chris/Desktop/Banff Experiments/banff/lib/python2.7/site-packages/pymysql/connections.py", line 727, in _read_query_result
result.read()
 File "/Users/chris/Desktop/Banff Experiments/banff/lib/python2.7/site-packages/pymysql/connections.py", line 1066, in read
first_packet = self.connection._read_packet()
 File "/Users/chris/Desktop/Banff Experiments/banff/lib/python2.7/site-packages/pymysql/connections.py", line 683, in _read_packet
packet.check_error()
 File "/Users/chris/Desktop/Banff Experiments/banff/lib/python2.7/site-packages/pymysql/protocol.py", line 220, in check_error
err.raise_mysql_exception(self._data)
 File "/Users/chris/Desktop/Banff Experiments/banff/lib/python2.7/site-packages/pymysql/err.py", line 109, in raise_mysql_exception
raise errorclass(errno, errval)
sqlalchemy.exc.IntegrityError: (pymysql.err.IntegrityError) (1062, u"Duplicate entry 'cookiemon' for key 'username'") [SQL: u'INSERT INTO users (username, email_address, phone, password, created_on, updated_on) VALUES (%(username)s, %(email_address)s, %(phone)s, %(password)s, %(created_on)s, %(updated_on)s)'] [parameters: {'username': 'cookiemon', 'phone': '416-555-1212', 'created_on': datetime.datetime(2018, 9, 14, 13, 44, 46, 916687), 'updated_on': datetime.datetime(2018, 9, 14, 13, 44, 46, 916712), 'password': 'passwordEW', 'email_address': '[email protected]'}] (Background on this error at: http://sqlalche.me/e/gkpj)

Upvotes: 1

Views: 2719

Answers (1)

nosklo
nosklo

Reputation: 223172

Now reading the comments, I think I understand your problem. This piece of code:

cookiemon = User(username='cookiemon',
    email_address='[email protected]',
    phone='416-555-1212',
    password='password'
    )

session.add(cookiemon)
session.commit()

Creates a new user and stores it in the database. You can't run it twice, because it is already stored in the database. You can only execute that once, or you get the error.

Now later, if you want to use the same user, you can't create it again. If you still have the same cookiemon variable in memory, you can just use it, but if you don't, you have to fetch the existing user from the database instead of creating a new one:

cookiemon = session.query(User).filter(User.username=='cookiemon').one()

You could combine both in a single execution:

# try to get the user from the databae
cookiemon = session.query(User).filter(User.username=='cookiemon').first()
if cookiemon is None: # not found, create new one:
    cookiemon = User(
        username='cookiemon',
        email_address='[email protected]',
        phone='416-555-1212',
        password='password'
    )
    session.add(cookiemon)
    session.commit()

Upvotes: 1

Related Questions