librelist archives

« back to archive

Newbie question: how to query a preexisting Sqlite DB with flask-sqlalchemy

Newbie question: how to query a preexisting Sqlite DB with flask-sqlalchemy

From:
David Li
Date:
2015-01-12 @ 21:15
Hi,

Being new to the flask and sqlchemy, I start to play with how to query an
pre-existing database built by sqlite C APIs. Before this I have
successfully created a new DB and a table, added and queried its contents
using flask sqlalchemy extension.

Now I want to see how I can make simple queries about an existing DB. The
DB table is very simple: A primary key is user ID in string format and a
login time also in string format. It has about 10+ entries already.

But my query returns an empty list which is wrong. The code snippet is like
this:


....

app.config['SQLALCHEMY_DATABASE_URI'] =
'sqlite:///'+os.path.join(basedir,'login.db')

#
# Create database
#

db = SQLAlchemy(app)

#
# Creat Table:  login record
#
class LoginRecord(db.Model):
    __tablename__="login record"
    id = db.Column(db.String, primary_key=True)
    loginTime = db.Column(db.String, unique=True)

    def __repr__(self):
        return "<LoginRecord: user ID %s, login time %s>" % (self.id,
self.loginTime)



....


print LoginRecord.query.all()


....

The bottom line is the above code has no problem to handle a new database
table but fails to return anything on a pre-existing table. I wonder if I
missed anything.

Thanks.

Re: [flask] Newbie question: how to query a preexisting Sqlite DB with flask-sqlalchemy

From:
Eric B
Date:
2015-01-12 @ 23:16
Hi david,

I would first suggest confirming the SQLALCHEMY_DATABASE_URI is pointing to
your currently existing database and not creating a new one instead.
On Mon Jan 12 2015 at 4:18:49 PM David Li <dlipubkey@gmail.com> wrote:

> Hi,
>
> Being new to the flask and sqlchemy, I start to play with how to query an
> pre-existing database built by sqlite C APIs. Before this I have
> successfully created a new DB and a table, added and queried its contents
> using flask sqlalchemy extension.
>
> Now I want to see how I can make simple queries about an existing DB. The
> DB table is very simple: A primary key is user ID in string format and a
> login time also in string format. It has about 10+ entries already.
>
> But my query returns an empty list which is wrong. The code snippet is
> like this:
>
>
> ....
>
> app.config['SQLALCHEMY_DATABASE_URI'] =
> 'sqlite:///'+os.path.join(basedir,'login.db')
>
> #
> # Create database
> #
>
> db = SQLAlchemy(app)
>
> #
> # Creat Table:  login reco rd
> #
> class LoginRecord(db.Model):
>     __tablename__="login record"
>     id = db.Column(db.String, primary_key=True)
>     loginTime = db.Column(db.String, unique=True)
>
>     def __repr__(self):
>         return "<LoginRecord: user ID %s, login time %s>" % (self.id,
> self.loginTime)
>
>
>
> ....
>
>
> print LoginRecord.query.all()
>
>
> ....
>
> The bottom line is the above code has no problem to handle a new database
> table but fails to return anything on a pre-existing table. I wonder if I
> missed anything.
>
> Thanks.
>

Re: [flask] Newbie question: how to query a preexisting Sqlite DB with flask-sqlalchemy

From:
David Li
Date:
2015-01-13 @ 02:55
Hi Eric,

Yes, it's pointing to the existing DB file with the same name.

On Mon, Jan 12, 2015 at 3:16 PM, Eric B <neurosnap@gmail.com> wrote:

> Hi david,
>
> I would first suggest confirming the SQLALCHEMY_DATABASE_URI is pointing
> to your currently existing database and not creating a new one instead.
> On Mon Jan 12 2015 at 4:18:49 PM David Li <dlipubkey@gmail.com> wrote:
>
>> Hi,
>>
>> Being new to the flask and sqlchemy, I start to play with how to query an
>> pre-existing database built by sqlite C APIs. Before this I have
>> successfully created a new DB and a table, added and queried its contents
>> using flask sqlalchemy extension.
>>
>> Now I want to see how I can make simple queries about an existing DB. The
>> DB table is very simple: A primary key is user ID in string format and a
>> login time also in stri ng format. It has about 10+ entries already.
>>
>> But my query returns an empty list which is wrong. The code snippet is
>> like this:
>>
>>
>> ....
>>
>> app.config['SQLALCHEMY_DATABASE_URI'] =
>> 'sqlite:///'+os.path.join(basedir,'login.db')
>>
>> #
>> # Create database
>> #
>>
>> db = SQLAlchemy(app)
>>
>> #
>> # Creat Table:  login reco rd
>> #
>> class LoginRecord(db.Model):
>>     __tablename__="login record"
>>     id = db.Column(db.String, primary_key=True)
>>     loginTime = db.Column(db.String, unique=True)
>>
>>     def __repr__(self):
>>         return "<LoginRecord: user ID %s, login time %s>" % (self.id,
>> self.loginTime)
>>
>>
>>
>> ....
>>
>>
>> print LoginRecord.query.all()
>>
>>
>> ....
>>
>> The bottom line is the above code has no problem to handle a new database
>> table but fails to return anything on a pre-existing table. I wonder if I
>> missed anything.
>>
>> Thanks.
>>
>