How would I use database in sqlalchemy using create_engine?

Question

Right now; I'm getting the following error:

    ProgrammingError: (psycopg2.ProgrammingError) relation "tableName" does not exist
    LINE 2: FROM tableName

I assumed it's because I'm not using that database for the session; so i went and procceded to use session.execute and use the sql statement USE to use the database but it is throwing me syntax error

engine = create_engine('postgresql://postgres:passwordhere@localhost:5432/test1')

sa.orm.configure_mappers()  # IMPORTANT!
Base.metadata.create_all(engine)
session = sessionmaker(bind=engine)()
session.connection().connection.set_isolation_level(0)
session.execute('USE DATABASE test1;')
session.connection().connection.set_isolation_level(1)

ERROR I GET FROM ABOVE SCRIPT:

 cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) syntax error at or near "USE"
LINE 1: USE DATABASE test1;
        ^
 [SQL: 'USE DATABASE test1;'] (Background on this error at: http://sqlalche.me/e/f405)

As suggested by people; here is my code that saves and checks the database before saving it.

check_if_exists = session.query(latest_movies_scraper).filter_by(name=dictionary['title']).first()

 if check_if_exists:
                    print check_if_exists.name
                    print 'skipping this...'
                    pass
                else:
                    insert_to_db = latest_movies_scraper(name=dictionary['title'], url=dictionary['href'], image_url=dictionary['featured_image'])
                    session.add(insert_to_db)
                    session.commit()   

class latest_movies_scraper(Base):
    __tablename__ = 'latest_movies_scraper'


    id = sa.Column(sa.Integer, primary_key=True)
    name = sa.Column(sa.Unicode(255))
    url = sa.Column(sa.Unicode(255))
    image_url = sa.Column(sa.Unicode(255))
    create = sa.Column(sa.DateTime, default=datetime.datetime.utcnow)
    search_vector = sa.Column(TSVectorType('name'))

Your db uri contains `test1` so this will be the DB you are using - no need to call `USE DATABASE test1`. Show us the code you are calling which is referring to `tableName` that is giving the error, and also your db schema. — match, Feb 20 '18 at 09:37
There is no USE DATABASE command in Postgresql, hence your syntax error. Read https://stackoverflow.com/questions/10335561/use-database-name-command-in-postgresql and https://www.postgresql.org/message-id/4C24C4CA.7060206%40postnewspapers.com.au. — Ilja Everilä, Feb 20 '18 at 10:01
Still not enough - what is `dictionary` ? And I can;t see anywhere that you are configuring relationships, though your error is about a relation... — match, Feb 21 '18 at 09:35

score -1 · Answer 1 · answered Oct 30 '18 at 20:47

mssql+pyodbc://servername/Databasename?driver=ODBC Driver 13 for SQL Server
eng = create_engine(mssql+pyodbc://servername/Databasename?driver=ODBC Driver 13 for SQL Server)

Session = sessionmaker(bind=eng, autoflush=false)
db = Session()

for select use db.query("select * from tablename") you can also use db.execute("select query")

How would I use database in sqlalchemy using create_engine?

1 Answers1