Python Pycharm和SQL Server连接 [英] Python Pycharm and SQL Server connection

问题描述

我想使用python在Pycharm中使用来自SQL Server的数据.我在Pycharm中设置了数据库连接，但是不确定如何在python代码中访问此数据.我想查询python代码中的数据(类似于我使用RODBC包在R中所做的操作).

I would like to use data from SQL server in Pycharm using python. I have my database connection set up in Pycharm, but not sure how to access this data within my python code. I would like to query the data within the python code (similar to what I would do in R using the RODBC package).

任何关于做什么或在哪里看的建议都将不胜感激.

Any suggestions on what to do or where to look would be much appreciated.

推荐答案

python中有一个称为OR(对象关系)映射的概念，可用于数据库连接.为此，您需要导入的模块之一是SQLAlchemy. 首先，您需要通过以下方式安装sqlalchemy:

There is a concept called OR(Object Relational) Mapping in python, which can be used for database connections. One of the modules that you need to import for the purpose is SQLAlchemy. First, you will need to install sqlalchemy by:

    pip install sqlalchemy

现在，对于数据库连接，我们在sqlalchemy中有一个Engine类，它负责数据库的连接.我们创建Engine类的对象以建立连接.

Now, for database connection, we have an Engine class in the sqlalchemy, which is responsible for the database connectivity. We create an object of the Engine class for establishing connection.

    from sqlalchemy import create_engine,MetaData,select
    engine=create_engine("mysql://user:pwd@localhost/dbname", echo=True)
    connection=engine.connect()

读取数据库和创建元数据的过程称为反射.

The process of reading the database and creating metadata is called Reflection.

    metadata=MetaData()
    query=select([Student]) #Assuming that my database already has a table named Student 
    result=connection.execute(query)
    row=result.fetchall()  #This works similar to the select* query

通过这种方式，您还可以使用sqlalchemy通过其他查询来操纵数据！

In this way, you can manipulate data through other queries too, using sqlalchemy!

这篇关于Python Pycharm和SQL Server连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！