Keerthesre1 opened a new issue, #29666:
URL: https://github.com/apache/doris/issues/29666
from sqlalchemy import create_engine, text
import pandas as pd
def create_table(engine, csv_file_path):
# Read the CSV file to get column names and data types
df = pd.read_csv(csv_file_path, nrows=1)
columns_and_types = [(column, dtype) for column, dtype in
zip(df.columns, df.dtypes.astype(str))]
# Construct the SQL query for creating the table
column_definitions = ", ".join([f'{col} {dtype}' for col, dtype in
columns_and_types])
create_table_query = (
f'CREATE TABLE my_doris_database.test_table1 '
f'({column_definitions}) '
f'UNIQUE KEY({", ".join([f"{col}" for col, _ in
columns_and_types])}) '
f'DISTRIBUTED BY HASH("{columns_and_types[0][0]}") BUCKETS 32 '
'PROPERTIES ("replication_num" = "1", "in_memory" = "false")'
)
# Execute the query to create the table
with engine.connect() as connection:
connection.execute(text(create_table_query))
print("Table 'test_table' created successfully.")
def insert_data_from_csv(engine, csv_file_path):
# Read CSV file into a DataFrame
df = pd.read_csv(csv_file_path)
# Define the SQL query to insert data into the table with placeholders
insert_data_query = text(f'INSERT INTO my_doris_database.test_table1
({", ".join(df.columns)}) VALUES ({", ".join([f":{param}" for param in
df.columns])})')
# Convert DataFrame to list of dictionaries
data_to_insert = df.to_dict(orient='records')
# Insert data into the table
with engine.connect() as connection:
connection.execute(insert_data_query, data_to_insert)
print(f"{len(data_to_insert)} rows of data inserted into 'test_table'
successfully.")
# Create an SQLAlchemy engine
engine = create_engine('doris://****:****@*******/my_doris_database')
# Specify the path to the CSV file
csv_file_path = '/Users/mac/Downloads/data123.csv'
# Create the table with dynamic columns from CSV
create_table(engine, csv_file_path)
# Insert data from CSV into the table
insert_data_from_csv(engine, csv_file_path)
Help me with completing this script properly so that using a csv file i will
be able to create table and then insert data.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
