CODEWITHSUNDEEP
pythonsqlalchemy
Chicken Sandwich No Pickles
Chicken Sandwich No Pickles

Reputation: 3382

SQL Alchemy NameError: name <table> is not defined

I wasn't sure what to call this, feel free to edit my post title.

Before I begin, I googled and looked here, but this didn't seem to help me.

My code:

import pyodbc
import pandas as pd
import numpy as np
import os
import sqlalchemy as sal
from sqlalchemy import create_engine
from sqlalchemy import MetaData
from sqlalchemy import Table, Column, Integer, Numeric, String, ForeignKey, Boolean
##
from datetime import datetime
from sqlalchemy import DateTime
from sqlalchemy import PrimaryKeyConstraint, UniqueConstraint, CheckConstraint
from sqlalchemy import Index
from sqlalchemy import ForeignKeyConstraint
from sqlalchemy import insert
from sqlalchemy.sql import select
from sqlalchemy.sql import func
from sqlalchemy import cast
from sqlalchemy import and_, or_, not_
from sqlalchemy import update, delete
from sqlalchemy import text
##
import urllib

#############################################################
server = 'fake_server'
database = '_Testing_Only'
driver = 'SQL+SERVER+NATIVE+CLIENT+11.0'
trusted_connection='yes'



database_connection =  'mssql+pyodbc://fake_server/' + database + '?trusted_connection=' + trusted_connection + '&driver=' + driver

engine = sal.create_engine(database_connection)
connection=engine.connect()
metadata = MetaData()
print(engine.table_names())

Here is the result of my print statement:

['cookies', 'line_items', 'orders', 'testing_sym_keys', 'users']

I then tried to run this code:

s = select([cookies])

I got the following error message:

Traceback (most recent call last):
  File "<pyshell#167>", line 1, in <module>
    s = select([cookies])
NameError: name 'cookies' is not defined

The table clearly exists, why am I getting the error message?

Upvotes: 1

Views: 4601

Answers (2)

jasoonami
jasoonami

Reputation: 1

Just found a fast solution for this. Try creating table object first for your desired table.

engine = create_engine(database_connection)
metadata = MetaData()
cookies = Table('cookies', metadata, autoload=True, autoload_with=engine)

So everytime you want to query a table, just do the above code each one so that you initialize the table in python.

Upvotes: 0

Ayman Arif
Ayman Arif

Reputation: 1699

The issue is that you are not binding tables in the engine variable to a variable in your Python code.

Trying running a native SQL query of the form:

engine = create_engine(database_connection)
metadata = MetaData(engine)
metadata.reflect()

with engine.begin() as conn:
    conn.execute("select * from cookies")

Other solution, if you want to use select method you can try this:

engine = create_engine(database_connection)
meta = MetaData(engine).reflect()
table = meta.tables['cookies']

# select * from 'cookies'
select_cookies = select([table])

Upvotes: 1

Related Questions