insert string problems..

Discussion in 'Python' started by Abandoned, Oct 28, 2007.

  1. Abandoned

    Abandoned Guest

    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, Oct 28, 2007
    #1
    1. Advertising

  2. Abandoned

    Abandoned Guest

    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 " ?
    Abandoned, Oct 28, 2007
    #2
    1. Advertising

  3. 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.
    Steven D'Aprano, Oct 28, 2007
    #3
  4. Abandoned

    Abandoned Guest

    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 ?
    Abandoned, Oct 28, 2007
    #4
  5. 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
    Marc 'BlackJack' Rintsch, Oct 28, 2007
    #5
  6. On Sun, 28 Oct 2007 00:24:34 -0700, Abandoned <>
    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://wlfraed.home.netcom.com/
    (Bestiaria Support Staff: )
    HTTP://www.bestiaria.com/
    Dennis Lee Bieber, Oct 28, 2007
    #6
  7. On Sun, 28 Oct 2007 00:33:45 -0700, Abandoned <>
    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

    HTTP://wlfraed.home.netcom.com/
    (Bestiaria Support Staff: )
    HTTP://www.bestiaria.com/
    Dennis Lee Bieber, Oct 28, 2007
    #7
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. cannontrodder
    Replies:
    1
    Views:
    721
    cannontrodder
    Jul 25, 2006
  2. Replies:
    1
    Views:
    446
  3. Replies:
    1
    Views:
    1,779
    Albert Hopkins
    Dec 6, 2008
  4. eagle

    Insert & Insert/Repeat buttons in a detailsview

    eagle, Jul 12, 2007, in forum: ASP .Net Datagrid Control
    Replies:
    0
    Views:
    1,046
    eagle
    Jul 12, 2007
  5. Feege
    Replies:
    0
    Views:
    448
    Feege
    Dec 20, 2005
Loading...

Share This Page