How to connect to mysql database from python using connection string

Question

Hi i am using python and database as Mysql, Now i want to connect to Mysql database from python and i wrote the below code

Method_1

import MySQLdb as mdb


conn = mdb.connect('ip_address', 'user_name', 'pass_word', 'database_name')

By using above i can connect to Mysql succesfully, but i want to know whether we can do the same by using a connection string and accessing like i mentioned below

Method_2

connectString=Server=ip_address;Database=database_name;UID=user_name;PWD=pass_word
conn = mdb.connect(connectString)

But i am getting an error by using above, so can anyone let me know whether we can access Mysql database only by method_1 or is there any way to declare the access credentials to some variable and using that variable to connect as i mentioned in method_2

Edited Code:

Actually what i am trying is given below

example_file.ini

[for_primary]

connectString=host="192.168.xx.xxx",user="username_1",passwd="password_1",db="database_1"

[for_secondary]

connectString=host="192.168.xx.xxx",user="username_2",passwd="password_2",db="database_2"

file.py:

import ConfigParser
import MySQLdb as mdb

configFeed = ConfigParser.ConfigParser()
configFeed.read('path to file/example_file.ini')
connectString = configFeed.get('for_primary', 'connectString')
conn = mdb.connect(connectString)
print conn

Result:

 File "/usr/lib64/python2.7/site-packages/MySQLdb/__init__.py", line 81, in Connect
    return Connection(*args, **kwargs)
  File "/usr/lib64/python2.7/site-packages/MySQLdb/connections.py", line 187, in __init__
    super(Connection, self).__init__(*args, **kwargs2)
_mysql_exceptions.OperationalError: (2005, 'Unknown MySQL server host \'host="192.168.xx.xxx",user="username_1", passwd="password_1",db="database_1"\' (0)')

So i am trying in this way because i need to connect to two databases depending upon selection in example_file.ini file. Is there any way to do like abobe by declaring to access credentials to another variable in .ini file. i am expecting is here if i get connection string from .ini file it taking those as string.

Martijn Pieters · Accepted Answer · 2012-08-22 12:22:31Z

9

You can't, MySQLdb.connect only supports the former option.

You can, of course, parse the connection string into it's constituents and use that as a set of keyword parameters for the .connect() function:

connectParams = dict(entry.split('=') for entry in connectString.split(';'))
mdb.connect(**connectParams)

The above splitting method is rather naive however; you probably would need a more sophisticated method that would remove unsupported options, convert certain parameter values (think use_unicode and True or False) and allow for escaping of ; and = characters where they are part of a parameter value. Refer to the .connect() documentation for supported keyword arguments.

answered Aug 22, 2012 at 12:22

Martijn Pieters

1.1m326 gold badges4.2k silver badges3.4k bronze badges

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

4 Comments

Shiva Krishna Bavandla Over a year ago

Thanks for your reply i had edited my code please have a look at it

Martijn Pieters Over a year ago

@Kouripm: That format will need a bit more parsing work; see what you can come up with yourself (python is fairly easy!) and post a new question if you get stuck.

Shiva Krishna Bavandla Over a year ago

actually i am using scrapy for parsing urls, i have been stucked here and seeking for help to move forward, i have googled many times and finally posted here, can u please assist me

bgusach Over a year ago

@Martijn Pieters @shivakrishna , this is a little bit of autospam, but I made a small python library to handle the parsing of the connection strings, it's called pyconstring and you can find it on PyPI (any criticism is welcome)

themanatuf · Accepted Answer · 2012-08-22 12:23:02Z

0

According to the documentation, you should be able to do this:

db=_mysql.connect(host="localhost",user="joebob", passwd="moonpie",db="thangs")

http://mysql-python.sourceforge.net/MySQLdb.html

Down in the _mysql examples section discusses the different ways to invoke connect().

Personally, I'm not aware how to do that as a single "connectionString" that you are referencing. But you should be able to use the keyword arguments with variables.

answered Aug 22, 2012 at 12:23

themanatuf

3,1603 gold badges28 silver badges42 bronze badges

3 Comments

themanatuf Over a year ago

I think @Martijn has the right answer for you. Execute that connectParams = dict(entry.split('=') for entry in connectString.split(';')) AFTER you get it from the ConfigParser and use that to call `mdb.connect(**connectParams).

Shiva Krishna Bavandla Over a year ago

thats returning as a string actually but conn.connect('string') does n't work right

themanatuf Over a year ago

I'm not sure I understand what you're saying. connectParams is a dictionary and in the snippet you'd be passing the dictionary to the connect function not a string. I don't have a mysql server to test with, but I made a simple python script to verify that func(**connectParams) does in fact pass a dictionary to the function and not a string.

Collectives™ on Stack Overflow

How to connect to mysql database from python using connection string

2 Answers 2

4 Comments

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

3 Comments

Linked

Related