Select Result from MySQL Database and Assign it to a Variable

Question

I am connecting to a MySQL database through a Python environment. I'm using pypyodbc as the connector. Everything works fine, I can Select and Insert results, etc.

The issue comes in when I attempt to actually assign results fetched from the database to a variable: It literally only works sometimes, and rarely.

Here's some code:

SQLCommand = ("SELECT TRIM(' ' FROM ColumnName) FROM MyDB.dbo.MyTable WHERE ColumnName='%s'" % (my_variable)
cursor.execute(SQLCommand)
print("Here is the result from the database ",  cursor.fetchone())

another_variable =  cursor.fetchone()
print("Here is the value from the database assigned to a variable: ", another_variable)

As you can see I attempt to assign the fetched result to a variable at:

another_variable =  cursor.fetchone()

The word I'm fetching will be in the database plain as day. Cursor.fetchone will fetch the result and print it every time. But it will only assign it to a variable sometimes. Rarely.

I'm baffled, is there some known issue doing this? Am I somehow missing anything? I need it to assign the result to the cursor everytime, not rarely.

When you call cursor.fetchone() a second time it will attempt to fetch a second row. Do you have multiple rows that satisfy the condition? — Selcuk
– Selcuk, Commented May 11, 2018 at 1:34
I only call fetchone once. I was just highlighting the space again where I'm trying to assign it to a variable. Is that what you mean? I'll check if multiple rows satisfy the condition though thank you — DennisJupiter
– DennisJupiter, Commented May 11, 2018 at 1:45

Tim Biegeleisen · Accepted Answer · 2018-05-11 01:34:18Z

1

First, you should be assigning an alias to your call to TRIM inside the query, e.g.

SELECT TRIM(' ' FROM ColumnName) AS val FROM MyAI_DB.dbo.MyDatabase ...

Then, to access a column/alias, you need to reference the row object returned by fetchone():

sql = "SELECT TRIM(' ' FROM ColumnName) AS val FROM MyAI_DB.dbo.MyDatabase " +
    "WHERE ColumnName = '%s'" % (my_variable)
cursor.execute(sql)
row = cursor.fetchone()
print print "%s" % (row["val"])

answered May 11, 2018 at 1:34

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Select Result from MySQL Database and Assign it to a Variable

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related