How to remove duplicate record from database sqlite3 in Python3?

Question

I have two databases of user's data and I need to remove duplicate records from the db_a, by checking one column (login) in db_a and db_b and if it found the specific login already exist in db_b it has just to remove the duplicate record from the db_a. To do this I thought about DELETE Statement in SQL, but it shows me error -

sqlite3.OperationalError: no such column: user_old_info.login

Both databases are data full. I use Python 3 & SQLite3.

import sqlite3

db_a = sqlite3.connect('new_users.db')
c_a = db_a.cursor()

c_a.execute("CREATE TABLE IF NOT EXISTS user_info (first TEXT, last TEXT, login TEXT)")
db_a.commit()

db_b = sqlite3.connect('main.db')
c_b = db_b.cursor()
c_b.execute("CREATE TABLE IF NOT EXISTS user_old_info (first TEXT, last TEXT, login TEXT)")
db_b.commit()

c_a.execute('DELETE FROM user_info WHERE user_info.login = user_old_info.login')
db_a.commit()

Also tried to use JOIN Statement:

c_a.execute('SELECT login FROM user_info JOIN user_old_info ON user_old_info.login=user_info.login')
db_a.commit()

The error is the same in both cases:

sqlite3.OperationalError: no such column: user_old_info.login

UPDATE: I tried the ATTACH DATABASE STATEMENT and here's the same error...

attachDatabaseSQL = "ATTACH DATABASE main.db AS checklogin"
dbSpec = ("main.db",)
c_a.execute(attachDatabaseSQL, dbSpec)
db_a.commit()

c_a.execute('DELETE FROM user_info WHERE user_info.login = checklogin.login')
db_a.commit()

Error--->

line 16, in <module>
c_a.execute(attachDatabaseSQL, dbSpec)
sqlite3.OperationalError: no such column: main.db

Did it according this Attach A Database File

Answer 1

You execute the query on db_a, which knows nothing of db_b. You would either need to use something like attach, or (if performance is not an issue) just separate the query by first getting the logins from db_b in one query, and then removing those that exist in db_a in another query.