2

I've done a bit of googling, and I can't find much information on this. I have a MySQL table that I'm pushing data to that has a key column. The only writing to this table is from my python script. I know you can use "auto-increment" columns to keep the key generation a sanitary process. I also know that when you insert into the table using normal MySQL, you're supposed to push a SQL NULL in to the row to get it to auto-increment properly. How would I set up the rows to have the proper NULL? Can I use pandas NaNs or NoneTypes?

E: Here's a sample setup.

Table: Logbook
| Key | Date  | Notes      |
|  1  | 02-18 | Successful |
|  2  | 03-18 | Failure    |

Using SQL to insert to the table:

INSERT INTO Logbook
VALUES (NULL, 04-18, Failure)

I want to accomplish this with pd.to_sql

riders994
  • 1,246
  • 5
  • 13
  • 33
  • 1
    "you're supposed to push a SQL NULL in to the row to get it to auto-increment properly." What? – roganjosh Aug 16 '18 at 15:23
  • 1
    What does 'push a sql null' mean? – Strawberry Aug 16 '18 at 15:23
  • As in, when you `INSERT` into the table (as if you're executing a query), the value that goes in to the column that is auto incremented is NULL. – riders994 Aug 16 '18 at 15:27
  • Why would there be a need to set the auto-increment field as `NULL`? – Dhruv Saxena Aug 16 '18 at 15:34
  • Because that's how it auto-increments? If I push a value, then it resets the autoincrementing from that value. – riders994 Aug 16 '18 at 15:38
  • Auto increment resets unless you pass a null value? – roganjosh Aug 16 '18 at 15:40
  • https://dev.mysql.com/doc/refman/8.0/en/example-auto-increment.html "No value was specified for the AUTO_INCREMENT column, so MySQL assigned sequence numbers automatically. You can also explicitly assign 0 to the column to generate sequence numbers, unless the NO_AUTO_VALUE_ON_ZERO SQL mode is enabled. If the column is declared NOT NULL, it is also possible to assign NULL to the column to generate sequence numbers." – Bill Karwin Aug 16 '18 at 17:28
  • "When you insert any other value into an AUTO_INCREMENT column, the column is set to that value and the sequence is reset so that the next automatically generated value follows sequentially from the largest column value." – Bill Karwin Aug 16 '18 at 17:29
  • In other words, the auto-increment will never be reset to 0, it will only be reset to a *higher* value if at all. – Bill Karwin Aug 16 '18 at 17:30
  • Yes, that's what I said. – riders994 Aug 16 '18 at 18:08

0 Answers0