Skip to content

Compatibility with Pandas #36

Open
Open
Compatibility with Pandas#36
@alitaker

Description

@alitaker
alitaker
opened on May 25, 2026

pd.to_sql() can not create a new collection or replace it.

When creating a collection with the following code

    from sqlalchemy import create_engine
    import pymongosql
    import logging
    logging.basicConfig(level=logging.DEBUG)
    logging.getLogger('sqlalchemy.engine').setLevel(logging.INFO)

    df = pd.read_csv('mycollection001.csv'), delimiter=';', header=0, skip_blank_lines=True)
    print(df.head())
    conn = create_engine("mongodb://admin:testdb@127.0.0.1:27017/testdb?authSource=admin&connectTimeoutMS=300")
    df.iloc[0].to_sql('mycollection001', conn, index=False, if_exists='append')

It returns the error:

sqlalchemy.exc.DBAPIError: (pymongosql.error.SqlSyntaxError) SQL parsing failed: Syntax error at line 1, column 45: mismatched input
[...]
[SQL: -- Collection will be created on first insert]

After creating the collection manually, it works as expected even with multiple rows:

    df.to_sql('mycollection001', conn, index=False, if_exists='append')

Here is the complete log:

DEBUG:pymongosql.helper:Parsed connection string - Mode: standard, Database: testdb
DEBUG:pymongo.topology:{"message": "Starting topology monitoring", "topologyId": {"$oid": "6a1459113872c2f1f12bfb33"}}
DEBUG:pymongo.topology:{"message": "Topology description changed", "topologyId": {"$oid": "6a1459113872c2f1f12bfb33"}, "previousDescription": "<TopologyDescription id: 6a1459113872c2f1f12bfb33, topology_type: Unknown, servers: []>", "newDescription": "<TopologyDescription id: 6a1459113872c2f1f12bfb33, topology_type: Unknown, servers: [<ServerDescription ('127.0.0.1', 27017) server_type: Unknown, rtt: None>]>"}
DEBUG:pymongo.topology:{"message": "Starting server monitoring", "topologyId": {"$oid": "6a1459113872c2f1f12bfb33"}, "serverHost": "127.0.0.1", "serverPort": 27017}
DEBUG:pymongo.connection:{"message": "Connection pool created", "clientId": {"$oid": "6a1459113872c2f1f12bfb33"}, "serverHost": "127.0.0.1", "serverPort": 27017}
DEBUG:pymongo.serverSelection:{"message": "Server selection started", "selector": "Primary()", "operation": "ping", "topologyDescription": "<TopologyDescription id: 6a1459113872c2f1f12bfb33, topology_type: Unknown, servers: [<ServerDescription ('127.0.0.1', 27017) server_type: Unknown, rtt: None>]>", "clientId": {"$oid": "6a1459113872c2f1f12bfb33"}}
DEBUG:pymongo.serverSelection:{"message": "Waiting for suitable server to become available", "selector": "Primary()", "operation": "ping", "topologyDescription": "<TopologyDescription id: 6a1459113872c2f1f12bfb33, topology_type: Unknown, servers: [<ServerDescription ('127.0.0.1', 27017) server_type: Unknown, rtt: None>]>", "clientId": {"$oid": "6a1459113872c2f1f12bfb33"}, "remainingTimeMS": 29999}
DEBUG:pymongo.topology:{"message": "Server heartbeat started", "topologyId": {"$oid": "6a1459113872c2f1f12bfb33"}, "driverConnectionId": 1, "serverHost": "127.0.0.1", "serverPort": 27017, "awaited": false}
DEBUG:pymongo.topology:{"message": "Server heartbeat succeeded", "topologyId": {"$oid": "6a1459113872c2f1f12bfb33"}, "driverConnectionId": 1, "serverConnectionId": 111971, "serverHost": "127.0.0.1", "serverPort": 27017, "awaited": false, "durationMS": 1.7358339973725379, "reply": "{\"helloOk\": true, \"ismaster\": true, \"topologyVersion\": {\"processId\": {\"$oid\": \"6a105252faef4a0dd66935b4\"}}, \"maxBsonObjectSize\": 16777216, \"maxMessageSizeBytes\": 48000000, \"maxWriteBatchSize\": 100000, \"localTime\": {\"$date\": \"2026-05-25T14:13:37.021Z\"}, \"logicalSessionTimeoutMinutes\": 30, \"connectionId\": 111971, \"maxWireVersion\": 28, \"ok\": 1.0}"}
DEBUG:pymongo.connection:{"message": "Connection pool ready", "clientId": {"$oid": "6a1459113872c2f1f12bfb33"}, "serverHost": "127.0.0.1", "serverPort": 27017}
DEBUG:pymongo.topology:{"message": "Topology description changed", "topologyId": {"$oid": "6a1459113872c2f1f12bfb33"}, "previousDescription": "<TopologyDescription id: 6a1459113872c2f1f12bfb33, topology_type: Unknown, servers: [<ServerDescription ('127.0.0.1', 27017) server_type: Unknown, rtt: None>]>", "newDescription": "<TopologyDescription id: 6a1459113872c2f1f12bfb33, topology_type: Single, servers: [<ServerDescription ('127.0.0.1', 27017) server_type: Standalone, rtt: 0.0017358339973725379>]>"}
DEBUG:pymongo.serverSelection:{"message": "Server selection succeeded", "selector": "Primary()", "operation": "ping", "topologyDescription": "<TopologyDescription id: 6a1459113872c2f1f12bfb33, topology_type: Single, servers: [<ServerDescription ('127.0.0.1', 27017) server_type: Standalone, rtt: 0.0017358339973725379>]>", "clientId": {"$oid": "6a1459113872c2f1f12bfb33"}, "serverHost": "127.0.0.1", "serverPort": 27017}
DEBUG:pymongo.topology:{"message": "Server heartbeat started", "topologyId": {"$oid": "6a1459113872c2f1f12bfb33"}, "driverConnectionId": 1, "serverConnectionId": 111971, "serverHost": "127.0.0.1", "serverPort": 27017, "awaited": true}
DEBUG:pymongo.connection:{"message": "Connection checkout started", "clientId": {"$oid": "6a1459113872c2f1f12bfb33"}, "serverHost": "127.0.0.1", "serverPort": 27017}
DEBUG:pymongo.connection:{"message": "Connection created", "clientId": {"$oid": "6a1459113872c2f1f12bfb33"}, "serverHost": "127.0.0.1", "serverPort": 27017, "driverConnectionId": 1}
DEBUG:pymongo.connection:{"message": "Connection ready", "clientId": {"$oid": "6a1459113872c2f1f12bfb33"}, "serverHost": "127.0.0.1", "serverPort": 27017, "driverConnectionId": 1, "durationMS": 0.011266257963143289}
DEBUG:pymongo.connection:{"message": "Connection checked out", "clientId": {"$oid": "6a1459113872c2f1f12bfb33"}, "serverHost": "127.0.0.1", "serverPort": 27017, "driverConnectionId": 1, "durationMS": 0.012126841000281274}
DEBUG:pymongo.command:{"message": "Command started", "clientId": {"$oid": "6a1459113872c2f1f12bfb33"}, "command": "{\"ping\": 1, \"lsid\": {\"id\": {\"$binary\": {\"base64\": \"ZnATLEMJSgCTuHQ3UD+mHQ==\", \"subType\": \"04\"}}}, \"$db\": \"admin\"}", "commandName": "ping", "databaseName": "admin", "requestId": 424238335, "operationId": 424238335, "driverConnectionId": 1, "serverConnectionId": 111973, "serverHost": "127.0.0.1", "serverPort": 27017}
DEBUG:pymongo.command:{"message": "Command succeeded", "clientId": {"$oid": "6a1459113872c2f1f12bfb33"}, "durationMS": 0.531, "reply": "{\"ok\": 1.0}", "commandName": "ping", "databaseName": "admin", "requestId": 424238335, "operationId": 424238335, "driverConnectionId": 1, "serverConnectionId": 111973, "serverHost": "127.0.0.1", "serverPort": 27017}
DEBUG:pymongo.connection:{"message": "Connection checked in", "clientId": {"$oid": "6a1459113872c2f1f12bfb33"}, "serverHost": "127.0.0.1", "serverPort": 27017, "driverConnectionId": 1}
INFO:pymongosql.connection:Successfully connected to MongoDB at mongodb://admin:pwd@127.0.0.1:27017/testdb?authSource=admin&connectTimeoutMS=300:27017
INFO:sqlalchemy.engine.Engine:BEGIN (implicit)
DEBUG:pymongo.serverSelection:{"message": "Server selection started", "selector": "Primary()", "operation": "listCollections", "topologyDescription": "<TopologyDescription id: 6a1459113872c2f1f12bfb33, topology_type: Single, servers: [<ServerDescription ('127.0.0.1', 27017) server_type: Standalone, rtt: 0.0017358339973725379>]>", "clientId": {"$oid": "6a1459113872c2f1f12bfb33"}}
DEBUG:pymongo.serverSelection:{"message": "Server selection succeeded", "selector": "Primary()", "operation": "listCollections", "topologyDescription": "<TopologyDescription id: 6a1459113872c2f1f12bfb33, topology_type: Single, servers: [<ServerDescription ('127.0.0.1', 27017) server_type: Standalone, rtt: 0.0017358339973725379>]>", "clientId": {"$oid": "6a1459113872c2f1f12bfb33"}, "serverHost": "127.0.0.1", "serverPort": 27017}
DEBUG:pymongo.connection:{"message": "Connection checkout started", "clientId": {"$oid": "6a1459113872c2f1f12bfb33"}, "serverHost": "127.0.0.1", "serverPort": 27017}
DEBUG:pymongo.connection:{"message": "Connection checked out", "clientId": {"$oid": "6a1459113872c2f1f12bfb33"}, "serverHost": "127.0.0.1", "serverPort": 27017, "driverConnectionId": 1, "durationMS": 5.721900379285216e-05}
DEBUG:pymongo.command:{"message": "Command started", "clientId": {"$oid": "6a1459113872c2f1f12bfb33"}, "command": "{\"listCollections\": 1, \"nameOnly\": true, \"lsid\": {\"id\": {\"$binary\": {\"base64\": \"ZnATLEMJSgCTuHQ3UD+mHQ==\", \"subType\": \"04\"}}}, \"$db\": \"testdb\"}", "commandName": "listCollections", "databaseName": "testdb", "requestId": 719885386, "operationId": 719885386, "driverConnectionId": 1, "serverConnectionId": 111973, "serverHost": "127.0.0.1", "serverPort": 27017}
DEBUG:pymongo.command:{"message": "Command succeeded", "clientId": {"$oid": "6a1459113872c2f1f12bfb33"}, "durationMS": 0.744, "reply": "{\"cursor\": {\"ns\": \"testdb.$cmd.listCollections\", \"firstBatch\": [{\"name\": \"persons\", \"type\": \"collection\"}, {\"name\": \"anagrafica\", \"type\": \"collection\"}, {\"name\": \"test\", \"type\": \"collection\"}, {\"name\": \"cars\", \"type\": \"collection\"}]}, \"ok\": 1.0}", "commandName": "listCollections", "databaseName": "testdb", "requestId": 719885386, "operationId": 719885386, "driverConnectionId": 1, "serverConnectionId": 111973, "serverHost": "127.0.0.1", "serverPort": 27017}
DEBUG:pymongo.connection:{"message": "Connection checked in", "clientId": {"$oid": "6a1459113872c2f1f12bfb33"}, "serverHost": "127.0.0.1", "serverPort": 27017, "driverConnectionId": 1}
INFO:sqlalchemy.engine.Engine:-- Collection will be created on first insert
INFO:sqlalchemy.engine.Engine:[no key 0.00007s] ()
DEBUG:pymongosql.executor:Using standard execution for query: -- Collection will be created on first insert
DEBUG:pymongosql.sql.parser:Preprocessed SQL: 
line 1:45 mismatched input '<EOF>' expecting {'ANY', 'AVG', 'BIT_LENGTH', 'CASE', 'CAST', 'CHARACTER_LENGTH', 'CHAR_LENGTH', 'COALESCE', 'COUNT', 'CREATE', 'CURRENT_DATE', 'CURRENT_USER', 'DATE', 'DATETIME', 'DELETE', 'DROP', 'EVERY', 'EXCLUDED', 'EXEC', 'EXISTS', 'EXPLAIN', 'EXTRACT', 'DATE_ADD', 'DATE_DIFF', 'FALSE', 'FROM', 'INSERT', 'LOWER', 'MAX', 'MIN', 'NOT', 'NULL', 'NULLIF', 'OCTET_LENGTH', 'OVERLAY', 'POSITION', 'REPLACE', 'SELECT', 'SET', 'SIZE', 'SOME', 'SUBSTRING', 'SUM', 'TIME', 'TIMESTAMP', 'TRIM', 'TRUE', 'UPDATE', 'UPPER', 'UPSERT', 'VALUES', 'LAG', 'LEAD', 'CAN_CAST', 'CAN_LOSSLESS_CAST', 'MISSING', 'PIVOT', 'REMOVE', 'LIST', 'SEXP', '+', '-', '@', '<<', '[', '{', '(', '?', LITERAL_STRING, LITERAL_INTEGER, LITERAL_DECIMAL, IDENTIFIER, IDENTIFIER_QUOTED, ION_CLOSURE}
ERROR:pymongosql.sql.parser:Syntax error at line 1, column 45: mismatched input '<EOF>' expecting {'ANY', 'AVG', 'BIT_LENGTH', 'CASE', 'CAST', 'CHARACTER_LENGTH', 'CHAR_LENGTH', 'COALESCE', 'COUNT', 'CREATE', 'CURRENT_DATE', 'CURRENT_USER', 'DATE', 'DATETIME', 'DELETE', 'DROP', 'EVERY', 'EXCLUDED', 'EXEC', 'EXISTS', 'EXPLAIN', 'EXTRACT', 'DATE_ADD', 'DATE_DIFF', 'FALSE', 'FROM', 'INSERT', 'LOWER', 'MAX', 'MIN', 'NOT', 'NULL', 'NULLIF', 'OCTET_LENGTH', 'OVERLAY', 'POSITION', 'REPLACE', 'SELECT', 'SET', 'SIZE', 'SOME', 'SUBSTRING', 'SUM', 'TIME', 'TIMESTAMP', 'TRIM', 'TRUE', 'UPDATE', 'UPPER', 'UPSERT', 'VALUES', 'LAG', 'LEAD', 'CAN_CAST', 'CAN_LOSSLESS_CAST', 'MISSING', 'PIVOT', 'REMOVE', 'LIST', 'SEXP', '+', '-', '@', '<<', '[', '{', '(', '?', LITERAL_STRING, LITERAL_INTEGER, LITERAL_DECIMAL, IDENTIFIER, IDENTIFIER_QUOTED, ION_CLOSURE}
ERROR:pymongosql.sql.parser:Failed to generate AST for SQL: -- Collection will be created on first insert
ERROR:pymongosql.sql.parser:Failed to process SQL statement: SQL parsing failed: Syntax error at line 1, column 45: mismatched input '<EOF>' expecting {'ANY', 'AVG', 'BIT_LENGTH', 'CASE', 'CAST', 'CHARACTER_LENGTH', 'CHAR_LENGTH', 'COALESCE', 'COUNT', 'CREATE', 'CURRENT_DATE', 'CURRENT_USER', 'DATE', 'DATETIME', 'DELETE', 'DROP', 'EVERY', 'EXCLUDED', 'EXEC', 'EXISTS', 'EXPLAIN', 'EXTRACT', 'DATE_ADD', 'DATE_DIFF', 'FALSE', 'FROM', 'INSERT', 'LOWER', 'MAX', 'MIN', 'NOT', 'NULL', 'NULLIF', 'OCTET_LENGTH', 'OVERLAY', 'POSITION', 'REPLACE', 'SELECT', 'SET', 'SIZE', 'SOME', 'SUBSTRING', 'SUM', 'TIME', 'TIMESTAMP', 'TRIM', 'TRUE', 'UPDATE', 'UPPER', 'UPSERT', 'VALUES', 'LAG', 'LEAD', 'CAN_CAST', 'CAN_LOSSLESS_CAST', 'MISSING', 'PIVOT', 'REMOVE', 'LIST', 'SEXP', '+', '-', '@', '<<', '[', '{', '(', '?', LITERAL_STRING, LITERAL_INTEGER, LITERAL_DECIMAL, IDENTIFIER, IDENTIFIER_QUOTED, ION_CLOSURE}
DEBUG:pymongosql.cursor:Cursor closed successfully
INFO:sqlalchemy.engine.Engine:COMMIT
Traceback (most recent call last):
  File "/opt/py310e/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1967, in _exec_single_context
    self.dialect.do_execute(
  File "/opt/py310e/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 952, in do_execute
    cursor.execute(statement, parameters)
  File "/opt/py310e/lib/python3.10/site-packages/pymongosql/cursor.py", line 100, in execute
    result = strategy.execute(context, self.connection, parameters)
  File "/opt/py310e/lib/python3.10/site-packages/pymongosql/executor.py", line 376, in execute
    self._execution_plan = self._parse_sql(processed_query)
  File "/opt/py310e/lib/python3.10/site-packages/pymongosql/executor.py", line 105, in _parse_sql
    parser = SQLParser(sql)
  File "/opt/py310e/lib/python3.10/site-packages/pymongosql/sql/parser.py", line 48, in __init__
    self._process_sql()
  File "/opt/py310e/lib/python3.10/site-packages/pymongosql/sql/parser.py", line 77, in _process_sql
    self._generate_ast()
  File "/opt/py310e/lib/python3.10/site-packages/pymongosql/sql/parser.py", line 119, in _generate_ast
    raise SqlSyntaxError(f"SQL parsing failed: {error_summary}")
pymongosql.error.SqlSyntaxError: SQL parsing failed: Syntax error at line 1, column 45: mismatched input '<EOF>' expecting {'ANY', 'AVG', 'BIT_LENGTH', 'CASE', 'CAST', 'CHARACTER_LENGTH', 'CHAR_LENGTH', 'COALESCE', 'COUNT', 'CREATE', 'CURRENT_DATE', 'CURRENT_USER', 'DATE', 'DATETIME', 'DELETE', 'DROP', 'EVERY', 'EXCLUDED', 'EXEC', 'EXISTS', 'EXPLAIN', 'EXTRACT', 'DATE_ADD', 'DATE_DIFF', 'FALSE', 'FROM', 'INSERT', 'LOWER', 'MAX', 'MIN', 'NOT', 'NULL', 'NULLIF', 'OCTET_LENGTH', 'OVERLAY', 'POSITION', 'REPLACE', 'SELECT', 'SET', 'SIZE', 'SOME', 'SUBSTRING', 'SUM', 'TIME', 'TIMESTAMP', 'TRIM', 'TRUE', 'UPDATE', 'UPPER', 'UPSERT', 'VALUES', 'LAG', 'LEAD', 'CAN_CAST', 'CAN_LOSSLESS_CAST', 'MISSING', 'PIVOT', 'REMOVE', 'LIST', 'SEXP', '+', '-', '@', '<<', '[', '{', '(', '?', LITERAL_STRING, LITERAL_INTEGER, LITERAL_DECIMAL, IDENTIFIER, IDENTIFIER_QUOTED, ION_CLOSURE}

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/src/testdb/run_queries_on_database.py", line 518, in <module>
    df.iloc[0].to_sql('mycollection001', conn, index=False, if_exists='append')  # , dtype=dtypes
  File "/opt/py310e/lib/python3.10/site-packages/pandas/util/_decorators.py", line 333, in wrapper
    return func(*args, **kwargs)
  File "/opt/py310e/lib/python3.10/site-packages/pandas/core/generic.py", line 3109, in to_sql
    return sql.to_sql(
  File "/opt/py310e/lib/python3.10/site-packages/pandas/io/sql.py", line 844, in to_sql
    return pandas_sql.to_sql(
  File "/opt/py310e/lib/python3.10/site-packages/pandas/io/sql.py", line 2020, in to_sql
    table = self.prep_table(
  File "/opt/py310e/lib/python3.10/site-packages/pandas/io/sql.py", line 1924, in prep_table
    table.create()
  File "/opt/py310e/lib/python3.10/site-packages/pandas/io/sql.py", line 997, in create
    self._execute_create()
  File "/opt/py310e/lib/python3.10/site-packages/pandas/io/sql.py", line 983, in _execute_create
    self.table.create(bind=self.pd_sql.con)
  File "/opt/py310e/lib/python3.10/site-packages/sqlalchemy/sql/schema.py", line 1288, in create
    bind._run_ddl_visitor(ddl.SchemaGenerator, self, checkfirst=checkfirst)
  File "/opt/py310e/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 2467, in _run_ddl_visitor
    ).traverse_single(element)
  File "/opt/py310e/lib/python3.10/site-packages/sqlalchemy/sql/visitors.py", line 661, in traverse_single
    return meth(obj, **kw)
  File "/opt/py310e/lib/python3.10/site-packages/sqlalchemy/sql/ddl.py", line 1022, in visit_table
    )._invoke_with(self.connection)
  File "/opt/py310e/lib/python3.10/site-packages/sqlalchemy/sql/ddl.py", line 321, in _invoke_with
    return bind.execute(self)
  File "/opt/py310e/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1419, in execute
    return meth(
  File "/opt/py310e/lib/python3.10/site-packages/sqlalchemy/sql/ddl.py", line 187, in _execute_on_connection
    return connection._execute_ddl(
  File "/opt/py310e/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1530, in _execute_ddl
    ret = self._execute_context(
  File "/opt/py310e/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1846, in _execute_context
    return self._exec_single_context(
  File "/opt/py310e/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1986, in _exec_single_context
    self._handle_dbapi_exception(
  File "/opt/py310e/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 2363, in _handle_dbapi_exception
    raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
  File "/opt/py310e/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1967, in _exec_single_context
    self.dialect.do_execute(
  File "/opt/py310e/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 952, in do_execute
    cursor.execute(statement, parameters)
  File "/opt/py310e/lib/python3.10/site-packages/pymongosql/cursor.py", line 100, in execute
    result = strategy.execute(context, self.connection, parameters)
  File "/opt/py310e/lib/python3.10/site-packages/pymongosql/executor.py", line 376, in execute
    self._execution_plan = self._parse_sql(processed_query)
  File "/opt/py310e/lib/python3.10/site-packages/pymongosql/executor.py", line 105, in _parse_sql
    parser = SQLParser(sql)
  File "/opt/py310e/lib/python3.10/site-packages/pymongosql/sql/parser.py", line 48, in __init__
    self._process_sql()
  File "/opt/py310e/lib/python3.10/site-packages/pymongosql/sql/parser.py", line 77, in _process_sql
    self._generate_ast()
  File "/opt/py310e/lib/python3.10/site-packages/pymongosql/sql/parser.py", line 119, in _generate_ast
    raise SqlSyntaxError(f"SQL parsing failed: {error_summary}")
sqlalchemy.exc.DBAPIError: (pymongosql.error.SqlSyntaxError) SQL parsing failed: Syntax error at line 1, column 45: mismatched input '<EOF>' expecting {'ANY', 'AVG', 'BIT_LENGTH', 'CASE', 'CAST', 'CHARACTER_LENGTH', 'CHAR_LENGTH', 'COALESCE', 'COUNT', 'CREATE', 'CURRENT_DATE', 'CURRENT_USER', 'DATE', 'DATETIME', 'DELETE', 'DROP', 'EVERY', 'EXCLUDED', 'EXEC', 'EXISTS', 'EXPLAIN', 'EXTRACT', 'DATE_ADD', 'DATE_DIFF', 'FALSE', 'FROM', 'INSERT', 'LOWER', 'MAX', 'MIN', 'NOT', 'NULL', 'NULLIF', 'OCTET_LENGTH', 'OVERLAY', 'POSITION', 'REPLACE', 'SELECT', 'SET', 'SIZE', 'SOME', 'SUBSTRING', 'SUM', 'TIME', 'TIMESTAMP', 'TRIM', 'TRUE', 'UPDATE', 'UPPER', 'UPSERT', 'VALUES', 'LAG', 'LEAD', 'CAN_CAST', 'CAN_LOSSLESS_CAST', 'MISSING', 'PIVOT', 'REMOVE', 'LIST', 'SEXP', '+', '-', '@', '<<', '[', '{', '(', '?', LITERAL_STRING, LITERAL_INTEGER, LITERAL_DECIMAL, IDENTIFIER, IDENTIFIER_QUOTED, ION_CLOSURE}
[SQL: -- Collection will be created on first insert]
(Background on this error at: https://sqlalche.me/e/20/dbapi)
DEBUG:pymongo.topology:{"message": "Server heartbeat failed", "topologyId": {"$oid": "6a1459113872c2f1f12bfb33"}, "serverHost": "127.0.0.1", "serverPort": 27017, "awaited": true, "durationMS": 501.33782398188487, "failure": "\"_OperationCancelled('operation cancelled')\"", "driverConnectionId": 1}

The same happens after creating the collection manually, then inserting with Pandas, using the replace parameter:

    df.iloc[0].to_sql('mycollection001', conn, index=False, if_exists='replace')

The error changes to:

sqlalchemy.exc.DBAPIError: (pymongosql.error.SqlSyntaxError) SQL parsing failed: Syntax error at line 1, column 29: mismatched input
[...]
[SQL: -- DROP COLLECTION mycollection001]

I guess the SQL returned must be a formally correct SQL statement, or at least must be bypassed by the parser.

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions