Insert data in sql server with python

Question

I want to insert data in the sql server from a raspberry pi with python. I tried with pypyodbc but it s not working properly. Can you guide me witch module to use.

import pyodbc 
conn = pyodbc.connect(
                     'DRIVER={SQL Server Native Client 11.0};'
                     'SERVER=server;'
                     'Integrated_Security=false;'
                     'Trusted_Connection=no;'
                     'UID=pi;'
                     'PWD=pi;'
                     'DATABASE= database'
                 )
cursor = conn.cursor()
cursor.execute('SELECT * FROM database.table')

for row in cursor:
    print(row)

yodbc.InterfaceError: ('28000', '[28000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Login failed for user \'pi\'. (18456) (SQLDriverConnect); [28000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Cannot open database " database" requested by the login. The login failed. (4060); [28000] [Microsoft][SQL Server Native Client 11.0]Invalid connection string attribute (0); [28000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Login failed for user \'pi\'. (18456); [28000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Cannot open database " database" requested by the login. The login failed. (4060); [28000] [Microsoft][SQL Server Native Client 11.0]Invalid connection string attribute (0)')

make sure in [sql server area configuration](https://www.blackbaud.com/files/support/infinityinstaller/content/installermaster/tkenablenamedpipesandtcpipconnections.htm), connection via tcp/ip and named pipes are enabled. — Manish Mishra, Jul 30 '19 at 08:20
@ManishMishra I set up the sql server . I am able to connect from a colleague computer with the sql credentials that i set up. The server is visible from the outside everything seems fine from the server side. the issue hat i have is that i cannot connect from pi via python. — Catalin, Jul 30 '19 at 08:22
well, the issue that you have pasted i.e. `Login failed for user \'pi\'`, says something wrong with access. Check if you can login via management studio through same credential, if yes, then its definitely named pipes access. Also make sure to restart your instance service for any changes — Manish Mishra, Jul 30 '19 at 08:24
I see `server` is server and database is `database`. Are these correct? Anyway, remove space before `database` text. Check whether you are able to login using this connection details directly through SQL Server client. — fiveelements, Jul 30 '19 at 08:29
@ManishMishra i can connect via ***.***.**.**\SQLEXPRESS on ssms with the credentials that sounds good to you? — Catalin, Jul 30 '19 at 08:30
@fiveelements i put that there so i won.t expose my server and data. — Catalin, Jul 30 '19 at 08:30
@Catalin there's no `Trusted_Connection` nor `Integrated_Security`. Using keywords at random will *guarantee* a failure. Either specify Windows Authentication or a username/password pair, not both — Panagiotis Kanavos, Jul 30 '19 at 08:31
@PanagiotisKanavos can you specify what do you mean about keyword at random? — Catalin, Jul 30 '19 at 08:34
@Catalin I already did. You used Integrated_Security *and* Trusted_Connection *and* UID *and* PWD in the same connection string. This guarantees that `Invalid connection string attribute` error. If you don't want to use Windows Authentication just use UID and PWD. The docs *aren't* wrong. — Panagiotis Kanavos, Jul 30 '19 at 08:37
@Catalin speaking of docs, try the connection string shown at [Step 3: Proof of concept connecting to SQL using pyodbc](https://learn.microsoft.com/en-us/sql/connect/python/pyodbc/step-3-proof-of-concept-connecting-to-sql-using-pyodbc?view=sql-server-2017#step-1--connect). The connection string uses the ODBC driver, not the (old) Native Client. Only UID and PWD are used to pass the credentials. — Panagiotis Kanavos, Jul 30 '19 at 08:39
@Catalin, no, you are using \SQLExpress to connect via ssms, but `server` instance in your python code. you should use `.\SqlExpress` in your python code too — Manish Mishra, Jul 30 '19 at 10:04
@ManishMishra now i am unable to install odbc driver 17 for sql server on debian 9 — Catalin, Jul 30 '19 at 10:12

Catalin · Accepted Answer · 2019-07-30T09:26:35.640

So a colleague just passed by me and told me to put all the data between '' only in 1 row Also i removed 'Integrated_Security=false;'Trusted_Connection=no; as someone told me

The working code

something='something'
import pyodbc 
conn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER=srv;DATABASE=datavase;UID=pi;PWD=pass')
cursor = conn.cursor()
cursor.execute("insert into test values (?, 'awesome library ')",var)
cursor.execute('SELECT * FROM test')
for row in cursor:
    print(row)

score -2 · Answer 2 · answered Jul 30 '19 at 08:24

-2

I would highly recommend using sqlite3

import sqlite3
conn = sqlite3.connect(
                      'DRIVER={SQL Server Native Client 11.0};'
                      'SERVER=server;'
                      'Integrated_Security=false;'
                      'Trusted_Connection=no;'
                      'UID=pi;'
                      'PWD=pi;'
                      'DATABASE= database'
                  )
cursor = conn.cursor()
cursor.execute('SELECT * FROM database.table')

for row in cursor:
    print(row)

answered Jul 30 '19 at 08:24

Ibtihaj Tahir

636
5
17

the question is about using `sql server`. and not `what db server would you recommend?` – Manish Mishra Jul 30 '19 at 08:25
I use sqlite3 as backup if the network goes of. i want to be able to write in the database directly and in case the network is down to write in a local .db – Catalin Jul 30 '19 at 08:32

Insert data in sql server with python

2 Answers2