API to_sql method doesn't provide the option to specify unique indexes

[manistal]

This could very well be something that just isn't clear to me from reading the docs, but I would like to have my index be unique when I dump the dataframe to SQLite table so that when I append more data I don't get duplicate rows. Here's what my resulting scheme looks like after using the to_sql function

sqlite> .schema
CREATE TABLE "Sniffs" (
"ID" TEXT,
"Day" TEXT,
"ModelName" TEXT,
"PassRate" FLOAT,
"RtxName" TEXT,
"Shadow" TEXT,
"Time" TEXT,
"Unit" TEXT
);
CREATE INDEX "ix_Sniffs_ID" ON "Sniffs" ("ID");

Looking at these docs:
http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.to_sql.html#pandas.DataFrame.to_sql

My resulting call looks something like this:
sniff_df.to_sql('Sniffs', sqleng, if_exists='append', index_label='ID')

Am I reading the docs wrong or anything/is there currently a method to get unique indexes in the output sql to avoid overlapping data? Could we get a kwarg like: unique_index=True ?

[ja-b]

+1

[artemyk]

So, I believe pandas already provides df.index.is_unique, which could be used to automatically set UNIQUE for indices. Perhaps a kwarg to suppress such behavior would be more helpful.

Or perhaps there should be a more powerful mechanism (with a sensical set of defaults) to specify in a unified manner which indices to create, whether they should be unique, and if so whether they should be PRIMARY KEYs. Perhaps an argument that can specify the set of constraints/indices/primary keys/foreign keys to pass onto the SQLAlchemy Table() constructor? (we could also have an argument with a dict that, for diff columns, specifies the kwargs to pass onto the SQLAlchemy Column() constructor; this could generalize what we do with dtypes now)

@jorisvandenbossche Thoughts?

[femtotrader]

+1 this feature will be great

[xingren23]

+1

[icyblade]

Please add support for primary key and indexes.
I'm using Pandas for data processing and Tableau for visualization, but Tableau sucks when processing data without an index.