2

After trying to solve this problem on my own I need some help or nudge in right direction.

I wrote and deployed Scrapy spider on Scrapinghub. This spider collects some data and after finish saves that data to remote Microsoft SQL Server. I use SQLAlchemy as ORM and Pyodbc as a driver. For connecting to a DB in spider code I use:

params = quote_plus('DRIVER={ODBC Driver 13 for SQL Server};SERVER="server";DATABASE="db";UID="user";PWD="pass")
engine = create_engine("mssql+pyodbc:///?odbc_connect={}".format(params))

On my local PC with Win10 all work well - spider successfully connects to a remote DB and save data. But if I try to run this spider on Scrapinghub I get an error: DBAPIError: (pyodbc.Error) ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'ODBC Driver 13 for SQL Server' : file not found (0) (SQLDriverConnect)")

Seems like a problem with DRIVER part. I tried to change DRIVER={ODBC Driver 13 for SQL Server} to a DRIVER={SQL Server} or DRIVER={FreeTDS} but still getting the same error can't open lib 'lib_name' : file not found.

Does Scrapinghub support connection to a Microsoft SQL Server at all? What driver parameters do I need to use in order to successfully connect?

Thank you!

Vlad
  • 348
  • 3
  • 10
  • I think you'll need [a custom Docker image](https://shub.readthedocs.io/en/stable/deploy-custom-image.html) with the required ODBC drivers – paul trmbrth Aug 09 '17 at 16:58
  • Thank you for your response @paul trmbrth! I also use Scrapy-Splash with this spider, can I implement all desired functionality if I use a custom Docker image? Can you advise maybe I already can use a premade image? I not very familiar with Docker deployment and try to follow with this tutorial: https://blog.scrapinghub.com/2016/09/08/how-to-deploy-custom-docker-images-for-your-web-crawlers/ But unfortunately all this doesn't work as expected and now I have two problems :) – Vlad Aug 09 '17 at 17:37
  • Using scrapy-splash is not a problem if you add a `pip install scrapy-splash` step in your Dockerfile. I don't know if Scrapinghub has pre-built image with those drivers. Best is to contact Scrapinghub support if they have one. – paul trmbrth Aug 09 '17 at 18:04
  • Thanks for advice, I'll try to contact them. – Vlad Aug 09 '17 at 18:26

1 Answers1

0

Can't open lib 'ODBC Driver 13 for SQL Server' : file not found

Above error is usually related to misconfiguration or missing odbcinst.ini file.

Run odbcinst -j and verify whether odbcinst.ini does exist and it has the right driver path, e.g.

[ODBC Driver 13 for SQL Server]
Description=Microsoft ODBC Driver 13 for SQL Server
Driver=/usr/local/lib/libmsodbcsql.13.dylib

Here is the example command creating user's config file (~/.odbcinst.ini):

printf "[ODBC Driver 13 for SQL Server]\nDescription=Microsoft ODBC Driver 13 for SQL Server\nDriver=/usr/local/lib/libmsodbcsql.13.dylib\n" >> ~/.odbcinst.ini

See: Installing the Microsoft ODBC Driver for SQL Server on Linux and macOS.

If you're using Anaconda, checkout this issue: ODBC Driver 13 for SQL Server can't open lib.

kenorb
  • 155,785
  • 88
  • 678
  • 743