1

So I am trying to write a data frame to Microsoft SQL Server using the pandas to_sql function.

I have created an engine using 

engine = sqlalchemy.create_engine(
  'mssql:///Server/Database?driver=SQL Server Native Client 11.0'
)

con = engine.connect()

switchers.to_sql('check',engine)

The error I am getting is as follows :

OperationalError: (pyodbc.OperationalError) ('08001', '[08001] [Microsoft][SQL Server Native Client 11.0]Named Pipes Provider: Could not open a connection to SQL Server [2]. (2) (SQLDriverConnect); [08001] [Microsoft][SQL Server Native Client 11.0]Login timeout expired (0); [08001] [Microsoft][SQL Server Native Client 11.0]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online. (2)') (Background on this error at: http://sqlalche.me/e/e3q8)

Any idea what I should be looking for?

Mitch Wheat
  • 295,962
  • 43
  • 465
  • 541
Durr
  • 31
  • 4
  • First obvious question: Have you created a server? – PL200 Oct 28 '18 at 23:32
  • Yes mate, its for an organization so the server already exists with data – Durr Oct 28 '18 at 23:42
  • Can you actually communicate with this server? There can be set strict permissions especially when this is production DB. – Raoslaw Szamszur Oct 28 '18 at 23:47
  • That connection string looks a bit dodgy, where's the authentication? I don't know pandas but SQL server does like a nice connection string :-). I also had a look over here https://stackoverflow.com/questions/15750711/connecting-to-sql-server-2012-using-sqlalchemy-and-pyodbc which has a nice answer that may suit what you are doing – MikeAinOz Oct 28 '18 at 23:52
  • OMG, the error was writing it as SQL Server Native Client 11.0 instead of SQL+Server+Native+Client+11.0. Thanks guys – Durr Oct 29 '18 at 02:26

1 Answers1

0

Your connection string should be:

engine = 
    sqlalchemy.create_engine('mssql+pyodbc://Server/Database?driver=SQL+Server+Native+Client+11.0')
Mitch Wheat
  • 295,962
  • 43
  • 465
  • 541