Raspberry PI can read and write on a Microsoft SQL server database.
In order to accomplish this you follow the instructions here: http://pymssql.org/en/stable/index.html
To summarize it in a nutshell, here is what you need to do:
- apt-get install freetds-dev
- pip install pymssql
Update: Above information is for a Raspberry 2 – Raspberry 3 needs the below information as far as I know:
- sudo apt-get install freetds-dev
- sudo pip3 install cython
- sudo pip3 install pymssql
Personally I had issues getting this to work in Python 3.x so I tested it in Python 2.x and it was working fine. The issue was simply that the module “pymssql” could not be found and therefor the IMPORT line already failed in the Python script. It should be a rather easy fix – like copying the files to the Python 3 modules folder, but as of now I did not have the time to investigate this further – as I was fine using Python 2 in my specific situation.
Here is a sample script
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | import pymssql server = "mySQLservername" user = "myuser" password = "mypassword" DB = "mydatabase" conn = pymssql.connect(server, user, password, DB) cur = conn.cursor() cur.execute('SELECT * FROM MyTable') for row in cur: #print(row['id']) print(row[0]) conn.close |
The example I tested used a SQL server user account. The documentation of PyMSSQL talks about the possibility to use Windows Authentication as well.
As for asking Google about this – there is a lot of confusion information out there – the top ranked posts aren’t really helpful, so I thought I just post it again hoping someone finds this helpful.
