Python SQLAlchemy Query: AttributeError: 'Connection' object has no attribute 'contextual_connect'

Question

I am trying to follow this tutorial from SQLAlchemy on how to create entries in and query a MYSQL database in python. When I try and query the database for the first time following along in their adding new objects section to test whether an object has been added to the database (see large code block below), I get the following error: AttributeError: 'Connection' object has no attribute 'contextual_connect'

I can query the database. For example, if I change the final line of code to our_user = session.query(User).filter_by(name='ed') it successfully returns a query object, but I cannot figure out how to get the object I entered into the database out of this query result.

Similarly, if I try to loop over the results as they suggest in their querying section:

for instance in session.query(User).order_by(User.id):
    print instance.name, instance.fullname

I get the same error. How can I fix this particular error and are there any other tutorials on using MYSQL in Python with SQLAlchemy that you could point me to?

My code:

import MySQLdb
from sqlalchemy import create_engine

db1 = MySQLdb.connect(host="127.0.0.1",
                      user="root",
                      passwd="****",
                      db="mydata")



from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

from sqlalchemy import Column, Integer, String

class User(Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)
    name = Column(String)
    fullname = Column(String)
    password = Column(String)

    def __init__(self, name, fullname, password):
        self.name = name
        self.fullname = fullname
        self.password = password

    def __repr__(self):
        return "<User('%s','%s', '%s')>" % (self.name, self.fullname, self.password)


ed_user = User('ed', 'Ed Jones', 'edspassword') 

from sqlalchemy.orm import sessionmaker
Session = sessionmaker()
Session.configure(bind=db1)

session = Session()
session.add(ed_user)

our_user = session.query(User).filter_by(name='ed').first()

Update/Working Code:

(1) Change to SQLAlchemy engine as discussed by codeape below.

(2) Remember to create the table: Base.metadata.create_all(engine)

(3) Use the "foolproof" version of the User class from SQLAlchemy's tutorial. Note to SQLAlchemy, we (at least I) feel like a fool and would like you to use to always use the foolproof version in the main body of your tutorial and not as an aside that a busy reader might skip over.

All that yields working code:

import MySQLdb
from sqlalchemy import create_engine

engine = create_engine("mysql://user:password@host/database")

from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

from sqlalchemy import Column, Integer, String, Sequence

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, Sequence('user_id_seq'), primary_key=True)
    name = Column(String(50))
    fullname = Column(String(50))
    password = Column(String(12))

    def __init__(self, name, fullname, password):
        self.name = name
        self.fullname = fullname
        self.password = password

    def __repr__(self):
        return "<User('%s','%s', '%s')>" % (self.name, self.fullname, self.password)



Base.metadata.create_all(engine)

ed_user = User('ed', 'Ed Jones', 'edspassword') 

from sqlalchemy.orm import sessionmaker
Session = sessionmaker()
Session.configure(bind=engine)

session = Session()
session.add(ed_user)



our_user = session.query(User).filter_by(name='ed').first()

print(our_user is ed_user)

Answer 1

You must bind the session to a SQLAlchemy engine, not directly to a MySQLDb connection object.

engine = create_engine("mysql://user:password@host/dbname")
Session.configure(bind=engine)

(You can remove your db1 variable.)

From the tutorial:

The return value of create_engine() is an instance of Engine, and it represents the core interface to the database, adapted through a dialect that handles the details of the database and DBAPI in use.

See also https://docs.sqlalchemy.org/en/latest/orm/tutorial.html

Answer 2

from sqlalchemy.orm import sessionmaker

engine = create_engine("mysql://user:password@host/dbname")
Session = sessionmaker(bind=engine)

session = Session()