Python MySQL

安装“MySQL Connector”

pip install mysql-connector

连接数据库

import mysql.connector

mydb = mysql.connector.connect(
    host = "localhost",
    user = "username",
    passwd = "password"
)

print(mydb)

创建一个database

import mysql.connector

mydb = mysql.connector.connect(
    host = "localhost",
    user = "username",
    passwd = "password"
)


mycursor = mydb.cursor()

mycursor.execute("CREATE DATABASE mydatabase")

# check

for x in mycursor:
    print(x)

连接时access the database

import mysql.connector.connect(
    host = "localhost",
    user = "username",
    passwd = "password",
    database = "mydatabase"
    )

创建一个table

import mysql.connector

mydb = mysql.connector.connect(
    host = "localhost",
    user = "username",
    passwd = "password",
    database = "mydatabase"
    )

mycursor = mydb.cursor()

mycursor.execte("CREATE TABLE persons(
name varchar(225),
age varchar(255)
)")

# check

for x in mycursor: 
    print(x)

INSERT

...
mycursor = mydb.cursor()

sql = "INSERT INTO persons(name, age)
VALUES (%s, %s)"
val = ("Dora", "18")
mycursor.execute(sql, val)

** mydb.commit() ** #make the changes

print(mycursor.rowcount, "record inserted.")

多行

... 
val = [
    ('Peter', '3'),
    ('Amy', '5'),
    ('lala', '6')
]

mycursor.executemany(sql, val)

mydb.commit()

print(mycursor.rowcount, "was inserted")

返回插入ID

...
print("1 record inserted, ID:", mycursor.lastrowid)

SELECT

...
mycursor = mydb.cursor()

mycursor.execute("SELECT * FROM persons")

myresult = mycursor.fetchall()

for x in myresult:
    print(x)

fetchall: 从上个执行动作中获取所有行
fetchone: 提取结果第一行

SELECT 语句相似替换

防止SQL注入，转义

...
sql = "SELECT * FROM persons WHERE age = %s"
adr = ("5", )

mycursor.execute(sql, adr)
...