Home  >  Article  >  Backend Development  >  Python database operations

Python database operations

高洛峰
高洛峰Original
2016-12-27 13:30:161061browse

Installing MySQL-python under Windows

Download address: https://pypi.python.org/pypi/MySQL-python/1.2.5 Just install it into the system.

Install MySQL-python under linux to connect to MySQL:

Download address: https://pypi.python.org/pypi/MySQL-python/

After decompression, enter the directory and execute python setup.py install

Common problems encountered during the installation process:

1. Tips If mysql_config cannot be found, it is usually because mysql is installed using the lnmp one-click installation package. The path is

. Solution: locate mysql_config to find the location of the mysql_config file, and then ln -s to make a soft connection. Go to /usr/bin/.

2. When Ubuntu prompts that 'x86_64-linux-gnu-gcc' is missing, you need to install the python-dev package:

Solution: sudo apt-get install python -dev -y

3. Command 'gcc' failed with exit status 1

is prompted under CentOS. Solution: yum install gcc python-devel -y

After the installation is complete, enter python and execute import MySQLdb to see if the import can be successful.

Supplement:

When I was operating under ubuntu, I found that I could not connect to the database. ss -lnt found that mysql only listened to the 3306 port on the loopback address, which needs to be modified.
Modify Ubuntu's mysql and change its listening port 127.0.0.1:3306 to allow external connections:
Edit /etc/mysql/my.cnf (the configuration parameters may be in other files in this directory, Look carefully)
Modify bind-address = 0.0.0.0 to allow access from any IP.
Then execute /etc/init.d/mysql restart to restart the mysqlserver service

# The following is an example of Python operating the database:

#!/usr/bin/env python
# -*- coding:utf8 -*-
import MySQLdb
conn = MySQLdb.connect(
host = '192.168.2.14',
port = 3306,
user = 'root',
passwd = '123456',
db = 'demo',
)
# 操作数据库首先需要创建游标
cur = conn.cursor()
# 通过游标cur操作execute()方法可以写入纯sql语句,如下:
# 创建数据表
# cur.execute("create table teacher (id int(5),name varchar(20),class varchar(20),age varchar(10))")
# 插入数据
# cur.execute("insert into teacher values(23,'zhangsan','science',15)")
# 修改数据
# cur.execute("update teacher set id=100 where name='zhangsan'")
# 删除数据
# cur.execute("delete from teacher where id=100")
#插入一条数据【也可以用像下面这种写法】
sqli="insert into teacher values(%s,%s,%s,%s)"
cur.execute(sqli, (23,'zhangsan','science',15))
# 使用executemany一次性向数据表中插入多条值,返回值为受影响的行数。
sqli="insert into teacher values(%s,%s,%s,%s)"
cur.executemany(sqli,[
(11,'wangwu','art',23),
(8,'john','math',22),
(3,'Tom','physical',25),
])
# 最后关闭游标,执行提交操作,并关闭数据库连接
cur.close()
conn.commit()
conn.close()

Retrieve and output data

#!/usr/bin/env python
# -*- coding:utf8 -*-
import MySQLdb
conn = MySQLdb.connect(
host = '192.168.2.14',
port = 3306,
user = 'root',
passwd = '123456',
db = 'demo',
)
cur = conn.cursor()
# 获得表中有多少条数据
aa = cur.execute("select * from teacher")
cur.fetchone() # fetchone()方法可以帮我们获得表中的数据,但是每执行一次输出一行满足条件的值
cur.fetchone()
......
cur.scroll(0,'absolute')# 这样能将游标定位到表中的第一条数据
info = cur.fetchmany(aa)
for i in info:
print i
cur.close()
conn.commit()
conn.close()

The editor will introduce you to this much about Python's database operations. I hope it will be helpful to you!

For more articles related to Python’s database operations, please pay attention to the PHP Chinese website!

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn