0

I am not able to transfer this dataframe into a sql server database.

import pyodbc
import pandas as pd

# Connect to the database
conn = pyodbc.connect("Driver={SQL Server};"
                      "Server=servername;"
                      "Database=databasename;"
                      "Trusted_Connection=yes;")

# Create the table
cursor = conn.cursor()
cursor.execute("CREATE TABLE emails (email VARCHAR(255))")

# Write the DataFrame to the database
df.to_sql("emails", conn, if_exists="replace", index=False)

# Commit the transaction
conn.commit()

# Close the connection
conn.close()

The error it fives me is

ProgrammingError: ('42S02', "[42S02] [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'sqlite_master'. (208) (SQLExecDirectW); [42S02] [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared. (8180)")

The above exception was the direct cause of the following exception:

DatabaseError: Execution failed on sql 'SELECT name FROM sqlite_master WHERE type='table' AND name=?;': ('42S02', "[42S02] [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'sqlite_master'. (208) (SQLExecDirectW); [42S02] [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared. (8180)")

What am I doing wrong?

Is pyodbc not correctly installed and if so how can i check it? Should I use another Driver as "ODBC Driver 17 for SQL Server" and if so how do I install it?

EDIT: I installed OCDB Driver 18 and added "TrustServerCertificate=YES;") to the connection bit. But now it just prints my first error.

('42S02', "[42S02] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Invalid object name 'sqlite_master'. (208) (SQLExecDirectW); [42S02] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Statement(s) could not be prepared. (8180)")

The above exception was the direct cause of the following exception:

DatabaseError: Execution failed on sql 'SELECT name FROM sqlite_master WHERE type='table' AND name=?;': ('42S02', "[42S02] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Invalid object name 'sqlite_master'. (208) (SQLExecDirectW); [42S02] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Statement(s) could not be prepared. (8180)")

Lehas123
  • 21
  • 5
  • 1
    `sqlite_master ` ? Are you sure this is the code that throws? There's no such table in SQL Server. As for the ODBC driver yes, you should install the latest ODBC driver and use it. Microsoft's [Python connectivity tutorial](https://learn.microsoft.com/en-us/sql/connect/python/pyodbc/python-sql-driver-pyodbc?view=sql-server-ver16) shows [where to download the driver from](https://learn.microsoft.com/en-us/sql/connect/python/pyodbc/step-1-configure-development-environment-for-pyodbc-python-development?view=sql-server-ver16) for each OS and how to use it – Panagiotis Kanavos Dec 16 '22 at 11:42
  • 1
    The latest SQL Server ODBC driver version is 18. You can find download and installation instructions for all OSs [here](https://learn.microsoft.com/en-us/sql/connect/odbc/download-odbc-driver-for-sql-server?view=sql-server-ver16). If pandas keeps getting confused you can specify the driver explicitly with `DRIVER={ODBC Driver 18 for SQL Server};` – Panagiotis Kanavos Dec 16 '22 at 11:43
  • BTW I don't remember ever using `Driver={SQL Server};` – Panagiotis Kanavos Dec 16 '22 at 11:48
  • Installed ODBC Driver 18 but now it gives me the error: "OperationalError: ('08001', '[08001] [Microsoft][ODBC Driver 18 for SQL Server]SSL Provider: The certificate chain was issued by an authority that is not trusted.\r\n (-2146893019) (SQLDriverConnect); [08001] [Microsoft][ODBC Driver 18 for SQL Server]Client unable to establish connection (-2146893019); [08001] [Microsoft][ODBC Driver 18 for SQL Server]Invalid connection string attribute (0)')" – Lehas123 Dec 16 '22 at 11:50
  • The latest SQL Server drivers use TLS encryption by default. Add `TrustServerCertificate=True` to the connection string to bypass certificate validation – Panagiotis Kanavos Dec 16 '22 at 12:05
  • @PanagiotisKanavos do you mean like this? conn = pyodbc.connect("DRIVER={ODBC Driver 18 for SQL Server};" "Server=DEBXT-MSDB-03\STUDENTS;" "Database=Studentsdb;" "Trusted_Connection=yes;" "TrustServerCertificate=True;")? because it prints out with that: OperationalError: ('08001', "[08001] [Microsoft][ODBC Driver 18 for SQL Server]Invalid value specified for connection string attribute 'TrustServerCertificate' (0) (SQLDriverConnect)") – Lehas123 Dec 16 '22 at 12:10
  • updated my post in the edit – Lehas123 Dec 16 '22 at 12:21

0 Answers0