1

I have a SQL file with the following info:

CREATE TABLE 'people' (
'id' int(11) UNSIGNED NOT NULL,
'email' varchar(255) DEFAULT NULL,
'name' varchar(255) DEFAULT
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO 'people' ('id', 'email', 'name') VALUES
(1, 'a@gmail.com', 'john doe'),
(2, 'b@gmail.com', 'jane doe'),
...
(1000, 'x@gmail.com', 'big cat');

How can I query this people.sql file with a list of emails (one per line) in a text file and write the email and name pairings to a new csv?

Rooben
  • 98
  • 1
  • 7
konrad
  • 11
  • 2

2 Answers2

0

Assume that you have execute file above and put data into mySQL DB.

You can use the select command to write to csv file.

SELECT email, name FROM people INTO OUTFILE 'yourfile.csv' FIELDS 
TERMINATED BY ','

In you updated comment, you simply want to do mapping from people name from text file to SQL command to perform query. If you break down the question, it will become two tasks. First is to select name from the given list, which can be done by:

SELECT email, name FROM people WHERE name IN ('john', 'alice', 'bob') INTO 
OUTFILE 'yourfile.csv' FIELDS TERMINATED BY ','

Above will produce a mapping according to the given list. But your second need is to get that list from text file. One way to do so is to transform a "one-name-per-line" to a "list-of-name-with-comma" using sed and tr utilities.

cat your-name-list.txt | sed -e "s/\([a-zA-Z0-9\=\.\-]*\)/'\1'/g" | tr "\n" ","

I don't know what is your environment. but basically, you want to use the result from this as a query condition in SQL above. Hope this give you enough idea.

digitake
  • 846
  • 7
  • 16
  • right but i have a list of specified emails so i only need the names of people in that list of emails to be pulled from the database – konrad Jun 06 '17 at 15:49
  • it depends on what you are using. and there is several way to solve. Beside the solution above, another way is to load you name list to temporary table and perform a join query. – digitake Jun 06 '17 at 16:46
0

Like this: 

import pymysql.cursors
import pandas as pd

connection = pymysql.connect(host='DB_IP_or_localhost',
                             user='myUser',
                             password='myPassword',
                             db='InnoDB',
                             cursorclass=pymysql.cursors.DictCursor)

with connection.cursor() as cursor:
    sql = "SELECT email, name FROM people"
    df = pd.read_sql(sql, connection)
connection.close()

df.to_csv("people.csv", encoding="utf-8")
cardamom
  • 6,873
  • 11
  • 48
  • 102
  • how can i query with dynamic names read from a file: currently have "emailList = text_file.readlines" so i want to do something like " SELECT email, FROM people WHERE email IN emailList – konrad Jun 07 '17 at 14:11
  • It's not completely clear what you want to do but as I understand it, you should modify the Pandas dataframe df before the last line df.to_csv. Use the method [here](https://stackoverflow.com/questions/17071871/select-rows-from-a-dataframe-based-on-values-in-a-column-in-pandas#17071908) to write `df.loc[df['email'].isin(emailList)]` to subset that list. OR write your emailList into a new column in a new database table and modify the query above to be an inner join to that. Hope it helps if so please vote up or green tick if it solves it – cardamom Jun 07 '17 at 15:20