CODEWITHSUNDEEP
pythonsqlpyodbc
user3656961
user3656961

Reputation: 21

Python pyodbc returning \x0e

I'm trying to return a hard coded value in my SQL query, but when running the query using pyodbc, random records return '\x0e' instead of the hard coded value (in this case '16'). If I run the query on the server (MS SQL Server 2008), the query returns all the correct results and values.

The beginning of the query looks like this:

My SQL Code:

Select '"16","' + S.ShipNum + '","'

My python code:

cursor.execute("""Select '\"16\",\"' + SS.ShipNum + '\",\"'

Is there another way to guarantee a value is returned from a query?

Upvotes: 2

Views: 355

Answers (2)

Bryan
Bryan

Reputation: 17703

It looks like you're trying to create a comma-delimited, quoted, string representation of the row. Don't try to do this in the database query, string formatting isn't one of T-SQL's strengths.

Pass the static value using a parameter, then join the row values. Using sys.databases for the example:

params = ("Some value",)
sql = "SELECT ?, name, user_access_desc FROM sys.databases"
for row in cursor.execute(sql):
    print(','.join('"{0}"'.format(column) for column in row))

Upvotes: 0

AhDev
AhDev

Reputation: 486

\016 is the oct representation of \x0e So I would think that it has more to do with the way in which you are escaping your double quotes. In your python you are actually geting \16 and not "16" as you desire.

You should try a prepared statment maybe. 

ps = db.prepare("SELECT 16")
ps()

returns: [(16,)]

Addtional examples can be seen here: [http://python.projects.pgfoundry.org/docs/0.8/driver.html#parameterized-statements]

You can see all of the ascii and other character sets here [http://donsnotes.com/tech/charsets/ascii.html]

Upvotes: 1

Related Questions