# COPY FROM using python http module as source .csv

**URL:** <https://community.cratedb.com/t/copy-from-using-python-http-module-as-source-csv/1672>\
**Category:** Community\
**Tags:** fundamentals\
**Created:** [December 14, 2023, 12:55am UTC](https://community.cratedb.com/t/copy-from-using-python-http-module-as-source-csv/1672 "2023-12-14T00:55:25Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![bfmcneill](https://avatars.discourse-cdn.com/v4/letter/b/ba9def/32.png) [@bfmcneill](https://community.cratedb.com/u/bfmcneill)\
**Post date:** [December 14, 2023, 12:55am UTC](https://community.cratedb.com/t/copy-from-using-python-http-module-as-source-csv/1672/1 "2023-12-14T00:55:25Z")

</div>

Hello,

I was trying to get COPY FROM using `http://192.168.252.1:8000/lr-sql-scada/output_sqlt_data_1_2017_11.csv'` which is the ip of my host. The CrateDb is running as a shard with docker compose on the same host.

I `cd` to the directory of my csv data and start the python http server.

```bash
cd /my/data/dir
python -m http.server 8000

```

- verified my webserver shows GET requests in the log when I triggered `copy from` statement

- I verified I can manually access the files in my browser and copy the link.

when i process the query it shows success but no records changed

 ![query screen](https://us1.discourse-cdn.com/flex020/uploads/crate/original/2X/2/25856a7fc12a29a475f3712417efa32c00a9e48d.png)

When I manually download that csv file using the same link in my browser it transmits at ~500 mb/s.

---

<div class="post-metadata">

**Author:** ![amotl](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/amotl/32/617_2.png) [@amotl](https://community.cratedb.com/u/amotl)\
**Post date:** [December 14, 2023, 8:25am UTC](https://community.cratedb.com/t/copy-from-using-python-http-module-as-source-csv/1672/2 "2023-12-14T08:25:23Z")

</div>

Dear Ben,

welcome to the forum, and thank you for reporting your problem.

May I ask you to append the `RETURN SUMMARY` clause to your `COPY FROM` statement? It might give you a clue already about what is going wrong.

For more information about that topic, see also the excellent [Fundamentals of the COPY FROM statement](https://community.cratedb.com/t/fundamentals-of-the-copy-from-statement/1178) document by @Marija.

This could actually be the cause of the problem:

> [@Fundamentals of the COPY FROM statement](https://community.cratedb.com/t/fundamentals-of-the-copy-from-statement/1178/1):
>
> CrateDB accepts files in JSON or CSV formats. Please have in mind that **if the format is not specified** , the file will proceed as JSON.

With kind regards,  
Andreas.

---

<div class="post-metadata">

**Author:** ![bfmcneill](https://avatars.discourse-cdn.com/v4/letter/b/ba9def/32.png) [@bfmcneill](https://community.cratedb.com/u/bfmcneill)\
**Post date:** [December 14, 2023, 2:23pm UTC](https://community.cratedb.com/t/copy-from-using-python-http-module-as-source-csv/1672/3 "2023-12-14T14:23:47Z")

</div>

Thanks for the reply, it looks like a connection is refused. Perhaps it is some kind of issue with windows firewall.

 ![error with return summary](https://us1.discourse-cdn.com/flex020/uploads/crate/original/2X/c/c6d1c0ffd5532eb700dbf7b11c7c1e4bba197c30.png)

---

<div class="post-metadata">

**Author:** ![amotl](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/amotl/32/617_2.png) [@amotl](https://community.cratedb.com/u/amotl)\
**Post date:** [December 14, 2023, 2:36pm UTC](https://community.cratedb.com/t/copy-from-using-python-http-module-as-source-csv/1672/4 "2023-12-14T14:36:37Z")

</div>

Hi again,

in your original post, you shared a non-TLS URL with us, starting with `http://192.168.252.1/`. Now, in your recent example, the URL shows up as `https://localhost:4443/`.

=\> Are you sure this endpoint is serving a sound X.509 certificate for `localhost:4443`, which the client can successfully verify on all details?

I don’t think `COPY FROM` accepts any parameters to relax the X.509 verification \[1\], so it is not unlikely to fail like this when there is a wrong certificate. A _Connection refused_ exception, in one way or another, would actually be expected in this case.

With kind regards,  
Andreas.

* * *

1. [COPY FROM — CrateDB: Reference](https://cratedb.com/docs/crate/reference/en/5.5/sql/statements/copy-from.html#with)

---

<div class="post-metadata">

**Author:** ![bfmcneill](https://avatars.discourse-cdn.com/v4/letter/b/ba9def/32.png) [@bfmcneill](https://community.cratedb.com/u/bfmcneill)\
**Post date:** [December 14, 2023, 2:43pm UTC](https://community.cratedb.com/t/copy-from-using-python-http-module-as-source-csv/1672/5 "2023-12-14T14:43:38Z")

</div>

I may have pulled a fast one on my self with that https.

This is the simplified version where I just call `python -m http.serve` when i invoke `submit query` on the cratedb ui with this method i see logs on the http.serve are showing status code 200.

 ![with return summary](https://us1.discourse-cdn.com/flex020/uploads/crate/original/2X/9/9b0a715474422d0096eaf704f2455c18f422af14.png)

---

<div class="post-metadata">

**Author:** ![bfmcneill](https://avatars.discourse-cdn.com/v4/letter/b/ba9def/32.png) [@bfmcneill](https://community.cratedb.com/u/bfmcneill)\
**Post date:** [December 14, 2023, 2:45pm UTC](https://community.cratedb.com/t/copy-from-using-python-http-module-as-source-csv/1672/6 "2023-12-14T14:45:29Z")

</div>

I tried with all the adapters. Sorry I think I need to be posting my issues in a networking forum. In the mean time I got the execute many script to run and supposedly that is way faster?

 ![cmd with 200 success](https://us1.discourse-cdn.com/flex020/uploads/crate/original/2X/f/fe35f03b7f08d8427ce89791e58f71e9fbc69efa.png)

---

<div class="post-metadata">

**Author:** ![amotl](https://sea2.discourse-cdn.com/flex020/user_avatar/community.cratedb.com/amotl/32/617_2.png) [@amotl](https://community.cratedb.com/u/amotl)\
**Post date:** [December 14, 2023, 3:09pm UTC](https://community.cratedb.com/t/copy-from-using-python-http-module-as-source-csv/1672/7 "2023-12-14T15:09:07Z")

</div>

Hi again,

that’s weird. Maybe you can share a sample of your data?

> In the mean time I got the execute many script to run and supposedly that is way faster?

I am not sure what you are referring to, but it reads like you have been successful on some other end?

Please let me know if you are still struggling, and maybe share a data sample, so I can have a closer look.

With kind regards,  
Andreas.

---

<div class="post-metadata">

**Author:** ![bfmcneill](https://avatars.discourse-cdn.com/v4/letter/b/ba9def/32.png) [@bfmcneill](https://community.cratedb.com/u/bfmcneill)\
**Post date:** [December 14, 2023, 3:20pm UTC](https://community.cratedb.com/t/copy-from-using-python-http-module-as-source-csv/1672/8 "2023-12-14T15:20:35Z")

</div>

I did get a solution working with the crate python client using execute many. still curious about the loader feature on web ui. It says new users cant load attachments.

```auto
t_stamp,entity,float_value,quality
1654064489856,site_a/equipment_01,512.000,196
1654064939895,site_a/equipment_01,463.500,196
1654066289969,site_a/equipment_01,586.500,196
1654066740002,site_a/equipment_01,491.167,196
1654068090078,site_a/equipment_01,622.714,196
1654068540111,site_a/equipment_01,701.667,196
1654069890183,site_a/equipment_01,329.200,196
1654070340222,site_a/equipment_01,342.833,196
1654071690296,site_a/equipment_02,309.769,196
1654072140345,site_a/equipment_02,1357.333,196
1654073490413,site_a/equipment_02,359.000,196
1654073940438,site_a/equipment_02,317.091,196
1654075290520,site_a/equipment_02,141.769,196
1654075740547,site_a/equipment_02,408.300,196
1654077090628,site_a/equipment_02,163.250,196
1654077540664,site_a/equipment_02,122.500,196
1654078890748,site_a/equipment_02,209.000,196
1654079340773,site_a/equipment_02,540.167,196
1654080690847,site_a/equipment_02,303.545,196

```

this is my create db ddl

```sql
CREATE TABLE spot_data_history (
"t_stamp" TIMESTAMP,
"entity" TEXT,
"float_value" DOUBLE,
"quality" INT,
PRIMARY KEY (t_stamp,entity)
);

```

The pipfile

```auto
[[source]]
url = "https://pypi.org/simple"
verify_ssl = true
name = "pypi"

[packages]
sqlalchemy = "*"
pyodbc = "*"
pandas = "*"
dynaconf = "*"
loguru = "*"
crate = {extras = ["sqlalchemy"], version = "*"}
pendulum = "*"

[dev-packages]
black = "*"

[requires]
python_version = "3.10"

```

The ingest

```python
from pathlib import Path
import csv
from crate import client
from loguru import logger # Add loguru for logging

connection = client.connect("http://localhost:4201")
cursor = connection.cursor()
sql = "INSERT INTO spot_data_history values (?,?,?,?)"

data_dir = Path( __file__ ).parent / "data"

csv_files = (data_dir).glob('*.csv')

for csv_file in csv_files:

    with open(csv_file, "r") as file:
        reader = csv.reader(file)
        next(reader) # Skip the header row
        

        chunk_size = 10_000 # Define your chunk size
        chunk = []

        for idx, row in enumerate(reader):
            # csv may have empty lines
            if len(row) == 0:
                continue

            t_stamp, entity, float_value, quality = row
            chunk.append([t_stamp, entity, float_value, quality])

            if len(chunk) >= chunk_size:
                logger.info("inserting...")
                cursor.executemany(sql, chunk)
                chunk = [] # Clear the chunk after insertion

    if chunk: # Insert any remaining rows
        cursor.executemany(sql, chunk)

cursor.close()
connection.close()

```

the docker compose used to spin up local crate db cluster

```yaml
version: '3.8'
services:
  cratedb01:
    image: crate:latest
    ports:
      - "4201:4200"
      - "5432:5432"      
    volumes:
      - ./data/crate/01:/data
    command: ["crate",
              "-Ccluster.name=crate-docker-cluster",
              "-Cnode.name=cratedb01",
              "-Cnode.data=true",
              "-Cnetwork.host=_site_",
              "-Cdiscovery.seed_hosts=cratedb02,cratedb03",
              "-Ccluster.initial_master_nodes=cratedb01,cratedb02,cratedb03",
              "-Cgateway.expected_data_nodes=3",
              "-Cgateway.recover_after_data_nodes=2"]
    deploy:
      replicas: 1
      restart_policy:
        condition: on-failure
    environment:
      - CRATE_HEAP_SIZE=2g

  cratedb02:
    image: crate:latest
    ports:
      - "4202:4200"
    volumes:
      - ./data/crate/02:/data
    command: ["crate",
              "-Ccluster.name=crate-docker-cluster",
              "-Cnode.name=cratedb02",
              "-Cnode.data=true",
              "-Cnetwork.host=_site_",
              "-Cdiscovery.seed_hosts=cratedb01,cratedb03",
              "-Ccluster.initial_master_nodes=cratedb01,cratedb02,cratedb03",
              "-Cgateway.expected_data_nodes=3",
              "-Cgateway.recover_after_data_nodes=2"]
    deploy:
      replicas: 1
      restart_policy:
        condition: on-failure
    environment:
      - CRATE_HEAP_SIZE=2g

  cratedb03:
    image: crate:latest
    ports:
      - "4203:4200"
    volumes:
      - ./data/crate/03:/data
    command: ["crate",
              "-Ccluster.name=crate-docker-cluster",
              "-Cnode.name=cratedb03",
              "-Cnode.data=true",
              "-Cnetwork.host=_site_",
              "-Cdiscovery.seed_hosts=cratedb01,cratedb02",
              "-Ccluster.initial_master_nodes=cratedb01,cratedb02,cratedb03",
              "-Cgateway.expected_data_nodes=3",
              "-Cgateway.recover_after_data_nodes=2"]
    deploy:
      replicas: 1
      restart_policy:
        condition: on-failure
    environment:
      - CRATE_HEAP_SIZE=2g

```