3

I have an flask app, using flask-slqalchemy to query a mysql database

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql://root:password@localhost/abc'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
db = SQLAlchemy(app)

there is a table "users" in "abc" database and it is already populated with several hundred rows. Now i need to import this existing table, rather than first defining it with db.Model how do i call the table? if i do this

from sqlalchemy import Table
USERS = Table('users', db.metadata,autoload=True, autoload_with=db.engine)

then i am not able to make a query like

USERS.query.filter_by(done=1).with_entities(USERS.name,USERS.country).paginate(page, 15, False)

it generates an error

AttributeError: 'Table' object has no attribute 'query'

because this is sqlchemy command, not flask-sqlchemy, i dont fully understand this.

I have to first define the table USERS like i am creating it for the first time :

class USERS(db.Model):
 __tablename__ = 'users'
 id = db.Column(db.Integer, primary_key=True, autoincrement=True)
 name = db.Column(db.VARCHAR(500))
 country = db.Column(db.VARCHAR(50))
 def __init__(self, id, name, country):
 self.id = id
 self.name = name
 self.country = country
 def __repr__(self):
 return self.id

only then i am able to use USERS to query the database through flask-sqlalchemy

How do i access the an existing table users using flask-sqlchemy in an flask app?

asked Dec 3, 2018 at 10:14

1 Answer 1

1

In sqlalchemy you should query table(s) with session if you want to query Table(). Because 'Table' object has no attribute 'query'. And you do not need to create table if it has existed, just use it. sqlalchemy existing database query

from sqlalchemy import Table, Column, String, create_engine, MetaData
from sqlalchemy.orm import sessionmaker
engine = create_engine()
metadata = MetaData()
test_ = Table('test', metadata,
 Column('msg', String, primary_key=True),
 Column('msg_', String)
)
Session = sessionmaker(bind=engine)
session = Session()
print(session.query(test_).filter_by(msg_ = "test").with_entities("msg","msg_").one())
# ('t', 'test')

In flask_sqlalchemy, it almost same as sqlalchemy did.

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = ""
db = SQLAlchemy(app)
class test(db.Model):
 msg = db.Column(db.String, primary_key=True)
 msg_ = db.Column(db.String)
 def __init__(self, msg, msg_):
 self.msg = msg
 self.msg_ = msg_
 def __repr__(self):
 return "msg: {} msg_: {}".format(self.msg,self.msg_)
result = test.query.filter_by(msg_="test").one()
print(result)
print(result.msg,result.msg_)
'''
msg: t msg_: test
t test
'''
answered Dec 10, 2018 at 8:23
2
  • 1
    i am using flask_sqlalchemy, so i have to define the table and list all its columns and rather than using any binding statement, it will bind it own its own? Commented Dec 15, 2018 at 15:59
  • Yes, if you are using class-base. You can try to list part of columns then the error will be raised. And __init__ is not necessary unless you want to override the constructor(ref). Commented Dec 16, 2018 at 2:01

Your Answer

Draft saved
Draft discarded

Sign up or log in

Sign up using Google
Sign up using Email and Password

Post as a guest

Required, but never shown

Post as a guest

Required, but never shown

By clicking "Post Your Answer", you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.