#🔒 Query works via DBeaver, fails with psycopg2
slate ocean
lethal geyser
no idea what DBeaver is. Does your table indeed have a column named "date"? Might "date" be a reserved word in Postgres SQL?
lethal geyser
https://www.postgresql.org/docs/16/sql-keywords-appendix.html#KEYWORDS-TABLE says it's reserved, and I bet it ignores case.
trail moth
But without double quotes, it should be case insensitive shouldn't it?
lethal geyser
If I were you, I'd choose a different name for that column
trail moth
Ah, shoot
Weird that it's only a problem when run through psycopg2, but I'll try that
No, same deal with a different name
bizarre
lethal geyser
the tutorial suggests it should work as you've got it -- https://www.postgresql.org/docs/current/tutorial-table.html#TUTORIAL-TABLE
so ... maybe it's time to post all your code
(as text, not a screenshot)
trail moth
One sec, need to simplify it
def rowCounter(conn):
load_dotenv()
cursor = conn.cursor()
db_snapshot_name = os.getenv("SNAPSHOT_DB_NAME")
query = """
SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'public'
"""
cursor.execute(query)
tables = cursor.fetchall()
for table in tables:
table_name = table[0]
print(table_name)
if table_name not in columnDatesToCheck():
continue
else:
date_column = columnDatesToCheck()[table_name]
cursor.execute(f"""
SELECT DATE_TRUNC('month', {date_column}) AS month_year, COUNT(*)
FROM {table_name}
WHERE DATE_TRUNC('month', {date_column}) < DATE_TRUNC('month', CURRENT_DATE)
GROUP BY DATE_TRUNC('month', {date_column})
ORDER BY month_year
""")
row_count_prod = cursor.fetchall()
print(row_count_prod)
cursor.close()
conn.close()
Again, if I take that query, put it in some database tool, and run it. It doesn't care about case sensitivity
but here it does
not sure why
psycopg2.errors.UndefinedColumn: column "dateofservice" does not exist
LINE 2: SELECT DATE_TRUNC('month', dateofservice) AS...
HINT: Perhaps you meant to reference the column "billing.DateOfService".
I don't think that's your complete code. I want the code in order to run it, not just look at it.
trail moth
def columnDatesToCheck():
"""
Dates to group by month for row counts
:return:
"""
return {
"billing": "dateofservice"
#"scheduling_data": "date",
}
That's pretty much it, I can't give you the data unfortunately
