search

blaze / odo

Data Migration for the Blaze Project
http://odo.readthedocs.org/
BSD 3-Clause "New" or "Revised" License
1.01k stars 138 forks source link

to import csv file to a table in mysql, should it already exist? #547

Open kunchamharsha opened 7 years ago

kunchamharsha commented 7 years ago

i am trying to build a dynamic table uploaded by a user using odo but the table is not being created dynamically. is there any inbuilt method i can use or should i resort to manually creating the table and then inserting it. datashape = discover(resource(csvfilepath)) uri=mysql+pymysql://'+username]+':'+password+'@localhost/'+databasename+"::"+tablename x=odo(csvfilepath,uri,dshape=datashape) this gives me an error that database.tablename doesnt exist

necaris commented 7 years ago

With a simple three-column CSV I have no trouble doing this with SQLite:

>>> odo.odo('demo.csv', 'sqlite:///demo.db::demo')
Table('demo', MetaData(bind=Engine(sqlite:///demo.db)), Column('0', Text(), table=<demo>), Column('1', BigInteger(), table=<demo>, nullable=False), Column('2', Text(), table=<demo>), schema=None)

This creates the SQLite database file and table as expected. The process would be the same for MySQL.

Please double check that the database you are trying to insert into exists, and the user that odo is running as has permission to create tables in it. Those could be causes of the error message you're seeing.

kunchamharsha commented 7 years ago

So the tables that are being created are dynamic generated based on the user input csv file, the user connecting the database is root. @necaris I am looking for a generic solution

necaris commented 7 years ago

@kunchamharsha It's not clear to me what you mean. Which part of the table is dynamic? The name? The schema? The existence of the table? All three?

If you had a program which would accept a user's CSV file, then based on its name create a corresponding table in the database, then what you describe should work providing that:

Of course, I would never advise connecting to a database as root unless you really have to, and especially not from a script -- consider creating a user that has permissions only on the database you want it to use.

kunchamharsha commented 7 years ago

so this is user flow, user uploads csv file, we analyse the csv and create a csv schema and then create the table and upload the content. I am currently using pandas dataframe but it is pretty slow.

necaris commented 7 years ago

If you have a smaller sample of the code that is not working and a full traceback, please provide them.