目录
1， mysql
1-1， mysql驱动
python要连接mysql需要安装mysql驱动
通过pip install mysql-connector-python命令安装mysql驱动
In [22]: pip install mysql-connector-python
Looking in indexes: http://mirrors.aliyun.com/pypi/simple/
Collecting mysql-connector-python
Downloading http://mirrors.aliyun.com/pypi/packages/d3/b8/6798a0f91e595c0704a94c1e32a00ca930f77cb8f0f96c7e4dad4f9db1fe/mysql_connector_python-8.1.0-cp311-cp311-win_amd64.whl (10.8 MB)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ 10.8/10.8 MB 777.8 kB/s eta 0:00:00
Requirement already satisfied: protobuf<=4.21.12,>=4.21.1 in c:\users\administrator\appdata\local\programs\python\python311\lib\site-packages (from mysql-connector-python) (4.21.12)
Installing collected packages: mysql-connector-python
Successfully installed mysql-connector-python-8.1.0
Note: you may need to restart the kernel to use updated packages.1-2， 连接mysql
先创建连接对象，才能mysql进行操作，命令如下：
conn = mysql.connector.conect(user=用户名， password=密码， database=数据库名)
# 导入mysql驱动
In [23]: import mysql.connector
# 创建连接
In [24]: conn = mysql.connector.connect(user='xxxx', password='xxxx', database='scott')1-3， 执行sql语句
要执行sql语句，需要通过"连接"创建游标，在通过游标执行sql语句
cursor = conn.cursor()：创建游标
cursor.execute(sql语句)：执行单挑sql语句
cursor.executemany(sql语句， 参数列表)
1-4， 数据表操作
1-4-1， 创建数据表
# 创建游标
In [27]: cursor = conn.cursor()
# 通过sql语句创建表
In [47]: cursor.execute('''create table user(
...: id bigint not null,
...: name varchar(20) not null,
...: job varchar(20) not null,
...: constraint pk_user_table primary key (id));''')
1-4-2， 查询数据表
# 执行sql语句，查询表
In [59]: cursor.execute('show tables;')
# 获取查询结果
In [60]: cursor.fetchall()
Out[60]:
[('bbb',),
('bbb_1',),
('dept',),
('emp',),
('salgrade',),
('snapshot_table',),
('user',)]1-4-3， 修改数据表
# 增加一列
In [25]: cursor.execute('alter table user add column address varchar(50) not null;')
# 修改address列名为address_1， 数据类型由varchar(50)修改为varchar(100)
In [29]: cursor.execute('alter table user change column address address_1 varchar(100) not null')
# 查询修改结果
In [30]: cursor.execute('desc user;')
In [31]: cursor.fetchall()
Out[31]:
[('id', b'bigint(20)', 'NO', 'PRI', None, ''),
('name', b'varchar(20)', 'NO', '', None, ''),
('job', b'varchar(20)', 'NO', '', None, ''),
('address_1', b'varchar(100)', 'NO', '', None, '')]
# 删除address_1列
In [33]: cursor.execute('alter table user drop column address_1')
In [34]: cursor.execute('desc user;')
In [35]: cursor.fetchall()
Out[35]:
[('id', b'bigint(20)', 'NO', 'PRI', None, ''),
('name', b'varchar(20)', 'NO', '', None, ''),
('job', b'varchar(20)', 'NO', '', None, '')]1-4-4， 删除数据表
In [39]: cursor.fetchall()
Out[39]:
[('bbb',),
('bbb_1',),
('dept',),
('emp',),
('salgrade',),
('snapshot_table',),
('user',),
('user_1',)]
# 删除数据表user_1
In [40]: cursor.execute('drop table user_1;')
In [41]: cursor.execute('show tables;')
In [42]: cursor.fetchall()
Out[42]:
[('bbb',),
('bbb_1',),
('dept',),
('emp',),
('salgrade',),
('snapshot_table',),
('user',)]1-5， 修改数据表内容
更新数据库数据后，需要提交，数据才能保存到数据库中
1-5-1， 插入数据
# 向数据表user中插入数据
In [69]: cursor.execute('''
...: insert into user
...: (id, name, job)
...: values
...: (1, 'aa', 'aa1'),
...: (2, 'bb', 'bb1'),
...: (3, 'cc', 'cc1')
...: ''')
# 获取收影响的行
In [70]: cursor.rowcount
Out[70]: 3
# 更新数据库数据后，需要提交，数据才能保存到数据库中
In [79]: conn.commit()
# 执行单条sql语句
# 注意这里的占位符为%s， sql语句与参数之间用的是逗号
In [160]: cursor.execute('insert into user (id, name, job) values (%s, %s, %s);', (20, 'fdsa', 'fdsaf'))
# 执行多条sql语句， sql语句与参数之间用的是逗号
In [137]: users
Out[137]: [(4, 'ee', 'ee1'), (5, 'ff', 'ff1')]
In [139]: cursor.executemany('''
...: insert into user
...: (id, name, job)
...: values
...: (%s, %s, %s);
...: ''', users)1-5-2， 查询数据
执行查询语句后不会立即回显数据，需要通过fetch*()方法来获取结果集
In [71]: cursor.execute('select * from user;')
1-5-3， 获取结果集
获取结果集通过命令：
cursor.fetchone()：获取结果集一条记录
cursor.fetchmany(n)：根据定义值(n)，获取结果集n条记录
cursor.fetchall()：获取所有结果集
# 通过cursor.fetchall()获取整个结果集
In [72]: cursor.fetchall()
Out[72]: [(1, 'aa', 'aa1'), (2, 'bb', 'bb1'), (3, 'cc', 'cc1')]
In [148]: cursor.execute('select * from user;')
# 获取结果集中一条记录
In [149]: cursor.fetchone()
Out[149]: (1, 'aa', 'aa1')
# 获取结果集中3条记录, 数字3,表示获取3条，根据定义的数字来获取结果集的条数
In [150]: cursor.fetchmany(3)
Out[150]: [(2, 'bb', 'bb1'), (3, 'cc', 'cc1'), (4, 'ee', 'ee1')]
# 若结果集没有被获取完时，再次执行sql语句，会报错
# 这时需要通过cursor.fetchall()获取完整结果集后，再执行sql语句
In [151]: cursor.execute('select * from user;')
---------------------------------------------------------------------------
InternalError Traceback (most recent call last)
Cell In[151], line 1
----> 1 cursor.execute('select * from user;')
File ~\AppData\Local\Programs\Python\Python311\Lib\site-packages\mysql\connector\cursor_cext.py:303, in CMySQLCursor.execute(self, operation, params, multi)
301 except (ProgrammingError, ReferenceError) as err:
302 raise ProgrammingError("Cursor is not connected", 2055) from err
--> 303 self._cnx.handle_unread_result()
305 stmt = ""
306 self.reset()
File ~\AppData\Local\Programs\Python\Python311\Lib\site-packages\mysql\connector\connection_cext.py:955, in CMySQLConnection.handle_unread_result(self, prepared)
953 self.consume_results()
954 elif unread_result:
--> 955 raise InternalError("Unread result found")
InternalError: Unread result found
# 获取完整结果集后，再次执行sql语句成功
In [154]: cursor.fetchall()
Out[154]: [(5, 'ff', 'ff1')]
In [156]: cursor.execute('select * from user;')1-5-4， 更新数据
# 更新id=1的name字段的值
In [66]: cursor.execute('update user set name="aadfsa" where id=1;')
# 注意：数据表数据修改后，需要进行提交，结果才会映射到数据库中
In [67]: conn.commit()
# 查询并获取修改结果
In [68]: cursor.execute('select name from user where id=1;')
In [69]: cursor.fetchall()
Out[69]: [('aadfsa',)]1-5-5， 删除数据
# 删除id = 2的记录
In [73]: cursor.execute('delete from user where id = 2;')
# 删除后需要提交才会影响到数据库
In [74]: conn.commit()
In [75]: cursor.execute("select * from user;")
In [76]: cursor.fetchall()
Out[76]: [(1, 'aadfsa', 'aa1'), (3, 'cc', 'cc1'), (4, 'ee', 'ee1'), (5, 'ff', 'ff1')]
# 删除整张表数据
In [77]: cursor.execute('delete from user;')
In [78]: conn.commit()
In [79]: cursor.execute("select * from user;")
In [80]: cursor.fetchall()
Out[80]: []1-6， 断开mysql连接
操作完mysql后，需要断开连接
cursor.close()：断开游标
conn.close()：断开连接
In [168]: cursor.close()
Out[168]: True
In [169]: conn.close()
927
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
