Connecting from Python to SQL Server

Question

I'd like to connect from IPython notebook to a SQL-Server database via integrated security.

Is this possible? I'm guessing yes it is.

How do I format the connection string in the following?

import pandas as pd
import pandas.io.sql as psql
sql = "SELECT * FROM WHdb.dbo.vw_smallTable"
cnx = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=WHdb;Data Source=OurServerName"
data = psql.read_sql(sql, cnx)

This just gives an error. Am I going about the cnx incorrectly?

@IanAuld had to step away from the work machine (to sleep!) - I'll add the msg tomorrow. On the face of it does it look ok? Am I correct using a string in that way as the second argument of read_sql? — whytheq
– whytheq, Commented Mar 15, 2016 at 21:18
I doubt I'll be much help as I've never used SQLServer with Python. However this question as written falls squarely in to the "What isn't this code working?" close reason. I won't vote to close but it does run the risk of being closed as is — kylieCatt
– kylieCatt, Commented Mar 15, 2016 at 23:10
@IanAuld I hope this helps me - will try tomorrow: stackoverflow.com/questions/16515420/… — whytheq
– whytheq, Commented Mar 15, 2016 at 23:24

user7110627 · Accepted Answer · 2016-11-03 15:13:04Z

12

You need to install the package, pypyodbc

!pip install pypyodbc

Then, you can import it as follows:

import pypyodbc as podbc

You can now create the connection:

conn = podbc.connect("Driver={SQL Server};Server=<YourServer>;Database=<YourDatabase>;uid=<YourUserName>;pwd=<YourPassword>"

Finally, you fetch your data as follows:

cursor = conn.cursor()
sql = "SELECT * FROM WHdb.dbo.vw_smallTable"
cursor.execute(sql)
data = cursor.fetchone()
while data:
    print(str(data[0]) + ", " + ... + ", " + str(data[n-1]))
    data = cursor.fetchone()
conn.close()

Note that n = number of columns in your table.

edited Nov 3, 2016 at 15:13

answered Nov 3, 2016 at 14:53

user7110627

1362 silver badges4 bronze badges

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

Comments

loveR · Accepted Answer · 2019-10-15 11:14:39Z

2

This is what worked for me well...

import pyodbc

server = 'myserver'

database = 'mydb'

username = 'myusername'

password = 'mypassword'



#Connection String

connection = pyodbc.connect('DRIVER={SQL Server Native Client 11.0};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)

cursor = connection.cursor()



#Sample select query

cursor.execute("SELECT @@version;")

row = cursor.fetchone()

while row:

    print row[0]

    row = cursor.fetchone()

answered Oct 15, 2019 at 11:14

loveR

5094 silver badges14 bronze badges

Collectives™ on Stack Overflow

Connecting from Python to SQL Server

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related