psycopg2 undefined column on column value-CodePudding

psycopg2 is being a preteen child to me right now.

cur = conn.cursor()
        url = "https://shofi-mod.s3.us-east-2.amazonaws.com/"   str(rawbucketkey)
        query = f"""UPDATE contentcreatorcontentfeedposts_contentfeedpost
                    SET picturemediatype = TRUE, mediakey ={rawbucketkey},
                    mediaurl= "{url}", active = TRUE, postsubmit = FALSE
                    WHERE contentcreator_id ={userid} AND
                    id ={contentpostid};
                    COMMIT;"""


        cur.execute(query)
        cur.close()

But I keep getting an error:

[ERROR] UndefinedColumn: column "https://shofi-mod.s3.us-east-2.amazonaws.com/061909729140543151" does not exist
LINE 3:                     mediaurl= "https://shofi-mod.s3.us-east-...
                                      ^

Traceback (most recent call last):
  File "/var/task/lambdarunner.py", line 163, in lambda_handler
    cur.execute(query)

and my database does in fact have a media column

class ContentFeedPost(models.Model):
   contentcreator = models.ForeignKey(ContentCreatorUsers, on_delete=models.CASCADE)
   creationtimestamp = models.DateTimeField(auto_now_add=True)
   likes = models.BigIntegerField(default= 0)
   favoritedtimes = models.BigIntegerField(default=0)
   tipcount = models.IntegerField(default=0)
   mediakey = models.CharField(max_length=200, null=True, blank=True)
   mediaurl = models.CharField(max_length=200, null=True, blank=True)
   audiomediatype = models.BooleanField(default=False)
   videomediatype = models.BooleanField(default=False)
   audiotitle = models.CharField(max_length=100, null=True, blank=True)
   videoplaceholderimage = models.CharField(max_length=200, blank=True, null=True)
   videoplaceholderimagekey = models.CharField(max_length=200, blank=True, null=True)
   audioplaceholderimage = models.CharField(max_length=200, blank=True, null=True)
   audioplaceholderimagekey = models.CharField(max_length=200, blank=True, null=True)
   picturemediatype = models.BooleanField(default=False)
   postsubmit = models.BooleanField(default=True)
   posttext = models.CharField(max_length=1000, blank=True, null=True)
   active = models.BooleanField(default=False)

Above is a django ORM definition

whats weird is that it looks like its complaining about the value im trying to set the column mediaurl to. This makes no sense. Is there something Im doing wrong that is obvious?

CodePudding user response：

String values in Postgres are delimited by single quotes, not double quotes. Double quotes are used to delimit complicated column names.

By the way, you should be letting psycopg2 do your substitution. That would have solved the problem right away:

cur = conn.cursor()
url = "https://shofi-mod.s3.us-east-2.amazonaws.com/"   str(rawbucketkey)
query = """UPDATE contentcreatorcontentfeedposts_contentfeedpost
                    SET picturemediatype = TRUE, mediakey = %s,
                    mediaurl= %s, active = TRUE, postsubmit = FALSE
                    WHERE contentcreator_id = %s AND
                    id = %s;
                    COMMIT;"""


cur.execute(query, (rawbucketkey, url, userid, contentpostid) )
cur.close()