python3数据库配置,远程连接mys

2023-01-31 02:01:57 数据库连接配置

python配置数据库mysql

安装Mysql数据库，方法如下

安装配置mysql数据库服务器

安装pymysql


#在python虚拟环境中安装，我采用的是这种方法

$ pip install PyMySQL
Collecting PyMySQL
Downloading PyMySQL-0.8.0-py2.py3-none-any.whl (83kB)
  100% |████████████████████████████████| 92kB 222kB/s 
Installing collected packages: PyMySQL
Successfully installed PyMySQL-0.8.0


#PyMySQL 是在 python3.x 版本中用于连接 MySQL 服务器的一个库，Python2中则使用mysqldb。



#PyMySQL 遵循 Python 数据库 api v2.0 规范，并包含了 pure-Python MySQL 客户端库。



#安装成功


# eric @ ray in ~/.local/virtualenvs/my_env3.6/code [18:54:30] 

$ python
Python 3.6.3 (default, Oct  6 2017, 08:44:35) 
[GCC 5.4.0 20160609] on linux
Type "help", "copyright", "credits" or "license" for more infORMation.
>>> import pymysql#输入不报错
>>>

连接mysql数据库

查询mysql版本信息


#!/usr/bin/python3                                                                                                              


# -*- coding: utf-8 -*-

"""

# Author: EricRay


# Created Time : 2017-12-27 16:45:02



# File Name: db.py


# Description:查询Mysql版本信息


"""
import pymysql


#打开数据库连接


db = pymysql.connect("192.168.122.58","eric","eric2017","test")

#pymysql.connect("localhost/IP","username","pwd","databs" )




#使用cursor()方法创建一个游标对象 cursor


cursor = db.cursor()


#使用execute()方法执行sql查询

cursor.execute("select VERSION()")


#使用 fetchone()方法获取单条数据

data = cursor.fetchone()

print ("database version : %s" % data)


#关闭连接

db.close()

建表


#!/usr/bin/python3                                                                                                                     


# -*- coding: utf-8 -*-

"""

# Author: EricRay


# Created Time : 2017-12-27 18:31:13



# File Name: cr_t.py


# Description:


"""
import pymysql


#打开数据库连接

db = pymysql.connect("192.168.122.58","eric","lyd2017","test")

cursor = db.cursor()

cursor.execute("drop table if exists employee")


#预处理语句建表

sql = """create table employee(
          tname varchar(20) not null default '',
          age int,
          sex varchar(1)) ENGINE=InnoDB DEFAULT CHARSET=utf8"""

cursor.execute(sql)

db.close()

插入数据


#!/usr/bin/python3                                                                                                                     


# -*- coding: utf-8 -*-

"""

# Author: EricRay


# Created Time : 2017-12-27 18:38:56



# File Name: insert.py


# Description:


"""


#!/usr/bin/python3


import pymysql


# 打开数据库连接

db = pymysql.connect("192.168.122.58","eric","lyd2017","test" )


# 使用cursor()方法获取操作游标

cursor = db.cursor()

sql = "insert into employee(tname,age,sex) \
  values('%s','%d','%s')" % \ 
  ('Mary',18,'F')


try:
  cursor.execute(sql)
  db.commit()
except:
  db.rollback()

db.close()

查询

Python查询Mysql使用 fetchone() 方法获取单条数据, 使用fetchall() 方法获取多条数据。

fetchone(): 该方法获取下一个查询结果集。结果集是一个对象
fetchall(): 接收全部的返回结果行.
rowcount: 这是一个只读属性，并返回执行execute()方法后影响的行数。


#!/usr/bin/python3                                                                                                                     


# -*- coding: utf-8 -*-

"""

# Author: EricRay


# Created Time : 2017-12-27 19:07:30


# File Name: select.py


# Description:

"""
import pymysql


# 打开数据库连接

db = pymysql.connect("192.168.122.58","eric","lyd2017","test" )


# 使用cursor()方法获取操作游标

cursor = db.cursor()


#查询

sql = "select * from employee"

try:
    cursor.execute(sql)

    result = cursor.fetchall()

    for row in result:
        tname = row[0]
        age = row[1]
        sex = row[2]

        #打印结果
        print('tname = %s, age = %d, sex = %s' % \ 
              (tname,age,sex))

except:
    print("Error: unable to fetch data")
db.close()

结果

(my_env3.6) 

# eric @ ray in ~/.local/virtualenvs/my_env3.6/code [19:14:05] 

$ python select.py 
tname = eric, age = 28, sex = M
tname = Mary, age = 18, sex = F

相关文章