Bulk Insert of rows into MSSQL database

[nikhilmakan02]

Hi All

I am wondering if we have a pandas DataFrame can this be appended to an existing table in a MSSQL database using Ibis? I thought I saw this in the docs somewhere but after running through it again I can't seem to find it.

I am using MSSQL as my backend, I know I can build a query expression in SQL and execute this through Ibis as one option.

Thanks!

[gforsyth]

Hey @nikhilmakan02 !

Yes, you can definitely do this. If you have a dataframe df and want to add it to an existing table named diamonds, you can do

con = ibis.mssql.connect(...)

con.insert("diamonds", df)

Column names and their types should match the schema of the table you are appending to. If you insert a dataframe that has some but not all of the column names, it will still work but you'll get NULLs for the missing values.

[nikhilmakan02]

@gforsyth unfortunately I tried that earlier with no luck. It throws an error related to changing databases is not currently supported, which tells me it actually means database and not schema as I set the database when creating the connection.

Database and schema do seem to be used interchangeably which adds to the confusion.

For example you setup a connection with, connection = ibis.mssql.connection(database='mydatabase', +otherparameters)

But then you can also call connection.database('myschema') which takes in the schema name as a parameter. You can then list all the tables under this schema by calling list_tables()

I tried a few other things which included calling insert on the table as well as calling insert on connection.database('myschema') with no success as the method insert does not exist on these objects, after reviewing the API I can't seem to locate the method that actually achieves this with the schema as a parameter.

I might have to resort to manually putting together a SQL query string that I submit using the connection by calling the sql method. However when you are able to, it would be good to confirm that its definitely not a possibility with the current implementation.

Thanks for your help.

[gforsyth]

Hey @nikhilmakan02 -- yeah, the schema / database delineation is not great.
It looks like inserting into a table in a specific schema is currently not possible -- I'm going to take a look through some of the rest of the implementation because we're definitely missing some pieces here, but for now, I think your plan of a manual SQL query string is a reasonable workaround.

[gforsyth]

You can also track this issue which should (:crossed_fingers: ) resolve these inconsistencies.

[lostmygithubaccount]

this issue? #6331 I think you forgot to link it

[nikhilmakan02]

@gforsyth thanks for the update on this. I have actually had to fall back to pymssql to do the above as I couldn't quite get the sql command to work correctly in Ibis. However I will post a separate question for this.

[cpcloud]

Closing this out in favor of the issue (#6821)!

Thanks all!