BUG: to_sql works only for strings

[pranav-ds]

Pandas version checks

• I have checked that this issue has not already been reported.

• I have confirmed this bug exists on the latest version of pandas.

• I have confirmed this bug exists on the main branch of pandas.

Reproducible Example

import panda as pd
from sqlalchemy.types import DOUBLE

data = # Panda Datafrme with timestamp, double and string along with different column types.
column_types_filtered_data = {col: DOUBLE() for col in data.columns}
data.to_sql(..., dtype=column_types_filtered_data)

Issue Description

For any type other than str this block in pandas.io.sql will fail.

for col, my_type in dtype.items():
                if not isinstance(my_type, str):
                    raise ValueError(f"{col} ({my_type}) not a string")

Expected Behavior

Different datatypes should be supported.

Installed Versions

pandas==2.2.3

[rhshadrach]

The block in question should only be hit when pandas falls back to sqlite3. What kind of connection are you supplying?

[parthsurati096]

IG, There is no problem at all you can try this solution make you will Dtype correctly

`import pandas as pd
from sqlalchemy import create_engine
from sqlalchemy.types import DOUBLE, String, DateTime

data = pd.DataFrame({
'timestamp': pd.date_range(start='2023-01-01', periods=3, freq='D'),
'value': [1.23, 4.56, 5.567],
'category': ['A', 'B', 'C']
})

data_check_DateTime = pd.DataFrame({
'timestamp': pd.date_range(start='2023-01-01', periods=3, freq='D')
})

data_double = pd.DataFrame({
'value': [1.23, 4.56, 5.567]
})

Create a dummy SQLite engine

engine = create_engine('sqlite:///:memory:')

column_types_filtered_data = {
'timestamp': DateTime(),
'value': DOUBLE(),
'category': String()
}

Writing DataFrame to SQL

data.to_sql('my_table', con=engine, if_exists='replace', index=False, dtype=column_types_filtered_data)

result_data = pd.read_sql('SELECT * FROM my_table', con=engine)
print(result_data)

column_types_filtered_data_datetime = {'timestamp': DateTime()}
data_check_DateTime.to_sql('my_table_datetime', con=engine, if_exists='replace', index=False, dtype=column_types_filtered_data_datetime)

Verify the DateTime DataFrame

result_datetime = pd.read_sql('SELECT * FROM my_table_datetime', con=engine)
print(result_datetime)

Writing the DOUBLE-only DataFrame to SQL

column_types_filtered_data_double = {'value': DOUBLE()}
data_double.to_sql('my_table_double', con=engine, if_exists='replace', index=False, dtype=column_types_filtered_data_double)

Verify the DOUBLE DataFrame

result_double = pd.read_sql('SELECT * FROM my_table_double', con=engine)
print(result_double)
`

[AnkitPrasad364]

Pandas version checks
• I have checked that this issue has not already been reported.
• I have confirmed this bug exists on the latest version of pandas.
• I have confirmed this bug exists on the main branch of pandas.
import pandas as pd
from sqlalchemy.types import DOUBLE

Example DataFrame with timestamp, float, and string columns

data = pd.DataFrame({
'timestamp': pd.date_range(start='2023-01-01', periods=3, freq='D'),
'value': [1.23, 4.56, 5.67],
'category': ['A', 'B', 'C']
})

Using SQLAlchemy data types

column_types_filtered_data = {col: DOUBLE() for col in data.columns}

This will raise an error if using a raw sqlite3 connection

data.to_sql('my_table', con='sqlite3_connection_here', dtype=column_types_filtered_data)

Issue Description

When using a raw DB-API connection (e.g., sqlite3.connect()), passing a non-string type like sqlalchemy.types.DOUBLE() to to_sql(dtype=...) raises this error:

ValueError: column_name (<sqlalchemy.sql.sqltypes.DOUBLE object>) not a string

This happens due to the following code in pandas.io.sql:

for col, my_type in dtype.items():
if not isinstance(my_type, str):
raise ValueError(f"{col} ({my_type}) not a string")

This block is only executed when pandas falls back to sqlite3 (or other DB-API connections without SQLAlchemy).

Expected Behavior

If SQLAlchemy types are unsupported with raw DB-API connections, this should be:
• Documented clearly in the to_sql API docs.
• Possibly provide a more informative error message suggesting the use of SQLAlchemy.

Alternatively, a fallback mechanism could convert known SQLAlchemy types into SQL strings automatically.

Update the documentation for to_sql() to specify:
• If using a SQLAlchemy engine, dtype can include SQLAlchemy types (DOUBLE(), String(), etc.).
• If using a DB-API connection, dtype must contain SQL string types ("TEXT", "FLOAT").

[rhshadrach]

Documented clearly in the to_sql API docs.

The API docs page says that the argument must be strings for sqlite3 legacy mode

Possibly provide a more informative error message suggesting the use of SQLAlchemy.

The error message says the argument must be strings.

Alternatively, a fallback mechanism could convert known SQLAlchemy types into SQL strings automatically.

I'm negative on this - it is best that such logic is not owned by pandas.