Inserting dataframe from Python into Snowflake

Question

When I try to insert dataframe into a table in snowflake using the writes_pandas function, only null values ​​appear in the table. I was trying to create a table that does not contain null values, but when I made code that inserts dataframe into Snowflake, it reported an error that NULL values ​​cannot be inserted into a table that stores NOT NULL values. Can someone maybe help me solve the problem? I checked, the dataframe in Python is not null.

import os
import csv
import pandas as pd
import snowflake.connector
import glob
from snowflake.connector.pandas_tools import write_pandas

ctx = snowflake.connector.connect(
   user='****',
   password='****',
   account='****'

)
cs= ctx.cursor()

def split(filehandler, keep_headers=True):
    reader = csv.reader(filehandler, delimiter=',')

    """
        Function split the file on row # basics
    """

    # Variable declartion:
    row_limit = 1000
    output_name_template = 'output_%s.csv'
    output_path = r'C:\Users\Nenad\Desktop\Data\mix'

    current_piece = 1
    current_out_path = os.path.join(
        output_path,
        output_name_template % current_piece
    )
    current_out_writer = csv.writer(open(current_out_path, 'w'), delimiter=',')
    current_limit = row_limit
    if keep_headers:
        headers = next(reader)
        current_out_writer.writerow(headers)
    for i, row in enumerate(reader):
        if i + 1 > current_limit:
            current_piece += 1
            current_limit = row_limit * current_piece
            current_out_path = os.path.join(
                output_path,
                output_name_template % current_piece
            )
            current_out_writer = csv.writer(open(current_out_path, 'w'), delimiter=',')
            if keep_headers:
                current_out_writer.writerow(headers)
        current_out_writer.writerow(row)


if __name__ == "__main__":
    print("file split Begins")
    split(open(r"C:\Users\Nenad\PycharmProjects\untitled15\bigtable_py.csv"))
    print("File split Ends")


os.chdir(r'C:\Users\Nenad\Desktop\Data\mix')
file_extension=".csv"
all_filenames = [i for i in glob.glob(f"*{file_extension}")]
sql = "USE role ACCOUNTADMIN" 
cs.execute(sql)
sql = "SELECT CURRENT_ROLE()"
cs.execute(sql)

for file in all_filenames:
 df=pd.read_csv(file, delimiter=',')
 cs.execute("USE DRAGANA")
 write_pandas(ctx, df, 'TABLES')

Answer 1

write_pandas() seems to have issues interpreting the dtypes of fields in DataFrame. SQLalchemy performs a much better job. I suggest using a snowflake sqlalchemy and get a sqlalchemy engine. Using DataFame.to_sql() solved most issues I personaaly had.