Passing table name as a parameter in psycopg2

Passing table name as a parameter in psycopg2

I have the following code, using pscyopg2:

sql = 'select %s from %s where utctime > %s and utctime < %s order by utctime asc;'  data = (dataItems, voyage, dateRangeLower, dateRangeUpper)  rows = cur.mogrify(sql, data)  

This outputs:

select 'waterTemp, airTemp, utctime' from 'ss2012_t02' where utctime > '2012-05-03T17:01:35+00:00'::timestamptz and utctime < '2012-05-01T17:01:35+00:00'::timestamptz order by utctime asc;  

When I execute this, it falls over - this is understandable, as the quotes around the table name are illegal.

Is there a way to legally pass the table name as a parameter, or do I need to do a (explicitly warned against) string concatenation, ie:

voyage = 'ss2012_t02'  sql = 'select %s from ' + voyage + ' where utctime > %s and utctime < %s order by utctime asc;'  

Cheers for any insights.

Answer by PearsonArtPhoto for Passing table name as a parameter in psycopg2

The table name cannot be passed as a parameter, but everything else can. Thus, the table name should be hard coded in your app (Don't take inputs or use anything outside of the program as a name). The code you have should work for this.

On the slight chance that you have a legitimate reason to take an outside table name, make sure that you don't allow the user to directly input it. Perhaps an index could be passed to select a table, or the table name could be looked up in some other way. You are right to be wary of doing this, however. This works, because there are relatively few table names around. Find a way to validate the table name, and you should be fine.

It would be possible to do something like this, to see if the table name exists. This is a parameterised version. Just make sure that you do this and verify the output prior to running the SQL code. Part of the idea for this comes from this answer.

SELECT 1 FROM information_schema.tables WHERE table_schema = 'public' and table_name=%s LIMIT 1  

Answer by malthe for Passing table name as a parameter in psycopg2

If you want to pass the table name as a parameter, you can use this wrapper:

class Literal(str):      def __conform__(self, quote):          return self        @classmethod      def mro(cls):          return (object, )        def getquoted(self):          return str(self)  

Usage: cursor.execute("CREATE TABLE %s ...", (Literal(name), ))

Answer by Tobias for Passing table name as a parameter in psycopg2

I have created a little utility for preprocessing of SQL statements with variable table (...) names:

from string import letters  NAMECHARS = frozenset(set(letters).union('.'))    def replace_names(sql, **kwargs):      """      Preprocess an SQL statement: securely replace table ... names      before handing the result over to the database adapter,      which will take care of the values.        There will be no quoting of names, because this would make them      case sensitive; instead it is ensured that no dangerous chars      are contained.        >>> replace_names('SELECT * FROM %(table)s WHERE val=%(val)s;',      ...               table='fozzie')      'SELECT * FROM fozzie WHERE val=%(val)s;'      """      for v in kwargs.values():          check_name(v)      dic = SmartDict(kwargs)      return sql % dic    def check_name(tablename):      """      Check the given name for being syntactically valid,      and usable without quoting      """      if not isinstance(tablename, basestring):          raise TypeError('%r is not a string' % (tablename,))      invalid = set(tablename).difference(NAMECHARS)      if invalid:          raise ValueError('Invalid chars: %s' % (tuple(invalid),))      for s in tablename.split('.'):          if not s:              raise ValueError('Empty segment in %r' % tablename)    class SmartDict(dict):      def __getitem__(self, key):          try:              return dict.__getitem__(self, key)          except KeyError:              check_name(key)              return key.join(('%(', ')s'))  

The SmartDict object returns %(key)s for every unknown key, preserving them for the value handling. The function could check for the absence of any quote characters, since all quoting now should be taken care of ...

Answer by jczaplew for Passing table name as a parameter in psycopg2

Per this answer you can do it as so:

import psycopg2  from psycopg2.extensions import AsIs    #Create your connection and cursor...    cursor.execute("SELECT * FROM %(table)s", {"table": AsIs("my_awesome_table")})  

Answer by Derek Bartron for Passing table name as a parameter in psycopg2

You can just use the module format for the table name and then use the regular paramaterization for the execute:

xlist = (column, table)  sql = 'select {0} from {1} where utctime > %s and utctime < %s order by utctime asc;'.format(xlist)  

Keep in mind if this is exposed to the end user, you will not be protected from SQL injection unless you write for it.

Answer by Samir Alajmovic for Passing table name as a parameter in psycopg2

Surprised no one has mentioned doing this:

sql = 'select {} from {} where utctime > {} and utctime < {} order by utctime asc;'.format(dataItems, voyage, dateRangeLower, dateRangeUpper)  rows = cur.mogrify(sql)  

format puts in the string without quotations.

Fatal error: Call to a member function getElementsByTagName() on a non-object in D:\XAMPP INSTALLASTION\xampp\htdocs\endunpratama9i\www-stackoverflow-info-proses.php on line 72