Member Avatar for DimaYasny

Guys, I'm stumped here. Trying to retrieve a row from mysql using MySQLdb, I receive the number of matching rows found, not the data itself.

here's the code:

def getKbd(kb_num):
    query="SELECT kbd_name, kbd_data, kbd_ps FROM kbd_ WHERE kbd_number = %d " % kb_num
    a = c.execute(query)
    return a

print getKb(3)

the output is 1 (since one row matches. if I do sELECT * I get the number of rows in db.

here's the same query in mysql:

mysql> SELECT kbd_name, kbd_data, kbd_ps FROM kbd_ WHERE kbd_number = 3 ;
+-------------------------------------+------------------------------------------------------------------+-----------+
| kbd_name | kbd_data | kbd_passes |
+-------------------------------------+------------------------------------------------------------------+-----------+
| kbd name number 3 | kbd data for number 3 in here | 0 |
+-------------------------------------+------------------------------------------------------------------+-----------+
1 row in set (0.00 sec)


What I want to receive as output is a list or tuple with something like

  • 2 Contributors
  • 2 Replies
  • 127 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by DimaYasny

Dani AI

Generated

Brief summary: the integer printed by the original snippet is the cursor's execution result (row count), not the result set. In MySQLdb the DB-API separates executing a statement from fetching rows — execute tells the driver to run the query and typically returns the number of matching/affected rows; the actual rows must be retrieved with the cursor's fetch methods (fetchone, fetchmany, fetchall). See the Python DB-API spec for the intended fetch/execute separation (PEP 249).

A practical pattern for a single-row lookup is to execute with parameter substitution (avoid building SQL with Python % formatting) and then fetch one row. For example:

# use parameter substitution to avoid injection
cursor.execute("SELECT col1, col2 FROM mytable WHERE id = %s", (id_val,))
row = cursor.fetchone()   # tuple or None

Notes and troubleshooting tips:

  • fetchone() returns a tuple for one row or None if no rows matched. fetchall() returns a list of tuples; fetchmany(size) can be used to limit memory use for large results.
  • Use cursor.rowcount to inspect how many rows were affected/returned when the adapter provides that attribute. Behavior of execute() (return value) can vary between DB-API modules, so rely on fetch methods for portability.
  • Prefer parameterized queries (placeholders are %s in MySQLdb) instead of formatting values into the SQL string. To get column-name access instead of index-based tuples, use a DictCursor or similar cursor factory.
  • Close cursors/connections when done; only DML needs commit.

This expands on 's correct suggestion to fetch results and clarifies why saw the number instead of the tuple, plus safer retrieval patterns to use going forward.

Recommended Answers

All 2 Replies

Member Avatar for xav.vijay

After

a = c.execute(query)
print c.fetchall()

should give u the output...

Member Avatar for DimaYasny

Thanks, I've just come up with the same. Strange how the cursor does everything standart sql syntax does, but doesn't return queries

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.