[newbie] - python list into a sql query

[Rami Chowdhury]

On Monday 01 March 2010 09:54:20 João wrote:
> Hi.
> 
> Can someone guide me into getting this to work? It's just really
> querying a DB of an Autodiscovery tool to have a bunch of updated dns
> files.

I wouldn't be building SQL queries by hand if I could avoid it -- is this just 
a few one-off scripts or a project that could usefully benefit from a database 
abstraction layer (like SQLAlchemy: http://www.sqlalchemy.org)?

> 
> sql = '''SELECT ip, host, dns FROM zabbix_tst_db.hosts WHERE hostid IN
> (
> select hostid from hosts_groups WHERE groupid IN (' +
> ','.join(map(str, %s)) + ')''' % grp_range
> 

I'm not sure what you expect this to evaluate to?

#>>> grp_range = [1, 2, 3] # dummy data
#>>> sql = '''SELECT ip, host, dns FROM zabbix_tst_db.hosts WHERE hostid IN
... (
... SELECT hostid FROM hosts_groups WHERE groupid IN (' + 
... ','.join(map(str, %s)) + ')''' % grp_range
#>>> print sql
SELECT ip, host, dns FROM zabbix_tst_db.hosts WHERE hostid IN
(
SELECT hostid FROM hosts_groups WHERE groupid IN (' + 
','.join(map(str, [1, 2, 3])) + ')

Since the triple-quoted string only ends at the corresponding triple quote, 
you're including some Python code into the string instead of executing it. If 
you really want to splice the elements of grp_range into the query, you should 
do something like:

#>>> sql = 'SELECT ip, host, dns FROM zabbix_tst_db.hosts WHERE hostid IN 
(SELECT hostid FROM hosts_groups WHERE groupid IN (%s))' % ",".join(str(x) for 
x in grp_range)
#>>> sql
'SELECT ip, host, dns FROM zabbix_tst_db.hosts WHERE hostid IN (SELECT hostid 
FROM hosts_groups WHERE groupid IN (1,2,3))'

Although, really, if you can, I'd parameterise the query (http://mysql-
python.sourceforge.net/MySQLdb-1.2.2/public/MySQLdb.cursors.BaseCursor-
class.html#execute) at the very least...

> 
> with
>     _mysql_exceptions.ProgrammingError: (1064, "You have an error in
> your SQL syntax; check the manual that corresponds to your MySQL
> server version for the right syntax to use near '' at line 1")
> 
> 
> 
> I'll gladly accept any other code change suggestion
> 
> 
>     #!/usr/bin/env python
> 
>     import MySQLdb
>     import sys, os
>     import code
> 
>     builder_path = '/opt/scripts/dns_builder'
>     grp_range = range(10,15)
> 
>     try:
>             db = MySQLdb.connect(host="localhost",
>                                user="tst",passwd="tst",db="tst_db" )
>     except MySQLdb.Error, e:
>         print "Error %d: %s" % (e.args[0], e.args[1])
> 
>     cursor = db.cursor()
> 
>     sql = '''SELECT ip, host, dns FROM zabbix_tst_db.hosts WHERE
> hostid IN (
>     select hostid from hosts_groups WHERE groupid IN (' +
> ','.join(map(str, %s)) + ')''' % grp_range
> 
>     cursor.execute(sql)
> 
>     f = open('%s/dns_unknown_list.txt', 'w+') % builder_path
>     data = cursor.fetchall()
> 
>     for row in data:
>     ip = row[0]
>     host = row[1]
>     dns = row[2]
>     if host == dns:
>       #tmn
>       if ip[0][:10] in ('140.254.30','10.13.74.')
>        group1_file = open('%s/group1.zone', 'w') % builder_path
>        print >>group1_file, '''$ORIGIN group1
> 
>        '''
>        print >>group1_file, '%s IN A    %s' % (dns, ip)
>       elif ip[0][:3] in ('8.128.46.','6.128.101')
>        group2_file = open('%s/group2.zone', 'w') % builder_path
>        print >>group2_file, '''$ORIGIN group2
> 
>        '''
>        print >>group2_file, '%s IN A    %s' % (dns, ip)
>       else:
>        print >>f, 'unknown     IN A    %s' % ip
> 
>     db.close()
>     input_file.close()
>     f.close()


----
Rami Chowdhury
"As an online discussion grows longer, the probability of a comparison 
involving Nazis or Hitler approaches one." -- Godwin's Law
408-597-7068 (US) / 07875-841-046 (UK) / 01819-245544 (BD)