Blog available for sell
This blog is available for sale. Please 'contact us' if interested.
Advertise with us
database mysql   0   2694

In part 1 we covered basic tasks like connecting with MySQL using mysql connector package, inserting, updating and deleting the records from table. Let's jump onto the next level.



Batch insert into table - Inserting multiple records in one query


To insert a batch of data into a MySQL database using Python3, you can use the following steps:


- Connect to the database using the mysql-connector-python module.

- Define the SQL query to insert the data into the table.

- Create a list of tuples that contains the data to be inserted.

- Use the executemany() method of the cursor object to execute the query with the list of tuples as its parameter.

- Commit the changes to the database using the commit() method of the connection object.

Here is an example code snippet that demonstrates how to insert a batch of data into a MySQL database using Python3:


import mysql.connector

# Connect to the database
cnx = mysql.connector.connect(user='your_username', password='your_password',
                              host='localhost', database='your_database')
cursor = cnx.cursor()

# Define the SQL query to insert data
query = "INSERT INTO your_table (col1, col2, col3) VALUES (%s, %s, %s)"

# Create a list of tuples containing the data to be inserted
data = [
  ('value1', 'value2', 'value3'),
  ('value4', 'value5', 'value6'),
  ('value7', 'value8', 'value9')
]

# Execute the query with the list of tuples as its parameter
cursor.executemany(query, data)

# Commit the changes to the database
cnx.commit()

# Close the cursor and connection
cursor.close()
cnx.close()

In the example above, replace your_username, your_password, localhost, your_database, your_table, col1, col2, and col3 with the appropriate values for your MySQL database and table. Also, replace the data list with your own data.



Creating a new Table

# Define the SQL query to create the table
query = """
CREATE TABLE your_table (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  age INT
)
"""

# Execute the query
cursor.execute(query)

# Commit the changes to the database
cnx.commit()



Truncate a Table:


# Define the SQL query to truncate the table
query = "TRUNCATE TABLE your_table"

# Execute the query
cursor.execute(query)

# Commit the changes to the database
cnx.commit()



Drop a Table:


# Define the SQL query to drop the table
query = "DROP TABLE your_table"

# Execute the query
cursor.execute(query)

# Commit the changes to the database
cnx.commit()


In next part, we will cover more advanced concepts.



Host your Django Application for free on PythonAnyWhere.
If you want complete control of your application and server, you should consider DigitalOcean. Create an account with this link and get $200 credits.



database mysql   0   2694
0 comments on 'Working With Mysql And Python3 - Part 2'
Login to comment


Related Articles:
Logging databases changes in Django Application
Logging databases changes in Django Application, Using Django signals to log the changes made to models, Model auditing using Django signals, Creating signals to track and log changes made to database tables, tracking model changes in Django, Change in table Django, Database auditing Django, tracking Django database table editing...
Using PostgreSQL Database with Python
Using PostgreSQL database with python script. Creating Database and tables in PostgreSQL. Insert, update, select and delete Data in PostgreSQL database using python script. Using psycopg package to connecto to PostgreSQL database...
How to backup database periodically on PythonAnyWhere server
Scheduling the database backup in Django, script to the backup database periodically in Django app, Delete old backup files periodically in Django app, PythonAnyWhere server database backup in Django,...
Using MongoDB in Python Django Application
Using MongoDB in a Django Project with the help of MongoEngine. Non relational schema in Django project. Using nosql database in Django application. MongoDB with python Django...
DigitalOcean Referral Badge

© 2022-2023 Python Circle   Contact   Sponsor   Archive   Sitemap