CODEWITHSUNDEEP
pythonsql-serverexcel
Delta
Delta

Reputation: 49

Python - Fill a table MSSQL with data from Excel

I have an xlsx file with 4 column and 68k+ lines. And i want to put all data in a table in MSSQL server. I made this.

def jb_treat_attributes(dicoval):
conn = pymssql.connect(dicoval['MSSQL_erver'],dicoval['MSSQL_Login'],dicoval['MSSQL_Password'],dicoval['MSSQL_Database_DELTA'])
    cursor = conn.cursor()
    cursor.execute('TRUNCATE TABLE delta.Attribute')
    for row in load_workbook(dicoval['path_root']+'Delta/attributes/excel.xlsx').worksheets[0].iter_rows():
        row = [cell.value.strip() if cell.value is not None else '' for cell in row]
        cursor.execute("INSERT INTO delta.Attribute VALUES (%s,%s,%s,%s)",(row[0],row[1],row[2],row[3]))
    conn.commit()
    conn.close()

And it's working, but with 340s of execution time. Does it exist a way to do it faster ?

Upvotes: 0

Views: 3324

Answers (3)

ASH
ASH

Reputation: 20322

Sorry, but what does this have to do with Python?! You should try to use the right tool for the job. If you need Python, R, or C#, use them; if you don't need them why use them? SQL Server and Excel work very, very, very well together. You could easily use the SQL Server Import Wizard to move the data in or out.

You could use SQL to do the job. 

SELECT * INTO EXCEL_IMPORT
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0; Database=C:\Excel\Spreadsheet.xls; HDR=YES; IMEX=1',
'SELECT * FROM [Sheet1$]');

You can just as well use VBA to get things done.

    Sub InsertInto()

'Declare some variables
Dim cnn As adodb.Connection
Dim cmd As adodb.Command
Dim strSQL As String

'Create a new Connection object
Set cnn = New adodb.Connection

'Set the connection string
cnn.ConnectionString = "Your_Server_Name;Database=Your_Database_Name;Trusted_Connection=True;"

'Create a new Command object
Set cmd = New adodb.Command

'Open the connection
cnn.Open
'Associate the command with the connection
cmd.ActiveConnection = cnn

'Tell the Command we are giving it a bit of SQL to run, not a stored procedure
cmd.CommandType = adCmdText

'Create the SQL
strSQL = "UPDATE TBL SET JOIN_DT = 2013-01-13 WHERE EMPID = 2"

'Pass the SQL to the Command object
cmd.CommandText = strSQL

'Open the Connection to the database
cnn.Open

'Execute the bit of SQL to update the database
cmd.Execute

'Close the connection again
cnn.Close

'Remove the objects
Set cmd = Nothing
Set cnn = Nothing

End Sub

https://www.excel-sql-server.com/excel-sql-server-import-export-using-vba.htm#Excel%20Data%20Export%20to%20SQL%20Server%20Test%20Code

Sorry for not answering your Python question, but I'm a huge proponent of using the right tool for the job. Python is awesome for countless things, but not this kind of thing.

Upvotes: 0

user1767754
user1767754

Reputation: 25094

First Quick Thoughts: Use Begin Transaction to tell that you are adding inserts

Put this line before you start cursor.execute

cursor.begin()

Packing Inserts:

There is no direct evidence, but packing values sometimes help uploading Inserts as a batch. (Which is basically what cursor.begin() is for.

I can't test it, but that's the idea. You collect your values all in a list of tuples and convert them to a string, which you finally execute it ones delivering all the Values

def jb_treat_attributes(dicoval):
    values = []
    conn = pymssql.connect(dicoval['MSSQL_erver'],dicoval['MSSQL_Login'],dicoval['MSSQL_Password'],dicoval['MSSQL_Database_DELTA'])
    cursor = conn.cursor()
    cursor.execute('TRUNCATE TABLE delta.Attribute')
    for row in load_workbook(dicoval['path_root']+'Delta/attributes/excel.xlsx').worksheets[0].iter_rows():
        row = [cell.value.strip() if cell.value is not None else '' for cell in row]
        values.append((row[0],row[1],row[2],row[3]))
    valueTuples = str(values)[1:-1]
    cursor.execute("INSERT INTO delta.Attribute VALUES " + valueTuples)
    conn.commit()
    conn.close()

Upvotes: 1

user2279962
user2279962

Reputation: 63

How about calling python to execute .sql file directly? You have a .sql file do the import excel to database like here https://learn.microsoft.com/en-us/sql/relational-databases/import-export/import-data-from-excel-to-sql

Upvotes: 0

Related Questions