search

mkleehammer / pyodbc

Python ODBC bridge
https://github.com/mkleehammer/pyodbc/wiki
MIT No Attribution
2.89k stars 562 forks source link

the query works when I send it via MS Access, but not via pyodbc #1060

Closed happy322 closed 2 years ago

happy322 commented 2 years ago

I need help with the following question. When sending a sql query via MS Access, I get the data I'm interested in. But when I try to send the same request via pyodbc I get an empty response.

I think the problem is in this piece of code: WHERE ([Table name].Name LIKE ‘[a-z]’) OR …

v-chojas commented 2 years ago

You need to provide far more details about your environment.

gordthompson commented 2 years ago

Please provide a minimal reproducible example.

happy322 commented 2 years ago

Python: 3.8.10 pyodbc: 4.0.32 OS: Win 10

Query that doesn't work correctly:

SELECT * FROM Table WHERE Table.Name LIKE ‘*[a-z]*’; This request is executed directly through the ms access request constructor, but when I send it through pyodbc the response is not correct.

gordthompson commented 2 years ago

By default the Access UI uses * and ? wildcards, whereas Access ODBC uses the ANSI wildcards % and . for LIKE statements. So, with pyodbc you should use

sql = "SELECT * FROM my_table WHERE my_table.name LIKE '%[a-z]%'"
happy322 commented 2 years ago

By default the Access UI uses * and ? wildcards, whereas Access ODBC uses the ANSI wildcards % and . for LIKE statements. So, with pyodbc you should use

sql = "SELECT * FROM my_table WHERE my_table.name LIKE '%[a-z]%'"

Oh, thx! It’s work!