sqlite SELECT statement returns None-CodePudding

I have an issue returning a string from my database query. First step was to create a database:

def create_database():

    # database setup
    try:
        con = sqlite3.connect('db/mydb.db')
        cur = con.cursor()
        cur.execute('CREATE TABLE IF NOT EXISTS user (id INTEGER NOT NULL PRIMARY KEY, balance REAL NOT NULL, text TEXT NOT NULL)')
        con.commit()
        con.close()
    except Error as e:
        print('Failed to setup database.\n'   e)
        exit(1)

def get_connection():

    try:
        con = sqlite3.connect('db/mydb.db')
        return con
    except:
        print('Unable to connect to database. Please try again later.\n')
        exit(1)

My second step was creating a user and add him with INSERT to my database:

def create_user(user_id : int):

    balance = 0.0; # base unit = USD
    text = create_text()

    # connect to database
    con = get_connection()
    cur = con.cursor()

    cur.execute('INSERT INTO user (id, balance, text) VALUES (?, ?, ?)', (user_id, balance, text))

    database = cur.execute('SELECT * FROM user').fetchone()
    print(database)
    
    con.commit()
    con.close()

def create_text():

    # do some stuff which creates my text
    # the text is something like 'LNURL...'
    
    return text

This is how the result of my database query looks like:

(393120847059091456, 0.0, 'LNURL1DP68GURN8GHJ7URP09JX7MTPDCHXGEF0D3H82UNVWQHKZURF9AMRZTMVDE6HYMP0XGMQA9V7RT')

If I try to query this database for my text it returns nothing/None. My print(text) just produces an empty new line.

def get_text(user_id : int):

    # connect to database
    con = get_connection()
    cur = con.cursor()

    cur.execute('SELECT text FROM user WHERE id=?', (user_id,))
    text = cur.fetchone()

    con.commit()
    con.close()

    print(text)
    return text

CodePudding user response：

I think my sqlite database used 32bit int values by default. So forcing it to use 64 bit when creating the table fixed my issue:

cur.execute('CREATE TABLE IF NOT EXISTS user (id INT8 PRIMARY KEY NOT NULL, balance REAL NOT NULL, text TEXT NOT NULL')

Than I can return my result of the query with this: return text[0]