Pandas to_sql auto increment

by

Using pandas to_sql() Method with Auto Increment

The to_sql() method in pandas can be used to directly upload data from a DataFrame to a SQL database table. To insert rows with auto-incremented primary keys, follow these steps:

  1. Make sure you have the required libraries installed:
<script type="text/python">
  pip install pandas sqlalchemy
</script>
  1. First, import the necessary libraries:
<script type="text/python">
  import pandas as pd
  from sqlalchemy import create_engine
</script>
  1. Read your data into a pandas DataFrame:
<script type="text/python">
  data = {'Name': ['John', 'Mike', 'Sara'],
          'Age': [25, 30, 28]}
  df = pd.DataFrame(data)
</script>
  1. Create a connection string with the details of your database:
<script type="text/python">
  database_type = 'mysql'  # Replace with your database type
  username = 'your_username'  # Replace with your username
  password = 'your_password'  # Replace with your password
  host = 'localhost'  # Replace with your host
  database_name = 'your_database'  # Replace with your database name
  
  con_str = f'{database_type}://{username}:{password}@{host}/{database_name}'
</script>
  1. Create a connection to the database:
<script type="text/python">
  engine = create_engine(con_str)
</script>
  1. Specify the table name and the connection, and use the if_exists='replace' parameter if you want to replace the existing table:
<script type="text/python">
  table_name = 'your_table_name'  # Replace with your table name
  
  with engine.connect() as conn:
      df.to_sql(name=table_name, con=conn, index=False, if_exists='replace')
</script>

Now, when you upload the dataframe to the database table using to_sql(), it will automatically create a new table with auto-incremented primary keys. If the table already exists, the if_exists='replace' parameter will drop the existing table and create a new one.

Make sure to replace the placeholders (your_username, your_password, your_host, your_database, your_table_name) with your actual configuration.

I hope this helps! Let me know if you have any further questions.

Leave a comment