Home > Article > Backend Development > Python database operations
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!