MySQL安装 请看 Window下安装MySQL
- 首先要下载mysql数据库连接驱动,mysql驱动有三个,只需安装其中一个:MySQLdb、pymysql、mysql-connector(官网提供)。通过命令行pip安装,安装命令如下:
pip install mysql-connector
- 创建数据库student_db, user=“改成你的mysql用户名”,passwd=“改成你设的密码”,我的passwd没有设置密码。
#!/usr/bin/python3 import mysql.connector mydb = mysql.connector.connect( host="localhost", user="root", passwd="") mycursor = mydb.cursor() mycursor.execute("create database student_db")
- 创建student表,属性:name,sex,age,cellPhone
#!/usr/bin/python3
import mysql.connector
db = mysql.connector.connect(host="localhost", user="root", passwd="",database="student_db")
cursor = db.cursor()
cursor.execute("create table student(name varchar(40), sex char(2), age int, cellPhone double)")
db.close()
4. 向student表插入数据
#!/usr/bin/python3
import mysql.connector
testdb = mysql.connector.connect(host='localhost', user='root', passwd='',database='student_db')
testcursor = testdb.cursor()
testcursor.execute("insert into student(name, sex, age, cellPhone) values("小明", "M", 21, 13577776666)")
5. 给student表添加主键
#!/usr/bin/python3
import mysql.connector
testdb = mysql.connector.connect(host='localhost', user='root', passwd='',database='student_db')
testcursor = testdb.cursor()
testcursor.execute("alter table student add column id int auto_increment primary key")
使用MySQLdb 驱动连接mysql,记得使用前先安装MySQLdb驱动(pip install MySQLdb)和mysql数据库。先创建TESTDB数据库,再创建teacher表,之后再进行如下操作。
#!/usr/bin/python3
#-*- coding: utf-8 -*-
import MySQLdb
db = MySQLdb.connect("localhost", "root", "", "TESTDB")
cursor = db.cursor()
sql = """insert into teacher (first_name, last_name,age,sex, income)
values('Mac', 'Tom', 21, 'M', 3000),('Chan', 'Jim', 23, 'M', 5000)"""
try:
cursor.execute(sql)
# 提交到数据库
db.commit()
print("提交成功!")
except:
# 出错回滚
db.rollback()
print("提交失败!")
cursor.close()
db.close()
使用pymysql 驱动连接mysql,记得使用前先安装pymysql驱动(pip install pymysql)和mysql数据库。更新teacher表
#!/usr/bin/python3
import pymysql
db = pymysql.connect("localhost", "root", "", "TESTDB")
cursor = db.cursor()
sql = "update teacher set age = age + 1 where sex = '%c'" % ('M')
try:
cursor.execute(sql)
db.commit()
print("更新成功!")
except:
db.rollback()
print("更新失败!")
cursor.close()
db.close()