FAQ

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)

Search Discussions

Discussion Posts

Previous

Related Discussions

Discussion Navigation
viewthread | post
posts ‹ prev | 3 of 3 | next ›
Discussion Overview
grouppython-list @
categoriespython
postedMar 1, '10 at 5:54p
activeMar 1, '10 at 7:04p
posts3
users3
websitepython.org

3 users in discussion

MRAB: 1 post Rami Chowdhury: 1 post João: 1 post

People

Translate

site design / logo © 2022 Grokbase