Connecting to a database with MySQLdb in python

Question

So Basically I was just trying to test this out:

import MySQLdb

conn = MySQLdb.connect(host="myhost.com",    user="myusername", passwd="mypassword", db="nameofmydatabase")

query = "INSERT INTO nameofmydatabase (columntitle) values ('sampletext')"
x = conn.cursor()
x.execute(query)
row = x.fetchall()

So this is the error that I got. I changed my actual info in the error but I was wondering how to fix this.

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "write2MySQL_test.py", line 3, in <module>
    conn = MySQLdb.connect(host="myhost.com", user="myusername", passwd="mypassword", db="nameofmydatabase")
  File "/usr/local/lib/python2.7/site-packages/MySQLdb/__init__.py", line 81, in Connect
    return Connection(*args, **kwargs)
  File "/usr/local/lib/python2.7/site-packages/MySQLdb/connections.py", line 193, in __init__
    super(Connection, self).__init__(*args, **kwargs2)
_mysql_exceptions.OperationalError: (2003, "Can't connect to MySQL server on  'myhost.com' (60)")

is your mysql daemon accessible from the outside world directly if on a host, or is it rightly hidden — Drew
– Drew, Commented Nov 3, 2015 at 18:01
Are you sure you are attempting to connect to the correct port? — CleverLikeAnOx
– CleverLikeAnOx, Commented Nov 3, 2015 at 18:09

Illusionist · Accepted Answer · 2015-11-03 19:19:41Z

2

Have you tried connecting to your MySQL database manually? (Using PhpMyAdmin or MySQL query browser etc) Are you able to reach the database from teh machine you are running the code on ? Ping the ip to see if there is a firewall.

Also, I am not sure how you set it up, is myhost.com pointing to something? Did you mean localhost?

Edit-

Try using your port like this

db = MySQLdb.connect(
    host = 'localhost', 
    user = 'root', 
    passwd = '', 
    db = 'dbname', 
    port = 3306)

I am not sure if your database is called "nameofmydatabase" so please make sure you fill the fields appropriately.

edited Nov 3, 2015 at 19:19

answered Nov 3, 2015 at 17:55

Illusionist

5,54912 gold badges49 silver badges82 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

sTr8_Struggin Over a year ago

I can connect to my database using my desktop application Sequel Pro

sTr8_Struggin Over a year ago

I type in the exact same credentials that i have in the python program

sTr8_Struggin Over a year ago

In addition I also specify the port 3306. Could it be that I need to specify the port in the python program

Lavanya Pant · Accepted Answer · 2016-05-25 20:23:17Z

Host Name is wrong you can use "localhost" in place of "myhost.com". I have created a sample Class for connecting and querying from Db.

You can use below code to connect to Database. Change the value of uname, passwd and Database Name

import MySQLdb
class DbFunctions(object):
    def _ _init_ _(self,server,uname,passwd,dbname):
        self.server = server
        self.uname = uname
        self.passwd = passwd
        self.dbname = dbname
        self.db = None
        self.cur = None

    def connection_open(self):
        self.db = MySQLdb.connect(host=self.server,user=self.uname,passwd=self.password,db=self.dbname)
        self.cur = self.db.cursor()

    def connection_close(self):
        self.db.close()

    def mysql_qry(self,sql,bool): # 1 for select and 0 for insert update delete
        self.connection_open()
        try:
            self.cur.execute(sql)
            if bool:
                return self.cur.fetchall()
            else:
                self.db.commit()
                return True
        except MySQLdb.Error, e:
            try:
                print "Mysql Error:- "+str(e)
            except IndexError:
                print "Mysql Error:- "+str(e)
        self.connection_close()

    def mysql_insert(self,table,fields,values):
        sql = "INSERT INTO " + table + " (" + fields + ") VALUES (" + values + ")";
        return self.mysql_qry(sql,0)

    def mysql_update(self,table,values,conditions):
        sql = "UPDATE " + table + " SET " + values + " WHERE " + conditions
        return self.mysql_qry(sql,0)

    def mysql_delete(self,table,condtions):
        sql = "DELETE FROM " + table + " WHERE " + condition;
        return self.mysql_qry(sql,0)

    def mysql_select(self,table):
        sql =  "SELECT * FROM "+table
        return self.mysql_qry(sql,1)

`db = DbFunctions("localhost","uname","passwd","database_name")

You can check out below link of my github account for more details https://github.com/pantlavanya/codes/blob/master/db_function_library.py

Collectives™ on Stack Overflow

Connecting to a database with MySQLdb in python

2 Answers 2

3 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Related