python MySQL

下载mysql.connector

pip命令安装

pip install mysql_connector

导入模块

import mysql.connector

创建数据库连接

import mysql.connector
 
mydb = mysql.connector.connect(
  host="localhost",       # 数据库主机地址
  user="root",    		  # 数据库用户名
  passwd=""   			  # 数据库密码
)

create database

import mysql.connector
 
mydb = mysql.connector.connect(
  host="localhost",       # 数据库主机地址
  user="root",    		  # 数据库用户名
  passwd=""   			  # 数据库密码
)
mycursor = mydb.cursor()
 
mycursor.execute("CREATE DATABASE runoob_db")

cretate table

import mysql.connector

mydb = mysql.connector.connect(
    host="localhost",
    user="root",
    passwd=""
    database="runoob_db"
)

mycursor = mydb.cursor()

mycursor.execute("create table Student (S_no varchar(20) primary key not null,"
                 "S_name varchar(30) not null,"
                 "S_age int(5) not null)")
print(mycursor.rowcount,"创建成功！")

insert

import mysql.connector

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

mycursor = mydb.cursor()

mycursor.execute("use runoob_db")
# 插入一条语句
# sql = "insert into Student() values(%s,%s,%s)"
# val = ['1001','sherrin',18]
# mycursor.execute(sql,val)
# mydb.commit()
# print(mycursor.rowcount,"插入成功！")


#插入多条数据
sql = "insert into Student(S_no,S_name,S_age) values (%s,%s,%s)"
val = [
    ('1001','sherrin',18),
    ('1002','刘武',22),
    ('1003','xiaoshier',22),
]
mycursor.executemany(sql,val)
mydb.commit()  #数据表内内容有改变，必须又该语句
print(mycursor.rowcount,"插入成功！")


#mycursor.execute("drop table Student")

delete

import mysql.connector

mydb = mysql.connector.connect(
    host='localhost',
    user='root',
    passwd='',
    database='runoob_db'
)
mycursor = mydb.cursor()

sql = 'delete from Student where S_name = %s'
val = ('刘武',)

mycursor.execute(sql,val)

mydb.commit()

print(mycursor.rowcount,"删除成功！")

update

import mysql.connector

mydb = mysql.connector.connect(
    host="localhost",
    user="root",
    passwd="",
    database="runoob_db"
)
mycursor = mydb.cursor()

sql = "update Student set S_name='邪灵' where S_name='xiaoshier'"

mycursor.execute(sql)
mydb.commit()
print(mycursor.rowcount,"修改成功！")

query

import mysql.connector

mybd = mysql.connector.connect(
    host='localhost',
    user='root',
    passwd='',
    database='runoob_db'
)
mycursor = mybd.cursor()

mycursor.execute("select * from Student")

myreslut = mycursor.fetchall()#fetchall()获取所有记录
#myreslut = mycursor.fetchone() #只获取一条数据

for i in myreslut:
    print(i)

整合至一个类

import mysql.connector

class JDBC:
    #创建连接
    def funa(self):
        mydb = mysql.connector.connect(
            host="localhost",
            user="root",
            passwd=""
        )
        mycursor = mydb.cursor()
        #创建一个数据库
        mycursor.execute("create database db_student")
        print(mycursor.rowcount,"create databas success!")
        mydb.close()
    #删除一个数据库
    def funb(self):
        mydb = mysql.connector.connect(
            host="localhost",
            user="root",
            passwd="",
            database="db_student"
        )
        mycursor = mydb.cursor()
        mycursor.execute("drop database if exists db_student")#sql语句
        print(mycursor.rowcount, "del database success!")
        mydb.close()
    def func(self):
        mydb = mysql.connector.connect(
            host="localhost",
            user="root",
            passwd="",
            database="db_student"
        )
        mycursor = mydb.cursor()
        mycursor.execute("create table Student("
               "Sno varchar(20) not null primary key,"
               "Sname varchar(20) not null,"
               "Ssex varchar(20) not null,"
               "Sbirthday datetime,"
               "Class varchar(20)"
               ")")
        print(mycursor.rowcount,"create success!")
        mydb.close()

    def funInsert(self):
        mydb = mysql.connector.connect(
            host="localhost",
            user="root",
            passwd="",
            database="db_student"
        )
        mycursor = mydb.cursor()
        mycursor.execute("insert into Student()"
      "values('108','曾华','男','1977-09-01','95033'),"
			"('105','匡明','男','1975-10-02','95031'),"
			"('107','王丽','女','1976-01-23','95033'),"
			"('101','李军','男','1976-02-20','95033'),"
			"('109','王芳','女','1975-02-10','95031'),"
			"('103','陆君','男','1974-06-03','95031')")
        mydb.commit()
        print(mycursor.rowcount, "insert success!")
        mydb.close()

    def funDelete(self):
        mydb = mysql.connector.connect(
            host="localhost",
            user="root",
            passwd="",
            database="db_student"
        )
        mycursor = mydb.cursor()
        sql = "delete from Student where Sno = %s"
        val = ['109', ]
        mycursor.execute(sql, val)
        mydb.commit()  # 提交数据，当表格数据改变时，必须加commit(),否则数据将添加不成功
        print(mycursor.rowcount, "delete,success！")
        mydb.close()

    def funUpdate(self):
        mydb = mysql.connector.connect(
            host='localhost',
            user='root',
            passwd='',
            database='db_Student'
        )
        mycursor = mydb.cursor()
        sql = "update Student set Sbirthday = %s where Sno = %s"
        val = ['2001-05-21', '108']
        mycursor.execute(sql, val)
        mydb.commit()
        print(mycursor.rowcount, "update success！")
        mydb.close()

    def funQuery(self):
        mydb = mysql.connector.connect(
            host="localhost",
            user="root",
            passwd="",
            database="db_Student"
        )
        mycursor = mydb.cursor()
        mycursor.execute("select * from Student")
        res = mycursor.fetchall()#fetchall()查询到多条数据,还有一个fetchone()只能查到一条数据
        for i in res:
            print(i)

#创建一个jdbc对象
jdbc = JDBC()
#创建数据库操作
jdbc.funa()
#删除数据库操作
#jdbc.funb()
#创建表操作
jdbc.func()
#插入数据
jdbc.funInsert()
#删除数据
jdbc.funDelete()
#更新数据
jdbc.funUpdate()
#查询数据
jdbc.funQuery()