Is there a function under pyodbc that can replace cursor.copy

I use a code that opens a csv file to store it in a database. I use SQL SERVER.

when the file is opened in the RAM, after a processing that is done before, we want to store it in the database.

under Postgresql we use the following code but I want an equivalent under SQL SERVER

# upload to db
    SQL_STATEMENT = """
        COPY %s FROM STDIN WITH
            CSV
            HEADER
            DELIMITER AS ','
        """


    cursor.copy_expert(sql=SQL_STATEMENT % tbl_name, file=my_file)

I have no idea how to change the code block without changing the code

CodePudding user response：

Whereas psycopg2 is a Postgres specific DB-API to maintain extended methods like copy_expert, copy_from, copy_to that are only supported in Postgres, pyodbc is a generalized DB-API that interfaces with any ODBC driver including SQL Server, Teradata, MS Access, even PostgreSQL ODBC drivers! Therefore, it is not likely an SQL Server specific convenience command exists to replace copy_expert.

However, consider submitting an SQL Server specific SQL command such as BULK INSERT that can read from flat files and then run cursor.execute. Below uses F-strings (introduced in Python 3.6) for string formatting:

# upload to db
SQL_STATEMENT = (
   f"BULK INSERT {tbl_name} "
   f"FROM '{my_file}' "
   "WITH (FORMAT='CSV');"
)

cur.execute(SQL_STATEMENT)
conn.commit()