Python3.5连接Mysql
Python3.5连接MysqlPython版本： 3.5.0MySqlDB官网只支持Python3.4，这里Python3.5使用第三方库PyMysql连接Mysql数据库。http://dev.mysql.com/downloads/connector/python/2.0.htmlPyMysql下载地址：https://pypi.python.org/pypi/PyMySQL#downloadsWindows下安装方法：下载解压后，进入PyMySql-0.6.7目录，执行python setup.py install安装
或者直接：
1
pip install pymysql
测试查询：
1
2
3
4
5
6
import pymysql
conn = pymysql.connect(host='localhost', port=3306,user='root',passwd='rusky',db='mysql',charset='UTF8')
cur = conn.cursor()
cur.execute("select version()")for i in cur: print(i)
cur.close()
conn.close()
执行结果：('5.6.23-enterprise-commercial-advanced-log',)*******VICTORY LOVES PREPARATION*******
测试写入：
1
2
3
4
5
6
7
8
9
10
import pymysql
conn = pymysql.connect(host='mysql.litianqiang.com', port=7150,user='xiaoshuo',passwd='qiangzi()',db='xiaoshuo',charset='UTF8')
cur = conn.cursor()
cur.execute("insert into testtable VALUES (1,'a')")
conn.commit()
for i in cur:
print(i)
cur.close()
conn.close()
执行结果：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
mysql> select * from testtable;
Empty set (0.03 sec)
mysql> select * from testtable;
+------+------+
| a | b |
+------+------+
| 2 | b |
+------+------+
1 row in set (0.03 sec)
mysql> select * from testtable;
+------+------+
| a | b |
+------+------+
| 2 | b |
| 1 | a |
+------+------+
2 rows in set (0.03 sec)
使用sqlalchemy连接mysql
1
pip install sqlalchemy
通过import导入必要的包
1
from sqlalchemy import create_engine,Table,Column,Integer,String,MetaData,ForeignKey
创建一个连接引擎
1
engine=create_engine("mysql+pymysql://root:a5230411@localhost:3306/test",echo=True)
我们将连接引擎放到engine里面方便后面使用。
create_engine("数据库类型+数据库驱动://数据库用户名:数据库密码@IP地址:端口/数据库"，其他参数)
上文当中echo=True是开启调试，这样当我们执行文件的时候会提示相应的文字。创建元数据什么是元数据？元数据就是描述数据的数据，举个简单的例子，小明身高170cm，体重50kg，性别男。其中身高，体重，性别就是元数据。当我们创建好连接引擎以后可以通过这个引擎抓取元数据。metadata=MetaData(engine)通过MetaData()方法创建了metadata实例，在这个方法里面带上engine的目的是绑定要连接引擎，当我们对这个metadata实例进行操作的时候就会直接连接到数据库。添加表结构设定好连接引擎和元数据，让我们向mysql里面创建表结构来进行测试。
1
2
3
4
user=Table('user',metadata, Column('id',Integer,primary_key=True), Column('name',String(20)), Column('fullname',String(40)),
)
address_table = Table('address', metadata, Column('id', Integer, primary_key=True), Column('user_id', None, ForeignKey('user.id')), Column('email', String(128), nullable=False)
)
其中Table()方法用来创建表，第一个参数为表明，第二是存入元数据，后面的参数使用Column()方法将数据库当中每一个字段的数据参数设置好。执行创建metadata.create_all()因为已将将表结构存到了metadata里面，然后让metadata执行create_all()方法，这样就向数据库里创建了user和address表。完整代码
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
#!/usr/bin/env python
# encoding: utf-8
"""
@version: ??
@author: phpergao
@license: Apache Licence
@file: 使用sqlalchemy连接mysql.py
@time: 2016/8/6 14:08
"""
from sqlalchemy import create_engine,Table,Column,Integer,String,MetaData,ForeignKey
engine=create_engine("mysql+pymysql://xiaoshuo:qiangzi()@mysql.litianqiang.com:7150/xiaoshuo",echo=True)
metadata=MetaData(engine)
user=Table('user',metadata,
Column('id',Integer,primary_key=True),
Column('name',String(20)),
Column('fullname',String(40)),
)
address_table = Table('address', metadata,
Column('id', Integer, primary_key=True),
Column('user_id', None, ForeignKey('user.id')),
Column('email', String(128), nullable=False)
)
metadata.create_all()
执行效果：
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
"D:\Program Files (x86)\Python35-32\python.exe" "F:/zuoye/堡垒机实例以及数据库操作/Python MySQL API/使用sqlalchemy连接mysql.py"
2016-08-06 14:11:14,070 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'sql_mode'
2016-08-06 14:11:14,070 INFO sqlalchemy.engine.base.Engine {}
2016-08-06 14:11:14,099 INFO sqlalchemy.engine.base.Engine SELECT DATABASE()
2016-08-06 14:11:14,100 INFO sqlalchemy.engine.base.Engine {}
2016-08-06 14:11:14,156 INFO sqlalchemy.engine.base.Engine show collation where `Charset` = 'utf8' and `Collation` = 'utf8_bin'
2016-08-06 14:11:14,157 INFO sqlalchemy.engine.base.Engine {}
2016-08-06 14:11:14,187 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS CHAR(60)) AS anon_1
2016-08-06 14:11:14,187 INFO sqlalchemy.engine.base.Engine {}
2016-08-06 14:11:14,216 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS CHAR(60)) AS anon_1
2016-08-06 14:11:14,216 INFO sqlalchemy.engine.base.Engine {}
2016-08-06 14:11:14,245 INFO sqlalchemy.engine.base.Engine SELECT CAST('test collated returns' AS CHAR CHARACTER SET utf8) COLLATE utf8_bin AS anon_1
2016-08-06 14:11:14,245 INFO sqlalchemy.engine.base.Engine {}
2016-08-06 14:11:14,304 INFO sqlalchemy.engine.base.Engine DESCRIBE `address`
2016-08-06 14:11:14,304 INFO sqlalchemy.engine.base.Engine {}
2016-08-06 14:11:14,332 INFO sqlalchemy.engine.base.Engine ROLLBACK
2016-08-06 14:11:14,360 INFO sqlalchemy.engine.base.Engine DESCRIBE `user`
2016-08-06 14:11:14,360 INFO sqlalchemy.engine.base.Engine {}
2016-08-06 14:11:14,389 INFO sqlalchemy.engine.base.Engine ROLLBACK
2016-08-06 14:11:14,418 INFO sqlalchemy.engine.base.Engine
CREATE TABLE user (
id INTEGER NOT NULL AUTO_INCREMENT,
name VARCHAR(20),
fullname VARCHAR(40),
PRIMARY KEY (id)
)
2016-08-06 14:11:14,418 INFO sqlalchemy.engine.base.Engine {}
2016-08-06 14:11:14,448 INFO sqlalchemy.engine.base.Engine COMMIT
2016-08-06 14:11:14,476 INFO sqlalchemy.engine.base.Engine
CREATE TABLE address (
id INTEGER NOT NULL AUTO_INCREMENT,
user_id INTEGER,
email VARCHAR(128) NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY(user_id) REFERENCES user (id)
)
2016-08-06 14:11:14,476 INFO sqlalchemy.engine.base.Engine {}
2016-08-06 14:11:14,536 INFO sqlalchemy.engine.base.Engine COMMIT
Process finished with exit code 0
1
2
3
4
5
6
7
8
9
10
mysql> show tables;
+--------------------+
| Tables_in_xiaoshuo |
+--------------------+
| address |
| user |
+--------------------+
3 rows in set (0.03 sec)
mysql>
页:
[1]