连接Python和MySQL

小D驿站 2018-08-30 22:49:44

安装“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)

...