Connect python with SQL server

Problem description:

1. cannot install pymssql

2. cannot connect pymssql with my MSSQL

Below is what I did for the whole thing

Install pymssql:

1. install pymssql package. One may install it using 'pip install pymssql'. An alternative is install the package from file->setting->python interpreter->click '+'->search for pymssql->click install. Unfortunately, none of these methods works for me. Both of them created a folder named "pymssql-2.1.1.dist-info" without any .py files in it. I have to use the very basic way: download the .tar.gz file and unzip it to the Python34->...->site-packages. This method works.

1.1 Debug 1: When I use the example code, I got error. To solve it, do not "from os import getenv"

1.2 Debug 2: hard code the connection string. Error: "...unknow reason". Google it. Some one got similar problem years ago. The solution he/she proposed was to amend the freetds.conf file by appending some configurations of the database at the end of the .conf file. So goto Step 2.

2. install freetds. I don't quite know what it is. I though it should come with pyCharm or at least come pymssql. However, I could not find freetds.conf file from my file explorer. Again, download the .tar.gz file and unzip it to the same location.

2.1 Amend the .conf file. [myDBname]/n host = servername (note: without instance. for example, use myDB instead of myDB\SQLExpress)/n port = 1433/n tds version = 8.0 (someone use 7.0, I used 8.0, anyway, it works).

SQL Server:

1. Change database security to SQL Authentication and Win authentication

2. Manually set the port of SQLExpress at SQL management tool to be 1433 (the default value for my SQL is empty)

3. Add a user, set its server roles to be public and sysadmin (other roles exist, not sure if other roles will also work)

4. User mapping, map the user to the specific database you want to login

Smple codes:

import pymssql

server = "myDBenginename"
user = "userID"
password = "userPW"

conn = pymssql.connect("myDBenginename", "userID", "userPW", "DBname")
cursor = conn.cursor()

# you must call commit() to persist your data if you don't set autocommit to True
conn.commit()

cursor.execute("SELECT top 10 * FROM Tablename")
row = cursor.fetchone()

while row:
    print("ID=%d, Name=%s" % (row[0], row[1]))
    row = cursor.fetchone()

conn.close()

sample code 2 (freetds.conf):

 [myDBname]

host = servername # not quotation mark, no instance name (e.g., \SQLExpress)

port = 1433 # the same as you defined in SQL server management tool

tds version = 8.0 # someone suggest 7.0, anyway, 8.0 works as well