mysql中的python_在python中 使用Mysql

在数据库中插入数据

# !/usr/bin/env/python

# .*. encoding:utf-8 -*-

import pymysql

def connect():

#num = int(input('部门编号:'))

#name = (input('部门名称:'))

#loc = (input('部门所在地:'))

#1.创建连接(主机,端口,用户名,口令,数据库名)

con = pymysql.connect(host='localhost',

port=3306,

user='root',

passwd='limenglei520',

database='hrs',

autocommit=True,

charset='utf8')

#autocommit=True 自动提交 charset要写不然编码有可能错

try:

with con.cursor() as cursour:

#插入一条

'''

result = cursour.execute(

'insert into TbDept values( %s, %s, %s)',

(num,name,loc)

)

'''

#插入多条

result = cursour.executemany(

'insert into TbDept values( %s, %s, %s)',

((51,'xx','xx'), (52,'nn','nn'), (53,'pp','pp'))

)

if result:

print('添加成功')

con.commit()

except pymysql.MySQLError as e:

print(e)

con.rollback()

finally:

con.close()

if __name__ == '__main__':

connect()

在数据库中更新数据

# !/usr/bin/env/python

# .*. encoding:utf-8 -*-

import pymysql

def connect():

id = int(input('更新的部门编号:'))

line = input('更新的列名:')

content = input('更新的内容:')

#1.创建连接(主机,端口,用户名,口令,数据库名)

con = pymysql.connect(host='localhost',

port=3306,

user='root',

passwd='limenglei520',

database='hrs',

autocommit=True,

charset='utf8')

try:

with con.cursor() as cursour:

#更新

update = cursour.execute(

f'update TbDept set {line}=(%s) where dno=(%s)',

(content,id)

)

if update:

print('更新成功')

con.commit()

except pymysql.MySQLError as e:

print(e)

con.rollback()

finally:

con.close()

if __name__ == '__main__':

connect()

从数据库中抓取数据

# !/usr/bin/env/python

# .*. encoding:utf-8 -*-

import pymysql

class Dept(object):

def __init__(self,no,name,loc):

self.no =no

self.name =name

self.loc =loc

def __str__(self):

return f'{self.no}\t{self.name}\t{self.loc}'

def connect():

con = pymysql.connect(host='localhost',

port=3306,

user='root',

passwd='limenglei520',

database='hrs',

autocommit=True,

charset='utf8',

cursorclass=pymysql.cursors.DictCursor)

#cursorclass 改游标类型

#cursorclass=pymysql.cursors.DictCursor把游标变成字典类型

try:

with con.cursor() as cursor:

cursor.execute('select dno no , dname name ,dloc loc from TbDept')

depts = cursor.fetchall()

for dept in depts:

print(dept)

# 方法一 print(dept['no'],dept['name'],dept['loc'])

# 方法二 dept = Dept(**dept) #解包

print(dept)

except pymysql.MySQLError as e:

pass