Скачать или смотреть Resolving cx_Oracle Error: "not all arguments converted during string formatting"

A guide to fixing the "TypeError: not all arguments converted during string formatting" error when working with cx_Oracle and MariaDB in Python.
---
This video is based on the question https://stackoverflow.com/q/69271239/ asked by the user 'CodingCat' ( https://stackoverflow.com/u/1413513/ ) and on the answer https://stackoverflow.com/a/69279068/ provided by the user 'CodingCat' ( https://stackoverflow.com/u/1413513/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: cx_Oracle: "not all arguments converted during string formatting"

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Understanding the cx_Oracle Error

Have you ever encountered the frustrating error: "TypeError: not all arguments converted during string formatting" while working with cx_Oracle? This issue typically arises when trying to execute SQL commands that utilize positional bindings but the syntax doesn't match the database handling library you are using. In this guide, we’ll explore this common problem and provide a detailed solution to help you get back on track.

The Scenario

Let’s set the stage. You are trying to update a table in a MariaDB database using cx_Oracle, a popular Oracle database interface for Python. You have a table structure that looks like this:

[[See Video to Reveal this Text or Code Snippet]]

And, here’s the Python code snippet you’re using to execute the update:

[[See Video to Reveal this Text or Code Snippet]]

You have defined four positional bindings for your query, but as you run it, the error surfaces.

Decoding the Issue

What Went Wrong?

Misunderstanding the Library: The main reason for this error is often a mismatch between how different libraries handle SQL queries. In your case, while you were using cx_Oracle, the actual SQL query was being processed by a different library, pymysql, which does not support :n positional bindings.

Positional vs. Named Placeholders: cx_Oracle uses named placeholders (e.g., :1, :2), but pymysql (and other similar libraries) use %s. This discrepancy needs to be addressed in your SQL query syntax.

The Solution

Adjusting Your Query

Replacing the named placeholders with %s placeholders resolves the issue. Here’s the corrected version of your SQL query:

[[See Video to Reveal this Text or Code Snippet]]

Putting It All Together

Your complete Python update code should now look like this:

[[See Video to Reveal this Text or Code Snippet]]

This change ensures that your query is compatible with the underlying database engine (pymysql in this case), eliminating the earlier error.

Conclusion

When working with SQL queries in Python, it's crucial to ensure that the placeholder syntax matches the database connector you are using. By switching from :n to %s placeholders, you can seamlessly execute your updates without encountering the "not all arguments converted during string formatting" error.

Happy coding, and may your databases always return the results you seek!