CODEWITHSUNDEEP
pythonflaskflask-sqlalchemy
Michael Woodham
Michael Woodham

Reputation: 21

SQLAlchemy Add A Table To An Already Existing Database

I'm attempting to add a table to an already existing Database in SQLAlchemy. My app.db is already compiled and populated with data. I'm just trying to add a new table to the database titled song_points. (The Database tracks songs over time)

My Models:

from app import db

class songs (db.Model):
    SongID = db.Column(db.Integer,primary_key=True, autoincrement=False)
    Artist = db.Column(db.String(120), primary_key=True)
    Title = db.Column(db.String(120), primary_key = True)
    def __repr__(self):
            return "%s, %s, %d\n" %(self.Artist, self.Title, self.SongID)

class points(db.Model):
    SongID = db.Column(db.Integer, db.ForeignKey("songs.SongID"), nullable= False, primary_key=True)
    Genre = db.Column(db.String(25), primary_key=True)
    Points = db.Column(db.Integer)
    Date = db.Column(db.Date, primary_key=True)
    Rank = db.Column(db.Integer)
    def __repr__self(self):
            return "%d, %s, %d\n" %(self.SongID, self.Genre, self.Points)
class artist_points(db.Model):
    Artist = db.Column(db.String(120), primary_key=True)
    Points = db.Column(db.Integer)
    def __repr__self(self):
        return "%s, %d\n" %(self.Artist, self.Points)

class graphs(db.Model): 
    SongID = db.Column(db.String(120), db.ForeignKey("songs.SongID"), nullable = False, primary_key = True)
    URL = db.Column(db.String(120), primary_key=True)
    def __repr__self(self):
        return "%s, %s\n" %(self.SongID, self.URL)

And here was my db creation script: 

from migrate.versioning import api
from config import SQLALCHEMY_DATABASE_URI
from config import SQLALCHEMY_MIGRATE_REPO
from app import db
import os.path
db.create_all()
if not os.path.exists(SQLALCHEMY_MIGRATE_REPO):
    api.create(SQLALCHEMY_MIGRATE_REPO, 'database repository')
    api.version_control(SQLALCHEMY_DATABASE_URI, SQLALCHEMY_MIGRATE_REPO)
else:
    api.version_control(SQLALCHEMY_DATABASE_URI, SQLALCHEMY_MIGRATE_REPO, api.version(SQLALCHEMY_MIGRATE_REPO))

This is the table I'm attempting to add:

class song_points(db.Model):
    SongID = db.Column(db.Integer,db.ForeignKey("songs.SongID"), nullable = False, primary_key = True)
    Genre = db.Column(db.String(25), db.ForeignKey("points.Genre"), nullable = False, primary_key = True)
    Points = db.Column(db.Integer)

Upvotes: 1

Views: 10903

Answers (3)

Bharath Gowda
Bharath Gowda

Reputation: 47

Just open terminal and type the following command:

from app import db
db.create_all()
exit()

Now open the database viewer and drop your database file. You must see the new table along with all old tables and their data.

Upvotes: 2

ichbinblau
ichbinblau

Reputation: 4809

Please follow the steps described in the Flask-Mega.

Firstly, add you new table definition in models.py

class song_points(db.Model):
    SongID = db.Column(db.Integer,db.ForeignKey("songs.SongID"), nullable = False, primary_key = True)
    Genre = db.Column(db.String(25), db.ForeignKey("points.Genre"), nullable = False, primary_key = True)
    Points = db.Column(db.Integer)

Secondly, add new db_migrate.py file for db upgrade:

import imp
from migrate.versioning import api
from app import db
from config import SQLALCHEMY_DATABASE_URI
from config import SQLALCHEMY_MIGRATE_REPO
v = api.db_version(SQLALCHEMY_DATABASE_URI, SQLALCHEMY_MIGRATE_REPO)
migration = SQLALCHEMY_MIGRATE_REPO + ('/versions/%03d_migration.py' % (v+1))
tmp_module = imp.new_module('old_model')
old_model = api.create_model(SQLALCHEMY_DATABASE_URI, SQLALCHEMY_MIGRATE_REPO)
exec(old_model, tmp_module.__dict__)
script = api.make_update_script_for_model(SQLALCHEMY_DATABASE_URI, SQLALCHEMY_MIGRATE_REPO, tmp_module.meta, db.metadata)
open(migration, "wt").write(script)
api.upgrade(SQLALCHEMY_DATABASE_URI, SQLALCHEMY_MIGRATE_REPO)
v = api.db_version(SQLALCHEMY_DATABASE_URI, SQLALCHEMY_MIGRATE_REPO)
print('New migration saved as ' + migration)
print('Current database version: ' + str(v))

After that, run python db_migrate.py to reflect all the changes in models.py. I made the code running locally. Let me know if you still cannot make it work.

BTW, there should be one correction in your code. SONGID should be integer type.

class graphs(db.Model): 
    SongID = db.Column(db.String(120), db.ForeignKey("songs.SongID"), nullable = False, primary_key = True)

To: 

class graphs(db.Model): 
    SongID = db.Column(db.Integer, db.ForeignKey("songs.SongID"), nullable = False, primary_key = True)

Upvotes: 2

Sergey Gornostaev
Sergey Gornostaev

Reputation: 7787

song_points.__table__.create(db.session.bind)

Upvotes: 4

Related Questions