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

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]

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

 

[Dennis Lee Bieber]

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

One: it is always best to use parameterized queries for the data
values.

Two: as mentioned by others, your join() clause is part of the
string literal... which makes no sense as SQL.

sql = """select ip, host, dns from zabbix_tst_db.hosts
where hostid in (%s)""" % ", ".join(["%s"] * len(grp_range))

Here, the .join() creates a string of (MySQLdb) placeholders for
each item in grp_range.

cursor.execute(sql, grp_range)
Here, the db-api adapter replaces each placeholder with the escaped
value of that item.

 

[pythonZen]

Thanks for the feedback guys.
I got it to work with Rami's for clause.

The triple quote thing was really obvious too
SQLAlchemy is actually on my to learn list but haven't had the time lately.
I'll read the parameterization link and other ones, it's clear there I still have to learn a few things.

Thanks again!

 

[pythonZen]

Actually....

Dennis,
I'd like to better understand your .join modification

sql = """select ip, host, dns from zabbix_tst_db.hosts
where hostid in (%s)""" % ", ".join(["%s"] * len(grp_range))

right after binding sql, if I print it, it returns

select ip, host, dns from zabbix_tst_db.hosts
where hostid in (%s, %s, %s, %s, %s)

isn't it lacking some % declaration?
the first %s interprets

", ".join(["%s"] * len(grp_range))

as a string, but where from is the %s in .join(["%s"] being fed..?
I apologize for my dumb doubt