Connect to a database url in python where the url is for jdbc

Question

I'm trying to write a Python script that will connect to a Database. The host URL of the database was given to me in jdbc format:

URL = jdbc:mysql://www.myurl.com:3306/

I can't figure out how to translate that URL into pythonese.

Any help would be appreciated. Thanks

import MySQLdb

conn = MySQLdb.connect(host="????",user="web3u4",passwd="password",db="web3db4")

cursor = conn.cursor ()
cursor.execute ("SELECT VERSION()")
row = cursor.fetchone ()
print "server version:", row[0]
cursor.close ()

# disconnect from server
db.close()

Solution 1

You should use the urlparse.urlparse to parse the jdbc string.

from urlparse import urlparse
jdbc = "jdbc:mysql://www.myurl.com:3306"
result=  urlparse(jdbc)


MySQLdb.connect(host=result.host,
                user=result.username,
                passwd=result.password,
                db="web3db4")

---

Not sure how you are planing on passing the db.. but if you add it I can help you with that as well.

Solution 2

What about parsing the JDBC string by re module? For example:

>>> import re
>>> jdbc_str = 'jdbc:mysql://repos.insttech.washington.edu:3306/johndoe?user=johndoe&password=jddb'
>>> jdbc_pattern = 'mysql://(.*?):(\d*)/'
>>> (host, port) = re.compile(jdbc_pattern).findall(jdbc_str)[0]
>>> print host
repos.insttech.washington.edu
>>> print port
3306

The connection string is copied from one sample [age, and I don't know whether the user and password fields are always in the connection string so I haven't put them in the pattern.

Besides, there is a module named JayDeBeApi which can be used for connecting JDBC server, have you tried it?

Comments

[HRVHackers]

Thanks but it's not working. Traceback (most recent call last): File "D:\Temp\1Python\Scripts2011\practice-DBconnection.py", line 14, in <module> MySQLdb.connect(host=result.host, AttributeError: 'ParseResult' object has no attribute 'host'

[HRVHackers]

Thanks, but I'm not quite following how this is different than me manually just copying the host url from the string and pasting into the parameter for host. e.g. host=repos.insttech.washington.edu

[hzm]

@gorbysbm If you only have one JDBC string, this method has no adventure than manually at all :) but I think it may simple the operation if there are several JDBC string.

[Jiangge Zhang]

result.host should be result.hostname

[tokland]

@JianggeZhang Indeed. Also: ParseResult(scheme='jdbc', netloc='', path='mysql://www.myurl.com:3306', params='', query='', fragment=''). The ":" within "jdb:mysql" must be removed before parsing.