0

I have a database that I access through SQL Server Management Studio, I'd like to be able to read and write tables into it using Python and Pandas. I've successfully read a SQL table into a Pandas dataframe with the following script:

import pyodbc
import pandas as pd

server = 'TFAPHQSQL1903\PCOTS'
database = 'Research'
query = 'SELECT * FROM [ts1Q10];'
conn = pyodbc.connect('DRIVER={SQL Server};SERVER='+server+';DATABASE='+database)
df = pd.read_sql(query, con=conn)

However, when I try to write to the database like this:

df.to_sql('test',conn, if_exists = 'append', index = False)

I get the following error:

DatabaseError: Execution failed on sql 'SELECT name FROM sqlite_master WHERE type='table' AND name=?;': ('42S02', "[42S02] [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'sqlite_master'. (208) (SQLExecDirectW); [42S02] [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared. (8180)")

What am I doing wrong?

industriousGnome
  • 11
  • 2
  • 1
    Are you sure you are using SQL Server? Your table name suggest SQLite. – Dale K May 11 '21 at 23:49
  • 1
    also sounds like the table doesn't exist, or maybe it does, but under a different schema. – Umar.H May 11 '21 at 23:49
  • @DaleK thanks for the quick response. I've edited the post to include the definition of 'conn' – industriousGnome May 11 '21 at 23:58
  • So you are 100% sure you have a table in your SQL Server database (what is your database name) called `sqlite_master`? Because the error says you don't. – Dale K May 12 '21 at 00:00
  • Why are you using pyodbc? The docs say: `con: sqlalchemy.engine.(Engine or Connection) or sqlite3.Connection` so try with sqlalchemy. – NotAName May 12 '21 at 00:02

0 Answers0