6

I have following query cur.execute("SELECT COUNT(addr) FROM list_table WHERE addr = '192.168.1.1'") to count the number of times the same address (192.168.1.1) appears on list_table table. addr is of inet type.

When I assign the query to a variable and print its result I get None:

res = cur.execute("SELECT COUNT(addr) FROM list_table WHERE addr = '192.168.1.1'")
print res # None

What is the proper way to get such thing?

Jay
  • 347
  • 2
  • 5
  • 10

4 Answers4

9

you can use the following steps for retrieving the data for a relational database using python:

    #!/usr/bin/python
    # import the desired package
    import MySQLdb

    # Open database connection
    db = MySQLdb.connect(hostaddress,user,password,db_name)

    # prepare a cursor object using cursor() method
    cursor = db.cursor()

    # execute SQL query using execute() method.
    cursor.execute("SELECT COUNT(addr) FROM list_table WHERE addr = '192.168.1.1'")

    # Fetch a single row using fetchone() method and store the result in a variable. 
    data = cursor.fetchone()

  #OR use fetchall() method to fetch multiple rows and store the result in a list variable. 
 data = cursor.fetchall()

    print data

    # disconnect from server
    db.close()
Mrityunjay Singh
  • 477
  • 5
  • 11
5

You have to use fetchone() or fetchall() to get the rows from the cursor.

Take a look at the available fetch methods.

In your case, something along the lines of:

res = cur.execute("SELECT COUNT(addr) FROM list_table WHERE addr = '192.168.1.1'")
row = cur.fetchone()
print(row)
4

Just a little bit more explanation.

execute() method prepares and executes a database operation and, according to the documentation:

The method returns None. If a query was executed, the returned values can be retrieved using fetch*() methods.

fetchone() is the most convenient to use since your query returns a single value, a count:

print(cur.fetchone())
alecxe
  • 462,703
  • 120
  • 1,088
  • 1,195
0

You can do something like this:

def ApplyQuery (query ,cursor):
   res = cur.execute(query)
   Return cur.fetchall()
   Sql1= “””SELECT COUNT(addr) FROM list_table WHERE addr = %d"””
   Rows=ApplyQuery(Sql1% 192.168.1.1,cursor)
   For r in rows:
      r1=r[0][0] #will give you first column in in first row and so on
Grey
  • 7
  • 3