Comments (1)
@hb2638 The Observed Behavior from your code,
The output from SQLAlchemy shows that the SQL table is created with a column named index, but the insert_callback
function receives ('index', '')
as the index column name in the list of columns. This discrepancy causes issues when trying to insert data into the table.
Expected output:-
The insert_callback
function should receive the correct column name for the index column, which should be index, not ('index', '')
.
To correct this behavior, you can preprocess the keys list to replace ('index', '')
with index before constructing the SQL insert statement.
Try out this code and let me know, if it works,
import math
import typing
import uuid
import pandas as pd
import sqlalchemy
import sqlalchemy.engine
def insert_callback(table: pd.io.sql.SQLTable, conn: sqlalchemy.engine.base.Connection, keys: list[str], data_iter: typing.Iterable[tuple]) -> None:
data = [list(d) for d in data_iter]
print(f"{data=}")
print(f"{keys=}")
print(f"{[c.name for c in table.table.columns]=}")
# Preprocess keys to replace ('index', '') with index
processed_keys = ["index" if k == "('index', '')" else k for k in keys]
sql = f"""
INSERT INTO [{table.name}]({", ".join(f"[{k}]" for k in processed_keys)})
VALUES ({", ".join(f":{i}" for i, _ in enumerate(processed_keys))})
""".strip()
for values in data:
conn.execute(sqlalchemy.text(sql), {str(i): v for i, v in enumerate(values)})
input_data = {
1: {('A', 'X'): math.nan, ('A', 'Y'): math.nan, ('B', 'X'): math.nan, ('B', 'Y'): math.nan},
2: {('A', 'X'): math.nan, ('A', 'Y'): math.nan, ('B', 'X'): math.nan, ('B', 'Y'): math.nan},
3: {('A', 'X'): math.nan, ('A', 'Y'): math.nan, ('B', 'X'): math.nan, ('B', 'Y'): math.nan},
4: {('A', 'X'): math.nan, ('A', 'Y'): math.nan, ('B', 'X'): math.nan, ('B', 'Y'): math.nan},
5: {('A', 'X'): math.nan, ('A', 'Y'): math.nan, ('B', 'X'): math.nan, ('B', 'Y'): math.nan},
}
df = pd.DataFrame.from_dict(input_data, "index")
conn_url = sqlalchemy.engine.URL.create("mssql+pyodbc", query={"odbc_connect": "DRIVER={ODBC Driver 18 for SQL Server};SERVER=.;Trusted_Connection=yes;TrustServerCertificate=yes"})
engine = sqlalchemy.create_engine(conn_url, echo=True)
with engine.connect() as conn:
with conn.begin():
df.to_sql(f"insert_callback_{str(uuid.uuid4())}", conn, index=True, method=insert_callback)
conn.rollback()
from pandas.
Related Issues (20)
- BUG: Series.gt (and other comparison methods) can fail with dtype=object HOT 9
- Unit Test/CI failing for multiple PRs HOT 2
- BUG: merging DataFrames on a column containing just NaN values triggers address violation in `safe_sort` HOT 4
- BUG: UnboundLocalError when full outer merging two dataframes HOT 3
- Python 3.13 wheel builds are failing HOT 2
- BUG: Inconsistent behavior between `.any` and `.all` with missing values HOT 1
- BUG: stacked bar graphs show invalid label position due to invalid rectangle bottom when data is 0 HOT 9
- BUG: `isna` on pyarrow backed Series is returning Series with `bool` dtype instead of `bool[pyarrow]` HOT 9
- BUG: dropna in pivot_table is not affecting the outcome
- BUG: pandas.DataFrame.plot crashes, if subplots argument receives a touple. HOT 3
- BUG: assert_frame_equal does not include the obj parameter in error when a MultiIndex is different HOT 4
- QST: How to use 'numba' for group by sum HOT 1
- BUG: Large XML files on Windows trigger false Encoding error HOT 2
- BUG: Series.replace(dict-like, dict-like) raises uninformative AttributeError HOT 1
- BUG: escapechar=',' Causes Double Commas in Output in Pandas 2.2.2 HOT 2
- DOC: fix docstring validation errors for `pandas.Timestamp` HOT 15
- DOC: Development on Gitpod have problems HOT 1
- BUG: Ability to set both color and style in pandas plotting HOT 2
- Title: Feature Request: Improve diff Function to Support Forward and Backward CompletionENH: HOT 2
- Title: Feature Request: Improve diff Function to Support Forward and Backward CompletionENH: HOT 2
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from pandas.