Connecting to the census public database in AWS using SQLalchemy in Python

Question

I am taking a course in DataCamp on databases and there is a part where I access the census database in AWS using SQLalchemy.

The code is:

engine = \
create_engine('postgresql+psycopg2://student:[email protected]:5432/census')

When I run this code and then try to access the data from my Jupyter Notebook I get a message that tells me that access is denied.

metadata = MetaData()

census = Table('census', metadata, autoload = True, autoload_with = engine)

connection = engine.connect()

stmt = select([census])

stmt = stmt.where(census.columns.state == 'New York')

results = connection.execute(stmt).fetchall()

ProgrammingError: (psycopg2.ProgrammingError) permission denied for relation census
 [SQL: 'SELECT census.state, census.sex, census.age, census.pop2000, census.pop2008 \nFROM census \nWHERE census.state = %(state_1)s'] [parameters: {'state_1': 'New York'}]

I have an account in AWS. What should I do to access the census database from my Jupyter Notebook?

Doesn't the course DataCamp have a forum or Q&A where students can ask questions regarding the lectures? Perhaps someone there had the same problem and it has already been solved. — Edgar Ramírez Mondragón
– Edgar Ramírez Mondragón, Commented Dec 12, 2018 at 7:42
@ Edgar: Thanks for the suggestion. They have a chat area in Slack and I have posted the question there as well. However, this is a more general question and can be answered by the Stackoverflow community as well and perhaps better and faster. — user8270077
– user8270077, Commented Dec 12, 2018 at 9:40

Edgar Ramírez Mondragón · Accepted Answer · 2018-12-12 18:21:44Z

0

Well...it seems to be an issue with user permissions.

I have no problem either reading the column names (census.state, census.sex, census.age, census.pop2000, census.pop2008), or running queries on other tables:

>>> res = connection.execute('SELECT * FROM data')
>>> res.fetchall()
[(10, 'boobs'), (11, 'ass'), (11, 'ass'), (12, 'usa'), (12, 'usa'), (12, 'usa'), (12, 'usa'), (12, 'usa'), (12, 'usa'), (12, 'usa'), (12, 'usa'), (12, 'usa'), (12, 'usa')]

The permission denied for relation tablename error might be due to insufficient privileges granted for the user on the census table. You might want to contact the DataCamp instructor or other students there.

answered Dec 12, 2018 at 18:21

Edgar Ramírez Mondragón

3,0613 gold badges23 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user8270077 Over a year ago

@ Edgar: Thanks Edgar for your time. As I have said I have already taken steps to contact DataCamp. Still, since I have an AWS account and census is a public dataset open to to everyone interested, could you guide me on the steps to get access to it?

Collectives™ on Stack Overflow

Connecting to the census public database in AWS using SQLalchemy in Python

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related