[I] write me a python Using CSV file to Create table and then insert data into the table from that csv file in APACHE DORIS to the database i created. [doris]

["Keerthesre1 (via GitHub)" <gi...@apache.org>]

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: commits-unsubscribe@doris.apache.org.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org

Re: [I] write me a python script Using CSV file to Create table and then insert data into the table from that csv file in APACHE DORIS to the database i created. [doris]

["morningman (via GitHub)" <gi...@apache.org>]

morningman closed issue #29666: write me a python script Using CSV file to Create table and then insert data into the table from that csv file in APACHE DORIS to the database i created.
URL: https://github.com/apache/doris/issues/29666


-- 
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: commits-unsubscribe@doris.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org

Re: [I] write me a python script Using CSV file to Create table and then insert data into the table from that csv file in APACHE DORIS to the database i created. [doris]

["github-actions[bot] (via GitHub)" <gi...@apache.org>]

github-actions[bot] commented on issue #29666:
URL: https://github.com/apache/doris/issues/29666#issuecomment-3287227649

   We're closing this issue because it hasn't been updated in a while.
   If you'd like to revive this issue, please reopen it and feel free a maintainer to remove the Stale tag!


-- 
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: commits-unsubscribe@doris.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org