一： 下载数据库连接器
|
1 2 3 |
pip install mysql-connector-python |
参考：https://dev.mysql.com/doc/connector-python/en/connector-python-installation-binary.html
二：测试连接
|
1 2 3 4 5 6 7 8 9 10 11 12 |
#/usr/bin/python3 #-*-coding:UTF-8-*- from mysql import connector #相关参数记得修改 cnx = connector.connect(user='root', password='', host='127.0.0.1', database='test') cnx.close() |
参考：https://dev.mysql.com/doc/connector-python/en/connector-python-example-connecting.html
原文导包语句用的是 import mysql.connector ，我这边用python3运行是会报错的，换成上面写的就行了。
三：基础操作
数据库创建，新增表项，数据插入及查询：
|
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 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 |
from mysql import connector from mysql.connector import errorcode def create_database(cursor): try: cursor.execute("create database {} default character set 'utf8'".format(DB_NAME)) except connector.Error as err: print('Failed creating database: {}'.format(err)) exit(1) def create_table(cursor, TABLES): for name in TABLES: table_description = TABLES[name] try: print('Creating table {}: '.format(name), end='' ) cursor.execute(table_description) except connector.Error as err: if err.errno == errorcode.ER_TABLE_EXISTS_ERROR: print('Table already exists.') else: print(err) exit(1) print('OK') DB_NAME = 'dic' TABLES = {} TABLES['row_data'] = ( "create table `rowdata`(" "`id` int(11) not null auto_increment," "`word` varchar(100) not null," "`rowdata` varchar(3000) not null," "primary key (`id`)" ") engine = InnoDB" ) cnx = connector.connect( user='root', password='', host='127.0.0.1', ) cursor = cnx.cursor() try: cursor.execute("use {}".format(DB_NAME)) except connector.Error as err: print('Database {} does not exists.'.format(DB_NAME)) if err.errno == errorcode.ER_BAD_DB_ERROR: create_database(cursor) print('Database {} created successfully.'.format(DB_NAME)) cnx.database = DB_NAME else: print(err) exit(1) create_table(cursor, TABLES) add_data = ( "insert into rowdata" "(word, rowdata)" "values " "(%s, %s)" ) data_value = ("test", "test:测试") cursor.execute(add_data, data_value) cnx.commit() query = ( "select * from rowdata where word like %s" ) query_word = "test" cursor.execute (query, (query_word,)) for (ID, word, rowdata) in cursor: print('%d %s %s' % ( ID, word, rowdata )) cursor.close() cnx.close() |
因为操作都比较简单，所以就都写一起了，有一个要注意的是这个语句
cursor.execute (query, (query_word,))
查询的值是一个元组，虽然只有一个，但也要写成这种格式: (value,) 来表明它元组的类型。
另外一点，如果查询之后不进行数据读取，即这条语句for (ID, word, rowdata) in cursor: ，会报错:
mysql.connector.errors.InternalError: Unread result found
读取一下就行了。