correct parameter usage for "select * where id in ..."

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • saniac
    #1

    correct parameter usage for "select * where id in ..."

    I am working on a little project using pysqlite. It's going to be
    exposed on the web, so I want to make sure I quote all incoming data
    correctly. However, I've run into a brick wall trying to use parameters
    to populate a query of the form "select * where col1 in ( ? )"

    The naive approach doesn't work:

    values=['foo', 'bar', 'baz']
    sql = """select * where value in (?)"""
    cu = cx.cursor()
    cu.execute(sql, (values))

    The code blows up because the cursor is expecting 1 arg and gets 3. I
    tried joining the array members with a comma, and that didn't work.
    I've also tried the equivalent with the named style, which pysqlite
    also supports, but that didn't work either.

    I can't find any documentation that demonstrates this kind of query.

    Is there a way to do this? It seems a bit odd not to have a way to
    escape this kind of query.

    Tags: None
    • Steve Holden
      #2
      Re: correct parameter usage for "select * where id in ..."

      saniac wrote:
      I am working on a little project using pysqlite. It's going to be
      exposed on the web, so I want to make sure I quote all incoming data
      correctly. However, I've run into a brick wall trying to use parameters
      to populate a query of the form "select * where col1 in ( ? )"
      >
      The naive approach doesn't work:
      >
      values=['foo', 'bar', 'baz']
      sql = """select * where value in (?)"""
      cu = cx.cursor()
      cu.execute(sql, (values))
      >
      The code blows up because the cursor is expecting 1 arg and gets 3. I
      tried joining the array members with a comma, and that didn't work.
      I've also tried the equivalent with the named style, which pysqlite
      also supports, but that didn't work either.
      >
      I can't find any documentation that demonstrates this kind of query.
      >
      Is there a way to do this? It seems a bit odd not to have a way to
      escape this kind of query.
      >
      Well, you could try using a tuple whose single element is that
      three-element tuple with your list if values:

      cu.execute(sql, (values, ))

      which I repsume is shat you really meant to do. Note, though, that not
      all DB API modules will accept lists and/or tuples as data elements of
      that kind, so you may be disappointed.

      regards
      Steve
      --
      Steve Holden +44 150 684 7255 +1 800 494 3119
      Holden Web LLC/Ltd http://www.holdenweb.com
      Skype: holdenweb http://holdenweb.blogspot.com
      Recent Ramblings http://del.icio.us/steve.holden

        Comment

        • Frank Millman
          #3
          Re: correct parameter usage for "select * where id in ..."


          saniac wrote:
          I am working on a little project using pysqlite. It's going to be
          exposed on the web, so I want to make sure I quote all incoming data
          correctly. However, I've run into a brick wall trying to use parameters
          to populate a query of the form "select * where col1 in ( ? )"
          >
          The naive approach doesn't work:
          >
          values=['foo', 'bar', 'baz']
          sql = """select * where value in (?)"""
          cu = cx.cursor()
          cu.execute(sql, (values))
          >
          The code blows up because the cursor is expecting 1 arg and gets 3.
          I assume you mean 'select * from table where...'

          Try this -

          values=['foo', 'bar', 'baz']
          sql = """select * from table where value in (?,?,?)"""
          cu = cx.cursor()
          cu.execute(sql, values)

          It works with odbc from pywin32. I have not tried pysqlite.

          If you want it to handle a variable number of values, you will have to
          programmaticall y construct the sql statement with the appropriate
          number of parameters.

          HTH

          Frank Millman

            Comment

            • paul
              #4
              Re: correct parameter usage for "select * where id in ..."

              Frank Millman schrieb:
              If you want it to handle a variable number of values, you will have to
              programmaticall y construct the sql statement with the appropriate
              number of parameters.
              >>vals = (1,2,3,4,5)
              >>sql = "select * from table where value in ("+','.join("?" *len(vals))+")"
              >>print sql
              'select * from table where value in (?,?,?,?,?)'

              cheers
              Paul

                Comment

                • saniac
                  #5
                  Re: correct parameter usage for "select * where id in ..."

                  paul wrote:
                  Frank Millman schrieb:
                  If you want it to handle a variable number of values, you will have to
                  programmaticall y construct the sql statement with the appropriate
                  number of parameters.
                  Yes, I should have made it clear it was the variable part that was
                  hard.
                  >vals = (1,2,3,4,5)
                  >sql = "select * from table where value in ("+','.join("?" *len(vals))+")"
                  >print sql
                  'select * from table where value in (?,?,?,?,?)'
                  Argh, I have a scripting language and I'm not building up strings
                  dynamically? What an idiot.

                  Thanks, that's just what I needed.

                    Comment

                    Previous template Next
                    Working...