blocks|key|103689|text|您可以将DataFrame输出为csv文件，然后使用mysqlimport将csv导入到mysql中。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|103690|编辑|103691|pandas's+build-in+sql+util似乎提供了一个write_frame函数，但只在sqlite中起作用。|103692|我发现了一些有用的东西，你可以试试this|103693|entityMap|0|LINK|mutability|MUTABLE|url|http://pandas.pydata.org/pandas-docs/dev/io.html?highlight=sql|1|https://gist.github.com/catawbasam/3164289^0|4|9|Q|B|18|5|0|0|X|B|0|Q|0|0|H|4|1|0^^$0|@$1|2|3|4|5|6|7|V|8|@$9|W|A|X|B|C]|$9|Y|A|Z|B|C]|$9|10|A|11|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|12|8|@]|D|@]|E|$]]|$1|H|3|I|5|6|7|13|8|@$9|14|A|15|B|C]]|D|@$9|16|A|17|1|18]]|E|$]]|$1|J|3|K|5|6|7|19|8|@]|D|@$9|1A|A|1B|1|1C]]|E|$]]|$1|L|3|-4|5|6|7|1D|8|@]|D|@]|E|$]]]|M|$N|$5|O|P|Q|E|$R|S]]|T|$5|O|P|Q|E|$R|U]]]]

You might output your <code>DataFrame</code> as a csv file and then use <code>mysqlimport</code> to import your csv into your <code>mysql</code>.

<h1>EDIT</h1>

Seems <a href="http://pandas.pydata.org/pandas-docs/dev/io.html?highlight=sql" rel="nofollow">pandas's build-in sql util</a> provide a <code>write_frame</code> function but only works in sqlite.

I found something useful, you might try <a href="https://gist.github.com/catawbasam/3164289" rel="nofollow">this</a>

blocks|key|320673|text|to_sql方法适用于我。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|320674|但是，请记住，它看起来将被弃用，取而代之的是SQLAlchemy：|320675|FutureWarning:+The+'mysql'+flavor+with+DBAPI+connection+is+deprecated+and+will+be+removed+in+future+versions.+MySQL+will+be+further+supported+with+SQLAlchemy+connectables.+chunksize=chunksize,+dtype=dtype)|code-block|syntax|javascript|320676|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|L|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|M|8|@]|9|@]|A|$G|H]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

The to_sql method works for me.

However, keep in mind that the it looks like it's going to be deprecated in favor of SQLAlchemy:

<pre><code>FutureWarning: The 'mysql' flavor with DBAPI connection is deprecated and will be removed in future versions. MySQL will be further supported with SQLAlchemy connectables. chunksize=chunksize, dtype=dtype)
</code></pre>

blocks|key|320807|text|Andy+Hayden提到了正确的函数(to_sql)。在这个答案中，我将给出一个完整的例子，我用Python3.5测试过，但也应该适用于Python2.7(和Python3.x)：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|320808|首先，让我们创建数据帧：|320809|#+Create+dataframe
import+pandas+as+pd
import+numpy+as+np

np.random.seed(0)
number_of_samples+=+10
frame+=+pd.DataFrame({
++++'feature1':+np.random.random(number_of_samples),
++++'feature2':+np.random.random(number_of_samples),
++++'class':++++np.random.binomial(2,+0.1,+size=number_of_samples),
++++},columns=['feature1','feature2','class'])

print(frame)|code-block|syntax|javascript|320810|这就给出了：|320811|+++feature1++feature2++class
0++0.548814++0.791725++++++1
1++0.715189++0.528895++++++0
2++0.602763++0.568045++++++0
3++0.544883++0.925597++++++0
4++0.423655++0.071036++++++0
5++0.645894++0.087129++++++0
6++0.437587++0.020218++++++0
7++0.891773++0.832620++++++1
8++0.963663++0.778157++++++0
9++0.383442++0.870012++++++0|320812|要将此数据帧导入MySQL表，请执行以下操作：|320813|#+Import+dataframe+into+MySQL
import+sqlalchemy
database_username+=+'ENTER+USERNAME'
database_password+=+'ENTER+USERNAME+PASSWORD'
database_ip+++++++=+'ENTER+DATABASE+IP'
database_name+++++=+'ENTER+DATABASE+NAME'
database_connection+=+sqlalchemy.create_engine('mysql%2Bmysqlconnector://{0}:{1}@{2}/{3}'.
+++++++++++++++++++++++++++++++++++++++++++++++format(database_username,+database_password,+
++++++++++++++++++++++++++++++++++++++++++++++++++++++database_ip,+database_name))
frame.to_sql(con=database_connection,+name='table_name_for_df',+if_exists='replace')|320814|一个技巧是MySQLdb不能与Python3.x一起工作。因此，我们改用mysqlconnector，它可能是如下所示的installed：|320815|pip+install+mysql-connector==2.1.4++#+version+avoids+Protobuf+error|320816|输出：|320817|​|320818|📷|atomic|320819|320820|请注意，如果数据库中不存在表和列，则to_sql会创建表和列。|320821|entityMap|0|LINK|mutability|MUTABLE|url|https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.to_sql.html|1|http://mysql-python.sourceforge.net/MySQLdb.html|2|https://stackoverflow.com/a/45470868/395857|3|IMAGE|IMMUTABLE|imageUrl|https://ask.qcloudimg.com/http-save/yehe-900000/7574358cde4b467210fe014b5e912379.png|imageAlt|4^0|K|6|K|6|0|0|0|0|0|0|0|0|10|E|5|7|1|1O|9|2|0|0|0|0|0|1|3|0|0|I|6|I|6|4|0^^$0|@$1|2|3|4|5|6|7|1R|8|@$9|1S|A|1T|B|C]]|D|@$9|1U|A|1V|1|1W]]|E|$]]|$1|F|3|G|5|6|7|1X|8|@]|D|@]|E|$]]|$1|H|3|I|5|J|7|1Y|8|@]|D|@]|E|$K|L]]|$1|M|3|N|5|6|7|1Z|8|@]|D|@]|E|$]]|$1|O|3|P|5|J|7|20|8|@]|D|@]|E|$K|L]]|$1|Q|3|R|5|6|7|21|8|@]|D|@]|E|$]]|$1|S|3|T|5|J|7|22|8|@]|D|@]|E|$K|L]]|$1|U|3|V|5|6|7|23|8|@$9|24|A|25|B|C]]|D|@$9|26|A|27|1|28]|$9|29|A|2A|1|2B]]|E|$]]|$1|W|3|X|5|J|7|2C|8|@]|D|@]|E|$K|L]]|$1|Y|3|Z|5|6|7|2D|8|@]|D|@]|E|$]]|$1|10|3|11|5|6|7|2E|8|@]|D|@]|E|$]]|$1|12|3|13|5|14|7|2F|8|@]|D|@$9|2G|A|2H|1|2I]]|E|$]]|$1|15|3|11|5|6|7|2J|8|@]|D|@]|E|$]]|$1|16|3|17|5|6|7|2K|8|@$9|2L|A|2M|B|C]]|D|@$9|2N|A|2O|1|2P]]|E|$]]|$1|18|3|-4|5|6|7|2Q|8|@]|D|@]|E|$]]]|19|$1A|$5|1B|1C|1D|E|$1E|1F]]|1G|$5|1B|1C|1D|E|$1E|1H]]|1I|$5|1B|1C|1D|E|$1E|1J]]|1K|$5|1L|1C|1M|E|$1N|1O|1P|-4]]|1Q|$5|1B|1C|1D|E|$1E|1F]]]]

Andy Hayden mentioned the correct function (<a href="https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.to_sql.html" rel="noreferrer"><code>to_sql</code></a>). In this answer, I'll give a complete example, which I tested with Python 3.5 but should also work for Python 2.7 (and Python 3.x):

First, let's create the dataframe:

<pre><code># Create dataframe
import pandas as pd
import numpy as np

np.random.seed(0)
number_of_samples = 10
frame = pd.DataFrame({
 'feature1': np.random.random(number_of_samples),
 'feature2': np.random.random(number_of_samples),
 'class': np.random.binomial(2, 0.1, size=number_of_samples),
 },columns=['feature1','feature2','class'])

print(frame)
</code></pre>

Which gives:

<pre><code> feature1 feature2 class
0 0.548814 0.791725 1
1 0.715189 0.528895 0
2 0.602763 0.568045 0
3 0.544883 0.925597 0
4 0.423655 0.071036 0
5 0.645894 0.087129 0
6 0.437587 0.020218 0
7 0.891773 0.832620 1
8 0.963663 0.778157 0
9 0.383442 0.870012 0
</code></pre>

To import this dataframe into a MySQL table:

<pre><code># Import dataframe into MySQL
import sqlalchemy
database_username = 'ENTER USERNAME'
database_password = 'ENTER USERNAME PASSWORD'
database_ip = 'ENTER DATABASE IP'
database_name = 'ENTER DATABASE NAME'
database_connection = sqlalchemy.create_engine('mysql+mysqlconnector://{0}:{1}@{2}/{3}'.
 format(database_username, database_password, 
 database_ip, database_name))
frame.to_sql(con=database_connection, name='table_name_for_df', if_exists='replace')
</code></pre>

One trick is that <a href="http://mysql-python.sourceforge.net/MySQLdb.html" rel="noreferrer">MySQLdb</a> doesn't work with Python 3.x. So instead we use <code>mysqlconnector</code>, which may be <a href="https://stackoverflow.com/a/45470868/395857">installed</a> as follows:

<pre><code>pip install mysql-connector==2.1.4 # version avoids Protobuf error
</code></pre>

Output:

<a href="https://i.stack.imgur.com/jsD1Q.png" rel="noreferrer"><img src="https://i.stack.imgur.com/jsD1Q.png" alt="enter image description here"></a>

Note that <a href="https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.to_sql.html" rel="noreferrer"><code>to_sql</code></a> creates the table as well as the columns if they do not already exist in the database.

blocks|key|103984|text|这对我很有效。首先，我只创建了数据库，没有创建预定义的表。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|103985|from+platform+import+python_version
print(python_version())
3.7.3

path='glass.data'
df=pd.read_csv(path)
df.head()


!conda+install+sqlalchemy
!conda+install+pymysql

pd.__version__
++++'0.24.2'

sqlalchemy.__version__
'1.3.20'|code-block|syntax|javascript|103986|已在安装后重新启动内核。|103987|from+sqlalchemy+import+create_engine
engine+=+create_engine('mysql%2Bpymysql://USER:PASSWORD@HOST:PORT/DATABASE_NAME',+echo=False)

try:
df.to_sql(name='glasstable',con=engine,index=False,+if_exists='replace')
print('Sucessfully+written+to+Database!!!')

except+Exception+as+e:
++++print(e)|103988|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

This has worked for me. At first I've created only the database, no predefined table I created.
<pre><code>from platform import python_version
print(python_version())
3.7.3

path='glass.data'
df=pd.read_csv(path)
df.head()


!conda install sqlalchemy
!conda install pymysql

pd.__version__
 '0.24.2'

sqlalchemy.__version__
'1.3.20'
</code></pre>
restarted the Kernel after installation.
<pre><code>from sqlalchemy import create_engine
engine = create_engine('mysql+pymysql://USER:PASSWORD@HOST:PORT/DATABASE_NAME', echo=False)

try:
df.to_sql(name='glasstable',con=engine,index=False, if_exists='replace')
print('Sucessfully written to Database!!!')

except Exception as e:
 print(e)
</code></pre>

blocks|key|104002|text|这应该能起到作用：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|104003|import+pandas+as+pd
import+pymysql
pymysql.install_as_MySQLdb()
from+sqlalchemy+import+create_engine

#+Create+engine
engine+=+create_engine('mysql://USER_NAME_HERE:PASS_HERE@HOST_ADRESS_HERE/DB_NAME_HERE')

#+Create+the+connection+and+close+it(whether+successed+of+failed)
with+engine.begin()+as+connection:
++df.to_sql(name='INSERT_TABLE_NAME_HERE/INSERT_NEW_TABLE_NAME',+con=connection,+if_exists='append',+index=False)|code-block|syntax|javascript|104004|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

This should do the trick:
<pre><code>import pandas as pd
import pymysql
pymysql.install_as_MySQLdb()
from sqlalchemy import create_engine

# Create engine
engine = create_engine('mysql://USER_NAME_HERE:PASS_HERE@HOST_ADRESS_HERE/DB_NAME_HERE')

# Create the connection and close it(whether successed of failed)
with engine.begin() as connection:
 df.to_sql(name='INSERT_TABLE_NAME_HERE/INSERT_NEW_TABLE_NAME', con=connection, if_exists='append', index=False)
</code></pre>

I can connect to my local mysql database from python, and I can create, select from, and insert individual rows.

My question is: can I directly instruct mysqldb to take an entire dataframe and insert it into an existing table, or do I need to iterate over the rows? 

In either case, what would the python script look like for a very simple table with ID and two data columns, and a matching dataframe?

How to insert pandas dataframe via mysqldb into database?

数据库

MySQL

我可以从python连接到我的本地mysql数据库，还可以创建、选择和插入单独的行。我的问题是:我是否可以直接指示mysqldb获取整个数据帧并将其插入到现有的表中，或者我是否需要迭代行？在这两种情况下，对于一个包含ID、两个数据列和一个匹配数据帧的非常简单的表，python脚本会是什么样子呢？

问如何通过mysqldb将pandas数据帧插入数据库？
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何通过mysqldb将pandas数据帧插入数据库？EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何通过mysqldb将pandas数据帧插入数据库？
EN