1

I am trying to delete rows from two tables with inner join. I don't really understand why this error pops up.

import sqlite3
login = 'uzytkownik6'
conn = sqlite3.connect('fiszki.db')    
c = conn.cursor()
c.execute("DELETE u.*, t.* FROM users u INNER JOIN translations t ON 
u.user_id=t.user_id WHERE u.user_name='{}'".format(login)) 
conn.commit()

But I get error:

OperationalError: near "u": syntax error
Hub_Ert
  • 11
  • 1
  • Shouldn't there be an `AS` in there? Also you shouldn't use string interpolation to put values in queries like that. – jonrsharpe Jan 18 '19 at 22:40
  • I has other codes with select where 'as' wasn't needed, but I checked it added 'as' and it didn't change the situation, still the same problem – Hub_Ert Jan 18 '19 at 22:42
  • Why do you think you can delete from multiple tables with one statement, or individual columns? Sqlite DELETE syntax: https://www.sqlite.org/lang_delete.html – Shawn Jan 18 '19 at 23:11
  • Possible duplicate of [How delete table inner join with other table in Sqlite?](https://stackoverflow.com/questions/24511153/how-delete-table-inner-join-with-other-table-in-sqlite) – manveti Jan 18 '19 at 23:32

1 Answers1

0

You should never use the normal python string formatting when executing SQL commands. Example: db.execute("DELETE FROM users WHERE userId = (?)", [userId]). Also, you don't really need to have run the db.cursor() method after connecting. See SQLite3 API documentation for Python 3.

mohshbool
  • 41
  • 2
  • 9
  • Thanks, wasn't aware of that, it worked in some other simple query. However when I changed it to `c.execute("DELETE u.*, t.* FROM users as u INNER JOIN translations as t ON u.user_id=t.user_id WHERE u.user_name=(?)", login)`, I still get the same problem -> `OperationalError: near "u": syntax error` – Hub_Ert Jan 18 '19 at 23:08
  • This answer isn't quite answering the question. It is better to give an answer that solves the observed problem, and then point out a superior approach. Also, if you include in your answer why you should never use the normal Python string formatting when executing SQL commands, that makes your answer much better. – David M Jan 19 '19 at 01:09
  • @DavidM, Thanks for reminding me of that, I'm still new to Stackoverflow. I will make sure to include it in future answers and focus more on the question. Also, Hub_Ert, I don't exactly know what is the fix to your specific questions, but my guess would be that the problem would be that you're trying to use a DELETE statement with an alias and a join, which isn't possible, see [this answer](https://stackoverflow.com/a/15832338/10244931). – mohshbool Jan 20 '19 at 02:05
  • And as for why you shouldn't use native python string formatting while executing SQL statements, is the vulnerability to [SQL Injection Attacks](https://www.w3schools.com/sql/sql_injection.asp). – mohshbool Jan 20 '19 at 02:06