Hello everyone,
I want to update table value using variable but getting error :
pymysql.err.InternalError: (1054, “Unknown column ‘Test_123’ in ‘where clause’”)
I tried to use:
testo = table
ttt = fieldname
rt is i in loop
Thanks in advance…
Try
frappe.db.sql("""UPDATE `tabTesto` SET `ttt` = '{qty}' WHERE `item_name` LIKE '{item_code}'""".format(qty=rt[“actual_qty”], item_name=rt[“item_code”]), as_list=True)
Thanks for replying @joelios,
getting this error :
pymysql.err.ProgrammingError: (1064, “You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘‘Finished_Goods__I’ = ‘50.0’ where item_name like ‘Testing_hi’’ at line 1”)
if using this :
frappe.db.sql(“”“update testo set ‘{warehouse}’ = ‘{qty}’ where item_name like ‘{item_name}’”“”.format(warehouse=ttt, qty=rt[“actual_qty”], item_name=rt[“item_code”]), as_list=True)
Is this field name correct? Is it not finished_goods_i or something?
@joelios this is my table,
select * from testo;
±-----------±--------------------±------------------+
| item_name | Work_In_Progress__I | Finished_Goods__I |
±-----------±--------------------±------------------+
| Testing_hi | 0 | 0 |
| Test_ti | 0 | 0 |
| Test_too | 50.0 | 40.0 |
±-----------±--------------------±------------------+
3 rows in set (0.00 sec)
hmm, try following:
frappe.db.sql("""UPDATE `tabTesto` SET `Finished_Goods__I` = '{qty}' WHERE `item_name` LIKE '%{item_code}%'""".format(qty=rt["actual_qty"], item_name=rt["item_code"]), as_list=True)
Why would you use “LIKE” in WHERE clause?
If your table “testo” effectiv is in lowercase, use:
frappe.db.sql("""UPDATE `tabtesto` SET `Finished_Goods__I` = '{qty}' WHERE `item_name` LIKE '%{item_code}%'""".format(qty=rt["actual_qty"], item_name=rt["item_code"]), as_list=True)
@joelios still giving error
pymysql.err.ProgrammingError: (1064, “You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘%Testing_hi%’ at line 1”)
Sorry, but I see no error in the code… 
I’ll ask a colleague…
Hi @Saditi,
can you please post the exact query when it was executed (e.g. using frappe.log_error(mysql_query)? Then you can look at what is exactly in the query. The error looks like something with upper quotes is wrong…
Thanks for replying @lasalesi, getting this in command line
10:29:23 web.1 | 127.0.0.1 - - [19/Oct/2019 10:29:23] “POST /api/method/frappe.desk.search.search_link HTTP/1.1” 200 -
10:29:24 web.1 | 127.0.0.1 - - [19/Oct/2019 10:29:24] “GET /api/method/frappe.desk.form.utils.validate_link?value=277678088a&options=Route+master&fetch=&_=1571461148553 HTTP/1.1” 200 -
10:29:25 web.1 | Syntax error in query:
10:29:25 web.1 | update testo set Work_In_Progress__I = 50.0 where item_name like %Testing_hi%
10:29:25 web.1 | Syntax error in query:
10:29:25 web.1 | update testo set Work_In_Progress__I = 50.0 where item_name like %Testing_hi%
10:29:25 web.1 | Traceback (most recent call last):
10:29:25 web.1 | File “/home/indiba/erpnext/apps/erpnext/erpnext/stock/doctype/route_allocation/route_allocation.py”, line 46, in get_items_list
10:29:25 web.1 | frappe.db.sql(“update testo set {warehouse} = {qty} where item_name like %{item_name}%”.format(warehouse=ttt, qty=rt[“actual_qty”],item_name=rt[“item_code”]), as_list=True)
10:29:25 web.1 | File “/home/indiba/erpnext/apps/frappe/frappe/database/database.py”, line 171, in sql
10:29:25 web.1 | self._cursor.execute(query)
10:29:25 web.1 | File “/home/indiba/erpnext/env/lib/python3.5/site-packages/pymysql/cursors.py”, line 170, in execute
10:29:25 web.1 | result = self._query(query)
10:29:25 web.1 | File “/home/indiba/erpnext/env/lib/python3.5/site-packages/pymysql/cursors.py”, line 328, in _query
10:29:25 web.1 | conn.query(q)
10:29:25 web.1 | File “/home/indiba/erpnext/env/lib/python3.5/site-packages/pymysql/connections.py”, line 517, in query
10:29:25 web.1 | self._affected_rows = self._read_query_result(unbuffered=unbuffered)
10:29:25 web.1 | File “/home/indiba/erpnext/env/lib/python3.5/site-packages/pymysql/connections.py”, line 732, in _read_query_result
10:29:25 web.1 | result.read()
10:29:25 web.1 | File “/home/indiba/erpnext/env/lib/python3.5/site-packages/pymysql/connections.py”, line 1075, in read
10:29:25 web.1 | first_packet = self.connection._read_packet()
10:29:25 web.1 | File “/home/indiba/erpnext/env/lib/python3.5/site-packages/pymysql/connections.py”, line 684, in _read_packet
10:29:25 web.1 | packet.check_error()
10:29:25 web.1 | File “/home/indiba/erpnext/env/lib/python3.5/site-packages/pymysql/protocol.py”, line 220, in check_error
10:29:25 web.1 | err.raise_mysql_exception(self._data)
10:29:25 web.1 | File “/home/indiba/erpnext/env/lib/python3.5/site-packages/pymysql/err.py”, line 109, in raise_mysql_exception
10:29:25 web.1 | raise errorclass(errno, errval)
10:29:25 web.1 | pymysql.err.ProgrammingError: (1064, “You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘%Testing_hi%’ at line 1”)
10:29:25 web.1 |
10:29:25 web.1 | During handling of the above exception, another exception occurred:
10:29:25 web.1 |
10:29:25 web.1 | Traceback (most recent call last):
10:29:25 web.1 | File “/home/indiba/erpnext/apps/frappe/frappe/app.py”, line 60, in application
10:29:25 web.1 | response = frappe.api.handle()
10:29:25 web.1 | File “/home/indiba/erpnext/apps/frappe/frappe/api.py”, line 55, in handle
10:29:25 web.1 | return frappe.handler.handle()
10:29:25 web.1 | File “/home/indiba/erpnext/apps/frappe/frappe/handler.py”, line 21, in handle
10:29:25 web.1 | data = execute_cmd(cmd)
10:29:25 web.1 | File “/home/indiba/erpnext/apps/frappe/frappe/handler.py”, line 56, in execute_cmd
10:29:25 web.1 | return frappe.call(method, **frappe.form_dict)
10:29:25 web.1 | File “/home/indiba/erpnext/apps/frappe/frappe/init.py”, line 1036, in call
10:29:25 web.1 | return fn(*args, **newargs)
10:29:25 web.1 | File “/home/indiba/erpnext/apps/erpnext/erpnext/stock/doctype/route_allocation/route_allocation.py”, line 50, in get_items_list
10:29:25 web.1 | frappe.log_error(frappe.db.sql(“update testo set {warehouse} = {qty} where item_name like %{item_name}%”.format(warehouse=ttt, qty=rt[“actual_qty”],item_name=rt[“item_code”]), as_list=True)
10:29:25 web.1 | File “/home/indiba/erpnext/apps/frappe/frappe/database/database.py”, line 171, in sql
10:29:25 web.1 | self._cursor.execute(query)
10:29:25 web.1 | File “/home/indiba/erpnext/env/lib/python3.5/site-packages/pymysql/cursors.py”, line 170, in execute
10:29:25 web.1 | result = self._query(query)
10:29:25 web.1 | File “/home/indiba/erpnext/env/lib/python3.5/site-packages/pymysql/cursors.py”, line 328, in _query
10:29:25 web.1 | conn.query(q)
10:29:25 web.1 | File “/home/indiba/erpnext/env/lib/python3.5/site-packages/pymysql/connections.py”, line 517, in query
10:29:25 web.1 | self._affected_rows = self._read_query_result(unbuffered=unbuffered)
10:29:25 web.1 | File “/home/indiba/erpnext/env/lib/python3.5/site-packages/pymysql/connections.py”, line 732, in _read_query_result
10:29:25 web.1 | result.read()
10:29:25 web.1 | File “/home/indiba/erpnext/env/lib/python3.5/site-packages/pymysql/connections.py”, line 1075, in read
10:29:25 web.1 | first_packet = self.connection._read_packet()
10:29:25 web.1 | File “/home/indiba/erpnext/env/lib/python3.5/site-packages/pymysql/connections.py”, line 684, in _read_packet
10:29:25 web.1 | packet.check_error()
10:29:25 web.1 | File “/home/indiba/erpnext/env/lib/python3.5/site-packages/pymysql/protocol.py”, line 220, in check_error
10:29:25 web.1 | err.raise_mysql_exception(self._data)
10:29:25 web.1 | File “/home/indiba/erpnext/env/lib/python3.5/site-packages/pymysql/err.py”, line 109, in raise_mysql_exception
10:29:25 web.1 | raise errorclass(errno, errval)
10:29:25 web.1 | pymysql.err.ProgrammingError: (1064, “You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘%Testing_hi%’ at line 1”)
If using this(I am not sure if using it correctly):
try:
frappe.db.sql(“update testo set {warehouse} = {qty} where item_name like %{item_name}%”.format(warehouse=ttt, qty=rt[“actual_qty”],item_name=rt[“item_code”]), as_list=True)
except Exception:
frappe.log_error(frappe.db.sql(“update testo set {warehouse} = {qty} where item_name like %{item_name}%”.format(warehouse=ttt, qty=rt[“actual_qty”],item_name=rt[“item_code”]), as_list=True)
The problem is that from this we can only guess the actual statement, as it depends on the warehouse, item_name, … values. And they might contain characters that break your query.
Can you please change your code to
query = """update `testo` set `{warehouse}` = {qty} where `item_name` like '%{item_name}%'”"".format(warehouse=ttt, qty=rt[“actual_qty”],item_name=rt[“item_code”]
frappe.log_error(query, "SQL debug")
frappe.db.sql(query, as_list=True)
Then execute the code an post the content of the Error Log in the system, it should contain the exact SQL query at it will be executed. Looking at this, the error might be obvious…
Is the table really “testo” and not “tabTesto” as it would be with a doctype in Frappe?
@lasalesi sorry for replying late,
I just changed table name from testo to routes and it is only routes not tabroutes
Same error again,
query = frappe.db.sql(“”“update routes set {warehouse} = {qty} where item_name like %{item_name}%”“”.format(warehouse=ttt, qty=rt[“actual_qty”],item_name=rt[“item_code”]))
10:29:09 web.1 | File “/home/indiba/erpnext/apps/frappe/frappe/database/database.py”, line 171, in sql
10:29:09 web.1 | self._cursor.execute(query)
10:29:09 web.1 | File “/home/indiba/erpnext/env/lib/python3.5/site-packages/pymysql/cursors.py”, line 170, in execute
10:29:09 web.1 | result = self._query(query)
10:29:09 web.1 | File “/home/indiba/erpnext/env/lib/python3.5/site-packages/pymysql/cursors.py”, line 328, in _query
10:29:09 web.1 | conn.query(q)
10:29:09 web.1 | File “/home/indiba/erpnext/env/lib/python3.5/site-packages/pymysql/connections.py”, line 517, in query
10:29:09 web.1 | self._affected_rows = self._read_query_result(unbuffered=unbuffered)
10:29:09 web.1 | File “/home/indiba/erpnext/env/lib/python3.5/site-packages/pymysql/connections.py”, line 732, in _read_query_result
10:29:09 web.1 | result.read()
10:29:09 web.1 | File “/home/indiba/erpnext/env/lib/python3.5/site-packages/pymysql/connections.py”, line 1075, in read
10:29:09 web.1 | first_packet = self.connection._read_packet()
10:29:09 web.1 | File “/home/indiba/erpnext/env/lib/python3.5/site-packages/pymysql/connections.py”, line 684, in _read_packet
10:29:09 web.1 | packet.check_error()
10:29:09 web.1 | File “/home/indiba/erpnext/env/lib/python3.5/site-packages/pymysql/protocol.py”, line 220, in check_error
10:29:09 web.1 | err.raise_mysql_exception(self._data)
10:29:09 web.1 | File “/home/indiba/erpnext/env/lib/python3.5/site-packages/pymysql/err.py”, line 109, in raise_mysql_exception
10:29:09 web.1 | raise errorclass(errno, errval)
10:29:09 web.1 | pymysql.err.ProgrammingError: (1064, “You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘%Testing_hi%’ at line 1”)
Sorry, I missed out on the upper quotes. Updated the post above. Still, if you build the query and then print it before executing it (see snippet above), it will be easier to debug…
How do you mean “snapshot of query”? I don’t have your data, so I don’t have a snapshot of the query (it would not work).
If you still have issues, please post the sql_query string before it gets executed, then someone from the community can help to troubleshoot the MySQl error message. Personally, I believe there is an issue with an unescaped character in your data, which gives a mismatch in the MySQL statement. Typical candidates are
` ' "