Python MySQL数据库基本操作及项目示例详解

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

#引入decimal模块

import pymysql

#连接数据库

db=pymysql.connect(host='localhost',user='root',password='1234',charset='utf8')

#创建一个游标对象（相当于指针）

cursor=db.cursor()

#执行创建数据库语句

cursor.execute('create schema wzg default charset=utf8;')

cursor.execute('show databases;')

#fetchone获取一条数据（元组类型）

print(cursor.fetchone())

#现在指针到了[1]的位置

#fetchall获取全部数据（字符串类型）

all=cursor.fetchall()

for i in all:

    print(i[0])

#关闭游标和数据库连接

cursor.close()

db.close()

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

import pymysql

#连接数据库，并打开wzg数据库（数据库已创建）

db=pymysql.connect(host='localhost',user='root',password='1234',charset='utf8',db='wzg')

#创建游标对象

cursor=db.cursor()

try:

    #创建student表，并执行

    sql='''create table student(

       SNO char(10),

       SNAME varchar(20) NOT NULL,

       SSEX varchar(1),

       primary key(SNO)

       )default charset=utf8;'''

    cursor.execute(sql)

    #插入一条数据，并执行

    insert_sql='''

    insert into student values('200303016','王智刚','男'),('20030001','小明','男')

    '''

    cursor.execute(insert_sql)

    #将数据提交给数据库（加入数据，修改数据要先提交）

    db.commit()

    #执行查询语句

    cursor.execute('select * from student')

    #打印全部数据

    all=cursor.fetchall()

    for i in all:

        print(i)

#发生错误时，打印报错原因

except Exception as e:

    print(e)

#无论是否报错都执行

finally:

    cursor.close()

    db.close()

1

2

3

4

5

select * from account where account_id=001 and Account_passwd=123456

if cursor.fetchall():

登录成功

else:

登录失败

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

import pymysql

# 连接数据库

db = pymysql.connect(host='localhost', user='root', password='1234', charset='utf8')

cursor = db.cursor()

# 创建bank库

cursor.execute('create database bank charset utf8;')

cursor.execute('use bank;')

try:

    # # 创建表

    # sql = '''create table account(

    #    account_id varchar(20) NOT NULL,

    #    account_passwd char(6) NOT NULL,

    #    money decimal(10,2),

    #    primary key(account_id)

    #    );'''

    # cursor.execute(sql)

    # # 插入数据

    # insert_sql = '''

    # insert into account values('001','123456',1000.00),('002','456789',5000.00)

    # '''

    # cursor.execute(insert_sql)

    # db.commit()

    # # 查询所有数据

    # cursor.execute('select * from account')

    # all = cursor.fetchall()

    # for i in all:

    #     print(i)

    # 输入账号和密码

    z=input("请输入账号：")

    m=input("请输入密码：")

    # 从account表中进行账号和密码的匹配

    cursor.execute('select * from account where account_id=%s and account_passwd=%s',(z,m))

    # 如果找到，则登录成功

    if cursor.fetchall():

        print('登录成功')

    else:

        print('登录失败')

except Exception as e:

    print(e)

finally:

    cursor.close()

    db.close()