gordthompson

import pandas as pd
import sqlalchemy as sa
from sqlalchemy_cockroachdb.transaction import run_transaction

engine = sa.create_engine(
    "cockroachdb+psycopg2://root@localhost:26257/defaultdb"
)


def crdb_insert_trans(table, eng, keys, data_iter):

gordthompson

import pandas as pd
import sqlalchemy as sa
from sqlalchemy_cockroachdb.transaction import run_transaction

connection_url = "cockroachdb+psycopg2://root@localhost:26257/defaultdb"
connection_url = "postgresql+psycopg2://scott:tiger@192.168.0.199/test"
engine = sa.create_engine(connection_url)


def read_sql_qry_trans(qry, engine_):

gordthompson

"""
A DataFrame that contains a lot of "null" values (NaN, etc.) can cause a
significant performance penalty with fast_executemany=True. For details, see

https://github.com/mkleehammer/pyodbc/issues/741

This routine scans the specified DataFrame to see how many "row scans" would be
required when using .to_sql() with fast_executemany=True. The first row must
always be scanned, but after that a re-scan will flush the current rows to the
server, increasing network traffic and slowing things down.

gordthompson

# version 1.2 - 2022-10-04
import pandas as pd
import sqlalchemy as sa


def df_upsert(data_frame, table_name, engine, schema=None, match_columns=None):
    """
    Perform an "upsert" on a PostgreSQL table from a DataFrame.

    Constructs an INSERT … ON CONFLICT statement, uploads the DataFrame to a

gordthompson

# Copyright 2023 Gordon D. Thompson, gord@gordthompson.com
#
# Licensed under the Apache License, Version 2.0 (the "License");
# you may not use this file except in compliance with the License.
# You may obtain a copy of the License at
#
#     http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,

gordthompson

# Alternative to_sql() *method* for mssql+pyodbc or mssql+pymssql
#
# adapted from https://pandas.pydata.org/docs/user_guide/io.html#io-sql-method
#
# version 1.6 - 2024-03-28
from datetime import date
import json

import pandas as pd
import sqlalchemy as sa

gordthompson

/*
 * Copyright 2017 Gordon D. Thompson
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *     http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software