Connecting from Python to SQL Server
21,331
Solution 1
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.
Solution 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()
Author by
whytheq
Current addictions: DAX / POWERSHELL Time served with: (T-)sql / MDX / VBA / SSRS Would like more time for the following: C# Python Maxim: if you build something idiot-proof, the world will build a better idiot
Updated on August 05, 2022Comments
-
whytheq over 1 year
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?