Python operation database cases mysql, redis, mongodb, neo4j

Article directory

• Preface
• 1. Installation of python library
• • MySQL
  • Redis
  • MongoDB
  • Neo4j
• MySQL case
• • pymysql case
  • mysql-connector-python case
• Redis case
• Mongodb case
• • Case number one
  • Case 2
• Neo4j case
• • Case number one
• Case 2
• Summarize

Foreword

With the rapid development of data science and big data technology, database management has become an indispensable skill. Python, as a widely used programming language, provides a rich library to simplify interaction with various databases. This article will introduce how to use Python to operate four mainstream databases: MySQL, Redis, MongoDB and Neo4j. We’ll start with the installation of the library and then dive into basic CRUD operations. Whether you are a data scientist, a back-end developer, or simply interested in databases, this article will provide you with practical guidance.

The code examples provided in this article are only for introductory reference and are designed to demonstrate the basic database operation process. However, each database and its corresponding Python library has a richer and more complex feature set that has a wide range of uses in real-world applications. Therefore, readers are strongly recommended to further refer to the official documentation of each database and library to obtain more comprehensive and in-depth technical details and application methods. This will not only help you meet project needs more accurately, but also broaden your professional horizons in database management and operations.

1. Installation of python library

MySQL

For MySQL, you can use the pymysql library. The installation command is as follows:

pip install pymysql

You can also use the following

pip install mysql-connector-python

Redis

For Redis, you can use the redis library. The installation command is as follows:

pip install redis

MongoDB

For MongoDB, you can use the pymongo library. The installation command is as follows:

pip install pymongo

Neo4j

For Neo4j, you can use the neo4j library. The installation command is as follows:

pip install neo4j

MySQL case

pymysql case

#!/usr/bin/env python3
# -*- coding: utf-8 -*-
importpymysql

# Connect to the database
conn = pymysql.connect(host='localhost', user='ai', password='12345678', database='mydatabase')
cursor = conn.cursor()

#Create table
cursor.execute("CREATE TABLE IF NOT EXISTS users (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), age INT)")

# Clear the table
cursor.execute("DELETE FROM users")
conn.commit()

#Insert a record
cursor.execute("INSERT INTO users (name, age) VALUES ('Alice', 30)")
conn.commit()

# Query records
cursor.execute("SELECT * FROM users")
print(cursor.fetchall())

#Update a record
cursor.execute("UPDATE users SET age=31 WHERE name='Alice'")
conn.commit()

# Delete a record
cursor.execute("DELETE FROM users WHERE name='Alice'")
conn.commit()

# Close the connection
cursor.close()
conn.close()

mysql-connector-python case

#!/usr/bin/env python3
# -*- coding: utf-8 -*-
import mysql.connector

# Connect to the database
conn = mysql.connector.connect(host='localhost', user='ai', password='12345678', database='mydatabase')
cursor = conn.cursor()

#Create table
cursor.execute("CREATE TABLE IF NOT EXISTS users (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), age INT)")

# Clear the table
cursor.execute("DELETE FROM users")

#Insert a record
cursor.execute("INSERT INTO users (name, age) VALUES ('Alice', 30)")
conn.commit()

# Query records
cursor.execute("SELECT * FROM users")
print(cursor.fetchall())

#Update a record
cursor.execute("UPDATE users SET age=31 WHERE name='Alice'")
conn.commit()

# Delete a record
cursor.execute("DELETE FROM users WHERE name='Alice'")
conn.commit()

# Close the connection
cursor.close()
conn.close()

Redis case

#!/usr/bin/env python3
# -*- coding: utf-8 -*-
import redis
# Connect to the database
r = redis.Redis(host='localhost', port=6379, db=0)
#Clear database
r.flushdb()
# Increase
r.set('name', 'Alice')
# Inquire
print(r.get('name'))
# renew
r.set('name', 'Bob')
# delete
r.delete('name')

Mongodb case

Case 1

#!/usr/bin/env python3
# -*- coding: utf-8 -*-
from pymongo import MongoClient
# Connect to the database
client = MongoClient('mongodb://localhost:27017/')
db = client['test_db']
# Clear the collection
db['users'].delete_many({<!-- -->})
# Increase
db['users'].insert_one({<!-- -->'name': 'Alice', 'age': 30})
# Inquire
print(list(db['users'].find()))
# renew
db['users'].update_one({<!-- -->'name': 'Alice'}, {<!-- -->'$set': {<! -- -->'age': 31}})
# delete
db['users'].delete_one({<!-- -->'name': 'Alice'})

Case 2

#!/usr/bin/env python3
# -*- coding: utf-8 -*-
from pymongo import MongoClient
# Connect to MongoDB
client = MongoClient('mongodb://localhost:27017/')
# Select or create a database
db = client['my_database']
# Get all collection names
collection_names = db.list_collection_names()
# Delete all collections
for name in collection_names:
    db.drop_collection(name)
print("All collections have been deleted.")
collection = db.my_collection
document = {<!-- -->"name": "John", "age": 30, "city": "New York"}
collection.insert_one(document)
documents = [
    {<!-- -->"name": "Tom", "age": 33, "city": "New York"},
    {<!-- -->"name": "Marie", "age": 22, "city": "Boston"},
    {<!-- -->"name": "Mike", "age": 32, "city": "Chicago"}
]
collection.insert_many(documents)
query = {<!-- -->"name": "John"}
result = collection.find_one(query)
print(result)
results = collection.find({<!-- -->"age": {<!-- -->"$gt": 25}})
for result in results:
    print(result)
update_query = {<!-- -->"name": "John"}
new_values = {<!-- -->"$set": {<!-- -->"age": 40}}
collection.update_one(update_query, new_values)
delete_query = {<!-- -->"name": "John"}
collection.delete_one(delete_query)
client.close()

Neo4j case

Case 1

#!/usr/bin/env python3
# -*- coding: utf-8 -*-
from neo4j import GraphDatabase
# Connect to the database
driver = GraphDatabase.driver("bolt://localhost:7687", auth=("neo4j", "password"))
#Clear database
with driver.session() as session:
    session.run("MATCH (n) DETACH DELETE n")
# Increase
with driver.session() as session:
    session.run("CREATE (a:Person {name: 'Alice', age: 30})")
# Inquire
with driver.session() as session:
    result = session.run("MATCH (a:Person) RETURN a.name, a.age")
    for record in result:
        print(record)
# renew
with driver.session() as session:
    session.run("MATCH (a:Person {name: 'Alice'}) SET a.age = 31")
# delete
with driver.session() as session:
    session.run("MATCH (a:Person {name: 'Alice'}) DELETE a")
# Close the connection
driver.close()

Case 2

The following is a more complex Neo4j use case, covering the addition, deletion, modification and query operations of nodes (Node) and edges (Relationship).

#!/usr/bin/env python3
# -*- coding: utf-8 -*-
from neo4j import GraphDatabase
#Initialize database connection
driver = GraphDatabase.driver("bolt://localhost:7687", auth=("neo4j", "password"))
#Clear the entire database
def clear_database(tx):
    tx.run("MATCH (n) DETACH DELETE n")
#Create nodes and edges
def create_node_and_relationship(tx):
    tx.run("CREATE (a:Person {name: 'Alice', age: 25})-[:KNOWS]->(b:Person {name: 'Bob', age: 30}) ")
#Query node
def query_node(tx):
    result = tx.run("MATCH (a:Person) WHERE a.name = 'Alice' RETURN a.name, a.age")
    for record in result:
        print(record)
# Update node attributes
def update_node(tx):
    tx.run("MATCH (a:Person {name: 'Alice'}) SET a.age = 26")
# Delete the node (and delete the edges related to it)
def delete_node(tx):
    tx.run("MATCH (a:Person {name: 'Alice'}) DETACH DELETE a")
# Create edges
def create_relationship(tx):
    tx.run("MATCH (a:Person), (b:Person) WHERE a.name = 'Alice' AND b.name = 'Bob' CREATE (a)-[:FRIENDS_WITH]->( b)")
# Delete edges
def delete_relationship(tx):
    tx.run("MATCH (a:Person)-[r:FRIENDS_WITH]->(b:Person) WHERE a.name = 'Alice' AND b.name = 'Bob' DELETE r")
# Perform operations
with driver.session() as session:
    session.execute_write(clear_database) # Clear the database
    session.execute_write(create_node_and_relationship)
    session.execute_read(query_node)
    session.execute_write(update_node)
    session.execute_write(create_relationship)
    session.execute_write(delete_relationship)
    session.execute_write(delete_node)

#Close database connection
driver.close()

Summary

This article details how to use Python to perform basic operations on MySQL, Redis, MongoDB and Neo4j databases. We started with how to install the corresponding Python library, and then discussed how to perform basic add, delete, modify and query operations. Through these basic cases, we can not only better understand the characteristics of each database, but also learn how to use the powerful functions of Python to simplify database management tasks. I hope this article can help you take a solid step in database management.