Get data from pandas into a SQL server with PYODBC

by

For the ‘write to sql server’ part, you can use the convenient to_sql method of pandas (so no need to iterate over the rows and do the insert manually). See the docs on interacting with SQL databases with pandas: http://pandas.pydata.org/pandas-docs/stable/io.html#io-sql

You will need at least pandas 0.14 to have this working, and you also need sqlalchemy installed. An example, assuming df is the DataFrame you got from read_table:

import sqlalchemy
import pyodbc
engine = sqlalchemy.create_engine("mssql+pyodbc://<username>:<password>@<dsnname>")

# write the DataFrame to a table in the sql database
df.to_sql("table_name", engine)

See also the documentation page of to_sql.
More info on how to create the connection engine with sqlalchemy for sql server with pyobdc, you can find here:http://docs.sqlalchemy.org/en/rel_1_1/dialects/mssql.html#dialect-mssql-pyodbc-connect

But if your goal is to just get the csv data into the SQL database, you could also consider doing this directly from SQL. See eg Import CSV file into SQL Server

More Related Contents:

  1. python pandas to_sql with sqlalchemy : how to speed up exporting to MS SQL?
  2. SQL / Pandas equivalent
  3. Pandas read_sql with parameters
  4. Pandas DENSE RANK
  5. How to create a large pandas dataframe from an sql query without running out of memory?
  6. PYODBC–Data source name not found and no default driver specified
  7. pyodbc – How to perform a select statement using a variable for a parameter
  8. Pandas left outer join multiple dataframes on multiple columns
  9. Python Pandas write to sql with NaN values
  10. Pandas query function not working with spaces in column names
  11. pyodbc insert into sql
  12. to_sql pyodbc count field incorrect or syntax error
  13. How to see the real SQL query in Python cursor.execute using pyodbc and MS-Access
  14. pyodbc the sql contains 0 parameter markers but 1 parameters were supplied’ ‘hy000’
  15. Remote connection to MS SQL – Error using pyodbc vs success using SQL Server Management Studio
  16. Pandas analogue to SQL MINUS / EXCEPT operator, using multiple columns
  17. pyodbc.connect() works, but not sqlalchemy.create_engine().connect()
  18. How do I select rows from a DataFrame based on column values?
  19. Pandas Melt Function
  20. First non-null value per row from a list of Pandas columns
  21. Fastest way to populate QTableView from Pandas data frame
  22. pandas DataFrame output end of csv
  23. Comparing floats in a pandas column
  24. Pandas sparse dataFrame to sparse matrix, without generating a dense matrix in memory
  25. Dictionary column in pandas dataframe
  26. How do I split a string into several columns in a dataframe with pandas Python?
  27. Pandas: combining header rows of a multiIndex DataFrame
  28. pandas groupby and join lists
  29. Row-wise average for a subset of columns with missing values
  30. Python: Adding hours to pandas timestamp

Leave a Comment