ITPub博客

首页 > 应用开发 > IT综合 > Python操作MySQL数据库

Python操作MySQL数据库

原创 IT综合 作者:dbSeeSee 时间:2016-12-15 23:05:26 0 删除 编辑
首先需要安装mysql-connector
可以下载安装http://dev.mysql.com/downloads/connector/python/

或者通过pip安装 pip install mysql-connector

确认安装成功:

C:\Users\aspwin01>python

Python 2.7.12 |Anaconda 4.2.0 (64-bit)| (default, Jun 29 2016, 11:07:13) [MSC v.1500 64 bit (AMD64)]

 on win32

Type "help", "copyright", "credits" or "license" for more information.

Anaconda is brought to you by Continuum Analytics.

Please check out: http://continuum.io/thanks and https://anaconda.org

>>> import mysql

>>>


安装配置MySQL

创建数据库asp测试用

create database asp;



操作MySQL的基本步骤

创建连接:connect

创建游标:cursor

执行SQL:excute、executemany

关闭连接:close



点击(此处)折叠或打开

  1. #测试完整的代码

  2. from mysql import connector

  3. #1-创建连接

  4. connectStr = dict(host='localhost',
  5.                               port=3306,
  6.                               user='root',
  7.                               password='root_123',
  8.                               database='asp')

  9. conn=connector.connect(**connectStr)

  10. #2-创建游标

  11. cursor=conn.cursor()

  12. print '3-执行DDL语句'

  13. ddl1='''
  14.         create table test1(
  15.         id int,
  16.         name varchar(20),
  17.         address varchar(30))
  18. '''
  19. #cursor.execute(ddl1)

  20. print '4-执行单条插入语句'

  21. sqltext1='''
  22.         insert into test1 (name, address) values('Tom', 'Beijing');
  23. '''

  24. cursor.execute(sqltext1)

  25. print '5-执行多条SQL语句'
  26. sqltext2="insert into test1(name, address) values (%s,%s)"

  27. cursor.executemany(sqltext2,[
  28.     ('Lili0', 'Sanghai'),
  29.     ('Lili1', 'Sanghai'),
  30.     ('Lili2', 'Sanghai'),
  31.     ])

  32. print '6-执行查询语句'
  33. select_text='''
  34.     select * from test1;
  35. '''

  36. cursor.execute(select_text)
  37. for row in cursor:
  38.     print row

  39. 结果:

  40. 3-执行DDL语句
  41. 4-执行单条插入语句
  42. 5-执行多条SQL语句
  43. 6-执行查询语句
  44. (37, u'Tom', u'Beijing')
  45. (38, u'Lili0', u'Sanghai')
  46. (39, u'Lili1', u'Sanghai')
  47. (40, u'Lili2', u'Sanghai')

参数化的SQL语句



点击(此处)折叠或打开

  1. print '6-参数化的SQL语句1'
  2. sqltext3="insert into test1(name,address) values (%s,%s)"
  3. v1=('Sari','Suzhou')
  4. cursor.execute(sqltext3,v1)
  5. conn.commit()

  6. print '7-参数化的SQL语句2'
  7. sqltext4="insert into test1(name,address) values (%s,%s)"
  8. v1=('Sari1','Suzhou')
  9. v2=('Sari2','Suzhou')
  10. v3=('Sari3','Suzhou')
  11. v4=('Sari4','Suzhou')
  12. vall=[v1,v2,v3,v4]

  13. cursor.executemany(sqltext4,vall)
  14. conn.commit()




来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29023300/viewspace-2130736/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2013-07-04

  • 博文量
    28
  • 访问量
    96057