CODEWITHSUNDEEP
pythonmysql
DennisJupiter
DennisJupiter

Reputation: 89

Select Result from MySQL Database and Assign it to a Variable

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.

Upvotes: 1

Views: 1629

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521179

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"])

Upvotes: 1

Related Questions