SqlAlchemy query result outputting

Question

I am trying to query one of my tables in my Postgres database using SqlAlchemy in Python 3. It runs the query fine but as I go through each row in the result that SqlAlchemy returns, I try to use the attribute 'text' (one of my column names). I receive this error:

'str' object has no attribute 'text'

I have printed the attribute like so:

for row in result:
 print(row.text)

This does not give the error. The code that produces the error is below. However, to give my environment:

I have two servers running. One is for my database the other is for my python server.

Database Server:

• Postgres v9.6 - On Amazon's RDS

Server with Python

• Linux 3.13.0-65-generic x86_64 - On an Amazon EC2 Instance
• SqlAlchemy v1.1.5
• Python v3.4.3
• Flask 0.11.1

Files related:

import sqlalchemy as sa
from sqlalchemy.ext.automap import automap_base
from sqlalchemy.orm import Session
import re
from nltk import sent_tokenize
class DocumentProcess:
 def __init__(self):
 ...
 Engine = sa.create_engine(
 CONFIG.POSTGRES_URL,
 client_encoding='utf8',
 pool_size=20,
 max_overflow=0
 )
 # initialize SQLAlchemy
 Base = automap_base()
 # reflect the tables
 Base.prepare(Engine, reflect=True)
 # Define all needed tables
 self.Document = Base.classes.documents
 self.session = Session(Engine)
 ...
 def process_documents(self):
 try:
 offset = 5
 limit = 50
 ###### This is the query in question ##########
 result = self.session.query(self.Document) \
 .order_by(self.Document.id) \
 .offset(offset) \
 .limit(limit)
 for row in result:
 # The print statement below does print out the text
 print(row.text)
 # when passing document.text to sent_tokenize, it
 # gives the following error:
 # 'str' object has no attribute 'text'
 snippets = sent_tokenize(row.text.strip('\n')) # I have removed strip, but the same problem
 except Exception as e:
 logging.info(format(e))
 raise e

This is my model for Document, in my PostgreSQL database:

class Document(db.Model):
 __tablename__ = "documents"
 id = db.Column(db.Integer, primary_key=True)
 text = db.Column(db.Text)
 tweet = db.Column(db.JSON)
 keywords = db.Column(db.ARRAY(db.String), nullable=True)
 def to_dict(self):
 return dict(
 id=self.id,
 text=self.text,
 tweet=self.tweet,
 keywords=self.keywords
 )
 def json(self):
 return jsonify(self.to_dict())
 def __repr__(self):
 return "<%s %r>" % (self.__class__, self.to_dict())

Things I have tried

1. Before, I did not have order_by in the Document query. This was working before. However, even removing order_by does not fix it anymore.

2. Used a SELECT statement and went through the result manually, but still the same result

What I haven't tried

1. I am wondering if its because I named the column 'text'. I noticed that when I write this query out in postgres, it highlights it as a reserved word. I'm confused why my query worked before, but now it doesn't work. Could this be the issue?

Any thoughts on this issue would be much appreciated.

• Ravi Kumar: What happens if you write print(row.text) twice in the for loop? I think sqlalchemy returns an iterator.
• Daniel Hair: I printed it three times, in three different ways like so: print(row.text), print(str(row.text)), my_text = row.text then passing my_text into a function and printing it. Each of those printed row.text perfectly (all within the for loop)

Answer 1

It turns out that text is a reserved word in PostgreSQL. I renamed the column name and refactored my code to match. This solved the issue.

Answer 2

You are likely to get this error in PostgreSQL if you are creating a foreign table and one of the column datatype is text. Change it to character varying() and the error disappears!