insert string problems..

[Abandoned]

Hi..
I want to insert some data to postgresql..
My insert code:
yer="019"
cursor.execute("INSERT INTO ids_%s (id) VALUES (%s)", (yer, id))
I don't want to use % when the insert operation.

in this code give me this error:
psycopg2.ProgrammingError: syntax error at or near "'019'"
LINE 1: SELECT link_id from linkkeywords_'019'

if i do int(yer) , 019 change to 19 ..
How can i do int yer string with 0 ?

 

[Abandoned]

Also..
a="123,245,1235,663"
cursor.execute("SELECT id, name FROM data WHERE id in (%s)", (a,))
In this query must be:
SELECT id, name FROM data WHERE id in (123,245,1235,663)
but it looks:
SELECT id, name FROM data WHERE id in ("123,245,1235,663")
How can i delete " ?

 

[Steven D'Aprano]

Hi..
I want to insert some data to postgresql.. My insert code:
yer="019"
cursor.execute("INSERT INTO ids_%s (id) VALUES (%s)", (yer, id)) I don't
want to use % when the insert operation.

in this code give me this error:
psycopg2.ProgrammingError: syntax error at or near "'019'" LINE 1:
SELECT link_id from linkkeywords_'019'

if i do int(yer) , 019 change to 19 .. How can i do int yer string with
0 ?

Integers with a leading 0 are interpreted as base 8 (octal). You can't
write 019, because there is no digit "9" in octal.

Why do you need a leading zero?

 

[Abandoned]

Integers with a leading 0 are interpreted as base 8 (octal). You can't
write 019, because there is no digit "9" in octal.

Why do you need a leading zero?

Thank you steven.
I must use 019 beacause my system algoritm in this way..
And what about second question ?

 

[Marc 'BlackJack' Rintsch]

Hi..
I want to insert some data to postgresql..
My insert code:
yer="019"
cursor.execute("INSERT INTO ids_%s (id) VALUES (%s)", (yer, id))
I don't want to use % when the insert operation.

in this code give me this error:
psycopg2.ProgrammingError: syntax error at or near "'019'"
LINE 1: SELECT link_id from linkkeywords_'019'

You are executing an INSERT and get an error about a SELECT!? Hard to
believe!

But in both SQL statements you try to insert table names via placeholders.
This doesn't work as those placeholders are *values* that will be escaped.
The errormessage is quite clear IMHO::

SELECT link_id from linkkeywords_'019'

That's not a valid table name because of the ' that got added when
inserting the *value* '019'.

Starting to number tables and the need to dynamically create table names is
usually sign of a bad schema design BTW.

Ciao,
Marc 'BlackJack' Rintsch

 

[Dennis Lee Bieber]

Ignoring the facet that the error message is from a SELECT
statement...

Hi..
I want to insert some data to postgresql..
My insert code:
yer="019"
cursor.execute("INSERT INTO ids_%s (id) VALUES (%s)", (yer, id))
I don't want to use % when the insert operation.

Place holders are used for DATA values. Your SQL, except for the
data values, SHOULD BE fixed. This means that table names, column names,
keywords/operators... can NOT be changed using parameterized queries.
Parameterized queries are used to ensure the data values are safely
delimited -- preventing injection of undesirable SQL.

If you have a database where TABLE names contain a year string (I'm
presuming from the "yer"), then you have a poor database schema. Instead
of multiple tables with identical definitions and differing only in the
table name, you should probably have ONE table (and one generic name)
and store the discriminant as another field. That is, instead of:

Table_123(ID, description, whatever)
Table_221(ID, description, whatever)
....

use:

TableSet(discriminant, ID, description, whatever)

And your insert becomes:

insert into TableSet (discriminant, ID) values (%s, %s)

If you MUST have dynamic table/column names, for safety they should
never be created directly from user input, but rather computed
internally from safe data.


Table_123(ID, description, whatever)
Table_221(ID, description, whatever)
....

Table_Names(suffix, tablename)
123, Table_123
221, Table_221

Then, if the suffix is user input, use something like:

select tablename from Table_Names where suffix = %s

taking the result and modifying the subsequent SQL using Python string
interpolation. This ensures that the parameter escaping of the user
input gives you a safe (eg, value retrieved from the database itself)
value that can be used to directly modify the SQL.
Note how the data placeholder had to be escaped (not needed for
database adapters that use ? as the placeholder)
--
Wulfraed Dennis Lee Bieber KD6MOG
(e-mail address removed) (e-mail address removed)
HTTP://wlfraed.home.netcom.com/
(Bestiaria Support Staff: (e-mail address removed))
HTTP://www.bestiaria.com/

 

[Dennis Lee Bieber]

Also..
a="123,245,1235,663"
cursor.execute("SELECT id, name FROM data WHERE id in (%s)", (a,))
In this query must be:
SELECT id, name FROM data WHERE id in (123,245,1235,663)
but it looks:
SELECT id, name FROM data WHERE id in ("123,245,1235,663")
How can i delete " ?

a is a single string value, so it will be delimited AS a
single string value.

How to get separate values?

First you need to parse a into separate (apparently integers?)...

a="123,245,1235,663"
aList = [int(i) for i in a.split(",")]
aList

[123, 245, 1235, 663]

aList is now a list of four separate integers... Now you need to
modify the SQL to expect the proper number of %s parameters

placeholders = ", ".join(["%s"] * len(aList))
placeholders

'%s, %s, %s, %s'

placeholders now has one %s for each integer in the list, so next
is to put them into the SQL itself.
Then, execute the SQL with the list of integers

cursor.execute(SQL, aList)
--
Wulfraed Dennis Lee Bieber KD6MOG
(e-mail address removed) (e-mail address removed)
HTTP://wlfraed.home.netcom.com/
(Bestiaria Support Staff: (e-mail address removed))
HTTP://www.bestiaria.com/