[FIXED] How to call cursor.execute if the connection "with" and "cur" are in another different py file?

Issue

Having self.con = sqlite3.connect (db) and self.cur = self.con.cursor() in the db.py file, how can I call them in the main.py file to use cursor.execute? I wrote db.self.cur.execute('SELECT Name FROM TableExample'), but obviously I was wrong, there is an error of course

P.S: In db.py do I have to insert the path of the database inside db of the sqlite3.connect(db)?

I had tried like this:

DB.PY

import sqlite3

class Database:
    def __init__(self, db):
        self.con = sqlite3.connect(db)
        self.cur = self.con.cursor()
        sql = """
        CREATE TABLE IF NOT EXISTS employees(
            ID Integer Primary Key,
            example1 integer,
            example2 integer
        )
        """
        self.cur.execute(sql)
        self.con.commit()

MAIN.PY

from db import Database

db = Database('/home/xxxx/database.db')

def example():
    db.self.cur.execute('SELECT Name FROM TableExample') #error
    result=[row[0] for row in cursor]
    return result

UPDATE: New example in main.py

def example():
    db.cur.execute('SELECT Name FROM TableExample')
    result=[row[0] for row in db.cur]
    return result

Solution

There are a few problems to fix here. The error on this line:

db.self.cur.execute('SELECT Name FROM TableExample') #error

is because of the use of self. You'll want to read up on how to use classes, but in short self (named that by convention, not requirement) is how an object instance refers to itself, such as where you set self.cur in __init__(). You don't refer to self outside the object.

Instead, you want to refer to the cur attribute of the db object like this:

db.cur.execute('SELECT Name FROM TableExample')

The next issue is the list expression that reads from the database. This:

result=[row[0] for row in cursor]

is going to cause an error, because cursor is not defined. You probably want to refer to the cur attribute in the db object:

result=[row[0] for row in db.cur]

However, while this works, it's not the best way to do it. You're implicitly using the results of the last query executed on the cursor, which does work, but is less obvious.

Better would be to create a new cursor object from the query and iterate that:

query = db.cur.execute('SELECT Name FROM TableExample')
result = [row[0] for row in query]

Answered By - sj95126