1. 如何创建MySQL数据表

'''
pymysql
pip install pymysql
'''

from pymysql import *

def connectDB(): db = connect('127.0.0.1', 'root', 'root', 'db', charset='utf8') return db

db = connectDB()
print(type(db))

def createTable(db): cursor = db.cursor() try: cursor.execute('''create table persons (id int primary key not null, name text not null, age int not null, address char(100), salary real);''') db.commit() return True except: db.rollback() return False if createTable(db): print('create table success')
else: print('create table failed')

  

 

  
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
 

<class 'pymysql.connections.Connection'>
create table success

  

 

  
1
  
2
 

2. 如何向MySQL表中插入数据

def insertRecords(db): cursor = db.cursor() try: cursor.execute('delete from persons') cursor.execute(''' insert into persons(id, name, age, address, salary) values(1, 'Bill', 32, 'Colifornia', 20000);''') cursor.execute(''' insert into persons(id, name, age, address, salary) values(2, 'Mike', 30, 'China', 10000);''') cursor.execute(''' insert into persons(id, name, age, address, salary) values(3, 'Jhon', 12, 'Norway', 30000);''') db.commit() return True except Exception as e: print(e) db.rollback() return False

if insertRecords(db): print('insert success')
else: print('insert failed')

  

 

  
1
  
2
  
3
  
4
  
5
  
6
  
7
  
8
  
9
  
10
  
11
  
12
  
13
  
14
  
15
  
16
  
17
  
18
  
19
  
20
  
21
  
22
  
23
  
24
 

insert success

  

 

  
1
 

3. 如何查询MySQL中的数据

import json

def selectRecords(db): cursor = db.cursor() sql = 'select name, age, salary from persons order by age desc' cursor.execute(sql) results = cursor.fetchall() print(type(results)) fields = ['name', 'age', 'salary'] records = [] for row in results: records.append(dict(zip(fields, row))) return json.dumps(records) print(selectRecords(db))
db.close()

  

 

  
1
  
2
  
3
  
4
  
5
  
6
  
7
  
8
  
9
  
10
  
11
  
12
  
13
  
14
  
15
  
16
 

<class 'tuple'>
[{"name": "Bill", "age": 32, "salary": 20000.0}, {"name": "Mike", "age": 30, "salary": 10000.0}, {"name": "Jhon", "age": 12, "salary": 30000.0}]

  

 

  
1
  
2
 

持续更新中。。。。

文章来源: ruochen.blog.csdn.net，作者：若尘，版权归原作者所有，如需转载，请联系作者。

原文链接：ruochen.blog.csdn.net/article/details/104613841