Thread: Python & MySQL

    #16
  1. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2004
    Posts
    330
    Rep Power
    15
    Hi Guys,

    Thanks for all your posts. It seems that I'm getting closer to get this going. mysqldb is installed in lib/site-packages now. I can type "import _mysql" in the python shell and it doesn't give any error. But when I type "import mysqldb" it again says no such module.
    I was wondering which sequence of commands you guys use in the beginning to connect to mysql and select a table for example. Do you type "import mysqldb" on the prompt line? I got this statement from one of the online tutorials.

    Thanks alot.
  2. #17
  3. Hello World :)
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2003
    Location
    Hull, UK
    Posts
    2,537
    Rep Power
    69
    Imports in Python are case sensitive, so for example "foo" isn't the same as "Foo". With this in mind, what you want to be typing is "import MySQLdb" rather than "import mysqldb". Give that a go and let me know if you have any problems .

    You might want to check out the Article linked to at the top of this thread, it is a very good one and should explain all you need to know about connecting to MySQL.

    I hope tall goes well ,

    Mark.
    programming language development: www.netytan.com Hula

  4. #18
  5. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2004
    Posts
    330
    Rep Power
    15
    Hi Mark,

    Thanks alot man, you have been very helpful. Yes the problem was the case sensitiveness. With correct cases it worked.
    Now the only problem is the connection part, according to the tutorial that you mentioned we have:

    Code:
    db= MySQLdb.connect(host="localhost", user="joe", passwd="secret", db="db56a")
    Usually when I open my MySQL window, it only asks me for my password, and then I can access the databases and tables.
    I'm not sure here about the "localhost" and "user". Is there a mysql command that shows the user name? Also when I type in the password it's not converted to starts which is usually the case! and I assume I have to type the name of my database instead of "db56a" here.

    Hofully I won't be bothering you much longer with my questions!

    Thanks alot,
    Sam
  6. #19
  7. Hello World :)
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2003
    Location
    Hull, UK
    Posts
    2,537
    Rep Power
    69
    You're not bothering anyone at all Sam . Don't worry about it! Anyway, unless something strange is going on you can leave the host as localhost (assuming that MySQL on the computer running the program).

    You should be able to get the username from MySQLadmin but by default I think it's root; if you didn't set one when installing/configuring MySQL. I can't give you too much instruction here since MySQLadmin isn't available for the Mac platform.

    The password you know: but no it's not converted to ****** in the Python program so you do need to be careful about who you give the code too! If you post your program here then replace the password with something like "password" the same goes for username but to a lesser degree .

    Lastly, yes: replace db with the name of the database you want to connect to.

    That should be it,

    Mark.
    programming language development: www.netytan.com Hula

  8. #20
  9. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2004
    Posts
    330
    Rep Power
    15
    Thanks alot for your help.
    I am enclosing the problem that I am having, it seems that it might be a permission problem or something. It says the access is denied. Do I have to change any parameters you think?

    Code:
    >>> import MySQLdb
    >>> db=MySQLdb.connect(host="localhost", user="root", passwd="******", db="tap")
    
    Traceback (most recent call last):
      File "<pyshell#1>", line 1, in -toplevel-
        db=MySQLdb.connect(host="localhost", user="root", passwd="*******", db="tap")
      File "C:\Python23\Lib\site-packages\MySQLdb\__init__.py", line 64, in Connect
        return apply(Connection, args, kwargs)
      File "C:\Python23\Lib\site-packages\MySQLdb\connections.py", line 116, in __init__
        self._make_connection(args, kwargs2)
      File "C:\Python23\Lib\site-packages\MySQLdb\connections.py", line 41, in _make_connection
        apply(super(ConnectionBase, self).__init__, args, kwargs)
    OperationalError: (1045, "Access denied for user 'root'@'localhost' (using password: YES)")
    Thanks for your help,
    Sam
  10. #21
  11. (retired)
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 2003
    Location
    The Laboratory
    Posts
    10,101
    Rep Power
    0
    Yep.

    1) Bad idea to use root for general MySQL access
    2) Check the password. Does MySQL's root user have a password?

    How do you connect to MySQL in the terminal?
  12. #22
  13. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2004
    Posts
    330
    Rep Power
    15
    Basically when I was installing MySQL I chose the default values. So now I don't even know if "root" is the correct user. I am using root since it was in one of the tutorials and also suggested here in postings.
    The way I connect to MySQL is that I open the "MySQL command line client" which is a MS_DOS looking black window on my desktop. Once it opens it only asks for my password, nothing else, and then it connects me to MySQL.
  14. #23
  15. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2004
    Posts
    330
    Rep Power
    15
    Ok in the same window that I mentioned, I just typed a wrong password, and then it gives me an error stating that access is denied for 'root'@'localhost'.
    So now at least I know the root and 'localhost' are correct in

    Code:
    db=MySQLdb.connect(host="localhost", user="root", passwd="******", db="tap")
    statement. I type in the correct password and "tap" is the name of the database I want to access, what else could be wrong then?!!!
  16. #24
  17. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2004
    Posts
    330
    Rep Power
    15
    Sorry for consecutive postings! I just thought that maybe Python is able to detect where MySQLdb is (in site-packages) but it can't find where the actual MySQL is. Could that be the case?
    Currently Python is in C:\Python23 and MySQL is in C:\Program Files\MySQL
  18. #25
  19. (retired)
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 2003
    Location
    The Laboratory
    Posts
    10,101
    Rep Power
    0
    Ok. Root is the MySQL superuser - it's not a good idea to use it for anything except user admin and maintenance. I recommend you create another user like 'mysql'.

    If you've just installed MySQL, then you probably have all the default accounts. So - you should be able to connect as 'root' and no password (try setting the password variable to ''). You can probably actually connect as ANYTHING with no password at the moment.

    These accounts (except root) will have very low permissions but should be able to do things like SELECT queries.

    I would recommend that you create another user for your application though.

    If this doesn't work, we need to check if MySQL is running. The easiest way to do this is to open a terminal or command prompt and:
    Code:
    cd c:\Program Files\MySQL\bin\
    mysql -u mysql
    (or)
    mysql -u root
    You should get a mysql prompt.

    If you don't then look inside c:\Program Files\MySQL\data for a file ending in .err - and read this.
  20. #26
  21. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2004
    Posts
    330
    Rep Power
    15
    I used the same statement at my work and it worked fine. I use a Windows 2000 there. So the problem should be a permission or something on my computer at home, which is a Windows XP pro. I turned the firewall off and on but didn't make a difference. Any suggestions?
  22. #27
  23. (retired)
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 2003
    Location
    The Laboratory
    Posts
    10,101
    Rep Power
    0
    Yeah. Check that you've spelt the username, pw and dbname correctly. And check if the username that you're using has enough permissions to do things:

    Code:
    SHOW GRANTS FOR username@localhost;
    You should see something like GRANT SELECT, INSERT, UPDATE, DELETE ON tablename.. etc.

    Localhost connects shouldn't be affected by the firewall. So I don't think that's the problem.

    Finally - I had problems with MySQLdb not working out where the socket is. The solution is here

    --Simon
  24. #28
  25. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2004
    Posts
    330
    Rep Power
    15
    Thanks alot for your help. My problem is now resolved, please don't ask what I was doing wrong, it's too embarassing!

    I am going to go with your advice of not using the "root" for general tasks. Could you please give me the command that you use to create a new user.

    Thanks alot,
  26. #29
  27. (retired)
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 2003
    Location
    The Laboratory
    Posts
    10,101
    Rep Power
    0
    Sure:

    GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO 'username'@'localhost';
    FLUSH PRIVILEGES;

    - you can change the *.* to a specific db name if you want the user to only have access to that db. You can get pretty fine-grain control - the command above allows the user to do SELECT, INSERT, UPDATE and DELETE queries only.

    You can also put things in there like:
    CREATE (to be able to create databases - not really necessary, I'd use root for this)
    RELOAD (reloads the server - don't give this permission out lightly)
    FILE (allow user to load data from files - this is a security hazard, but not too bad).

    To add all permissions (which is just another root account) you can GRANT ALL ON *.* ... etc.

    Update the root password while you're at it:
    SET PASSWORD FOR root@localhost = PASSWORD('secret');

    Also a good idea to remove all other default accounts (especially ANYTHING which can connect from '%' -- anywhere).

    DROP USER username

    (remove the account '' -empty username if it's there)

    You can also do it like this:
    DELETE FROM mysql.user WHERE User = 'user' AND Host='Hostname';

    Finally, after EVERY permission change, you'll need to run 'FLUSH PRIVILEGES' to reload the grant tables.

    --Simon
  28. #30
  29. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2004
    Location
    Bimingham,AL
    Posts
    68
    Rep Power
    11
    kind of out of context here , but have you guys taken a look at gadfly its cool

    http://starship.python.net/crew/lornew/

IMN logo majestic logo threadwatch logo seochat tools logo