8: Standup Your DB
Last updated
Last updated
By now you should have created your module or blueprint’s folder in your project’s directory and another in your templates. Now, we'll talk about what we should add into the models/ folder
I can create an ERD/UML to describe the data that will be stored in my app.
I can write new relational database objects and successfully stand up my database.
Here's a semi-accurate diagram of Flaskinni's starting objects. Make a copy of this document and share it with your team. Compare your UML's and your wireframes, to make sure all the data you need in your app is stored in a logical hierarchy.
Fipplr allows businesses to log work, manage invoices, and accept payment. This set of tools can be used by freelancers and employers. Fipplr works similarly to many other accounting tools but its simplicity and guided structure protects young entrepreneurs.
https://www.codementor.io/sheena/understanding-sqlalchemy-cheat-sheet-du107lawl
Invoice Count:
Invoice.query.count()
len(Invoice.query.all())
Count how many invoices have been submitted system-wide
Paid/Unpaid:
Invoice.query.filter(Invoice.date_paid != None).all()
Check to see if an invoice has been officially paid. This will be a simple boolean check to see if it has been paid or not. Eventually, you will be able to filter paid and unpaid but to begin, it will just be paid.
Project List:
Project.query.join(Project_User).filter(Project_User.user_id==current_user.id).all()
Print the list of all your current working (live) projects. For employers, this list would include the project name and who is assigned to it. For freelancers, it will list all the projects you are assigned to.
Client/Employer List:
[Function name]
Find the full list of your clients. For employers, each client would be your subcontractors. For freelancers it will list all the clients that have hired you.
Company List:
Company.query.join(Company_User).filter(Company_User.user_id==current_user.id).all()
All companies a user is connected to.
Invoiced Work Check:
Query: [Function name]
Find the full list of work officially invoiced (submitted). If work is not invoiced pull up a separate list. In total there will be two lists, one for the officially invoiced (submitted) work and another for the unofficially invoiced (submitted) work.
Star Sight is a collaborative story editor with a growing array of tools to help writers. You can control access to your work, track your characters, and roll dice to help make decisions. Stories also support rich media so your work can stand out.
Database architecture, the way we build and connect elements in an app, often goes unappreciated by the average user. It’s a complex software engineering challenge that’s unique to every problem being solved. In this example we have some of the following relationships:
Story_Chapter
User_Story: A user has its own attributes (First and last name, email, and ID). A story also has its own attributes (A series it belongs to, and its own ID). In order to connect these two things, a helper table is created and used by pairing the ID of the user and the story it’s associated with.Its an additional object that connects to the two. It told to, it also gives the user permission to give other users access to a particular story.
https://www.codementor.io/sheena/understanding-sqlalchemy-cheat-sheet-du107lawl
https://www.codepowered.com/manuals/SQLAlchemy-0.6.9-doc/html/orm/query.html
Story.query.join(Story_User).filter(User.id==current_user.id).all()
All stories the current user has
Chapter.query.join(Character_Story).filter(Character.id==x.id).all()
All chapters that have a certain character
Series.query.join(Story).join(Story_User).filter(User.id==1).all()
All series that the current user has
User.query.join(Story_User)).all()
All users that have stories linked in the Story_User join table
User.query.except_(User.query.join(Story_User)).all()
All users that don’t have stories
Overhaul Repair is an app that allows users to sign up to request a mail-in phone repair service. This web app will allow users to create accounts to monitor their device status and also add their devices into the account, allowing users to easily create repair orders from previously added devices. The app will also include an admin only finance management system.
https://www.codementor.io/sheena/understanding-sqlalchemy-cheat-sheet-du107lawl
https://stackoverflow.com/questions/4926757/sqlalchemy-query-where-a-column-contains-a-substring
Query: Task.query.filter(Task.date_completed==None)
Explanation: Query searching through all phones that have unsolved open issues
Query: Task.query.order_by(Task.price)
Explanation: Order all tasks by price
Query:
Explanation: All phones that have completed repairs
Query: Device.query.filter(device. odel_number.contains(‘A1’))
Explanation:
Query: Message.query.filter(Message.user_id.contains('Robert Hayek'))
Explanation: All messages sent by employees
Tgene is a website that connects the Gilmour Academy molecular genetics lab to others around the world. My app allows the students and lab instructor, Doctor Edward Turk, to write and upload blog posts, pictures, and research about the current work being done in the lab. Through this shared information, collaboration can occur between labs through a contact feature to get teachers and researchers in touch. Collaboration also includes the sale of plasmids from the Gilmour Academy lab to other schools. Tgene is a project that allows Gilmour Academy high school molecular genetics class to share their research, work, and plasmids to other students and researchers across the country.
Class for my register form that allows me to register as an admin
Query: Moneyplasmid = DBSession.query(Plasmid).filter(plasmid.price)
Explanation: Returns all plasmids that cost money
Query: Freeplasmid= DBSession.query(Plasmid).filter(plasmid.price<1)
Explanation: Returns all plasmids that are free/ link to assistance
Query: PlasmidOrder=DBSession.query(Plasmid).order_by(Plasmid.price)
Explanation: Query all plasmids ordered by price
Query: Plasmidall= DBSession.query(plasmid)
For plasmid in Plasmid:
print plasmid.name
Explanation: Query all available plasmids for sale (free and $$)
plasmidgene = DBSession.query(Book).filter_by(gene_id=str)
Query that shows all the plasmids bought that include a specific gene (sort by genes)
An app to organize announcements for school clubs and teams and to efficiently communicate them to students. Our platform is a way for groups to connect with users. Members can subscribe to groups to stay up to date on what is going on with a group. Groups can post announcements and members can comments and react to them.
https://www.codementor.io/sheena/understanding-sqlalchemy-cheat-sheet-du107lawl
Lists all the groups a certain user is in
Group.query.join(Membership).filter(Membership.user_id == 2).all()
Lists all of the announcement for a particular school base on school id
Announcement.query.filter(Announcement.school_id == 2).all()
Lists all of the groups at a particular school
Group.query.filter(Group.school_id == 2).all()
# Gives all the announcements that a certain user has made
Announcement.query.filter_by(user_id=2).all()
# Gives all of the announcements of a particular group
Announcement.query.filter(Announcement.group_id == 2).all()
Membership.query.filter(Membership.user_id==current_user.id, Membership.role=='Leader').all()
# NOT WORKING---- Gives all of the announcements for a particular user based on their groups
Announcement.query.join(Group.query.join(Membership)).filter(Membership.user_id == 2).all()
.filter(Membership.user_id == 2).all()
Group.query.join(Membership).filter(Announcement.query.filter_by(user_id=2).all()
Assuming you’re responsible enough to use some sort of project management tool like Trello, use the UML or ERD you’ve sketched as a reference to build out your todo list. For almost every table you’ve created in your database, you’ll probably need:
A form built in your forms.py that defines each field a user will fill out
A .html template where that form can be displayed. This form will be used to create a new object as well as editing an existing object
A route and function in your views.py for both the new and edit mode of your object form
A link that will trigger the creation of a new object or editing an existing object
The point here is to think through the absolute minimal displays required to add and manage your data. Then layer in some basic commands next to that table of data.
https://www.python.org/dev/peps/pep-0257/#what-is-a-docstring
Once your models.py
file is complete and your app is running, the first route I recommend building is /superadmin.
We'll build control tables, here's how we load the data for the first one, users.
There's a few ways to trigger a database query. I like to call the object statically so I can explain to students about those terms. You'll more often see db.session.query(Post)
.
More info for the try-hards.