Insert pandas data frame into SQL temp table

Question

I am attempting to great a temporary table in an SQL database and populate the table from a pandas dataframe. I am receiving an error when using the df.to_sql to populate the temp table. Thank you for the assistance.

import pandas as pd
from sqlalchemy import create_engine
import pandas.io.sql as psql
import urllib

params = urllib.quote_plus("DRIVER={SQL Server};SERVER=ServerAddressHere;DATABASE=DatabaseNameHere;Trusted_Connection=yes")

engine = create_engine("mssql+pyodbc:///?odbc_connect=%s" % params)
connection = engine.connect()
resoverall = connection.execute('''SELECT DISTINCT
a.CountryRegionID AS ISO_Short,
b.Name
FROM
CustTable AS a
LEFT JOIN AddressCountryRegion AS b
ON b.CountryRegionID = a.CountryRegionID''')


Countries= pd.DataFrame(resoverall.fetchall())
Countries.columns = resoverall.keys()

Countries= pd.Countries['ISO_Short'].str.upper()

Countries= pd.DataFrame(data=Countries)

temp = connection.execute('''
create table #tempTable
(
ISO_Short varchar(5)
)
''')

Countries.to_sql('Countries',engine)

The error I'm receiving is:

ProgrammingError: (pyodbc.ProgrammingError) ('42000', "[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]CREATE TABLE permission denied in database 'databasename'. (262) (SQLExecDirectW)") [SQL: u'\nCREATE TABLE [Countries] (\n\t[index] BIGINT NULL, \n\t[ISO_Short] VARCHAR(max) NULL\n)\n\n'

UPDATE:

The other option I thought of is to use Pyodbc and convert Countries to a dictionary and then pass the dictionary values into the temporary table. Using this method, everything works until I try and pass the dictionary to the temp table. I have the following code using this approach:

import pandas as pd
import pyodbc
import pandas.io.sql as psql

cnxn = pyodbc.connect('''DRIVER={SQL Server};SERVER=telsmith;
DATABASE=DatabaseNameHere;Trusted_Connection=yes;''')
cursor = cnxn.cursor()

Countries= '''
SELECT DISTINCT
a.CountryRegionID AS ISO_Short,
b.Name
FROM
CustTable AS a
LEFT JOIN AddressCountryRegion AS b
ON b.CountryRegionID = a.CountryRegionID
'''

Countries= psql.read_sql(Countries, cnxn)


Countries= Countries['ISO_Short'].str.upper()

Countries= pd.DataFrame(data=Countries)

Countriesdict = Countries.to_dict()


Temp = '''
create table #tempTable
(
    ISO_Short varchar(5)
)

'''

cnxn.commit()

# This is where I run into difficulty
placeholders = ', '.join(['%s'] * len(Countriesdict ))
columns = ', '.join(Countriesdict .keys())
sql = "INSERT INTO #tempTable VALUES ( %s )" % (placeholders)
cursor.execute(sql, Countriesdict.values())

Udit Solanki · Accepted Answer · 2018-05-01 05:05:45Z

1

This might sound little dumb but look at the error:

ProgrammingError: (pyodbc.ProgrammingError) ('42000', "[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]CREATE TABLE permission denied in database 'databasename'. (262) (SQLExecDirectW)") [SQL: u'\nCREATE TABLE [Countries] (\n\t[index] BIGINT NULL, \n\t[ISO_Short] VARCHAR(max) NULL\n)\n\n'

Do you have any database called databasename? Since It can't find database, it can't create table. I ran the same code and it worked just fine. I believe that's the reason

edited May 1, 2018 at 5:05

answered May 1, 2018 at 4:47

Udit Solanki

5315 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

manglano · Accepted Answer · 2016-03-29 17:38:48Z

0

Not strictly a SQLAlchemy concern. You need to obtain "CREATE TABLE" permissions on the server from your DBA for some username and password, and use those credentials to access your DB. Try including "UID=uname;PWD=pword;" in your params for some set of permissioned credentials.

edited Mar 29, 2016 at 17:38

answered Mar 29, 2016 at 17:26

manglano

8441 gold badge8 silver badges21 bronze badges

3 Comments

Andrew Over a year ago

Thank you for the response. I have tested the create query in SQL Server and it works fine.

manglano Over a year ago

Respectfully, your login params do not specify a username and password for the server, so it seems that the connection has no authentication for credentials. Try including "UID=uname;PWD=pword;" in your params.

Andrew Over a year ago

My connection is set up through windows authentication, so I have 'Trusted_Connection=yes' in my connection.

Dharman · Accepted Answer · 2019-10-14 11:25:24Z

0

I might have a solution that worked for me:

from sqlalchemy import create_engine
import urllib
params = urllib.parse.quote_plus("DRIVER={SQL Server};SERVER=10.233.6.52;DATABASE=databaseName;UID=xxx;PWD=Welcome1!")

engine = create_engine("mssql+pyodbc:///?odbc_connect=%s" % params)
connection = engine.connect()
df.to_sql('tempTable',engine)

edited Oct 14, 2019 at 11:25

Dharman♦

33.9k27 gold badges103 silver badges156 bronze badges

answered Oct 14, 2019 at 11:24

Laurent T

3073 silver badges8 bronze badges

1 Comment

Laurent T Over a year ago

df is the dataframe , you do what you want on it before to integrate it

Collectives™ on Stack Overflow

Insert pandas data frame into SQL temp table

3 Answers 3

Comments

3 Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

1 Comment

Related