PYmssql python connection string

Question

I have downloaded Pymssql to connect to the sqlserver db but the connection string is throwing error-pymssql.connect(pymssql.c.:7990)

import pymssql
pymssql.connect(host='username\SQLEXPRESS',user='username',password='pwd',database='master')

Anyone had luck connecting to the sqlserver?

Error:
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "pymssql.pyx", line 556, in pymssql.connect (pymssql.c:7990)
    raise OperationalError(e[0])
pymssql.OperationalError: (20009, 'Net-Lib error during Unknown error')

Answer 1

I ran into the same Net-Lib error during Unknown error error message. For me, the issue was that I needed to enable TCP/IP and Named Pipes in my instance of SQL Server Express.

Try going into SQL Server Configuration Manager and turning on both TCP/IP and Named Pipes.

Answer 2

My setup:

• Microsoft Windows XP SP3.
• Microsoft SQL Server 2008 (not R2).
• CPython 2.7.4.
• pymssql 2.0.0 (installed with easy_install, IIRC).

I had forgot to create a sa login account, so I remember scavenging a few weeks ago for a way to create it; previously, I was logging in (e.g. with SQL Server Management Studio) with Windows authentication. I followed https://stackoverflow.com/a/3781737 to create the sa user account.

After that intermediary step, I started Python.

import pymssql
conn = pymssql.connect(host=r'MACHINE\SQLEXPRESS', user=r'sa', password=r'password', database=r'MYDB')
cur = conn.cursor()
cur.execute(r'SELECT COUNT(*) FROM mytable')
row = cur.fetchone()
print row[0]
cur.close()
conn.close()

My guess with your problem is that you should have used raw strings in the connection parameters -- particularly the host parameter, which takes in a backslash.

I also tried it with a CentOS 5.8 64-bit machine (pymssql 1.0.2, freetds 0.91). To that effect, I had also created a $HOME/.freetds.conf file with contents as

[global]
tds version = 10.0

[MACHINE]
host = 192.168.1.2
port =1433
tds version = 10.0
encryption = request

I forgot where I picked that file's configuration example from.

Hope that helps.

Answer 3

you must declare the charset sample:

pymssql.connect(host='username\SQLEXPRESS',user='username',password='pwd',database='master',chartset="your_charset")