How can we use Python/Pandas to push data to Azure SQL Server?

Question

I read the documentation in the two links below.

https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-set-database-firewall-rule-azure-sql-database?view=azuresqldb-current

https://www.virtual-dba.com/blog/firewalls-database-level-azure-sql/

I just setup my firewall ruls this past weekend and everything looks ok, I think. When I run the script below, I get the start and end IP addresses from my laptop.

USE [ryan_sql_db]
GO
SELECT * FROM sys.database_firewall_rules

I think we're good here. Now, I am trying to login to the Azure database using the code below, loop through several CSV files in a folder, and push each one into my database.

import os
import glob
import pandas as pd
import ntpath

import urllib.parse
params = urllib.parse.quote_plus(
    'Driver=%s;' % '{ODBC Driver 17 for SQL Server}' +
    'Server=%s,1433;' % 'ryan-server.database.windows.net' +
    'Database=%s;' % 'ryan_sql_db' +
    'Uid=%s;' % 'MyUid'  +
    'Pwd={%s};' % 'MyPwd' +
    'Encrypt=no;' +
    'TrustServerCertificate=no;'
    )
    
from sqlalchemy.engine import create_engine
conn_str = 'mssql+pyodbc:///?odbc_connect=' + params
engine = create_engine(conn_str)
        
connection = engine.connect()
connection

    
# specifying the path to csv files
path = 'C:\\Users\\ryans\\Desktop\\test\\'
csvname= ntpath.basename(path)
  
# csv files in the path
files = glob.glob(path + "*.csv")
  
# defining an empty list to store content
df = pd.DataFrame()
content = []
i = 1  

# checking all the csv files in the specified path
for filename in files:
    print(filename)
    df = pd.read_csv(filename, usecols=range(15), header=1)
    df['filename'] = os.path.basename(filename)
    content.append(df)
    df = pd.concat(content, ignore_index=True)  
    print(i)
    df = df.set_index('filename') 
    i = i + 1  

    try:
        df.to_sql('health', engine, if_exists='append', chunksize=100000, method=None,index=False)
    except Exception as e:
        print(e)

The code just runs and runs. It never finishes and it never throws an error. What am I doing wrong here? If I run the script below in Azure, I'm seeing 0 records, even after several minutes.

SELECT TOP (1000) * FROM [dbo].[health]

Query succeeded: Affected rows: 0

Try reducing your chunk size to 1,000 and set method=multi. Your current set up is trying to run 100K insert statements in a single transaction. — gvee
– gvee, Commented Jan 24, 2022 at 14:53
Thanks. I made the changes you suggested; still getting the same thing. It runs and runs and runs, but it doesn't actually load any data into the destination table. I never get to the line that prints the exception, so I can't tell what the issue is. Weird. — ASH
– ASH, Commented Jan 24, 2022 at 23:02
I just retired it with a very, very small data sample. Now, I am getting this message: (Background on this error at: sqlalche.me/e/14/dbapi) — ASH
– ASH, Commented Jan 24, 2022 at 23:11
As it turns out, there was some crappy data in my files and that was throwing things off. When I cleaned the data, making strings actual strings and floats actual floats, everything worked as expected. The original code that I posted is totally fine, as is. Thanks for trying to help gvee!! — ASH
– ASH, Commented Jan 25, 2022 at 0:31

SaiKarri-MT · Accepted Answer · 2022-01-25 12:18:26Z

1

Have a look on the below code to send the data to SQL table which is working.

    import pyodbc
    import pandas as pd
    
    df = pd.read_csv("c:\\user\\username\department.csv")
    
    server = 'yourservername' 
    database = 'AdventureWorks' 
    username = 'username' 
    password = 'yourpassword' 
    cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)
    cursor = cnxn.cursor()
    #Insert Dataframe into SQL Server:
    for index, row in df.iterrows():
         cursor.execute("INSERT INTO HumanResources.DepartmentTest (DepartmentID,Name,GroupName) values(?,?,?)", row.DepartmentID, row.Name, row.GroupName)
    cnxn.commit()
    cursor.close()

Refer to this official doc for detailed explanation.

answered Jan 25, 2022 at 12:18

SaiKarri-MT

1,3011 gold badge5 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How can we use Python/Pandas to push data to Azure SQL Server?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related