Uploading table to SQL Server from python using to_sql

Question

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 = 'johndoe@email.com'
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://johndoe@email.com: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

This is an Azure SQL db, yes? – squillman Aug 18 '22 at 20:49 — squillman, Aug 18 '22 at 20:49

score 0 · Answer 1 · edited Aug 18 '22 at 21:41

0

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

edited Aug 18 '22 at 21:41

Dale K

25,246
15
42
71

answered Aug 18 '22 at 21:07

David Browne - Microsoft

80,331
6
39
67

I tried this as well and it doesn't seem to be working either. `engine = sqlalchemy.create_engine("mssql+pyodbc://johndoe@email.com:MyPassword!@myhost:1000/mydatabase"?driver=ODBC+Driver+17+for+SQL+Server")` I am not sure what 'myhost' is referring to – Aug 18 '22 at 21:11
Myhost is the server name. Do you have that ODBC driver installed? – David Browne - Microsoft Aug 18 '22 at 21:16
Oh okay, let me try replacing myhost, with my specific server name and see if that works. I do have the ODBC driver installed. – Aug 18 '22 at 21:19
Tried this and it is giving me a similar as the error at the bottom of the original post – Aug 18 '22 at 21:31
Update the question with the exact code and error message, and test that you can connect to that server with SSMS or Azure Data Studio. – David Browne - Microsoft Aug 18 '22 at 21:35
Hi David, I have made the update to the question with the exact code and the error message, please look under 'Update 3'. I am able to log onto my company's SSMS with the server name I tried to input into the engine. Don't know if this is noteworthy but I do have to use a Azure Active Directory - Universal with MFA. – Aug 18 '22 at 22:28
Which line is failing? – David Browne - Microsoft Aug 18 '22 at 23:14
'df.to_sql('dfnew', con = engine, schema= 'dbo', if_exists='replace', index=False)' this is the line that seems to be giving me the error. when I run the engine alone, there is no error but as soon as I add the df.to_sql, i get the error. – Aug 19 '22 at 14:24

score 0 · Answer 2 · answered Aug 18 '22 at 21:49

0

This might be due to the @ in the user name 'johndoe@email.com'. Try replacing it with its escape code %40 in the url, e.g.

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

answered Aug 18 '22 at 21:49

Nina

53
5

I tried this and it did not work. Got the same error – Aug 19 '22 at 14:23
Mhm the same might be the case for the password? Any special characters in the password, like ":" or "@"? I guess you also cannot read from that server, right? Have you used pyodbc.connect() for the problematic server or only for another one? – Nina Aug 25 '22 at 08:29

Uploading table to SQL Server from python using to_sql

2 Answers2