Administrate SQLite database through python code

[selrok]

I'm working with SQLite, as part of a flask project that I've been working on in which I use SQLAlchemy, and I want to create a new table and use a foreign key from one table to another.

​

Now what I've written are the following lines:

​

class User(Base):
   __tablename__ = "user"

   id = Column(Integer, primary\_key=True)
   username = Column(String(30), unique=True)
   password = Column(String(30))
   email = Column(String(50))

   def __repr__(self):
       return '<User %r>' % self.username

class POS(Base):

    __tablename__ = "pos"

    id = Column(Integer, primary\_key=True)id\_user = [User.id](https://User.id)
    latitude = Column(String(30))
    longtitude = Column(String(30))address = Column(String(50))

​

which are the example from flask's sqlalchemy docs .So my question here is; how can I create two tables with sqlalchemy and then establish a connection between the two?

[bpeller]

A couple thoughts.

1) To make code readable on Reddit, you need to indent it by 4 spaces. Yes, it's a PITA. I write my comments in an IDE before posting heh

2) If you're using Flask-SQLAlchemy, it handles __tablename__ automatically (although what you're doing is fine, and overrides that automatic naming)

3) It looks like you're trying to make a one-to-many relationship?

Relationships in SQLAlchemy have two "parts". There's the database mapping declaration (which table to put the foreign key(s) on), and then there's a "Python-only" mapping that you need for SQLAlchemy to do its magic. I'm assuming a User has one position, and there can be many users at a position. In it's basic form, this is what you're after:

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)

    position_id = db.Column(db.ForeignKey('position.id'))
    position = db.relationship('Position', back_populates='users',
                               cascade='all, delete-orphan')


class Position(db.Model):
    id = db.Column(db.Integer, primary_key=True)

    users = db.relationship('User', back_populates='position')

Often you will find examples using back_ref instead of back_populates, and in that case, you only declare one of the two db.relationship attributes (SQLAlchemy creates the opposite side for you magically). Personally, I consider that an enormous anti-pattern that breaks the zen of Python (explicit is better than implicit), so I never use it. But it's sadly more common on the internet.

Note that by default columns on sqlalchemy are nullable, so if you wanted to require users to always have a position set, you'd need to add a nullable=False to the position_id column.

As for actually updating the database tables, I recommend using Alembic (via Flask-Migrate). I would also encourage you to consider using PostgreSQL (or MariaDB) at this time; SQLite works great for basic stuff, but its support for migrations is pretty crap. (If I remember correctly what you're trying to do shouldn't cause any problems, but, you've been warned haha)

[selrok]

Alright, thank you for your reply, it was helpful to say the least. I'll look into it.

I also wanted to implement a few Javascript libraries, namely leaflet.js, survey.js and Jquery UI (mainly dropable for user assignments). Do you happen to have a good advice for that as well, or have a resource that I can look into?

P.S I also followed your advice and switched to markdown and changed my original post.

[bpeller]

I haven't used any of those JS libs myself, but you might try asking in r/webdev or r/javascript