Python operates MySQL and sqlite databases.

Operating MySQL and SQLite Databases with Python#

1 MySQL#

For MySQL knowledge, you can refer to my previous blog: Mysql - 眰恦 | BLOG (gitee.io)

Using Python to Operate MySQL Database @Fox Tutorial:~# (freeaihub.com)

Import the `pymysql` library#

import pymysql

Connect to the MySQL database#

db = pymysql.connect(host='127.0.0.1', user='root', password="", database='database_name')

Create a database operation cursor#

cur = conn.cursor() # Database operator cursor

Using pymysql.cursors.DictCursor, data can be returned as a dictionary

cur = conn.cursor(pymysql.cursors.DictCursor) # Return data as a dictionary

Create a table in MySQL#

Insert data into MySQL#

cur.execute('insert into table_name values values') # Insert data

Example: cur.execute('insert into t7(id,name) values (2,"zhangsan")') # Insert data

Delete data from MySQL#

cur.execute('delete from table_name where condition') # Delete data

Example: cur.execute('delete from t7 where id = 1') # Delete data

Modify data in MySQL#

cur.execute('update table_name set modified_condition where condition') # Modify data

Example: cur.execute('update t7 set id=1 where id = 2') # Modify data

Note: After executing insert, delete, or update operations, you need to commit the database conn.commit() # Commit data

Query data from MySQL#

cur.execute('select * from table_name where condition') # Query data

ret_1=cur.fetchone()  # Query one row of data
print(ret_1)  # Print only one row
ret_n=cur.fetchmany(2)  # Query n rows of data, fill in how many rows you want to print in parentheses
print(ret_n)  # Print n rows of data
ret_all = cur.fetchall()  # Query all data
print(ret_all)
# Note: cur is like a pointer, it will point to the next data after each query

For MySQL's insert, delete, and update operations, you can store individual SQL statements and then run them with cur.execute(sql)

After executing insert, delete, or update operations, remember to close the cursor cur.close() and close the connection conn.close()

2 SQLite#

Releases · sqlitebrowser/sqlitebrowser (github.com)

Using Python to Operate SQLite3 Online @Fox Tutorial:~# (freeaihub.com)

import sqlite3  # Import sqlite3

# Database file
db_file = 'Student.db'

# Connect to the database
conn = sqlite3.connect(db_file)

# SQL statement
sql = 'select * from score'

cur = conn.cursor()  # Database operator cursor
cur.execute(sql)  # Execute SQL statement

# Print the result
print(cur.fetchall())

# Close the database
conn.close()

SQLite database is an embedded database, and its database is a file sqlite.db

SQLite database is written in C language, with a small size, and is often integrated into various applications, even iOS, Android, Mac OS, and Linux all have built-in SQLite databases

Python has built-in SQLite database and can be used directly

SQLite database is a relational database, and there will be multiple tables in a database, which are associated with each other through primary and foreign keys

Operating SQLite database in Python

Get connection

After connecting, you need to open a cursor cursor and execute SQL statements through cursor

Close the connection and release resources

Tangwx