'Dynamical loading data from Pandas into MSSQL Server using python

I'm trying to load database with number of tables with different number of columns from pandas into MS SQL Server, I've reached the last step: I am dynamically creating the new tables in the database, base on the rows in pandas, fetching the data ... but I have issue with last step - adding the data into the columns, names of rows are being gathered dynamically, however I cannot pass them to the system so it would threat them as name of rows not as strings

               with conn.cursor() as cursor:
                   cursor.execute("IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '"+foldern[0]+"' AND TABLE_SCHEMA = 'dbo') DROP TABLE [dbo].["+foldern[0]+"];")
                   skuel = "CREATE TABLE " + foldern[0] +  " ( "+all_columns+" ) AS NODE"
                   print(foldern[0])
                   cursor.execute(skuel)
                   conn.commit()
               for index, row in df2.iterrows():
                   skuel2 = "INSERT INTO " + foldern[0] + " ( "+all_columns2+" ) values("+all_columns3+")"
                   cursor.execute(skuel2, *namesofrows)

Under +all_columns2+ - I do get name of columns, under values I got as many "?" as there is columns

Under "namesofrows" is a tupple that contains the names of rows from df2 (in format row.ROWNAME ), but those rows are not "resolved", what I mean by that, is that instead of having the values of rows coming from pandas, I'm having number of rows with same string which is the name of row (like row.id, row.types)

Any idea how to proceed with that?

pythonsql-serverpandasdatabase-cursor


Solution 1:[1]

That is good point, I was not aware of SQLAlchemy possibility which I will probably use - but - btw. I did solve the issue

resolvedvalues = []
for e in namesofrows:    
    resolvedvalues.append(eval(e))
cursor.execute(skuel2,(resolvedvalues))

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 sagar43

Related Questions