Re: Encountered an error

Did you mean to add a column called "26.2 LTRS"? SQL identifiers with
embedded spaces must always be enclosed in quotes.

Please don't use Python string formatting to interpolate values in your
SQL UPDATE statement since that invites SQL injection vulnerabilities.
Here's the psycopg2 documentation about using it to interpolate values
safely:

http://pythonhosted.org/psycopg2/usage.html#passing-parameters-to-sql-queries

On 03/14/2016 11:11 AM, Shaan Repswal wrote:
> I tried to format the strings the Python way but... I'm getting this error:-
>
> Traceback (most recent call last):
>   File "C:\Python34\prototype_husk.py", line 133, in submit_data
>     cur.execute(add_column)
> psycopg2.ProgrammingError: syntax error at or near "26.2"
> LINE 2:                             ADD 26.2 LTRS int DEFAULT NULL
>
> Here is my code:-
>
>     def submit_data(self, whatevs):
>         conn = psycopg2.connect("dbname=prototypedb user=postgres
> password=superman123")
>         cur = conn.cursor()
>
>         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()])
>
>         for x in self.quantity_list:
>             if x[0].get_text() !="" and x[1].get_text() != "":
>                 add_column = """ALTER TABLE inventory
>                             ADD %s int DEFAULT NULL""" % x[0].get_text()
>                 cur.execute(add_column)
>                 add_details = """UPDATE inventory
>                             SET %s = %s
>                             WHERE name = %s;""" % (x[0].get_text(),
> x[1].get_text(), self.entry_product_name.get_text())
>                 cur.execute(add_details)
>             else:
>                 continue;
>
>         conn.commit()
>         cur.close()
>         conn.close()
>
> On Mon, Mar 14, 2016 at 7:41 PM, Shaan Repswal
> <shaun.reapswaal@gmail.com <mailto:shaun.reapswaal@gmail.com>> wrote:
>
>     Hey guys I built the string in python and then passed it to the
>     cur.execute() function. This was so simple! It scares me to see that
>     I didn't get it the first time around. But anyway, thanks for laying
>     to rest my confusions. Thank you so much!
>
>     On Fri, Mar 11, 2016 at 3:33 AM, Karsten Hilbert
>     <Karsten.Hilbert@gmx.net <mailto:Karsten.Hilbert@gmx.net>> wrote:
>
>         On Fri, Mar 11, 2016 at 01:16:33AM +0530, Shaan Repswal wrote:
>
>         > I am new to databases and am a novice to programming in general.
>
>         Don't worry.
>
>         > @Karsten - I know, I got the feeling that the design felt awry as well. The
>         > database will be on the local machine. And the client side will have the
>         > code to error check the column names being added and also if there already
>         > is a column by this name or not.
>
>         You can add a second table (pseudo code)
>
>                 inventory_detail
>                         pk serial primary key
>                         fk_inventory integer foreign key (inventory.pk
>         <http://inventory.pk>)
>                         detail_name text
>                         detail_value text
>
>         and then - instead of creating columns in table inventory -
>         add rows to the inventory_detail table where .detail_name
>         would hold what otherwise would be the column name of the new
>         column in inventory and .detail_value the value you would
>         have put into the new column in table inventory. The
>         fk_inventory column would hold the primary key of the row in
>         the inventory table this inventory_detail row belongs to.
>
>         Just a suggestion.
>
>         Karsten
>         --
>         GPG key ID E4071346 @ eu.pool.sks-keyservers.net
>         <http://eu.pool.sks-keyservers.net>
>         E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346
>
>
>         --
>         Sent via psycopg mailing list (psycopg@postgresql.org
>         <mailto:psycopg@postgresql.org>)
>         To make changes to your subscription:
>         http://www.postgresql.org/mailpref/psycopg
>
>
>


--
Jonathan Rogers
Socialserve.com by Emphasys Software
jrogers@emphasys-software.com