sqlalchemy insert data does not work

Question

In models.py I have define:

class slidephoto(db.Model):
    __tablename__ = 'slide_photo'
    id = db.Column(db.Integer, primary_key=True)
    uid = db.Column(db.Integer, nullable=False)
    photo = db.Column(db.String(collation='utf8_bin'), nullable=False)

    def __init__(self, uid, photo):
        self.uid = uid
        self.photo = photo

    def __repr__(self):
        return "{'photo': " + str(self.photo) + "}"

I select data like this (for example):

@app.route('/index/')
def index():
    user_photo = slidephoto.query.filter_by(uid=5).all()

Now I want to know how to insert data. I tried this:

@app.route('/insert/')
def insert():
    act = slidephoto.query.insert().execute(uid='2016', photo='niloofar.jpg')
    return 'done'

But it does not do what I need. What should I do?

I have read and tested other answers and solutions, but none of them was useful for my script.

================ update ================

I don't no if it helps... but here is all imports and configs in app.py:

import os, sys
from niloofar import *
from flask import Flask, request, url_for, render_template, make_response, redirect
from flask_sqlalchemy import SQLAlchemy
from werkzeug.utils import secure_filename

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql://myusername:mypassword@localhost/mydbname'
db = SQLAlchemy(app)
app.config['UPLOAD_FOLDER'] = UPLOAD_FOLDER

Oh, you're actually not even inserting correctly. What you have would give you an error instead of doing something else that you didn't expect. Try db.session.add(slidephoto(uid='2016', photo='niloofar.jpg')) (and then committing, of course). — univerio
– univerio, Commented Aug 17, 2016 at 18:30
@univerio, I faced with this error: AttributeError: 'function' object has no attribute 'session'. — Andishe
– Andishe, Commented Aug 18, 2016 at 5:06
It looks like you have shadowed your db import with a function named db. — univerio
– univerio, Commented Aug 18, 2016 at 18:11

Jalal · Accepted Answer · 2016-08-18 14:14:18Z

2

I hope that my answer will help you solving the problem.

from sqlalchemy import create_engine, MetaData, Table, insert
# I have tested this using my local postgres db.
engine = create_engine('postgresql://localhost/db', convert_unicode=True)
metadata = MetaData(bind=engine)
con = engine.connect()
act = insert(slidephoto).values(uid='2016', photo='niloofer.jpg')
con.execute(act)

edited Aug 18, 2016 at 14:14

answered Aug 18, 2016 at 13:09

Jalal

4174 silver badges5 bronze badges

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

2 Comments

Jalal Over a year ago

just to add that when I tested this, I had to change the model column attribute by defining the length of the string like this: photo = db.Column(db.String(30), nullable=False)

alexyichu Over a year ago

This solution helped a lot because I became aware I forgot to call execute on the sql actions...

sting_roc · Accepted Answer · 2016-08-18 09:36:17Z

I write a simple demo that do insert work, you can take it as a reference:

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

db = SQLAlchemy()


class FirstTest(db.Model):
    __tablename__ = "first_test"

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String, nullable=False)

    def __init__(self, name):
        self.name = name


# Fill your db info here
mysql_info = {
    "user": "",
    "pwd": "",
    "host": "",
    "port": 3306,
    "db": "",
}

app = Flask(__name__)

app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = True
# Here I use pymysql
app.config["SQLALCHEMY_DATABASE_URI"] = "mysql+pymysql://{0}:{1}@{2}:{3}/{4}".format(
    mysql_info["user"], mysql_info["pwd"], mysql_info["host"],
    mysql_info["port"], mysql_info["db"])

db.__init__(app)


@app.route("/")
def hello():
    return "Hello World!"

if __name__ == "__main__":
    with app.test_request_context("/"):
        record = FirstTest("test")
        db.session.add(record)
        db.session.commit()

vlado · Accepted Answer · 2016-08-18 09:40:45Z

-1

You should use query as a method. Like 'query()'

answered Aug 18, 2016 at 9:40

vlado

94 bronze badges

Collectives™ on Stack Overflow

sqlalchemy insert data does not work

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related