Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > Python > insert string problems..

Reply
Thread Tools

insert string problems..

 
 
Abandoned
Guest
Posts: n/a
 
      10-28-2007
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 ?

 
Reply With Quote
 
 
 
 
Abandoned
Guest
Posts: n/a
 
      10-28-2007
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 " ?

 
Reply With Quote
 
 
 
 
Steven D'Aprano
Guest
Posts: n/a
 
      10-28-2007
On Sun, 28 Oct 2007 00:24:34 -0700, Abandoned wrote:

> 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?


--
Steven.
 
Reply With Quote
 
Abandoned
Guest
Posts: n/a
 
      10-28-2007
On Oct 28, 9:45 am, Steven D'Aprano <st...@REMOVE-THIS-
cybersource.com.au> wrote:
> On Sun, 28 Oct 2007 00:24:34 -0700, Abandoned wrote:
> > 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?
>
> --
> Steven.

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

 
Reply With Quote
 
Marc 'BlackJack' Rintsch
Guest
Posts: n/a
 
      10-28-2007
On Sun, 28 Oct 2007 00:24:34 -0700, Abandoned wrote:

> 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
 
Reply With Quote
 
Dennis Lee Bieber
Guest
Posts: n/a
 
      10-28-2007
On Sun, 28 Oct 2007 00:24:34 -0700, Abandoned <(E-Mail Removed)>
declaimed the following in comp.lang.python:

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.

>>> result_table_name = "table_123"
>>> "insert into %s (ID) values (%%s)" % result_table_name

'insert into table_123 (ID) values (%s)'
>>>

Note how the data placeholder had to be escaped (not needed for
database adapters that use ? as the placeholder)
--
Wulfraed Dennis Lee Bieber KD6MOG
http://www.velocityreviews.com/forums/(E-Mail Removed) (E-Mail Removed)
HTTP://wlfraed.home.netcom.com/
(Bestiaria Support Staff: (E-Mail Removed))
HTTP://www.bestiaria.com/
 
Reply With Quote
 
Dennis Lee Bieber
Guest
Posts: n/a
 
      10-28-2007
On Sun, 28 Oct 2007 00:33:45 -0700, Abandoned <(E-Mail Removed)>
declaimed the following in comp.lang.python:

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

>>> SQL = "select id, name from data where id in (%s)" % placeholders
>>> SQL

'select id, name from data where id in (%s, %s, %s, %s)'
>>>

Then, execute the SQL with the list of integers

cursor.execute(SQL, aList)
--
Wulfraed Dennis Lee Bieber KD6MOG
(E-Mail Removed) (E-Mail Removed)
HTTP://wlfraed.home.netcom.com/
(Bestiaria Support Staff: (E-Mail Removed))
HTTP://www.bestiaria.com/
 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Insert string into string Francesco Pietra Python 5 07-27-2008 03:34 PM
Insert a string into a string HansWernerMarschke@web.de C Programming 6 04-30-2008 01:29 AM
Cut String & Insert in String vunet.us@gmail.com ASP General 5 05-14-2007 08:20 PM
Detailsview loses insert mode contents when Insert fails... cannontrodder ASP .Net 1 07-25-2006 08:38 AM
insert a string into another string at a certain position? CK Javascript 2 04-26-2006 03:51 PM



Advertisments