Re: Encountered an error - Mailing list psycopg

On 03/09/2016 09:37 PM, Shaan Repswal wrote:
> I am trying to insert a new record into my "prototypedb" database. This
> here below is the section of my code that I am using to access and work
> on my database.
>
> What I am trying to do here is enter one record (three values) in three
> columns name, code and type. Then create a new column and then add one
> value to the new column for the aforementioned already entered record.
>
> cur = conn.cursor()
>
>          #Enter the three value basic record
>          cur.execute("""INSERT INTO inventory (name, code, type)
>                      VALUES (%s, %s, %s);""",
> [self.entry_product_name.get_text(), self.new_product_code.get_text(),
> self.new_product_type.get_text()])
>
>          #Take out list of Text Entry boxes
>          #self.quantity_list is a list of two value tuples each of which
> holds a reference to a text box in my GUI
>          for x in self.quantity_list:
>              if x[0].get_text() !="" and x[1].get_text() != "":
>                  #First make the appropriate column
>                  cur.execute("""ALTER TABLE inventory
>                              ADD %s integer DEFAULT NULL;""",
> [x[0].get_text()])
>                  #Then give that column the appropriate value for the
> current record
>                  cur.execute("""UPDATE inventory
>                              SET %s = %s
>                              WHERE name = %s;""", [x[0].get_text(),
> x[1].get_text(), self.entry_product_name.get_text()])
>              else:
>                  continue;
>
>          conn.commit()
>          cur.close()
>          conn.close()
>
>
> But Alas! There is an Error!
>
> Traceback (most recent call last):
>    File "C:\Python34\prototype_husk.py", line 134, in submit_data
>      ADD %s integer DEFAULT NULL;""", [x[0].get_text()])
> psycopg2.ProgrammingError: syntax error at or near "'26.2 LTRS'"
> LINE 2:                             ADD '26.2 LTRS' integer DEFAULT ...
>                                          ^
>

This will not work for the reasons Daniele gave. You have two choices:

1) Build the SQL string using Python string formatting.

2) Do it with the Postgres format(). From a psql session:

test=> select format('ALTER tbl_a ADD %I int DEFAULT NULL', 'fld_2');
                  format
----------------------------------------
  ALTER tbl_a ADD fld_2 int DEFAULT NULL

In psycopg this is a two step process, submit the query and then
resubmit the returned string.

>
> I have attached herein a screenshot of the values entered. So the error
> can make more sense.
>
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com