Python data frames to SQL server DB

cbjzeqam 于 2023-03-28 发布在 Python

关注(0)|答案(1)|浏览(135)

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()

sql-server

来源：https://stackoverflow.com/questions/75822277/python-data-frames-to-sql-server-db

1条答案

按热度按时间

3lxsmp7m1#

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")

赞(0）回复(0）举报 2023-03-28

我来回答

Python data frames to SQL server DB

1条答案

相关问题

热门标签

最新问答