1

I made a simple database for movies.

import sqlite3

connection = sqlite3.connect("movies.db")
cursor = connection.cursor()

cursor.execute("""CREATE TABLE IF NOT EXISTS movies(
                   movie_id INTEGER PRIMARY KEY AUTOINCREMENT,
                   Name VARCHAR NOT NULL,
                   Studio VARCHAR)""")

cursor.execute("""CREATE TABLE IF NOT EXISTS info(
                   sid INTEGER PRIMARY KEY AUTOINCREMENT,  
                   year INTEGER,
                   imdb INTEGER)""")


class InDB:


   def add(self):
       cursor.execute("""INSERT INTO movies
                                       (Name, Studio)
                                       VALUES
                                       (?, ?)""", (self.movie_name, self.studio_name))

       connection.commit()

   def add_in(self):
       cursor.execute("""INSERT INTO info
                                               (year, imdb)
                                               VALUES
                                               (?, ?)""", (self.year, self.imdb))
       connection.commit()

   def delete(self):
       cursor.execute("DELETE FROM movies WHERE movie_id = ?", [self.movie_id])

       connection.commit()

       cursor.execute("DELETE FROM info WHERE sid = ?", [self.movie_id])

       connection.commit()

   def update(self):
       cursor.execute("""UPDATE movies SET Name  = ?, Studio = ?
                               WHERE movie_id = ?
                                """, (self.movie_name, self.studio_name, self.movie_id))
       connection.commit()

       cursor.execute("""UPDATE info SET year  = ?, imdb = ?
                                               WHERE sid = ?
                                                """, (self.year, self.imdb, self.movie_id))
       connection.commit()

   def select(self):

       cursor.execute("""SELECT movie_id, Name, Studio, year, imdb FROM movies
                           JOIN info ON movies.movie_id = info.sid""")

       data = cursor.fetchall()

       for index, item in enumerate(data, 1):
           print(f"{index}. Name: {item[1]} Studio: {item[2]} Year: {item[3]} IMDB: {item[4]}")

   def search(self):

       cursor.execute("""SELECT movie_id, Name, Studio,  imdb FROM movies
                                   JOIN info ON movies.movie_id = info.sid
                                   where year = ? """, [self.year])

       data = cursor.fetchall()

       for index, item in enumerate(data, 1):
           print(f"{index}. ID: {item[0]} Name: {item[1]} Studio: {item[2]}  IMDB: {item[3]}")


def menu():
   choice = None

   while choice != 'exit':

       print('\nChoose number: ')
       print('1. Add movie')
       print('2. Delete movie')
       print('3. Update movie')
       print('4. List of movies')
       print("5. Search movie by year: ")
       print("Type 'exit' to exit")

       choice = input("\nChoose number:")
       if choice == '1':
           first = InDB()
           second = InDB()
           first.movie_name = input("Movie: ")
           first.studio_name = input("Studio")
           second.year = int(input('Year: '))
           second.imdb = float(input("IMDB: "))
           second.add_in()
           first.add()

       elif choice == '2':
           number = InDB()
           number.movie_id = int(input("ID: "))
           number.delete()

       elif choice == '3':
           r1 = InDB()

           r1.movie_id = int(input("Enter the movie ID you want to change: "))
           r1.movie_name = input("Enter a new movie name: ")
           r1.studio_name = input("Enter a studio name: ")
           r1.year = int(input("Enter the year: "))
           r1.imdb = float(input("Enter IMDB: "))
           r1.update()

       elif choice == '4':
           k1 = InDB()
           k1.select()

       elif choice == '5':
           s1 = InDB()
           s1.year = int(input("Enter the year: "))
           s1.search()

       else:
           print("\nPlease choose number correctly!\n")


menu()

cursor.execute("""SELECT movie_id, Name, Studio, year, imdb FROM movies
                   JOIN info ON movies.movie_id = info.sid""")

data = cursor.fetchall()

for item in data:
   print(item)

cursor.close()
connection.close()

I want to remove this error for that I need to create __init__ function Pycharm offered me to add this code:

class InDB:

   def __init__(self):
       self.movie_id = None
       self.imdb = None
       self.year = None
       self.movie_name = None
       self.studio_name = None

Now there is no error. Can someone explain how and where should I use __init__ function, I am new to programming but is this code right?

 def __init__(self):
        self.movie_id = None
        self.imdb = None
        self.year = None
        self.movie_name = None
        self.studio_name = None

I have seen code like this can you explain what is difference between this two code?

    def __init__(self, movie_id, movie_name, studio_name, imdb, year):
        self.movie_id = movie_id
        self.movie_name = movie_name
        self.studio_name = studio_name
        self.imdb = imdb
        self.year = year

New code:


connection = sqlite3.connect("movies.db")

cursor = connection.cursor()
connection.execute("PRAGMA foreign_keys = ON")
cursor.execute("""CREATE TABLE IF NOT EXISTS movies(
                    movie_id INTEGER PRIMARY KEY AUTOINCREMENT,
                    Name VARCHAR NOT NULL,
                    Studio VARCHAR
                    
                    )
                     """)

cursor.execute("""CREATE TABLE IF NOT EXISTS info(
                    sid INTEGER  PRIMARY KEY AUTOINCREMENT,
                    year INTEGER,
                    imdb INTEGER,
                    CONSTRAINT fk_sid
                        FOREIGN KEY (sid) references movies(movie_id) ON DELETE CASCADE)""")


class InDB:

    def __init__(self):
        pass

    def add(self, movie_name, studio_name, year, imdb):
        cursor.execute("""INSERT INTO movies
        (Name, Studio)
        VALUES
        (?, ?)""", (movie_name, studio_name))

        cursor.execute("""INSERT INTO info
                        (year, imdb)
                        VALUES
                        (?, ?)""", (year, imdb))
        connection.commit()

    def delete(self, movie_name, studio_name):

        cursor.execute("DELETE FROM movies WHERE Name = ? and  Studio = ?", (movie_name, studio_name))
        connection.commit()

    def update(self, old_movie_name, old_studio_name, new_mn, new_sn, year, imdb, sid):

        cursor.execute("""UPDATE movies SET Name  = ?, Studio = ?
                                WHERE Name = ? and Studio = ?
                                 """, (new_mn, new_sn, old_movie_name, old_studio_name,))

        cursor.execute("""UPDATE info SET year  = ?, imdb = ?
                                           WHERE  sid = ?""", (year, imdb, sid))
        connection.commit()

    def select(self):

        cursor.execute("""SELECT movie_id, Name, Studio, year, imdb FROM movies
                            JOIN info ON movies.movie_id = info.sid""")

        data = cursor.fetchall()

        for index, item in enumerate(data, 1):
            print(f"{index}. Name: {item[1]} Studio: {item[2]} Year: {item[3]} IMDB: {item[4]}")

    def search(self, year):

        cursor.execute("""SELECT movie_id, Name, Studio,  imdb FROM movies
                                    JOIN info ON movies.movie_id = info.sid
                                    where year = ? """, [year])

        data = cursor.fetchall()

        for index, item in enumerate(data, 1):
            print(f"{index}. ID: {item[0]} Name: {item[1]} Studio: {item[2]}  IMDB: {item[3]}")

    def update_search(self, movie_name, studio_name):
        cursor.execute("""SELECT movie_id, Name, Studio,  imdb , year FROM movies
        JOIN info ON movies.movie_id = info.sid
        where Name = ? AND Studio = ? """,
                       (movie_name, studio_name))

        data = cursor.fetchall()
        global int1
        for item in data:
            print(f"ID: {item[0]} Name: {item[1]} Studio: {item[2]}  IMDB: {item[3]} Year: {item[4]}")
            tuple1 = data[0]
            int1 = tuple1[0]


def menu():
    choice = None

    while choice != 'exit':

        print('\nChoose number: ')
        print('1. Add movie')
        print('2. Delete movie')
        print('3. Update movie')
        print('4. List of movies')
        print("5. Search movie by year: ")
        print("Type 'exit' to exit")

        choice = input("\nChoose number:")
        if choice == '1':
            first = InDB()
            movie_name = input("Movie: ")
            studio_name = input("Studio")
            year = int(input('Year: '))
            imdb = float(input("IMDB: "))
            first.add(movie_name, studio_name, year, imdb)

        elif choice == '2':
            delete_movie = InDB()
            movie_name = input("Movie name: ")
            studio_name = input("Studio name: ")
            delete_movie.delete(movie_name, studio_name)

        elif choice == '3':
            r1 = InDB()
            movie_name = input("Enter old movie: ")
            studio_name = input("Enter old studio name: ")
            r1.update_search(movie_name, studio_name)

            print("1. Update: ")
            print("2. Exit: ")

            second_choice = (input("\nChoose number:"))
            if second_choice == "1":
                print("Enter info: ")
                old_movie_name = movie_name
                old_studio_name = studio_name
                sid = int1
                new_mn = input("Enter a new movie nam: ")
                new_sn = input("Enter a studio name: ")
                year = int(input('Year: '))
                imdb = int(input("IMDB: "))
                r1.update(old_movie_name, old_studio_name, new_mn, new_sn, year, imdb, sid)
            else:
                break

        elif choice == '4':
            k1 = InDB()
            k1.select()

        elif choice == '5':
            s1 = InDB()
            year = int(input("Enter the year: "))
            s1.search(year)

        else:
            print("\nPlease choose number correctly!\n")


menu()

cursor.close()
connection.close()
import sqlite3

connection = sqlite3.connect("movies.db")

cursor = connection.cursor()
connection.execute("PRAGMA foreign_keys = ON")
cursor.execute("""CREATE TABLE IF NOT EXISTS movies(
                    movie_id INTEGER PRIMARY KEY AUTOINCREMENT,
                    Name VARCHAR NOT NULL,
                    Studio VARCHAR
                    
                    )
                     """)

cursor.execute("""CREATE TABLE IF NOT EXISTS info(
                    sid INTEGER  PRIMARY KEY AUTOINCREMENT,
                    year INTEGER,
                    imdb INTEGER,
                    CONSTRAINT fk_sid
                        FOREIGN KEY (sid) references movies(movie_id) ON DELETE CASCADE)""")


class InDB:

    def __init__(self):
        pass

    def add(self, movie_name, studio_name, year, imdb):
        cursor.execute("""INSERT INTO movies
        (Name, Studio)
        VALUES
        (?, ?)""", (movie_name, studio_name))

        cursor.execute("""INSERT INTO info
                        (year, imdb)
                        VALUES
                        (?, ?)""", (year, imdb))
        connection.commit()

    def delete(self, movie_name, studio_name):

        cursor.execute("DELETE FROM movies WHERE Name = ? and  Studio = ?", (movie_name, studio_name))
        connection.commit()

    def update(self, old_movie_name, old_studio_name, new_mn, new_sn, year, imdb, sid):

        cursor.execute("""UPDATE movies SET Name  = ?, Studio = ?
                                WHERE Name = ? and Studio = ?
                                 """, (new_mn, new_sn, old_movie_name, old_studio_name,))

        cursor.execute("""UPDATE info SET year  = ?, imdb = ?
                                           WHERE  sid = ?""", (year, imdb, sid))
        connection.commit()

    def select(self):

        cursor.execute("""SELECT movie_id, Name, Studio, year, imdb FROM movies
                            JOIN info ON movies.movie_id = info.sid""")

        data = cursor.fetchall()

        for index, item in enumerate(data, 1):
            print(f"{index}. Name: {item[1]} Studio: {item[2]} Year: {item[3]} IMDB: {item[4]}")

    def search(self, year):

        cursor.execute("""SELECT movie_id, Name, Studio,  imdb FROM movies
                                    JOIN info ON movies.movie_id = info.sid
                                    where year = ? """, [year])

        data = cursor.fetchall()

        for index, item in enumerate(data, 1):
            print(f"{index}. ID: {item[0]} Name: {item[1]} Studio: {item[2]}  IMDB: {item[3]}")

    def update_search(self, movie_name, studio_name):
        cursor.execute("""SELECT movie_id, Name, Studio,  imdb , year FROM movies
        JOIN info ON movies.movie_id = info.sid
        where Name = ? AND Studio = ? """,
                       (movie_name, studio_name))

        data = cursor.fetchall()
        global int1
        for item in data:
            print(f"ID: {item[0]} Name: {item[1]} Studio: {item[2]}  IMDB: {item[3]} Year: {item[4]}")
            tuple1 = data[0]
            int1 = tuple1[0]


def menu():
    choice = None

    while choice != 'exit':

        print('\nChoose number: ')
        print('1. Add movie')
        print('2. Delete movie')
        print('3. Update movie')
        print('4. List of movies')
        print("5. Search movie by year: ")
        print("Type 'exit' to exit")

        choice = input("\nChoose number:")
        if choice == '1':
            first = InDB()
            movie_name = input("Movie: ")
            studio_name = input("Studio")
            year = int(input('Year: '))
            imdb = float(input("IMDB: "))
            first.add(movie_name, studio_name, year, imdb)

        elif choice == '2':
            delete_movie = InDB()
            movie_name = input("Movie name: ")
            studio_name = input("Studio name: ")
            delete_movie.delete(movie_name, studio_name)

        elif choice == '3':
            r1 = InDB()
            movie_name = input("Enter old movie: ")
            studio_name = input("Enter old studio name: ")
            r1.update_search(movie_name, studio_name)

            print("1. Update: ")
            print("2. Exit: ")

            second_choice = (input("\nChoose number:"))
            if second_choice == "1":
                print("Enter info: ")
                old_movie_name = movie_name
                old_studio_name = studio_name
                sid = int1
                new_mn = input("Enter a new movie nam: ")
                new_sn = input("Enter a studio name: ")
                year = int(input('Year: '))
                imdb = int(input("IMDB: "))
                r1.update(old_movie_name, old_studio_name, new_mn, new_sn, year, imdb, sid)
            else:
                break

        elif choice == '4':
            k1 = InDB()
            k1.select()

        elif choice == '5':
            s1 = InDB()
            year = int(input("Enter the year: "))
            s1.search(year)

        else:
            print("\nPlease choose number correctly!\n")


menu()

cursor.close()
connection.close()
Onl1X
  • 31
  • 5
  • One sets all attributes to `None` and the other sets them to the values passed to `__init__`. – mkrieger1 Jun 19 '21 at 11:42
  • 1
    In this case, rather than __init__ you should have been passing the variables in your methods, i.e. rather than`def update(self):` you should have `def update(self, movie_name, studio_name, movie_id)` and use these variables in the methods. This is because you are going to want to have different values for your variables `movie_id, movie_name, studio_name, imdb, year, etc.` each time you call your methods. – DarrylG Jun 19 '21 at 11:42
  • Does this answer your question? [Why do we use \_\_init\_\_ in Python classes?](https://stackoverflow.com/questions/8609153/why-do-we-use-init-in-python-classes) – mkrieger1 Jun 19 '21 at 11:44
  • 2
    When you write programs, design data structures and the operations on data before thinking about code. OOP helps you do this. If you are creating objects that don't change, then it is natural to pass all the values to init. – stark Jun 19 '21 at 11:49
  • How does the user know the movie_id that must be specified to update or delete a movie? – DarrylG Jun 19 '21 at 12:27
  • @DarrylG This isn't a real project I am new at programming. Just doing some exercise. The user does not know ID. This isn't important for this time. This code is just an exercise for practice. I changed the code and now there is no warnings – Onl1X Jun 19 '21 at 12:54
  • @Onl1X--I realize its a learning exercise so this is just for practice. So, after a user does choice (1) i.e. adds a movie, how would they delete it? How do they know the movie ID? – DarrylG Jun 19 '21 at 13:06
  • @Onl1X--yes, but how does the user know the ID? It's not provided by choice 4--list of movies. It is only provided when you do a search of movies by year. I'm just sugggesting that it should also be provided with list of movies. – DarrylG Jun 19 '21 at 13:24
  • @DarrylG I have updated the code. Now user can change data by movie name and studio. If you have any advice regarding the code tell me – Onl1X Jun 20 '21 at 13:58
  • @Onl1X--added a response to show some changes that could be made. – DarrylG Jun 20 '21 at 20:24

1 Answers1

1
import sqlite3


class InDB:
    def __init__(self, name_database):
        # Database Setup
        self.connection = sqlite3.connect(name_database)

        self.cursor = self.connection.cursor()
        self.connection.execute("PRAGMA foreign_keys = ON")

        self.cursor.execute("""CREATE TABLE IF NOT EXISTS movies(
                           movie_id INTEGER PRIMARY KEY AUTOINCREMENT,
                           Name VARCHAR NOT NULL,
                           Studio VARCHAR)""")

        self.cursor.execute("""CREATE TABLE IF NOT EXISTS info(
                           sid INTEGER PRIMARY KEY AUTOINCREMENT,  
                           year INTEGER,
                           imdb INTEGER,
                           CONSTRAINT fk_sid
                                FOREIGN KEY (sid) references movies(movie_id) ON DELETE CASCADE)""")

    def __del__(self):
        # Deconstructor
        print('Done...current database contents')
        self.select()  # Show database contents

        self.cursor.close()
        self.connection.close()

    def add(self, movie_name, studio_name):
        self.cursor.execute("""INSERT INTO movies
                                       (Name, Studio)
                                       VALUES
                                       (?, ?)""", (movie_name, studio_name))

        self.connection.commit()

    def add_in(self, year, imdb):
        self.cursor.execute("""INSERT INTO info
                                               (year, imdb)
                                               VALUES
                                               (?, ?)""", (year, imdb))
        self.connection.commit()

    def delete(self, movie_name, studio_name):
        self.cursor.execute("DELETE FROM movies WHERE Name = ? and  Studio = ?", (movie_name, studio_name))
        self.connection.commit()

    def update(self, movie_name, studio_name, movie_id, year, imdb):
        self.cursor.execute("""UPDATE movies SET Name  = ?, Studio = ?
                               WHERE movie_id = ?
                                """, (movie_name, studio_name, movie_id))
        self.connection.commit()

        self.cursor.execute("""UPDATE info SET year  = ?, imdb = ?
                                               WHERE sid = ?
                                                """, (year, imdb, movie_id))
        self.connection.commit()

    def select(self):

        self.cursor.execute("""SELECT movie_id, Name, Studio, year, imdb FROM movies
                           JOIN info ON movies.movie_id = info.sid""")

        data = self.cursor.fetchall()

        for index, item in enumerate(data, 1):
            print(f"{index}. ID: {item[0]} Name: {item[1]} Studio: {item[2]} Year: {item[3]} IMDB: {item[4]}")

    def search(self, year):

        self.cursor.execute("""SELECT movie_id, Name, Studio,  imdb FROM movies
                                   JOIN info ON movies.movie_id = info.sid
                                   where year = ? """, [year])

        data = self.cursor.fetchall()

        for index, item in enumerate(data, 1):
            print(f"{index}. ID: {item[0]} Name: {item[1]} Studio: {item[2]}  IMDB: {item[3]}")

    def search_by_name(self, movie_name, studio_name):

        self.cursor.execute("""SELECT movie_id, Name, Studio,  imdb FROM movies
                                   JOIN info ON movies.movie_id = info.sid
                                   where Name = ? AND Studio = ?""", (movie_name, studio_name))

        data = self.cursor.fetchall()

        for item in data:
            print(f"ID: {item[0]} Name: {item[1]} Studio: {item[2]}  IMDB: {item[3]}")


def menu():
    choice = None

    db_api = InDB('movies.db')  # Object instance to handle interactions with database

    while choice != 'exit':
        print('''
            Choose number: 
            1. Add movie')
            2. Delete movie')
            3. Update movie')
            4. List of movies')
            5. Search movie by year
            or Type exit if done''')

        choice = input("\nChoose number (or Type exit if done):")
        if choice == '1':
            movie_name = input("Movie: ")
            studio_name = input("Studio")
            year = int(input('Year: '))
            imdb = float(input("IMDB: "))
            db_api.add(movie_name, studio_name)
            db_api.add_in(year, imdb)


        elif choice == '2':
            movie_name = input("Movie name: ")
            studio_name = input("Studio name: ")
            db_api.delete(movie_name, studio_name)


        elif choice == '3':

            movie_name = input("Enter old movie: ")

            studio_name = input("Enter old studio name: ")

            db_api.search_by_name(movie_name, studio_name)

            print("1. Update: ")

            print("2. Exit: ")

            second_choice = (input("\nChoose number:"))

            if second_choice == "1":
                movie_id = int(input("Enter the movie ID you want to change: "))
                movie_name = input("Enter a new movie name: ")
                studio_name = input("Enter a studio name: ")
                year = int(input("Enter the year: "))
                imdb = float(input("Enter IMDB: "))
                db_api.update(movie_name, studio_name, movie_id, year, imdb )
            else:

                break

        elif choice == '4':
            db_api.select()

        elif choice == '5':
            year = int(input("Enter the year: "))
            db_api.search(year)

        elif choice == 'exit':
            return

        else:
            print("\nPlease choose number correctly!\n")


menu()
Onl1X
  • 31
  • 5
  • @Onl1X--okay,looks great--I upvoted your question and answer. – DarrylG Jun 21 '21 at 15:24
  • @Onl1X--Only concern with using movie name and studio as an identifier is movies with the same name i.e. [11 movies with the same name that are totally different](https://www.digitalspy.com/movies/a850564/movies-with-same-name/). Possibly the studio is different in all the cases. – DarrylG Jun 21 '21 at 15:30