MySQL Connector/Python——操作mysql

———————————————python3.3_connector1.1.4_windows—————————————————————

首先在http://dev.mysql.com/downloads/connector/python/下载connector/python，然后直接点击安装包进行安装，如果安装python的时候选择的是只为自己安装这个选项，那么就会出现在安装connector/python的时候提示说找不到python3.3...

 

 

1、连接mysql

 

import mysql.connector
cnx=mysql.connector.connect(user= ' root ',password= '',host= ' 127.0.0.1 ',database= ' lazystudy ')
cnx.close()

View Code

 

 

 

2、创建表和创建数据库

from mysql.connector  import errorcode
import mysql.connector
from symbol  import except_clause

DB_NAME= ' lazystudy '
TABLES={}

TABLES[ ' Book ']= """
    create table book(
        id int(11) auto_increment primary key,
        bookname varchar(20) not null,
        price int(11) not null
    )
"""

TABLES[ ' People ']= """
    create table people(
        id int(11) auto_increment primary key,
        name varchar(20) not null
    )
"""

cnx=mysql.connector.connect(user= ' root ',password= '',host= ' 127.0.0.1 ')

cursor=cnx.cursor()

def create_database(cursor):
     try:
        cursor.execute( " create database {} default character set 'utf-8' ".format(DB_NAME))
     except mysql.connector.Error as err:
         print( ' failed creating database:{} '.format(DB_NAME))
        exit(1)

try:
    cnx.database=DB_NAME
except mysql.connector.Error as err:
     if(err.errno==errorcode.ER_BAD_DB_ERROR):
        create_database(cursor)
        cnx.database=DB_NAME
     else:
         print(err)
        exit(1)


for name,ddl  in TABLES.items():
     try:
         print( ' creating table {} '.format(name),end= '   ')
        cursor.execute(ddl)
     except mysql.connector.Error as err:
         if(err.errno==errorcode.ER_TABLE_EXISTS_ERROR):
             print( " already exits... ")
         else:
             print(err.msg)
     else:
         print( " successfully... ")


cursor.close()
cnx.close()

View Code

 

3、插入数据

"""
insert into db
"""
import mysql.connector
cnx=mysql.connector.connect(user= ' root ',password= '',database= ' lazystudy ',host= ' 127.0.0.1 ')
cursor=cnx.cursor()

add_book= """
    insert into book (bookname,price) values(%s,%s)
"""
data_book=( ' the one ',100)

add_people= """
    insert into people (name) values(%s)
"""
data_people=( ' lazy ',) # 记得加上','  ——不然会出错...这是python元组的特性..。

# insert...
cursor.execute(add_book,data_book)
cursor.execute(add_people,data_people)

# close
cnx.commit()
cursor.close()
cnx.close()

View Code

 

 4、查询数据

import mysql.connector
cnx=mysql.connector.connect(user= ' root ',password= '',database= ' lazystudy ')
cursor=cnx.cursor()
query= """
    select * from people where id between %s and %s
"""
query_limit=(1,3)
cursor.execute(query,query_limit)

for info  in cursor:
     print( " name:{} ".format(info[1]))

cursor.close()
cnx.close()

View Code

 

 5、在django中的配置

DATABASES = {
     ' default ': {
         ' NAME ':  ' lazystudy ',
         ' ENGINE ':  ' mysql.connector.django ',
         ' USER ':  ' root ',
         ' PASSWORD ':  '',
         ' OPTIONS ': {
           ' autocommit ': True,
        },
    }
}

View Code

 

 

 

 

转载于:https://www.cnblogs.com/lazyzhong/p/3489693.html