Python data frames to SQL server DB

Question

I have the following code, and it is failing to connect to a Microsoft SQL,

import pandas as pd
import pyodbc

# set up a connection to the database
server = '1.1.1.1'
database = 'testDB'
username = 'tuser'
password = 'xxxxx'


cnxn = pyodbc.connect('DRIVER={{SQL Server}};SERVER='+server+';DATABASE='+database+';ENCRYPT=yes;UID='+username+';PWD='+ password)
# create a DataFrame to write to the database
data = {
    'id': [1, 2, 3, 4],
    'name': ['Alice', 'Bob', 'Charlie', 'David'],
    'age': [25, 32, 18, 47]
}
df = pd.DataFrame(data)

# write the DataFrame to a new table in the database
table_name = 'MyTable'
df.to_sql(table_name, cnxn, if_exists='replace')

# close the connection
cnxn.close()

I first tried it with

cnxn = pyodbc.connect(f'DRIVER={{SQL Server}};SERVER={server}....

And it errors with

pandas.errors.DatabaseError: Execution failed on sql 'SELECT name FROM sqlite_master ........

Then I tried using sqlalchemy, as per some post I had read, and then tried

cnxn = pyodbc.connect('DRIVER={ODBC Driver 18 for SQL Server};

But I have not managed to get any of them to work, at the all i want to do is to dump the df to a table, using both the append and replace methods.

What does work is below, so I can connect using pyodbc.connect and list the tables, but I can't seem to write a df to the DB using pandas?

At the moment I am testing on Windows but eventuly this will run on Linux so need a solution that is portable. Any ideas?

...
...
...
cnxn = pyodbc.connect(f'DRIVER={{SQL Server}};SERVER={server};DATABASE={database};UID={username};PWD={password}')

# get a list of all table names in the database
cursor = cnxn.cursor()
table_names = [row.table_name for row in cursor.tables(tableType='TABLE')]

# iterate through each table and drop it
for table_name in table_names:
    print (table_name)

# close the connection
cnxn.close()

Did you check the Pandas docs? read_sql and to_sql require sqlalchemy. The con parameter is a sqlalchemy engine or connection: consqlalchemy.engine.(Engine or Connection) or sqlite3.Connection — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Mar 23, 2023 at 11:16
Does this answer your question? Get data from pandas into a SQL server with PYODBC — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Mar 23, 2023 at 11:18
that was one i was looking at I wanted to use the hostname method but i think i might be getting lost on the drivers, sqlalchemy.exc.InterfaceError: (pyodbc.InterfaceError) ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)') not sure what to put use to get this working — DevilWAH
– DevilWAH, Commented Mar 23, 2023 at 11:45

Panagiotis Kanavos · Accepted Answer · 2023-03-23 12:45:49Z

0

Pandas uses SQLAlchemy to read and write to databases. It can't use PyODBC directly. A SQLAlchemy connection string isn't the same as a PyODBC connection string either.

I use this function to create a SQLAlchemy engine from a server and database name.

def create_engine(server: str , db:str) -> sqlalchemy.engine.Engine:
    connection_string = f"DRIVER=ODBC Driver 18 for SQL Server;Server={server};Database={db};Trusted_Connection=yes"
    connection_url = sqlalchemy.engine.URL.create(
        "mssql+pyodbc", query={"odbc_connect": connection_string}
    )
    engine = sqlalchemy.engine.create_engine(
        connection_url, fast_executemany=True)
    return engine

This creates a PyODBC connection s.tring that uses Windows Authentication, then uses sqlalchemy.engine.URL.create to construct a SQLAlchemy connection string from it.

Finally, fast_executemany=True is used to enable fast insertions

When the time comes to save data to the database, this snippet opens a connection and saves the

engine = create_engine("localhost","MyDB")
...
with engine.begin() as conn:
     df.to_sql(tableName, conn, if_exists="append", index_label="ID")

answered Mar 23, 2023 at 12:45

Panagiotis Kanavos

134k16 gold badges211 silver badges270 bronze badges

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

1 Comment

DevilWAH Over a year ago

one question if i am trying a connections string like engine = sqlalchemy.create_engine('mssql+pyodbc://user:p@[email protected]/DB?driver=ODBC+Driver+18+for+SQL+Server') how can i deal with the @ in the password? i was given the password so before i ask for it to be changed can i deal with it in the engine creation?

Collectives™ on Stack Overflow

Python data frames to SQL server DB

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related