Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- from sqlalchemy import create_engine
- engine2 = create_engine('mysql://THE DATABASE I AM ACCESSING')
- connection2 = engine2.connect()
- dataid = 1022
- resoverall = connection2.execute("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'"%dataid)
- from pandas import DataFrame
- df = DataFrame(resoverall.fetchall())
- df.columns = resoverall.keys()
- import pandas as pd
- df = pd.read_sql(sql, cnxn)
- import pyodbc
- import pandas.io.sql as psql
- cnxn = pyodbc.connect(connection_info)
- cursor = cnxn.cursor()
- sql = "SELECT * FROM TABLE"
- df = psql.frame_query(sql, cnxn)
- cnxn.close()
- df = pd.read_sql(query.statement, query.session.bind)
- data_records = [rec.__dict__ for rec in query.all()]
- df = pandas.DataFrame.from_records(data_records)
- import decimal
- import pydobc
- 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%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
- query = session.query(tbl.Field1, tbl.Field2)
- DataFrame(query.all(), columns=[column['name'] for column in query.column_descriptions])
- 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)
- conn = mdb.connect('localhost','myname','mypass','testdb');
- df = sql.read_frame('select * from testTable', conn)
- 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))
- my_dic = session.query(query.all())
- my_df = pandas.DataFrame.from_dict(my_dic)
- db.execute(query) where db=db_class() #database class
- mydata=[x for x in db.fetchall()]
- df=pd.DataFrame(data=mydata)
- 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
- df = pd.DataFrame([dict(r) for r in resoverall])
- import pandas as pd
- import mysql.connector
- # Setup MySQL connection
- db = mysql.connector.connect(
- host="<IP>", # your host, usually localhost
- user="<USER>", # your username
- password="<PASS>", # your password
- database="<DATABASE>" # name of the data base
- )
- # You must create a Cursor object. It will let you execute all the queries you need
- cur = db.cursor()
- # Use all the SQL you like
- cur.execute("SELECT * FROM <TABLE>")
- # Put it all to a data frame
- sql_data = pd.DataFrame(cur.fetchall())
- sql_data.columns = cur.column_names
- # Close the session
- db.close()
- # Show the data
- print(sql_data.head())
- 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)
Add Comment
Please, Sign In to add comment