Support
Please support this website. Visit the Amazon using this affiliate link. There won't be any difference in your purchage, we will get some commission for every purchase you make.
Advertise with us
database mysql   1   2188


To work with MySQL in Python 3, you can use the Python MySQL Connector module. Here are the steps to connect to a MySQL database and perform CRUD (Create, Read, Update, Delete) operations using Python:


Install MySQL Connector

You can install MySQL Connector using pip command in your terminal:

pip install mysql-connector-python


Connect to the MySQL Database

To connect to the MySQL database, you need to import the mysql.connector module and create a connection object by specifying the host, user, password, and database name. Here is an example:

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  password="yourpassword",
  database="yourdatabase"
)

print(mydb)


Create cursor object

After connecting to the MySQL database, you need to create a cursor object to execute SQL queries. Here is an example:

mycursor = mydb.cursor()


Execute SQL Queries

Once you have the cursor object, you can execute SQL queries using the execute() method. Here are some examples of SQL queries:

Create a table:

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  password="yourpassword",
  database="yourdatabase"
)

mycursor = mydb.cursor()

# Create a table
mycursor.execute("CREATE TABLE customers (name VARCHAR(255), address VARCHAR(255))")


Insert into a table:

# Insert a record
sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
val = ("John", "Highway 21")
mycursor.execute(sql, val)
mydb.commit()
print(mycursor.rowcount, "record inserted.")


Selecting data from a table:

# Select all records
mycursor.execute("SELECT * FROM customers")
myresult = mycursor.fetchall()
for x in myresult:
  print(x)


Updating a row in Table:

# Update a record
sql = "UPDATE customers SET address = 'Canyon 123' WHERE name = 'John'"
mycursor.execute(sql)
mydb.commit()
print(mycursor.rowcount, "record(s) affected")


Deleting a record from a Table:

# Delete a record
sql = "DELETE FROM customers WHERE address = 'Highway 21'"
mycursor.execute(sql)
mydb.commit()
print(mycursor.rowcount, "record(s) deleted")


Close the Connection

After you finish working with the database, you should close the connection using the close() method. Here is an example:

mydb.close()


In the next part, we will cover advance tasks we can perform on MySQL using Pythong.


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   1   2188
1 comment on 'Working With Mysql And Python3'
Login to comment

Anurag April 5, 2023, 9:57 a.m.
https://pythoncircle.com/post/785/working-with-mysql-and-python3-part-2/

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