Pandas DataFrame to_sql Python
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/46979264/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
pandas DataFrame to_sql Python
提问by AdiR
I want to create new DB in mysql based on few csv files. what do I need to add? And how do I open a new db from python without manually opening it from phpmyadmin?
我想基于几个 csv 文件在 mysql 中创建新数据库。我需要添加什么？以及如何在不从 phpmyadmin 手动打开的情况下从 python 打开一个新数据库？
import pymysql
import pandas as pd
# Creating the DB:
DB = pymysql.connect(host='localhost',
user='root',
passwd='',
db='DB')
csv1 = pd.read_csv('C:/.........csv')
csv1SQL =pd.DataFrame.to_sql(name='Orders', con=DB, flavor=None, schema=None, if_exists='fail', index=True, index_label=None, chunksize=None, dtype=None)
cursor.execute(csv1SQL)
游标。执行（csv1SQL）
cursor = pymysql.cursor()
the error:
错误：
"pandas.io.sql.DatabaseError: Execution failed on sql 'SELECT name FROM sqlite_master WHERE type='table' AND name=?;': not all arguments converted during string formatting"
回答by Filipe Lemos
As I looked into other topics I found out that a solution like this one from James at questions about pandas.to_sqlcould be the solution for your problem. Here is what he said.
当我查看其他主题时，我发现 James 在有关 pandas.to_sql的问题中的解决方案可能是您问题的解决方案。这是他所说的。
Your way is not supported anymore. Try this?
不再支持您的方式。尝试这个？
from sqlalchemy import create_engine
import pandas as pd
engine = create_engine("mysql://root:matt123@localhost/ada")
df=pd.DataFrame(['A','B'],columns=['new_tablecol'])
df.to_sql(name='new_table',con=engine,if_exists='append')
Syntax is:
语法是：
engine = create_engine("mysql://USER:PASSWORD@HOST/DATABASE")
回答by NL23codes
I'm not sure if the use of pysql is a necessity, but in the event sqlite3 will suffice, then it could look like this:
我不确定是否需要使用 pysql，但如果 sqlite3 就足够了，那么它可能如下所示：
import pandas
import sqlite3 as db
DB = db.connect('DB.db')
csv1 = pandas.read_csv('C:\…..csv')
csv1.to_sql(name='Orders', con=DB, if_exists='replace')
#replace is one of three options available for the if_exists parameter
DB.close()
However, this format and method are probably unrelated to the error you received, which may have had something to do with the data within your csv file. Without seeing it, it's hard to be certain.
但是，这种格式和方法可能与您收到的错误无关，这可能与您的 csv 文件中的数据有关。没有亲眼所见，很难确定。
