0

I have Pandas object created using cross tabulation function

df = pd.crosstab(db['Age Category'], db['Category'])

| Age Category | A | B  | C  | D |
|--------------|---|----|----|---|
| 21-26        | 2 | 2  | 4  | 1 |
| 26-31        | 7 | 11 | 12 | 5 |
| 31-36        | 3 | 5  | 5  | 2 |
| 36-41        | 2 | 4  | 1  | 7 |
| 41-46        | 0 | 1  | 3  | 2 |
| 46-51        | 0 | 0  | 2  | 3 |
| Above 51     | 0 | 3  | 0  | 6 |

df.dtype give me

Age Category
A int64
B int64
C int64
D int64 
dtype: object

But, when i am writing this to MySQL I am not getting first column

The Output of MySQL is shown below:

| A | B  | C  | D |
|---|----|----|---|
|   |    |    |   |
| 2 | 2  | 4  | 1 |
| 7 | 11 | 12 | 5 |
| 3 | 5  | 5  | 2 |
| 2 | 4  | 1  | 7 |
| 0 | 1  | 3  | 2 |
| 0 | 0  | 2  | 3 |
| 0 | 3  | 0  | 6 |

I want to write in MySQL with First column.

I have created connection using SQLAlchemy and PyMySQL

engine = create_engine('mysql+pymysql://[user]:[passwd]@[host]:[port]/[database]')

and I am writing using pd.to_sql()

df.to_sql(name = 'demo', con = engine, if_exists = 'replace', index = False) but this is not giving me first column in MySQL.

Thank you for your time and consideration.

  • 2
    Maybe try `index=True` in `to_sql` method? Or alternatively `demograph.reset_index().to_sql(...)` – Chris Adams Dec 05 '19 at 11:38
  • @ChrisA After using `index = True` I am getting `InternalError: (pymysql.err.InternalError) (1170, "BLOB/TEXT column 'age_category' used in key specification without a key length") [SQL: CREATE INDEX ix_age_demograph_age_category ON age_demograph (age_category)] (Background on this error at: http://sqlalche.me/e/2j85)` –  Dec 05 '19 at 11:40
  • Check this [link](https://stackoverflow.com/questions/1827063/mysql-error-key-specification-without-a-key-length) ..? – Chris Adams Dec 05 '19 at 11:42
  • @ChrisA After using `.reset_index()` I am getting `AttributeError: 'function' object has no attribute 'to_sql'` –  Dec 05 '19 at 11:42
  • are you sure you've got the parentheses after `reset_index`...? It should return a `DataFrame` if called properly – Chris Adams Dec 05 '19 at 11:44
  • @ChrisA After putting parenthesis. I am getting `TypeError: cannot insert an item into a CategoricalIndex that is not already an existing category` –  Dec 05 '19 at 11:46

0 Answers0