blocks|key|278922|text|resoverall是一个sqlalchemy+ResultProxy对象。你可以在sqlalchemy+docs中阅读更多关于它的内容，后者解释了使用引擎和连接的基本用法。这里很重要的一点是，resoverall类似于dict。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|278923|Pandas喜欢像dict一样的对象来创建它的数据结构，请参见online+docs|278924|祝sqlalchemy和pandas好运。|278925|entityMap|0|LINK|mutability|MUTABLE|url|http://docs.sqlalchemy.org/en/rel_0_7/core/connections.html?highlight=execute%2520examples#basic-usage|1|http://pandas.pydata.org/pandas-docs/stable/^0|0|A|2P|A|16|F|0|0|V|B|1|0|0^^$0|@$1|2|3|4|5|6|7|T|8|@$9|U|A|V|B|C]|$9|W|A|X|B|C]]|D|@$9|Y|A|Z|1|10]]|E|$]]|$1|F|3|G|5|6|7|11|8|@]|D|@$9|12|A|13|1|14]]|E|$]]|$1|H|3|I|5|6|7|15|8|@]|D|@]|E|$]]|$1|J|3|-4|5|6|7|16|8|@]|D|@]|E|$]]]|K|$L|$5|M|N|O|E|$P|Q]]|R|$5|M|N|O|E|$P|S]]]]

<code>resoverall</code> is a sqlalchemy ResultProxy object. You can read more about it in the <a href="http://docs.sqlalchemy.org/en/rel_0_7/core/connections.html?highlight=execute%20examples#basic-usage" rel="nofollow">sqlalchemy docs</a>, the latter explains basic usage of working with Engines and Connections. Important here is that <code>resoverall</code> is dict like.

Pandas likes dict like objects to create its data structures, see the <a href="http://pandas.pydata.org/pandas-docs/stable/" rel="nofollow">online docs</a>

Good luck with sqlalchemy and pandas.

blocks|key|495830|text|编辑2014-09-30：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|495831|pandas现在有了一个read_sql函数。你肯定想用它来代替它。|offset|length|style|CODE|495832|原始答案：|495833|我不能帮助您使用SQLAlchemy+--我总是根据需要使用pyodbc、MySQLdb或psychopg2。但在这样做时，像下面这样简单的函数往往会满足我的需求：|495834|import+decimal

import+pyodbc+#just+corrected+a+typo+here
import+numpy+as+np
import+pandas

cnn,+cur+=+myConnectToDBfunction()
cmd+=+"SELECT+*+FROM+myTable"
cur.execute(cmd)
dataframe+=+__processCursor(cur,+dataframe=True)

def+__processCursor(cur,+dataframe=False,+index=None):
++++'''
++++Processes+a+database+cursor+with+data+on+it+into+either
++++a+structured+numpy+array+or+a+pandas+dataframe.

++++input:
++++cur+-+a+pyodbc+cursor+that+has+just+received+data
++++dataframe+-+bool.+if+false,+a+numpy+record+array+is+returned
++++++++++++++++if+true,+return+a+pandas+dataframe
++++index+-+list+of+column(s)+to+use+as+index+in+a+pandas+dataframe
++++'''
++++datatypes+=+[]
++++colinfo+=+cur.description
++++for+col+in+colinfo:
++++++++if+col[1]+==+unicode:
++++++++++++datatypes.append((col[0],+'U%25d'+%25+col[3]))
++++++++elif+col[1]+==+str:
++++++++++++datatypes.append((col[0],+'S%25d'+%25+col[3]))
++++++++elif+col[1]+in+[float,+decimal.Decimal]:
++++++++++++datatypes.append((col[0],+'f4'))
++++++++elif+col[1]+==+datetime.datetime:
++++++++++++datatypes.append((col[0],+'O4'))
++++++++elif+col[1]+==+int:
++++++++++++datatypes.append((col[0],+'i4'))

++++data+=+[]
++++for+row+in+cur:
++++++++data.append(tuple(row))

++++array+=+np.array(data,+dtype=datatypes)
++++if+dataframe:
++++++++output+=+pandas.DataFrame.from_records(array)

++++++++if+index+is+not+None:
++++++++++++output+=+output.set_index(index)

++++else:
++++++++output+=+array

++++return+output|code-block|syntax|javascript|495835|entityMap^0|0|C|8|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|T|8|@$D|U|E|V|F|G]]|9|@]|A|$]]|$1|H|3|I|5|6|7|W|8|@]|9|@]|A|$]]|$1|J|3|K|5|6|7|X|8|@]|9|@]|A|$]]|$1|L|3|M|5|N|7|Y|8|@]|9|@]|A|$O|P]]|$1|Q|3|-4|5|6|7|Z|8|@]|9|@]|A|$]]]|R|$]]

<h3 id="edit-2014-09-30-qzwi">Edit 2014-09-30:</h3>
pandas now has a <code>read_sql</code> function. You definitely want to use that instead.
<h3 id="original-answer-b974">Original answer:</h3>
I can't help you with SQLAlchemy -- I always use pyodbc, MySQLdb, or psychopg2 as needed. But when doing so, a function as simple as the one below tends to suit my needs:
<pre><code>import decimal

import pyodbc #just corrected a typo here
import numpy as np
import pandas

cnn, cur = myConnectToDBfunction()
cmd = &quot;SELECT * FROM myTable&quot;
cur.execute(cmd)
dataframe = __processCursor(cur, dataframe=True)

def __processCursor(cur, dataframe=False, index=None):
 '''
 Processes a database cursor with data on it into either
 a structured numpy array or a pandas dataframe.

 input:
 cur - a pyodbc cursor that has just received data
 dataframe - bool. if false, a numpy record array is returned
 if true, return a pandas dataframe
 index - list of column(s) to use as index in a pandas dataframe
 '''
 datatypes = []
 colinfo = cur.description
 for col in colinfo:
 if col[1] == unicode:
 datatypes.append((col[0], 'U%d' % col[3]))
 elif col[1] == str:
 datatypes.append((col[0], 'S%d' % col[3]))
 elif col[1] in [float, decimal.Decimal]:
 datatypes.append((col[0], 'f4'))
 elif col[1] == datetime.datetime:
 datatypes.append((col[0], 'O4'))
 elif col[1] == int:
 datatypes.append((col[0], 'i4'))

 data = []
 for row in cur:
 data.append(tuple(row))

 array = np.array(data, dtype=datatypes)
 if dataframe:
 output = pandas.DataFrame.from_records(array)

 if index is not None:
 output = output.set_index(index)

 else:
 output = array

 return output
</code></pre>

blocks|key|279043|text|下面是完成这项工作的最短代码：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|279044|from+pandas+import+DataFrame
df+=+DataFrame(resoverall.fetchall())
df.columns+=+resoverall.keys()|code-block|syntax|javascript|279045|您可以像Paul的答案中那样更深入地解析类型。|279046|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

Here's the shortest code that will do the job:

<pre><code>from pandas import DataFrame
df = DataFrame(resoverall.fetchall())
df.columns = resoverall.keys()
</code></pre>

You can go fancier and parse the types as in Paul's answer.

blocks|key|279125|text|这个问题很老了，但我想补充我的两分钱。我把问题理解为“我想对我的mySQL数据库运行一个查询，并将返回的数据存储为Pandas数据结构DataFrame”。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|279126|从代码看，您似乎指的是mysql数据库，并假定您指的是pandas+DataFrame。|279127|import+MySQLdb+as+mdb
import+pandas.io.sql+as+sql
from+pandas+import+*

conn+=+mdb.connect('<server>','<user>','<pass>','<db>');
df+=+sql.read_frame('<query>',+conn)|code-block|syntax|javascript|279128|例如,|279129|conn+=+mdb.connect('localhost','myname','mypass','testdb');
df+=+sql.read_frame('select+*+from+testTable',+conn)|279130|这会将testTable的所有行导入到DataFrame中。|279131|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|R|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|S|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|T|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|U|8|@]|9|@]|A|$G|H]]|$1|M|3|N|5|6|7|V|8|@]|9|@]|A|$]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

This question is old, but I wanted to add my two-cents. I read the question as " I want to run a query to my [my]SQL database and store the returned data as Pandas data structure [DataFrame]."

From the code it looks like you mean mysql database and assume you mean pandas DataFrame.

<pre><code>import MySQLdb as mdb
import pandas.io.sql as sql
from pandas import *

conn = mdb.connect('&lt;server&gt;','&lt;user&gt;','&lt;pass&gt;','&lt;db&gt;');
df = sql.read_frame('&lt;query&gt;', conn)
</code></pre>

For example,

<pre><code>conn = mdb.connect('localhost','myname','mypass','testdb');
df = sql.read_frame('select * from testTable', conn)
</code></pre>

This will import all rows of testTable into a DataFrame.

blocks|key|496012|text|如果您使用的是SQLAlchemy而不是表达式语言，那么您可能会发现自己想要将sqlalchemy.orm.query.Query类型的对象转换为Pandas数据框。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|496013|最干净的方法是从查询的statement属性中获取生成的SQL，然后使用pandas的read_sql()方法执行它。例如，从名为query的查询对象开始|496014|df+=+pd.read_sql(query.statement,+query.session.bind)|code-block|syntax|javascript|496015|entityMap^0|13|Q|0|17|A|1T|5|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@$9|P|A|Q|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|R|8|@$9|S|A|T|B|C]|$9|U|A|V|B|C]]|D|@]|E|$]]|$1|H|3|I|5|J|7|W|8|@]|D|@]|E|$K|L]]|$1|M|3|-4|5|6|7|X|8|@]|D|@]|E|$]]]|N|$]]

If you are using SQLAlchemy's ORM rather than the expression language, you might find yourself wanting to convert an object of type <code>sqlalchemy.orm.query.Query</code> to a Pandas data frame. 

The cleanest approach is to get the generated SQL from the query's statement attribute, and then execute it with pandas's <code>read_sql()</code> method. E.g., starting with a Query object called <code>query</code>:

<pre><code>df = pd.read_sql(query.statement, query.session.bind)
</code></pre>

blocks|key|279229|text|像Nathan一样，我经常希望将sqlalchemy或sqlsoup查询的结果转储到Pandas数据框中。我自己的解决方案是：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|279230|query+=+session.query(tbl.Field1,+tbl.Field2)
DataFrame(query.all(),+columns=[column['name']+for+column+in+query.column_descriptions])|code-block|syntax|javascript|279231|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|$]]

Like Nathan, I often want to dump the results of a sqlalchemy or sqlsoup Query into a Pandas data frame. My own solution for this is:

<pre><code>query = session.query(tbl.Field1, tbl.Field2)
DataFrame(query.all(), columns=[column['name'] for column in query.column_descriptions])
</code></pre>

blocks|key|279257|text|距离上一篇文章很久了，但也许它能帮助某些人……|type|unstyled|depth|inlineStyleRanges|entityRanges|data|279258|比Paul+H更短的方式：|279259|my_dic+=+session.query(query.all())
my_df+=+pandas.DataFrame.from_dict(my_dic)|code-block|syntax|javascript|279260|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|$]]

Long time from last post but maybe it helps someone...

Shorted way than Paul H:

<pre><code>my_dic = session.query(query.all())
my_df = pandas.DataFrame.from_dict(my_dic)
</code></pre>

blocks|key|496132|text|我做这件事的最好方法|type|unstyled|depth|inlineStyleRanges|entityRanges|data|496133|db.execute(query)+where+db=db_class()+#database+class
++++mydata=[x+for+x+in+db.fetchall()]
++++df=pd.DataFrame(data=mydata)|code-block|syntax|javascript|496134|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|$]]

best way I do this

<pre><code>db.execute(query) where db=db_class() #database class
 mydata=[x for x in db.fetchall()]
 df=pd.DataFrame(data=mydata)
</code></pre>

blocks|key|279354|text|这是我的。如果你使用的是"pymysql"：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|279355|import+pymysql
from+pandas+import+DataFrame

host+++=+'localhost'
port+++=+3306
user+++=+'yourUserName'
passwd+=+'yourPassword'
db+++++=+'yourDatabase'

cnx++++=+pymysql.connect(host=host,+port=port,+user=user,+passwd=passwd,+db=db)
cur++++=+cnx.cursor()

query++=+"""+SELECT+*+FROM+yourTable+LIMIT+10"""
cur.execute(query)

field_names+=+[i[0]+for+i+in+cur.description]
get_data+=+[xx+for+xx+in+cur]

cur.close()
cnx.close()

df+=+DataFrame(get_data)
df.columns+=+field_names|code-block|syntax|javascript|279356|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|$]]

Here is mine. Just in case if you are using "pymysql":

<pre><code>import pymysql
from pandas import DataFrame

host = 'localhost'
port = 3306
user = 'yourUserName'
passwd = 'yourPassword'
db = 'yourDatabase'

cnx = pymysql.connect(host=host, port=port, user=user, passwd=passwd, db=db)
cur = cnx.cursor()

query = """ SELECT * FROM yourTable LIMIT 10"""
cur.execute(query)

field_names = [i[0] for i in cur.description]
get_data = [xx for xx in cur]

cur.close()
cnx.close()

df = DataFrame(get_data)
df.columns = field_names
</code></pre>

blocks|key|279413|text|只需同时使用pandas和pyodbc即可。您必须根据您的数据库规范修改连接字符串(connstr)。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|279414|import+pyodbc
import+pandas+as+pd

#+MSSQL+Connection+String+Example
connstr+=+"Server=myServerAddress;Database=myDB;User+Id=myUsername;Password=myPass;"

#+Query+Database+and+Create+DataFrame+Using+Results
df+=+pd.read_sql("select+*+from+myTable",+pyodbc.connect(connstr))|code-block|syntax|javascript|279415|我在几个企业数据库(例如SQL+Server、MySQL、MariaDB、IBM)中使用过pyodbc。|279416|entityMap^0|6|6|D|6|16|7|0|0|19|6|0^^$0|@$1|2|3|4|5|6|7|O|8|@$9|P|A|Q|B|C]|$9|R|A|S|B|C]|$9|T|A|U|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|V|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|W|8|@$9|X|A|Y|B|C]]|D|@]|E|$]]|$1|M|3|-4|5|6|7|Z|8|@]|D|@]|E|$]]]|N|$]]

Simply use <code>pandas</code> and <code>pyodbc</code> together. You'll have to modify your connection string (<code>connstr</code>) according to your database specifications.

<pre><code>import pyodbc
import pandas as pd

# MSSQL Connection String Example
connstr = "Server=myServerAddress;Database=myDB;User Id=myUsername;Password=myPass;"

# Query Database and Create DataFrame Using Results
df = pd.read_sql("select * from myTable", pyodbc.connect(connstr))
</code></pre>

I've used <code>pyodbc</code> with several enterprise databases (e.g. SQL Server, MySQL, MariaDB, IBM).

blocks|key|279425|text|下面是我使用的代码。希望这能有所帮助。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|279426|import+pandas+as+pd
from+sqlalchemy+import+create_engine

def+getData():
++#+Parameters
++ServerName+=+"my_server"
++Database+=+"my_db"
++UserPwd+=+"user:pwd"
++Driver+=+"driver=SQL+Server+Native+Client+11.0"

++#+Create+the+connection
++engine+=+create_engine('mssql%2Bpyodbc://'+%2B+UserPwd+%2B+'@'+%2B+ServerName+%2B+'/'+%2B+Database+%2B+"?"+%2B+Driver)

++sql+=+"select+*+from+mytable"
++df+=+pd.read_sql(sql,+engine)
++return+df

df2+=+getData()
print(df2)|code-block|syntax|javascript|279427|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|$]]

Here's the code I use. Hope this helps.

<pre><code>import pandas as pd
from sqlalchemy import create_engine

def getData():
 # Parameters
 ServerName = "my_server"
 Database = "my_db"
 UserPwd = "user:pwd"
 Driver = "driver=SQL Server Native Client 11.0"

 # Create the connection
 engine = create_engine('mssql+pyodbc://' + UserPwd + '@' + ServerName + '/' + Database + "?" + Driver)

 sql = "select * from mytable"
 df = pd.read_sql(sql, engine)
 return df

df2 = getData()
print(df2)
</code></pre>

blocks|key|279470|text|这是对你的问题的简短而干脆的回答：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|279471|from+__future__+import+print_function
import+MySQLdb
import+numpy+as+np
import+pandas+as+pd
import+xlrd

#+Connecting+to+MySQL+Database
connection+=+MySQLdb.connect(
+++++++++++++host="hostname",
+++++++++++++port=0000,
+++++++++++++user="userID",
+++++++++++++passwd="password",
+++++++++++++db="table_documents",
+++++++++++++charset='utf8'
+++++++++++)
print(connection)
#getting+data+from+database+into+a+dataframe
sql_for_df+=+'select+*+from+tabledata'
df_from_database+=+pd.read_sql(sql_for_df+,+connection)|code-block|syntax|javascript|279472|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 is a short and crisp answer to your problem:

<pre><code>from __future__ import print_function
import MySQLdb
import numpy as np
import pandas as pd
import xlrd

# Connecting to MySQL Database
connection = MySQLdb.connect(
 host="hostname",
 port=0000,
 user="userID",
 passwd="password",
 db="table_documents",
 charset='utf8'
 )
print(connection)
#getting data from database into a dataframe
sql_for_df = 'select * from tabledata'
df_from_database = pd.read_sql(sql_for_df , connection)
</code></pre>

blocks|key|279519|text|1.使用MySQL-connector-python|type|unstyled|depth|inlineStyleRanges|entityRanges|data|279520|#+pip+install+mysql-connector-python

import+mysql.connector
import+pandas+as+pd

mydb+=+mysql.connector.connect(
++++host+=+'host',
++++user+=+'username',
++++passwd+=+'pass',
++++database+=+'db_name'
)
query+=+'select+*+from+table_name'
df+=+pd.read_sql(query,+con+=+mydb)
print(df)|code-block|syntax|javascript|279521|2.使用SQLAlchemy|279522|#+pip+install+pymysql
#+pip+install+sqlalchemy

import+pandas+as+pd
import+sqlalchemy

engine+=+sqlalchemy.create_engine('mysql%2Bpymysql://username:password@localhost:3306/db_name')

query+=+'''
select+*+from+table_name
'''
df+=+pd.read_sql_query(query,+engine)
print(df)|279523|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|$]]

<h1>1. Using MySQL-connector-python</h1>
<pre><code># pip install mysql-connector-python

import mysql.connector
import pandas as pd

mydb = mysql.connector.connect(
 host = 'host',
 user = 'username',
 passwd = 'pass',
 database = 'db_name'
)
query = 'select * from table_name'
df = pd.read_sql(query, con = mydb)
print(df)
</code></pre>
<h1>2. Using SQLAlchemy</h1>
<pre><code># pip install pymysql
# pip install sqlalchemy

import pandas as pd
import sqlalchemy

engine = sqlalchemy.create_engine('mysql+pymysql://username:password@localhost:3306/db_name')

query = '''
select * from table_name
'''
df = pd.read_sql_query(query, engine)
print(df)
</code></pre>

blocks|key|279547|text|pandas.io.sql.write_frame已弃用。https://pandas.pydata.org/pandas-docs/version/0.15.2/generated/pandas.io.sql.write_frame.html|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|279548|应更改为使用pandas.DataFrame.to_sql+https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.to_sql.html|279549|还有另一种解决方案。PYODBC+to+Pandas+-+DataFrame+not+working+-+Shape+of+passed+values+is+(x,y),+indices+imply+(w,z)|279550|从Pandas+0.12+(我相信)开始，你可以做到：|279551|import+pandas
import+pyodbc

sql+=+'select+*+from+table'
cnn+=+pyodbc.connect(...)

data+=+pandas.read_sql(sql,+cnn)|code-block|syntax|javascript|279552|在0.12之前，您可以执行以下操作：|279553|import+pandas
from+pandas.io.sql+import+read_frame
import+pyodbc

sql+=+'select+*+from+table'
cnn+=+pyodbc.connect(...)

data+=+read_frame(sql,+cnn)|279554|entityMap|0|LINK|mutability|MUTABLE|url|https://pandas.pydata.org/pandas-docs/version/0.15.2/generated/pandas.io.sql.write_frame.html|1|https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.to_sql.html|2|https://stackoverflow.com/questions/20055257/pyodbc-to-pandas-dataframe-not-working-shape-of-passed-values-is-x-y-indi^0|T|2L|0|0|U|2F|1|0|A|2N|2|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|14|8|@]|9|@$A|15|B|16|1|17]]|C|$]]|$1|D|3|E|5|6|7|18|8|@]|9|@$A|19|B|1A|1|1B]]|C|$]]|$1|F|3|G|5|6|7|1C|8|@]|9|@$A|1D|B|1E|1|1F]]|C|$]]|$1|H|3|I|5|6|7|1G|8|@]|9|@]|C|$]]|$1|J|3|K|5|L|7|1H|8|@]|9|@]|C|$M|N]]|$1|O|3|P|5|6|7|1I|8|@]|9|@]|C|$]]|$1|Q|3|R|5|L|7|1J|8|@]|9|@]|C|$M|N]]|$1|S|3|-4|5|6|7|1K|8|@]|9|@]|C|$]]]|T|$U|$5|V|W|X|C|$Y|Z]]|10|$5|V|W|X|C|$Y|11]]|12|$5|V|W|X|C|$Y|13]]]]

pandas.io.sql.write_frame is DEPRECATED.
<a href="https://pandas.pydata.org/pandas-docs/version/0.15.2/generated/pandas.io.sql.write_frame.html" rel="nofollow noreferrer">https://pandas.pydata.org/pandas-docs/version/0.15.2/generated/pandas.io.sql.write_frame.html</a>

Should change to use pandas.DataFrame.to_sql
<a href="https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.to_sql.html" rel="nofollow noreferrer">https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.to_sql.html</a>

There is another solution.
<a href="https://stackoverflow.com/questions/20055257/pyodbc-to-pandas-dataframe-not-working-shape-of-passed-values-is-x-y-indi">PYODBC to Pandas - DataFrame not working - Shape of passed values is (x,y), indices imply (w,z)</a>

As of Pandas 0.12 (I believe) you can do:

<pre><code>import pandas
import pyodbc

sql = 'select * from table'
cnn = pyodbc.connect(...)

data = pandas.read_sql(sql, cnn)
</code></pre>

Prior to 0.12, you could do:

<pre><code>import pandas
from pandas.io.sql import read_frame
import pyodbc

sql = 'select * from table'
cnn = pyodbc.connect(...)

data = read_frame(sql, cnn)
</code></pre>

blocks|key|279599|text|下面是我喜欢的一个简单的解决方案：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|279600|将您的数据库连接信息放在一个安全位置的YAML文件中(不要在代码代码库中对其进行版本控制)。|279601|---
host:+'hostname'
port:+port_number_integer
database:+'databasename'
user:+'username'
password:+'password'|code-block|syntax|javascript|279602|然后将conf加载到字典中，打开数据库连接，并将SQL查询的结果集加载到数据框中：|279603|import+yaml
import+pymysql
import+pandas+as+pd

db_conf_path+=+'/path/to/db-conf.yaml'

#+Load+DB+conf
with+open(db_conf_path)+as+db_conf_file:
++++db_conf+=+yaml.safe_load(db_conf_file)

#+Connect+to+the+DB
db_connection+=+pymysql.connect(**db_conf)

#+Load+the+data+into+a+DF
query+=+'''
SELECT+*
FROM+my_table
LIMIT+10
'''

df+=+pd.read_sql(query,+con=db_connection)|279604|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|Q|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|R|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|S|8|@]|9|@]|A|$G|H]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

Here is a simple solution I like:
Put your DB connection info in a YAML file in a secure location (do not version it in the code repo).
<pre class="lang-yaml prettyprint-override"><code>---
host: 'hostname'
port: port_number_integer
database: 'databasename'
user: 'username'
password: 'password'
</code></pre>
Then load the conf in a dictionary, open the db connection and load the result set of the SQL query in a data frame:
<pre class="lang-py prettyprint-override"><code>import yaml
import pymysql
import pandas as pd

db_conf_path = '/path/to/db-conf.yaml'

# Load DB conf
with open(db_conf_path) as db_conf_file:
 db_conf = yaml.safe_load(db_conf_file)

# Connect to the DB
db_connection = pymysql.connect(**db_conf)

# Load the data into a DF
query = '''
SELECT *
FROM my_table
LIMIT 10
'''

df = pd.read_sql(query, con=db_connection)
</code></pre>

Any help on this problem will be greatly appreciated.
So basically I want to run a query to my SQL database and store the returned data as Pandas data structure.
I have attached code for query.
I am reading the documentation on Pandas, but I have problem to identify the return type of my query.
I tried to print the query result, but it doesn't give any useful information.
Thanks!!!!
<pre class="lang-py prettyprint-override"><code>from sqlalchemy import create_engine

engine2 = create_engine('mysql://THE DATABASE I AM ACCESSING')
connection2 = engine2.connect()
dataid = 1022
resoverall = connection2.execute(&quot;
 SELECT 
 sum(BLABLA) AS BLA,
 sum(BLABLABLA2) AS BLABLABLA2,
 sum(SOME_INT) AS SOME_INT,
 sum(SOME_INT2) AS SOME_INT2,
 100*sum(SOME_INT2)/sum(SOME_INT) AS ctr,
 sum(SOME_INT2)/sum(SOME_INT) AS cpc
 FROM daily_report_cooked
 WHERE campaign_id = '%s'&quot;,
 %dataid
)
</code></pre>
So I sort of want to understand what's the format/datatype of my variable &quot;resoverall&quot; and how to put it with PANDAS data structure.

How to convert SQL Query result to PANDAS Data Structure?

数据存储

数据结构

SQL数据库

SQL Server

数据库

MariaDB

MySQL

在这个问题上的任何帮助都将非常感谢。所以基本上我想对我的SQL数据库运行一个查询，并将返回的数据存储为Pandas数据结构。我有附加的查询代码。我正在阅读关于Pandas的文档，但我在识别查询的返回类型时遇到了问题。我试图打印查询结果，但它没有提供任何有用的信息。谢谢！from sqlalchemy import cr...

问如何将SQL查询结果转换为PANDAS数据结构？
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何将SQL查询结果转换为PANDAS数据结构？EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何将SQL查询结果转换为PANDAS数据结构？
EN