问如何使用python中的sqlalchemy在查询中创建sql server表变量。
EN

Stack Overflow用户

提问于 2018-12-04 03:22:52

回答 2查看 1.6K关注 0票数 1

我正在尝试在SQL Server中创建一个表变量，对其进行查询，然后将结果返回给pandas数据帧(参见示例)。我想这样做是为了在将数据发送到pandas数据帧之前聚合数据库中的数据。我记得设置NOCOUNT ON会允许这样做，因为它在执行每个查询时不会返回任何内容。但这不管用。这显然是一个示例代码，但我可以在这里重新创建错误。按照建议的链接，您将获得documentating for ProgrammingErrors。我并不觉得它有多大帮助。

import urllib
import sqlalchemy
import pandas as pd

quoted = urllib.parse.quote_plus('DRIVER={ODBC Driver 17 for SQL Server};Server=127.0.0.1;Database=mydb;UID=myuser;PWD=mypasswd;Port=1433;')
engine = sqlalchemy.create_engine('mssql+pyodbc:///?odbc_connect={}'.format(quoted))

query = """
SET NOCOUNT ON;

DECLARE @n_majors TABLE (id varchar(9), n_majors int)

INSERT INTO @n_majors
SELECT m.student_id_fk
, COUNT(DISTINCT dc.category) AS [N majors declared]
FROM msu_db.dbo.Majors AS m
JOIN department_categories AS dc
ON dc.dept_name = m.dept_name
WHERE m.Student_Level_Code = 'UN'
GROUP BY m.student_id_fk

DECLARE @grad_category TABLE (id varchar(9), category varchar(20))

INSERT INTO @grad_category
select m.student_id_fk
, MIN(dc.category)
from Majors AS m
join department_categories as dc
on dc.dept_name = m.dept_name
WHERE m.Student_Level_Code = 'UN'
and graduated = 'CONF'
GROUP BY m.student_id_fk

DECLARE @first_category TABLE (id varchar(9), category varchar(20))

INSERT INTO @first_category
select m.student_id_fk
, MIN(dc.category) as cat
from Majors AS m
join department_categories as dc
on dc.dept_name = m.dept_name
WHERE m.Student_Level_Code = 'UN'
and graduated IS NULL
GROUP BY m.student_id_fk

DECLARE @first_semester_grades TABLE (id varchar(9), avg_grade float, std_grade float, first_Semester_seq_id varchar(4))

INSERT INTO @first_semester_grades
SELECT c.student_id_fk
, AVG(c.Grade) AS [mean grade]
, STDEV(c.Grade) AS [stdev grade]
, MIN(c.Term_Seq_Id) AS Term_Seq_Id
FROM Courses AS c
WHERE c.Student_Level_Code = 'UN'
GROUP BY c.student_id_fk

SET NOCOUNT OFF;

SELECT  s.[student_id_fk]
      ,[gender]
      ,[ethnicity]
      ,[first_course_datetime]
      ,[hs_gpa]
      ,[math_placement_score]
      ,[math_act]
      ,[natsci_act]
      ,COUNT(c.[transfer institution name]) AS [N AP courses]
      , nm.n_majors AS [n-categories]
      , fc.category
      , gc.category AS [grad category]
      , fsg.avg_grade AS first_term_avg
      , fsg.std_grade AS first_term_std
      , fsg.first_Semester_seq_id
  FROM [msu_db].[dbo].[Students] AS s
  LEFT JOIN msu_db.dbo.Courses AS c
  ON s.student_id_fk = c.student_id_fk
  AND c.[transfer institution name] = 'Advanced Placement'

  LEFT JOIN @n_majors as nm
  ON s.student_id_fk = nm.id

  LEFT JOIN @grad_category as gc
  ON s.student_id_fk = gc.id

  LEFT JOIN @first_category AS fc
  ON s.student_id_fk = fc.id

  LEFT JOIN @first_semester_grades AS fsg
  ON s.student_id_fk = fsg.id

  WHERE s.first_course_datetime BETWEEN '1993' AND '2013'

  GROUP BY s.[student_id_fk]
      ,[gender]
      ,[ethnicity]
      ,[first_course_datetime]
      ,[hs_gpa]
      ,[math_placement_score]
      ,[math_act]
      ,[natsci_act]
      , nm.n_majors
      , fc.category
      , gc.category
      , fsg.avg_grade
      , fsg.std_grade
      , fsg.first_Semester_seq_id
    """
pd.read_sql_query(query, engine)

输出的错误消息如下：

     --------------------------------------------------------------------------
AttributeError                            Traceback (most recent call last)
~/anaconda3/envs/research/lib/python3.6/site-packages/sqlalchemy/engine/result.py in _fetchall_impl(self)
   1081         try:
-> 1082             return self.cursor.fetchall()
   1083         except AttributeError:

AttributeError: 'NoneType' object has no attribute 'fetchall'

During handling of the above exception, another exception occurred:

ResourceClosedError                       Traceback (most recent call last)
<ipython-input-3-2a0ea765a8e2> in <module>()
----> 1 df = pd.read_sql_query(query, engine)

~/anaconda3/envs/research/lib/python3.6/site-packages/pandas/io/sql.py in read_sql_query(sql, con, index_col, coerce_float, params, parse_dates, chunksize)
    312     return pandas_sql.read_query(
    313         sql, index_col=index_col, params=params, coerce_float=coerce_float,
--> 314         parse_dates=parse_dates, chunksize=chunksize)
    315 
    316 

~/anaconda3/envs/research/lib/python3.6/site-packages/pandas/io/sql.py in read_query(self, sql, index_col, coerce_float, parse_dates, params, chunksize)
   1070                                         parse_dates=parse_dates)
   1071         else:
-> 1072             data = result.fetchall()
   1073             frame = _wrap_result(data, columns, index_col=index_col,
   1074                                  coerce_float=coerce_float,

~/anaconda3/envs/research/lib/python3.6/site-packages/sqlalchemy/engine/result.py in fetchall(self)
   1135             self.connection._handle_dbapi_exception(
   1136                 e, None, None,
-> 1137                 self.cursor, self.context)
   1138 
   1139     def fetchmany(self, size=None):

~/anaconda3/envs/research/lib/python3.6/site-packages/sqlalchemy/engine/base.py in _handle_dbapi_exception(self, e, statement, parameters, cursor, context)
   1414                 )
   1415             else:
-> 1416                 util.reraise(*exc_info)
   1417 
   1418         finally:

~/anaconda3/envs/research/lib/python3.6/site-packages/sqlalchemy/util/compat.py in reraise(tp, value, tb, cause)
    185         if value.__traceback__ is not tb:
    186             raise value.with_traceback(tb)
--> 187         raise value
    188 
    189 else:

~/anaconda3/envs/research/lib/python3.6/site-packages/sqlalchemy/engine/result.py in fetchall(self)
   1129 
   1130         try:
-> 1131             l = self.process_rows(self._fetchall_impl())
   1132             self._soft_close()
   1133             return l

~/anaconda3/envs/research/lib/python3.6/site-packages/sqlalchemy/engine/result.py in _fetchall_impl(self)
   1082             return self.cursor.fetchall()
   1083         except AttributeError:
-> 1084             return self._non_result([])
   1085 
   1086     def _non_result(self, default):

~/anaconda3/envs/research/lib/python3.6/site-packages/sqlalchemy/engine/result.py in _non_result(self, default)
   1087         if self._metadata is None:
   1088             raise exc.ResourceClosedError(
-> 1089                 "This result object does not return rows. "
   1090                 "It has been closed automatically.",
   1091             )

ResourceClosedError: This result object does not return rows. It has been closed automatically.

似乎只要传递NoneType对象，它就会失败。我不明白的是为什么首先要传递一个NoneType对象。难道不应该传递查询结果吗？

python

sql-server

pandas

table-variable

回答 2

Stack Overflow用户

发布于 2018-12-04 03:46:54

你在变量声明中拼写错误的table -它有一个1而不是l。如果你认为应该工作的东西不工作，首先检查你的假设。

更新：

import urllib
import sqlalchemy
import pandas as pd

quoted = urllib.parse.quote_plus('DRIVER={ODBC Driver 17 for SQL Server};Server=127.0.0.1;Database=mydb;UID=myuser;PWD=mypasswd;Port=1433;')
engine = sqlalchemy.create_engine('mssql+pyodbc:///?odbc_connect={}'.format(quoted))

query = """
SET NOCOUNT ON
DECLARE @table TABLE (id int, value float)
INSERT INTO @table VALUES (1, 2.7)
INSERT INTO @table VALUES (2, 4.5)
INSERT INTO @table VALUES (3, 1.2)

SELECT * FROM @table
"""
pd.read_sql_query(query, engine)

票数 5

Stack Overflow用户

发布于 2018-12-04 04:16:42

必须先关闭NOCOUNT，然后才能返回要从SQL Server返回的受影响的正确行消息的查询结果：

import urllib
import sqlalchemy
import pandas as pd

quoted = urllib.parse.quote_plus('DRIVER={ODBC Driver 17 for SQL Server};Server=127.0.0.1;Database=mydb;UID=myuser;PWD=mypasswd;Port=1433;')
engine = sqlalchemy.create_engine('mssql+pyodbc:///?odbc_connect={}'.format(quoted))

query = """
SET NOCOUNT ON
DECLARE @table TABLE (id int, value float)
INSERT INTO @table VALUES (1, 2.7)
INSERT INTO @table VALUES (2, 4.5)
INSERT INTO @table VALUES (3, 1.2)

SET NOCOUNT OFF
SELECT * FROM @table
"""
pd.read_sql_query(query, engine)

票数 1

页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://stackoverflow.com/questions/53600500

复制

相似问题

问如何使用python中的sqlalchemy在查询中创建sql server表变量。
EN

回答 2

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何使用python中的sqlalchemy在查询中创建sql server表变量。EN

回答 2

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何使用python中的sqlalchemy在查询中创建sql server表变量。
EN