# Database connection parameters
username = 'root'
password = '123456'
database_name = 'example_db'

Selecting a Database

from pymysql import Connection

connection = Connection(
    host='localhost',
    port=3306,
    user='root',
    password='123456'
)

# Switch to a specific database
connection.select_db('mq')

Creating a Database

from pymysql import Connection

connection = Connection(
    host='localhost',
    port=3306,
    user='root',
    password='123456'
)

# Create a cursor instance
cursor = connection.cursor()

# Execute SQL to create a database if it doesn't exist
create_query = "CREATE DATABASE IF NOT EXISTS mql"
cursor.execute(create_query)

# Close the connection
connection.close()

Listing All Databases

from pymysql import Connection

connection = Connection(
    host='localhost',
    port=3306,
    user='root',
    password='123456'
)

# Create a cursor
cursor = connection.cursor()

# Query all databases
show_query = "SHOW DATABASES"
cursor.execute(show_query)

# Print results
for db in cursor.fetchall():
    print(db)

# Output example:
# ('information_schema',)
# ('mq',)
# ('mql',)
# ('mysql',)
# ('performance_schema',)
# ('sys',)

# Close connection
connection.close()

Drpoping a Database

from pymysql import Connection

connection = Connection(
    host='localhost',
    port=3306,
    user='root',
    password='123456'
)

# Create a cursor
cursor = connection.cursor()

# Drop a database if exists
drop_query = "DROP DATABASE IF EXISTS mq1"
cursor.execute(drop_query)

# Close connection
connection.close()

Using a Specific Database

from pymysql import Connection

connection = Connection(
    host='localhost',
    port=3306,
    user='root',
    password='123456'
)

# Use a specific database
cursor = connection.cursor()
cursor.execute("USE mq")

Checking Current Database

from pymysql import Connection

connection = Connection(
    host='localhost',
    port=3306,
    user='root',
    password='123456'
)

# Create a cursor
cursor = connection.cursor()

# Check currently selected database
cursor.execute("SELECT DATABASE()")
print(cursor.fetchone())  # Output: ('mq',)

# Close connection
connection.close()

Table Operations

Creaitng a Table

from pymysql import Connection

connection = Connection(
    host='localhost',
    port=3306,
    user='root',
    password='123456',
    autocommit=True
)

# Create a cursor
cursor = connection.cursor()

# Select database
connection.select_db('mq')

# Create table
create_table_sql = '''
CREATE TABLE `students` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;'''
cursor.execute(create_table_sql)

# Close connection
connection.close()

Removing a Table

from pymysql import Connection

connection = Connection(
    host='localhost',
    port=3306,
    user='root',
    password='123456'
)

# Create a cursor
cursor = connection.cursor()

# Select database
connection.select_db('mq')

# Drop table if exists
drop_table_sql = "DROP TABLE IF EXISTS `students`"
cursor.execute(drop_table_sql)

# Close connection
connection.close()

Retrieving Data

from pymysql import Connection

connection = Connection(
    host='localhost',
    port=3306,
    user='root',
    password='123456'
)

# Create a cursor
cursor = connection.cursor()

# Select database
connection.select_db('mq')

# Retrieve data
cursor.execute("SELECT * FROM student")
data = cursor.fetchall()

print(data)

# Output example:
# (('小九', 16, 1007), ('小凡', 19, 1003), ...)

for record in data:
    print(record)

# Close connection
connection.close()

Inserting Data

from pymysql import Connection

connection = Connection(
    host='localhost',
    port=3306,
    user='root',
    password='123456'
)

# Create a cursor
cursor = connection.cursor()

# Select database
connection.select_db('mq')

# Insert new record
cursor.execute('INSERT INTO student VALUES ("小吉", 18, 1009)')

# Commit transaction
connection.commit()

# Close connection
connection.close()

To avoid manual commit, enable auto-commit:

connection = Connection(
    host='localhost',
    port=3306,
    user='root',
    password='123456',
    autocommit=True
)

Working with MongoDB

To work with MongoDB in Python, use the pymongo library.

pip install pymongo

from pymongo import MongoClient

# Connect to MongoDB
client = MongoClient('mongodb://localhost:27017/')

# Access a database
db = client['example_db']

# Access a collection
collection = db['documents']

# Insert a document
new_doc = {"name": "Alice", "age": 30}
collection.insert_one(new_doc)

# Find documents
results = collection.find({"age": {"$gte": 25}})
for doc in results:
    print(doc)