Starting My First Command Line Project
Tags: sql, cli
Published: 2023-08-28 21:21:30

Starting out, I had a few ideas on what to create my project about, one being a weekly planner that you could fill in daily what you wanted to accomplish or plan to do. So, if you ever have problems remembering what you are supposed to do on a particular day, you could look to the CLI Weekly Planner. I also mauled over the idea of creating a little interactive game depending on the chosen path but stirred away from the idea because I couldn't determine what to make the game about. Lastly, I stumbled over a Password Manager project that would store your login information to a particular website. This seemed more straightforward, so that's what I went with. Sweet, moving on!

Setting up the environment to start my project was relatively easy. A file was provided for me that I had to connect to my Git Hub so I could use all the fancy features of git add, git commit, and git push. Next, I had to create some data tables to store all the login details for the user which I used sqlalchemy and alembic to get that started.

```
pipenv install sqlalchemy alembic
alembic init migrations
```
After installing that, I had to configure the alembic to generate my database properly. In the eny.py file in migrations, I updated the target_metadata to equal Base.metadata from models and renamed my database in the alembic.ini file. Lastly, I ran 
`alembic revision --autogenerate -m'intialize'`
to get my database generated and setup my project.

I found two main parts of my project the most difficult: setting up the schema for my database and creating the proper functionality for my command line to interact with my database.

For my models.py, I created two tables named User and Website that contained the user's login information and a data table that stored all the websites that would be validated when entering a new login entry. The Website model is used to check whether the site exists before entering the data into the user's login info for that website. The user can add more websites to the data table through the command line application. 
`
class User(Base):
    __tablename__ = 'users'

    id= Column(Integer(), primary_key=True)
    website_name= Column(String())
    username= Column(String())
    password= Column(String())
    website_id= Column(Integer(), ForeignKey('websites.id'))
    browser_id= Column(Integer(), ForeignKey('browsers.id'))

    def __repr__(self):
        return f'User(id={self.id},' + \
        f'website_name={self.website_name},' + \
        f'username={self.username},' + \
        f'password={self.password})'

class Website(Base):
    __tablename__= 'websites'

    id= Column(Integer(), primary_key=True)
    website= Column(String())
    created= Column(DateTime())

    users= relationship('User', backref=backref('website'))

    def __repr__(self):
        return f'Website(id={self.id},' + \
        f'website={self.website},' + \
        f'created={self.created})'`

I was establishing a one-to-many relationship between the user and the website so that when a login entry was entered, there would be a corresponding website.id on the user table to show what login was "owned" by which website. Using the relationship from SQLalchemy helps to establish this relationship with the help of backref's and ForeignKey. In the above code, I defined a variable 'users' to equal relationship and assigned the 'User' class to the first argument of the relationship and a backref or back reference to be 'website' so that the one-to-many relationship would look back at the website table to fill in the necessary data for the user. Next, in the User class, I defined website_id to equal its column on the table and fill in that data from websites. I chose to use websites.id instead of websites.website_name to fill in the data, but either option works to set up the one-to-many relationship.

For creating a new entry to be added to the data table, I made the functionality in its file called create.py that would take the user's inputs and store them, and set up the session that would interact with the database. This is done in the function called 'add_to_database().'

```
def create_new_entry(session, website_name, username, password, entered_browser):
    valid_website=session.query(Website).filter_by(website=website_name).first()
    valid_browser= session.query(Browser).filter_by(browser_name=entered_browser).first()

if not valid_website:
        print(f'{website_name} is not a valid website')

    if not valid_browser:
        print(f'{entered_browser} is not a valid browser')

    #dictionary
    new_entry={'website_name': website_name, 'username': username, 'password': password, 'website_id':valid_website.id, 'browser_id': valid_browser.id}
    session.add(User(**new_entry))
    session.commit()
    return new_entry

def add_to_database():
    engine=create_engine('sqlite:///projectdatabase.db')
    Session= sessionmaker(bind=engine)
    session= Session()

    entered_browser=input('What browser do you prefer? ')
    website_name=input('Name of website: ')
    username=input('Username: ')
    password=input('Password: ')

    entry=create_new_entry(session, website_name, username, password, entered_browser)

    print("New entry created!")

    session.close()
```
After the session is created and the input stored, I pass the data to the function that will make the new entry called 'create_new_entry.' The first snippet of code is a query session that looks to see if the website_name given by the user is in the database. If the website isn't in the database, then the program responds back that the website isn't valid and will exit out of the command line. However, if the website is already in the database, everything gets bundled into a dictionary. Then, the function calls upon the User class and creates an instance with the dictionary given, and using the unpack method allows for a new entry to be created and added to the data table. After the entry is added, the command line will respond with "New entry created!" from add_to_database and close the session.

To conclude, setting up the models with the right tools and knowing how to use the tools the right way played a big part in setting a one-to-many relationship with the relationship and backref belonging to the owner or what's being referenced. The ForeignKey allows for that table to look back at which table it needs to get the data off of to fill in that portion of the table. Lastly, finding the right data point from a data base using session.query to see if specific data existed before creating the entry as the filter_by method takes in where to look, that being 'website' and what to compare that data to. It then created a new entry with the User class and appended it to the table with session.