Flask and SQLAlchemy - error binding parameter

Question

I'm fairly new to programming in general and I'm trying to build a simple website as a pet project.

It's built with python using Flask and SQLAlchemy for the db.

I'm trying to store a QR Pillow Image(as generated or later converted to PNG) in the database by converting it to a byte stream with BufferIO and then encoding it to base64 UTF-8 as a byte string (this is where I might be doing something wrong) and then storing it in a String Column in the database.

Printing the string to the console, I can't see anything wrong with it, but I'm being met with a database error and it seems I hit a wall.

I would appreciate if anyone could point me in the right direction or if I should try something else entirely as I tried a few different methods and nothing seems to work. Thank you!

The error I`m faced with:

(sqlite3.InterfaceError) Error binding parameter 2 - probably unsupported type.
[SQL: INSERT INTO qrdb (data, qr_b64s, date, owner_id) VALUES (?, ?, CURRENT_TIMESTAMP, ?)]
[parameters: ('test', 'iVBORw0KGgoAAAANSUhEUgAAASIAAAEiAQAAAAB1xeIbAAABjklEQVR4nO2aQW6kMBRE3x9bytJIOUCOYm4wZ50bNEeZA0Syl0hu1SzAnSa9mGxoaLAXH7Ce5BL6KsoWJv4_hl8_gKBRjWpUoxq1d ... (310 characters truncated) ... q50xhxy2KypyCmuesSuX5rN6sq_n-drd_9a9J-W_PInem4WM0wZWWc55I3eV78pus34muI1IP55jkbs73hNFa369IPZxjQs33SrR8vyZl7d-oRjWqUY06BfUPhs3JLUffqKQAAAAASUVORK5CYII=', <User 1>)]

The generator function:

def enq(data):
    qr = qrcode.make(data)
    img_buffer = BytesIO()
    qr.save(img_buffer, format='PNG')
    byte_data = img_buffer.getvalue()
    b64s = base64.urlsafe_b64encode(byte_data)
    string = str(b64s, 'UTF-8')
    return string

Posting to DB:

if request.method == 'POST':

    data = request.form.get('data')
    
    if len(data) < 1:
        flash('Data too short!', category='error')
    else:
        new_qr = Qrdb(qr_b64s=codegen.enq(data), data=data, owner_id=current_user)
        db.session.add(new_qr)
        db.session.commit
        print('Qr added to DB.')

Database model:

class Qrdb(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    data = db.Column(db.String(10000))
    qr_b64s = db.Column(db.String(10000))
    date = db.Column(db.DateTime(timezone=True), default=func.now())
    owner_id = db.Column(db.Integer, db.ForeignKey('user.id'))

snakecharmerb · Accepted Answer · 2021-07-03 08:41:50Z

1

The line

new_qr = Qrdb(qr_b64s=codegen.enq(data), data=data, owner_id=current_user)

should be

new_qr = Qrdb(qr_b64s=codegen.enq(data), data=data, owner_id=current_user.id)

because Qrdb.owner_id is an integer column. If you change the Qrdb model to add a relationship to the User model then you could pass a User instance instead of an integer.

answered Jul 3, 2021 at 8:41

snakecharmerb

57k13 gold badges136 silver badges200 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

init_1_asec Over a year ago

Thanks a lot for the advice. I will make the due changes to the new_qr variable. Do you have any idea why I can't bind the byte string to the qr_b64s column?

snakecharmerb Over a year ago

I don't have any problem binding that column - parameter 2 in the error message refers to the owner/user.

init_1_asec Over a year ago

Can you please explain why that might be? Any way I look at it, to me, parameter 2 seems to be the byte string. I will run the program with the new_qr change once I get home from work. Thanks a lot for your help!

snakecharmerb Over a year ago

I don't know the details of the implementation, but at a guess, it only counts values which are variables (so not the keyword CURRENT_TIMESTAMP, and uses zero-based counting, so the third variable, owner_id is at index 2).

init_1_asec Over a year ago

Many thanks to @snakecharmerb, you were right, the issues I was facing were all due the missing .id . It`s all working now, thanks again!

Collectives™ on Stack Overflow

Flask and SQLAlchemy - error binding parameter

1 Answer 1

5 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Related