How to write data in a data frame to the database in Python

This time I will show you how to write the data in the data frame to the database in Python. What are the precautions for writing the data in the data frame to the database in Python? The following is a practical case. Let’s take a look. .

Main content:

1. Method of writing data frame data to mongdb

2. Method of writing data frame data to mysql

In order not to reinvent the wheel in the future, here is a summary of how to write data frame data into mysql and mongodb and record it, so as to save you from digging around. What is recorded below are all highlights.

Write mongodb code snippet (using pymongo library):

##########################写入mongodb 数据库######################
###########################python操作mongodb数据库
from pymongo import MongoClient
con=MongoClient() ##连接客户端
db = con.Class ##创建数据库
post=db.Classdata ##创建集合
##插入数据(df是数据框)
##循环写入（以字典的方式一条一条插入）
for i in range(0,len(df)):
  u=dict(Class =df.iloc[i,0], Course =df.iloc[i,1],Title=df.iloc[i,7],Section=df.iloc[i,5],Type=df.iloc[i,8], \
      Days=df.iloc[i,2],Time=df.iloc[i,6],Room=df.iloc[i,4],Location=df.iloc[i,3],instructors=df.iloc[i,9],status=df.iloc[i,10])
  print u
  post.insert(u)

Write mysql code snippet (use pymysql library):

##############################写入mysql数据库#################################
import pymysql
## 加上字符集参数，防止中文乱码
dbconn=pymysql.connect(
 host="127.0.0.1",
 database="cgjr",
 user="root",
 password="12345",
 port=3306,
 charset='utf8'
 )
# 执行sql语句
try:
  with dbconn.cursor() as cursor:
    # 执行sql语句，插入记录
    sql = 'INSERT INTO t_tao_info (num, price, city, shop_name, title,number,link,sale) VALUES (%s, %s, %s, %s, %s,%s,%s,%s)'
    for i in range(0,len(data)):
      print "正在插入数据：" + str(i)
      cursor.execute(sql, (data.iloc[i,0], data.iloc[i,1], data.iloc[i,2],data.iloc[i,3],data.iloc[i,4],data.iloc[i,5],data.iloc[i,6],data.iloc[i,7]))
      # 没有设置默认自动提交，需要主动提交，以保存所执行的语句
      dbconn.commit()
except dbconn.Error, e:
  print "Error %d: %s" % (e.args[0], e.args[1])
  sys.exit(1)
finally:
  dbconn.close()
  print ('数据已插入，插入数据库成功！')

I believe you have mastered the method after reading the case in this article. For more exciting information, please pay attention to other related articles on the php Chinese website!

Recommended reading:

How to install opencv in Python3.5 in Window10

Python makes a news aggregation project

The above is the detailed content of How to write data in a data frame to the database in Python. For more information, please follow other related articles on the PHP Chinese website!