[newbie] - python list into a sql query

[João]

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.
(Thought I'm still building the first script steps) I was able to
successfully query the DB against a single groupid, but am failing in
passing a list of integers into the sql query.

I'm failing miserably in,

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

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()

[MRAB]

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.
> (Thought I'm still building the first script steps) I was able to
> successfully query the DB against a single groupid, but am failing in
> passing a list of integers into the sql query.
>
> I'm failing miserably in,
>
> 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
>
>
> 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")
>

[snip]
The triple-quoted string starts at:

'''SELECT ...

and ends at:

...)''' % grp_range

which means that the part where you do the .join is _inside_ the string.

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

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