Search code examples
pythonpostgresqldatetimepsycopg

Put date in filename postgres with psycopg in a python script

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
Solution

  • 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.