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()
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.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 insertionsWhen the time comes to save data to the database, this snippet opens a connection and saves the