Uploading table to SQL Server from python using to

I have connected to my SQL Server database to extract a table and then I use python to manipulate this table. After the table is edited, I would like insert it back into my works SQL Server database as a new table.

I have tried numerous methods of to_sql and creating an engine but seem to be missing something. Any help would be greatly appreciated!

Everything seems to run fine until I get to the to_sql portion of the code.

import pandas as pd
import pyodbc
import datetime as dt
import sqlalchemy

conn = pyodbc.connect(DRIVER= '{ODBC Driver 17 for SQL Server}',
                    SERVER='myserver',
                    DATABASE = 'mydatabase',
                    PORT=myport,
                    UID = 'myuser',
                    PWD = 'mypass',
                    Authentication = 'ActiveDirectoryPassword'
                    )


df = pd.read_sql('''select * from TableX a left join TableY b on a.ID = b.ID
Union 
select * from [TableX] a left join [TableZ] b on a.ID = b.ID
''', conn)

#code used to manipulate table
df[Profit] = df[Revenue] - df[Cost]


USERNAME = '[email protected]'
PASSWORD = 'JohnDoePass!'
SERVER = 'c-c.database.windows.net'
DATABASE = 'database_name'
DRIVER = 'ODBC Driver 17 for SQL Server'
DATABASE_CONNECTION = f"mssql://{USERNAME}:{PASSWORD}@{SERVER}/{DATABASE}?driver = {DRIVER}"

engine = sqlalchemy.create_engine(DATABASE_CONNECTION)

df.to_sql('dfnew', con = engine, schema= 'dbo', if_exists='replace', index=False)

The error that I am receiving is this:

InterfaceError: (pyodbc.InterfaceError) ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)') (Background on this error at: https://sqlalche.me/e/14/rvf5)

Update 3:

import pandas as pd
import pyodbc
import datetime as dt
import sqlalchemy

conn = pyodbc.connect(DRIVER= '{ODBC Driver 17 for SQL Server}',
                    SERVER='myserver',
                    DATABASE = 'mydatabase',
                    PORT=myport,
                    UID = 'myuser',
                    PWD = 'mypass',
                    Authentication = 'ActiveDirectoryPassword'
                    )


df = pd.read_sql('''select * from TableX a left join TableY b on a.ID = b.ID
Union 
select * from [TableX] a left join [TableZ] b on a.ID = b.ID
''', conn)

#code used to manipulate table
df[Profit] = df[Revenue] - df[Cost]


engine = sqlalchemy.create_engine(
    "mssql pyodbc://[email protected]:MyPass!@myserver:1433/mydatabasename"
    "?driver=ODBC Driver 17 for SQL Server"
    "&authentication=ActiveDirectoryPassword"

df.to_sql('dfnew', con = engine, schema= 'dbo', if_exists='replace', index=False)

Error: (pyodbc.OperationalError) ('08001', '[08001] [Microsoft][ODBC Driver 17 for SQL Server]Named Pipes Provider: Could not open a connection to SQL Server [53]. (53) (SQLDriverConnect); [08001] [Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired (0); [08001] [Microsoft][ODBC Driver 17 for SQL Server]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. (53)') (Background on this error at: https://sqlalche.me/e/14/e3q8)

Confirmed that the server name is the correct one and that I am able to log into SSMS with that server name. Authentication is Azure Active Directory - Universal with MFA

CodePudding user response：

That connection URL doesn't look correct. This is from the docs:

engine = create_engine("mssql pyodbc://scott:tiger@myhost:port/databasename?driver=ODBC Driver 17 for SQL Server")

https://docs.sqlalchemy.org/en/14/dialects/mssql.html#module-sqlalchemy.dialects.mssql.pyodbc

CodePudding user response：

This might be due to the @ in the user name '[email protected]'. Try replacing it with its escape code @ in the url, e.g.

DATABASE_CONNECTION = f"mssql://{USERNAME.replace('@', '@')}:{PASSWORD}@{SERVER}/{DATABASE}?driver = {DRIVER}"