Read csv into database SQLite3 ODO Python

Question

I am trying to read in a csv into a new table in a new databased using ODO, SQLite3 and Python.

I am following these guides:

https://media.readthedocs.org/pdf/odo/latest/odo.pdf http://odo.pydata.org/en/latest/perf.html?highlight=sqlite#csv-sqlite3-57m-31s

I am trying the following:

import sqlite3
import csv
from odo import odo

file_path = 'my_path/'
# In this case 'my_path/' is a substitute for my real path

db_name = 'data.sqlite'

conn = sqlite3.connect(file_path + db_name)

This creates a new sqlite file data.sqlite within file_path. I can see it there in the folder.

When I then try to read my csv into this database I get the following error:

csv_path = 'my_path/data.csv'
odo(csv_path, file_path + db_name)
conn.close()

NotImplementedError: Unable to parse uri to data resource: # lists my path

Can you help?

Answer 1

No thanks to the ODO documentation, this succesfully created a new table in a new database and read in the csv file to that database:

import sqlite3
import csv
from odo import odo

# [1]

#  Specify file path
file_path = 'my_path/'
# In this case 'my_path/' is a substitute for my real path

# Specify csv file path and name
csv_path = file_path + 'data.csv'

# Specify database name
db_name = 'data.sqlite'

# Connect to new database
conn = sqlite3.connect(file_path + db_name)

# [2]

# Use Odo to detect the shape and datatype of your csv:
data_shape = discover(resource(csv_path))

# Ready in csv to new table called 'data' within database 'data.sqlite'
odo(pd.read_csv(csv_path), 'sqlite:///' + file_path + 'data.sqlite::data', dshape=data_shape)

# Close database
conn.close()

Sources used in [1]:

https://docs.python.org/2/library/sqlite3.html python odo sql AssertionError: datashape must be Record type, got 0 * {...}

Sources used in [2]:

https://stackoverflow.com/a/41584832/2254228 http://sebastianraschka.com/Articles/2014_sqlite_in_python_tutorial.html#creating-a-new-sqlite-database https://stackoverflow.com/a/33316230/2254228 what is difference between .sqlite and .db file?

The ODO documentation is here (good luck...) https://media.readthedocs.org/pdf/odo/latest/odo.pdf

Answer 2

I found the document in the document website and in github are different. Please use github version as reference.

The

NotImplementedError: Unable to parse uri to data resource

error is mentioned in this section.

You could solve by using

pip install odo[sqlite] or pip install odo[sqlalchemy]

Then you may encounter another error if you use windows and odo 0.5.0:

AttributeError: 'DiGraph object has no attribute 'edge'

Install networkx 1.11 instead of networkx 2.0 could solve this error. (reference)

pip uninstall networkx
pip install networkx==1.11

I hope this will help