使用Python连接MySQL数据库

一、准备工作

安装连接数据库的包pymysql

pip install pymysql

二、连接数据库

1、连接数据库

import pymysql
# ---------数据库连接--------------
connect = pymysql.connect(host='localhost',                # 数据库地址
								            user='root',                        # 数据库用户名
                                            password='****',          # 数据库密码
                                            db='phone',              # 数据库名
                                            charset='utf8')                   # 数据库编码
cur = connect.cursor()
print(cur)                          #输出返回了一个游标实例对象，说明连接成功

2、对数据库进行增删查改

创建表

# --------------------创建表-----------------
try:
    create_sql = "create table phone_location (id int, phone varchar(11),province varchar(30),city varchar(30));"
    cur.execute(create_sql)
except Exception as e:
    print("创建表失败:", e)
else:
    print("成功创建表")

插入

# ---------------插入---------
try:
    insert_sql = "insert into phone_location values(1, 12345678911,'北京','北京');"
    cur.execute(insert_sql)
except Exception as e:
    print("插入数据失败:", e)
else:
    # 插入数据时， 需要要提交数据， 不然数据库中找不到要插入的数据;
    connect.commit()
    print("插入数据成功")

使用变量向SQL语句中传递参数:（其他的操作传参类似）

# ---------------插入传参---------
try:
    id = 2
    phone = '12345678912'
    province = '山东'
    city = "威海"
    insert_sql = "insert into phone_location(id,phone,province,city) values(%s, %s, %s, %s);"
    cur.execute(insert_sql,(id,phone,province,city))
    connect.commit()
    print("插入数据成功")
except Exception as e:
    print("插入数据失败:", e)

删除

# -------------删除---------
try:
    deletc_sql = "delete from phone_location where id=1;"
    cur.execute(deletc_sql)
except Exception as e:
    print("删除数据失败:", e)
else:
    connect.commit()
    print("删除数据成功")

修改

# ------------修改-------------
try:
    update_sql = "update phone_location set province='河南', city='郑州' where id=2;"
    cur.execute(update_sql)
except Exception as e:
    print("修改数据失败:", e)
else:
    connect.commit()
    print("修改数据成功")

查询

# ------------查询-------------
try:
    select_sql = "select * from phone_location where id=2;"
    cur.execute(select_sql)
except Exception as e:
    print("修改数据失败:", e)
else:
    connect.commit()
    print("修改数据成功")