Put date in filename postgres with psycopg in a python script

Question

I am streaming tweets to a postgres database with a python script (using psycopg2). I would like to be able to schedule this script in a windows task manager. The only issue I have to overcome is to be able to rename the table in postgres. Is it possible?

x = datetime.date.today() - datetime.timedelta(days=1)
con = psycopg2.connect("dbname='test' user='postgres'")   

cur = con.cursor()
cur.execute("DROP TABLE IF EXISTS schemaname.%s", (x))

** UPDATE

That answer does get my further, now it just complains about the numbers.

Traceback (most recent call last):
File "Z:/deso-gis/scripts/test123.py", line 26, in <module>
cur.execute("DROP TABLE IF EXISTS tweets_days.%s" % x)
psycopg2.ProgrammingError: syntax error at or near ".2016"
LINE 1: DROP TABLE IF EXISTS tweets_days.2016-02-29

omikron · Accepted Answer · 2016-03-01 10:57:24Z

1

I believe you are getting arror at line

cur.execute("DROP TABLE IF EXISTS schemaname.%s", (x))

because psycopg generates not what you want:

DROP TABLE IF EXISTS schemaname."table_name"

try using

cur.execute("DROP TABLE IF EXISTS schemaname.%s" % x)

This is not as secure as could be but now table name is name not SQL string.

answered Mar 1, 2016 at 10:57

omikron

2,8461 gold badge28 silver badges35 bronze badges

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

5 Comments

Zuenie Over a year ago

I tried your answer and it got me further, not yet there though. My question is updated.

Zuenie Over a year ago

I figured it out. I had to add " to the statement, like how postgres likes it. The following line did the trick. cur.execute("DROP TABLE IF EXISTS tweets_days."'"%s"'"" % x)

Clodoaldo Neto Over a year ago

@Zuenie Use triple quotes to avoid escaping mess: '''DROP TABLE IF EXISTS tweets_days."%s"''' % x

omikron Over a year ago

I recommend to change a little bit table name: cur.execute("DROP TABLE IF EXISTS schemaname.%s" % str(x).replace('-', '_')) so there is no special chars in name and you don't have to quote them at all.

Zuenie Over a year ago

man stackoverflow is awesome. I used the two tips in the comments here and its running perfectly now :) thanks a lot!

Collectives™ on Stack Overflow

Put date in filename postgres with psycopg in a python script

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related