Sqlite3 doesnt save data in python

Question

Hello I am trying to create api using flask but i am facing issue where sqlite is not saving data. here is the code below:

def data_insert(link):
    try:
        c.execute(f"INSERT INTO ping(links) VALUES ('{link}');")
        conn.commit()
        return "sucess"
    except sqlite3.DatabaseError as e:
        print("AN ERROR HAS OCCURED")
        print(chalk.red(e))
        return "an internal error has occured."
def get_links():
    try:
        links = c.fetchall()
        print(f"links:{links}")
        return links
    except sqlite3.DatabaseError or sqlite3.DataError as e:
        print(chalk.red(e))
        return "an internal error has occured."

and in flask.py:

@app.route("/submit-url")
def main():
    url = request.args.get("url")
    if url == None:
        return "please enter url",404
    else:
        output = db.data_insert(url)
        links = db.get_links()
        print(links)
        return output

What should have happened: terminal should show list of data what actually happened: It returns empty list.

Answer 1

Your get_links() function is not making a query; it's iterating over whatever are the rows returned by the last use of c. (You really do not want to share a cursor; get a new one for each query.)

Instead of just links = c.fetchall() with whatever you've last executed with c, you'll need a new cursor, and to actually query things:

c = conn.cursor()
c.execute("SELECT links FROM ping")
links = [r[0] for r in c.fetchall()]  # unpeel the row tuples to get just the single column

You should probably also get rid of the exception handling within those functions (and do it elsewhere); also, you'll want to use ? placeholders for those insertions so all in all:

def data_insert(link):
    c = conn.cursor()
    c.execute("INSERT INTO ping(links) VALUES (?)", (link,))
    conn.commit()


def get_links():
    c = conn.cursor()
    c.execute("SELECT links FROM ping")
    return [row[0] for row in c.fetchall()]