MySql Database connection with python

Question

I've got an issue trying to connect to a database with python. It compiles without error but it doesn't seem to do anything. I'm not sure if I'm instantiating the class incorrectly or what the issue may be. Could someone point me in the right direction?

import _mysql
import MySQLdb

class Operations:
    def open():
        db=_mysql.connect("127.0.0.1","root","admin","test")
        c=db.cursor()

    #deletes the cursor
    def close(self):
        c.close()

        #verifies the credentials and advances
    def login(self):
        print "Welcome to the online bookstore login!"
        x = raw_input('Please enter your user id. ')
        y = raw_input('Please enter your password. ')

        c.execute("""SELECT userid,password FROM members WHERE userid = %s""", (x,))
        z = c.password

        if y == z:
            member_menu()
        else:
            close()


    def new_user(self):
        print "Welcome to the Online book store new user registration page!"
        print "To begin, please enter your first name: "
        fName = raw_input('Please enter your first name: ')
        lName = raw_input('Please enter your last name: ')
        address = raw_input('Please enter your street address: ')
        city = raw_input('Please enter your city: ')
        state = raw_input('Please enter your state: ')
        zip_code = raw_input('Please enter your zip code: ')
        phone = raw_input('Please enter your phone number: ')
        email = raw_input('Please enter your email: ')
        user_ID = raw_input('Please enter your user id: ')
        password = raw_input('Please enter your password: ')

        c.executemany("""INSERT INTO members(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s,) VALUES (fName, lName, address, city, state, zip_code, phone, email, user_id, password,)""")

        print "Your account has been created. returning to the welcome menu. "
        welcome()

    def welcome(self):
        choice = NONE;

        print "**********************************************************************\n"
        print "***\t\t\t\t\t\t\t\t   ***\n"
        print "***\t\tWelcome to the Online Book Store\t\t   ***\n"
        print "***\t\t\t\t\t\t\t\t   ***\n"
        print "**********************************************************************\n"
        print "1. Member Login\n"
        print "2. New Member Registration\n"
        print "3. Quit\n"
        choice = raw_input('Type in your option: ')

        if choice == 1:
            login()
        elif x == 2:
            new_user()
        else:
            close()


    def member_menu(self):
        x = NONE
        print "**********************************************************************\n"
        print "***\t\t\t\t\t\t\t\t   ***\n"
        print "***\t\t   Welcome to the Online Book Store   \t\t   ***\n"
        print "***\t\t\t    Member Menu   \t\t\t   ***\n"
        print "***\t\t\t\t\t\t\t\t   ***\n"
        print "**********************************************************************\n"
        print "1. Search by Author/Title/Subject\n"
        print "2. View/Edit Shopping Cart\n"
        print "3. Check Order Status\n"
        print "4. One Click Check Out\n"
        print "5. Logout\n"
        print "Type in your option: "
        x = raw_input('Please enter your choice. ')

        if x == 1:
            close_conn(),
        elif  x == 2:
            close_conn(),
        elif  x ==  3:
            close_conn(),
        elif  x ==  4:
            close_conn(),
        else:
            close_conn()

    def main():
        start = Operations()
        print "Opening conenction to database"
        start.welcome

    if __name__ == '__main__':
        main()

Answer 1

Well, there are so many problems with your code, that I'll probably miss some of them anyway.

1. Nothing happens, because your main() function and condition are both parts of the class definition, so all the interpreter sees are actually two imports and a class definition.

2. Let's say we unindented the main() definition and the condition. All that would happen then is creating an instance of Operations (with no special effects, as you have no custom constructor defined) and printing "Opening connection to database" to the screen, because all the last line in main() does is getting a reference to the welcome() method and ignoring it. You need to call it: start.welcome()

3. When you do call it, much more problems will appear. NameErrors will probably come first, as you are using identifiers that do not exist in given scopes. It seems you're new to Python's object model and probably coming from a language with a different approach, like C++. In Python all non-static and non-class instance methods take a reference to the object they're operating on as the first parameter, traditionally called 'self'. If you want to access any of the fields of the object, you need to do this through 'self', they are not visible to the interpreter otherwise. E.g.: you open a connection and keep the cursor as c, which you later reuse in other methods:

   def open():
       # ...
       c=db.cursor()
   # ...
   def login(self):
       # ...
       c.execute("...")
   

   That's incorrect for two reasons:

   • your open() method does not take self as a parameter
   • you're creating c as a local variable in scope of the open() method and then trying to access it in login(), which essentialy results in a "reference before assignment" error.

   In order to be correct, it should be written like this:

   def open(self):
       # ...
       self.c = db.cursor()
   # ...
   def login(self):
       # ...
       self.c.execute("...")
   

   You're making the same mistake in many places. You need to call self.login(), self.new_user(), self.close(), etc.

4. You're using Python 2, at least according to the question's tags and there is one thing you need to remember when declaring classes in Python 2. There exist so called old- and new-style classes and what you want to do is use the new-style ones. Therefore your class must inherit from object:

   class Operations(object):
       # ...
   

   They've finally decided to drop the old-style classes support in Python 3 and there's no need to explicitly inherit from object anymore, but while in Python 2, you need to cope with it.

While there are still some errors or potential errors (what is close_connection()?), I think it's enough for a good start ;). Good luck.