Python中对MySQL进行操作

最新推荐文章于 2024-07-13 22:30:00 发布

程序之巅

最新推荐文章于 2024-07-13 22:30:00 发布

阅读量850

点赞数

分类专栏： MySQL

本文链接：https://blog.csdn.net/zhuguiqin1/article/details/79317260

版权

MySQL 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

1. 去官网下载Windows版本的MySQL

https://dev.mysql.com/downloads/installer/

安装过程中可以设置密码为 root

安装完成后在"MySQL 5.7 Command Line Client"输入已经设置的密码便可以访问MySQL server

至此，便可以使用SQL相关的命操作命令对数据库进行操作，你可以在官方网页上找到对应的帮助文档，

https://dev.mysql.com/doc/refman/8.0/en/

例如： "create database kenny;" 创建名字为kenny的数据库

2. 用Python对MySQL进行操作

在Python中如果你想访问MySQL，你必须要下载支持Python访问的MySQL驱动，可以从官方网站里面下载connector，网址如 https://www.mysql.com/products/connector/.

由于我的python的版本是2.7.14版本所以我下载了与之对应的connector版本mysql-connector-python-2.1.7-py2.7-windows-x86-64bit.msi 直接手动安装便成功。

安装完成后，如果你打开Pycharm， File -> Setting -> Project Interpreter 可以看到mysql-connector-python, 表示你在Pycharm中可以通过这个驱动访问MySQL数据库了。

你还可以在官网https://dev.mysql.com/doc/connector-python/en/找到MySQL对应的帮助文档以及论坛

Python 示例代码（未完待续）

import mysql.connector
from mysql.connector import errorcode

#Connecting to MySQL Using Connector
cnx = None
try:
  cnx = mysql.connector.connect(user='root', password='root')
except mysql.connector.Error as err:
  if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
    print("Something is wrong with your user name or password")
  else:
    print(err)
else:
  print("Successfully connect to mysql")
  cursor = cnx.cursor()

# Creating Tables Using Connector


TABLES = {}
TABLES['employees'] = (
"CREATE TABLE `employees` ("
"  `emp_no` int(11) NOT NULL AUTO_INCREMENT,"
"  `birth_date` date NOT NULL,"
"  `first_name` varchar(14) NOT NULL,"
"  `last_name` varchar(16) NOT NULL,"
"  `gender` enum('M','F') NOT NULL,"
"  `hire_date` date NOT NULL,"
"  PRIMARY KEY (`emp_no`)"
") ENGINE=InnoDB")

TABLES['departments'] = (
"CREATE TABLE `departments` ("
"  `dept_no` char(4) NOT NULL,"
"  `dept_name` varchar(40) NOT NULL,"
"  PRIMARY KEY (`dept_no`), UNIQUE KEY `dept_name` (`dept_name`)"
") ENGINE=InnoDB")

TABLES['salaries'] = (
"CREATE TABLE `salaries` ("
"  `emp_no` int(11) NOT NULL,"
"  `salary` int(11) NOT NULL,"
"  `from_date` date NOT NULL,"
"  `to_date` date NOT NULL,"
"  PRIMARY KEY (`emp_no`,`from_date`), KEY `emp_no` (`emp_no`),"
"  CONSTRAINT `salaries_ibfk_1` FOREIGN KEY (`emp_no`) "
"     REFERENCES `employees` (`emp_no`) ON DELETE CASCADE"
") ENGINE=InnoDB")

TABLES['dept_emp'] = (
"CREATE TABLE `dept_emp` ("
"  `emp_no` int(11) NOT NULL,"
"  `dept_no` char(4) NOT NULL,"
"  `from_date` date NOT NULL,"
"  `to_date` date NOT NULL,"
"  PRIMARY KEY (`emp_no`,`dept_no`), KEY `emp_no` (`emp_no`),"
"  KEY `dept_no` (`dept_no`),"
"  CONSTRAINT `dept_emp_ibfk_1` FOREIGN KEY (`emp_no`) "
"     REFERENCES `employees` (`emp_no`) ON DELETE CASCADE,"
"  CONSTRAINT `dept_emp_ibfk_2` FOREIGN KEY (`dept_no`) "
"     REFERENCES `departments` (`dept_no`) ON DELETE CASCADE"
") ENGINE=InnoDB")

TABLES['dept_manager'] = (
"  CREATE TABLE `dept_manager` ("
"  `dept_no` char(4) NOT NULL,"
"  `emp_no` int(11) NOT NULL,"
"  `from_date` date NOT NULL,"
"  `to_date` date NOT NULL,"
"  PRIMARY KEY (`emp_no`,`dept_no`),"
"  KEY `emp_no` (`emp_no`),"
"  KEY `dept_no` (`dept_no`),"
"  CONSTRAINT `dept_manager_ibfk_1` FOREIGN KEY (`emp_no`) "
"     REFERENCES `employees` (`emp_no`) ON DELETE CASCADE,"
"  CONSTRAINT `dept_manager_ibfk_2` FOREIGN KEY (`dept_no`) "
"     REFERENCES `departments` (`dept_no`) ON DELETE CASCADE"
") ENGINE=InnoDB")

TABLES['titles'] = (
"CREATE TABLE `titles` ("
"  `emp_no` int(11) NOT NULL,"
"  `title` varchar(50) NOT NULL,"
"  `from_date` date NOT NULL,"
"  `to_date` date DEFAULT NULL,"
"  PRIMARY KEY (`emp_no`,`title`,`from_date`), KEY `emp_no` (`emp_no`),"
"  CONSTRAINT `titles_ibfk_1` FOREIGN KEY (`emp_no`)"
"     REFERENCES `employees` (`emp_no`) ON DELETE CASCADE"
") ENGINE=InnoDB")



DB_NAME = 'employees'

def create_database(cursor):
    try:
        cursor.execute(
            "CREATE DATABASE {} DEFAULT CHARACTER SET 'utf8'".format(DB_NAME))
    except mysql.connector.Error as err:
        print("Failed creating database: {}".format(err))
        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.iteritems():
    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 exists.")
        else:
            print(err.msg)
    else:
        print("OK")

cursor.close()
cnx.close()

4. "show databases;" 显示已经创建的数据库

5. "use kenny;" 使用已经创建的数据库

6. "show tables;" 显示数据库的表

mysql> show tables;
+-----------------+
| Tables_in_kenny |
+-----------------+
| user |
+-----------------+

1 row in set (0.00 sec)

7. "SELECT * FROM user"

+----+---------+
| id | name |
+----+---------+
| 1 | Michael |
+----+---------+
1 row in set (0.00 sec)

3.使用SQLAlchemy

首先从官方网页http://www.sqlalchemy.org/download.html 下载SQLAlchemy对应的安装包SQLAlchemy-1.1.15.tar.gz 然后使用命令python setup.py install 来安装

这里要注意的一点是如果你安装了不同版本的python，比如python2.7和python3.5同时被安装了，那么你就要在每个目录下都要安装一遍，否则有可能你的project有可能找不到sqlalchemy 。当你在pycharm里面看到了下面的SQLAlchemy的话，说明你已经安装成功了。

为什么要用SQLAlchemy，下面是官方的解释

SQL databases behave less like object collections the moresize and performance start to matter; object collections behave less liketables and rows the more abstraction starts to matter. SQLAlchemy aims toaccommodate both of these principles.

SQLAlchemy considers the database to be a relational algebra engine, not just a collection of tables. Rows can be selected from not only tables but also joins and other select statements; any of these units can be composed into a larger structure. SQLAlchemy's expression language builds on this concept from its core.

SQLAlchemy is most famous for its object-relational mapper (ORM), an optional component that provides the data mapper pattern, where classes can be mapped to the database in open ended, multiple ways - allowing the object model and database schema to develop in a cleanly decoupled way from the beginning.

SQLAlchemy's overall approach to these problems is entirely differentfrom that of most other SQL / ORM tools, rooted in a so-called complimentarity-oriented approach; instead of hiding away SQL and object relational details behind awall of automation, all processes are fully exposed within a series of composable, transparent tools. The library takes on the job of automating redundant tasks while the developer remains in control of how the database is organized and how SQL is constructed.

The main goal of SQLAlchemy is to change the way you think about databasesand SQL!