Connecting python to mysql

Assumption

Python, and mysql are installed and working. So you should be able to connect mysql and start python from shell as below.

%> mysql -hlocalhost -uroot -p

%> python
>>>

On python prompt if following command does not produce an error that means python-mysql api can load without problems;

>>> import MySQLdb
>>>

If it produces error as shown below that most probably means that python-mysqldb (python MySQL api) is not installed properly.

“Traceback (most recent call last):
File ”
“, line 1, in
import MySQLDb
ImportError: No module named MySQLDb”

Solution:

On my Ubuntu system I uninstalled and installed python-mysql api by running

%> sudo apt-get remove python-mysql
%> sudo apt-get install python-mysql

This fixed the problem. If you still get error, try purge.

%> sudo apt-get –purge remove python-mysql

If mysql is not installed on the default path then make sure mysql_config is in the your path.

%> export PATH=$PATH:/path_to_mysql_config

If it still does not solve the issue and you decide to remove mysql completely and reinstall it. I found using Synaptic Package Manager is much easier as it automatically installs all dependencies along.
Assuming (hopefully) by now python and mysql is working fine.

Working with python-mysql:

import MySQLdb
cn = MySQLdb.connect (
host = “localhost”,
user = “dbuser”,
passwd = “password”,
db = “play”
)
cr = cn.cursor()

#””” lets you span multiple rows

cr.execute(“””
create table if not exists state (
name varchar(40),
abbr char(2))
“””)
cr.execute(“””
Insert into state
values
(‘New York’,’NY’),
(‘New Jersey’,’NJ’),
(‘Maryland’, ‘MD’),
(‘Virginia’,’VA’),
(‘Washington’, ‘WA’)
“””)

cr.execute(“Select * from state”)
while 1:
row = cr.fetchone()
if row == None:
break
print “%s –> %s” % (row[0], row[1]

Python & mysql are real fun!

SQL Server 2005 Browser Service

SQL Server Browser service was introduced in version 90 or SQL Server 2005. On my first encounter I thought it was service for SQL Server Management Studio (SSMS)…Wrong.

What Browser Service is for:

If you want to get a list of SQL Servers on your subnet in other words browse the list of available servers. Browser service is the one which will help you get that. BTW you can the list of SQL Servers listening on your subnet by running

sqlcmd -L

SQL Server by default listens to port 1433. The name of that default instance is same as the name of host it is running on. Browser service does not play any role in resolving the default instance because port 1433 was assigned to SQL Server by Internet Assigned Numbers Authority (IANA). Its official and fixed. Any extra SQL Server instances (named instances) running on a machine are allocated dynamic port by default. Which means the port might change whenever the named instance is stopped/started. This behavior can be changed by assigning fixed port to that named instance and default port 0f 1433 can be changed as well. The down side of assigning fixed port to named instance or changing default port is that all the client must be updated to include the port number.

SQL Server Browser starts and claims UDP port 1434 (since it uses UDP it can listens to all broadcasts) Browser service reads the registry, identifies all instances of SQL Server on the computer, and notes the ports that they use. When a server has two or more network cards, SQL Server Browser returns the first enabled port it encounters for SQL Server. When client application instantiate connection to a named instance by hostname\instance_name. The client network library sends a UDP message to the server using port 1434. SQL Server Browser responds by returning the TCP/IP port of the requested instance. The network library on the client application then completes the connection by sending a request to the server using the port of the desired instance.

Prior to Microsoft SQL Server 2000, only one instance of SQL Server could be installed on a computer. Multiple instance support was introduced in SQL Server 2000. SQL Server Resolution Protocol (SSRP) was developed to listen on UDP port 1434 and repond to client requests by returning the names of the installed instances, and the ports or named pipes used by the instance. SQL Server 2005 introduced Browser service as a replacement for SSRP.

If Browser service is not running:

1. Client must specify full connection parameters including the TCP/IP port or else it would not be able to connect.
2. Browser service help connecting to dedicated administrator connection (DAC) endpoints without it DAC to named or default instance will not work.
3. sqlcmd -L or enumerating servers any where will not work.
4. Analysis redirector service will not work.

Security Consideration:

Since the Browser service accepts unathuenticated request it is recommended to run it under low privileged account. For SQL Server 2005, HideInstance flag can be set to stop SQL Server Browser service from responding with information about that server instance.

Cluster:

Browser service is not cluster aware so must run on each node of cluster. SQL Server Browser listens on any IP. As a cluster has multiple IPs when you enable listening on specific IPs, the user must configure the same TCP port on each IP, because SQL Server Browser returns the first IP/port pair that it encounters.

Summary:

Any remote connection error specially for named instance or OLAP redirector may have to do with browser service. For named instance make sure it is not configured to use fixed TCP/IP port. If it is using fixed port then make sure client connection is providing that port number when connecting to the named instance.